V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
MySQL 5.5 Community Server
MySQL 5.6 Community Server
Percona Configuration Wizard
XtraBackup 搭建主从复制
Great Sites on MySQL
Percona
MySQL Performance Blog
Severalnines
推荐管理工具
Sequel Pro
phpMyAdmin
推荐书目
MySQL Cookbook
MySQL 相关项目
MariaDB
Drizzle
参考文档
http://mysql-python.sourceforge.net/MySQLdb.html
fantasts
V2EX  ›  MySQL

mysql 配置优化问题

  •  
  •   fantasts · 2015-03-13 23:07:57 +08:00 · 2820 次点击
    这是一个创建于 3303 天前的主题,其中的信息可能已经有所发展或是发生改变。
    服务器是32GB内存,网站特点是查询数据大,链接多。
    请问该如何进行针对优化呢?
    参考网上的教程自己优化后效果不太好。
    5 条回复    2015-03-14 15:30:47 +08:00
    Actrace
        1
    Actrace  
       2015-03-14 09:00:56 +08:00   ❤️ 1
    simple,开缓存。
    msg7086
        2
    msg7086  
       2015-03-14 09:57:17 +08:00 via iPhone   ❤️ 1
    Query cache
    fantasts
        3
    fantasts  
    OP
       2015-03-14 13:43:37 +08:00
    @Actrace
    @msg7086 开启的
    fantasts
        4
    fantasts  
    OP
       2015-03-14 15:23:16 +08:00
    工具的建议
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 5m 29s (52K q [159.957 qps], 924 conn, TX: 25B, RX: 14M)
    [--] Reads / Writes: 96% / 4%
    [--] Total buffers: 3.3G global + 21.2M per thread (3000 max threads)
    [!!] Maximum possible memory usage: 65.6G (210% of installed RAM)
    fantasts
        5
    fantasts  
    OP
       2015-03-14 15:30:47 +08:00
    我的配置文件
    [client]
    port = 3306
    socket = /tmp/mysql.sock

    [mysqld]
    port = 3306
    socket = /tmp/mysql.sock

    basedir = /usr/local/mysql
    datadir = /data/mysql
    pid-file = /data/mysql/mysql.pid
    user = mysql
    bind-address = 0.0.0.0
    server-id = 1

    skip-name-resolve
    #skip-networking
    back_log = 600

    max_connections = 3000
    max_connect_errors = 6000
    open_files_limit = 65535
    table_open_cache = 1024
    max_allowed_packet = 16M
    binlog_cache_size = 1M
    max_heap_table_size = 256M
    tmp_table_size = 256M

    thread_concurrency = 16

    read_buffer_size = 1M
    read_rnd_buffer_size = 16M
    sort_buffer_size = 2M
    join_buffer_size = 2M
    key_buffer_size = 1024M
    thread_cache_size = 128

    query_cache_type = 1
    query_cache_size = 32M
    query_cache_limit = 4M

    ft_min_word_len = 4

    #log_bin = mysql-bin
    #binlog_format = mixed
    expire_logs_days = 30

    log_error = /data/mysql/mysql-error.log
    slow_query_log = 1
    long_query_time = 1
    slow_query_log_file = /data/mysql/mysql-slow.log

    performance_schema = 0
    explicit_defaults_for_timestamp

    #lower_case_table_names = 1

    skip-external-locking

    default_storage_engine = InnoDB
    #default-storage-engine = MyISAM
    innodb_file_per_table = 1
    innodb_open_files = 500
    innodb_buffer_pool_size = 2048M
    innodb_write_io_threads = 4
    innodb_read_io_threads = 4
    innodb_thread_concurrency = 16
    innodb_purge_threads = 1
    innodb_flush_log_at_trx_commit = 2
    innodb_log_buffer_size = 8M
    innodb_log_file_size = 128M
    innodb_log_files_in_group = 3
    innodb_max_dirty_pages_pct = 90
    innodb_lock_wait_timeout = 120
    innodb_force_recovery = 1

    bulk_insert_buffer_size = 64M
    myisam_sort_buffer_size = 128M
    myisam_max_sort_file_size = 10G
    myisam_repair_threads = 1

    interactive_timeout = 28800
    wait_timeout = 28800

    [mysqldump]
    quick
    max_allowed_packet = 16M

    [myisamchk]
    key_buffer_size = 1024M
    sort_buffer_size = 2M
    read_buffer = 4M
    write_buffer = 4M
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   1434 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 24ms · UTC 17:27 · PVG 01:27 · LAX 10:27 · JFK 13:27
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.