MySQL數據庫常見調優方法及參數設置 數據庫是 IO 密集型應用, 一塊帶 Cache 的 Raid 卡比以下大多數配置效果都來得明顯! www.2cto.com 1. 關閉 SELinux 1 vim /etc/selinux/config 更改 SELINUX=enforcing 為 SELINUX=disabled 2. 更改 IO Schedule, 對於 SSD 硬盤無需更改 1 echo deadline > /sys/block/sda/queue/scheduler 3. 更改 ulimit 1 vim /etc/security/limits.conf 2 3 * soft nofile 65535 4 * hard nofile 65535 5 root soft nofile 65535 6 root hard nofile 65535 4. 更改內核參數 01 vim /etc/sysctl.conf 02 03 net.core.netdev_max_backlog = 3000 04 net.core.optmem_max = 20480 05 net.core.rmem_default = 8388608 06 net.core.rmem_max = 8388608 07 net.core.wmem_default = 1048576 08 net.core.wmem_max = 1048576 09 10 net.ipv4.tcp_mem = 786432 1048576 1572864 11 net.ipv4.tcp_rmem = 32768 4194304 8388608 12 net.ipv4.tcp_wmem = 8192 4194304 8388608 13 14 net.ipv4.tcp_max_syn_backlog = 2048 15 16 net.ipv4.tcp_retries2 = 5 17 net.ipv4.tcp_fin_timeout = 30 18 19 net.ipv4.tcp_keepalive_time = 3600 20 net.ipv4.tcp_keepalive_intvl = 30 21 net.ipv4.tcp_keepalive_probes = 9 22 23 net.ipv4.tcp_max_tw_buckets = 6000 24 net.ipv4.ip_local_port_range = 10240 61000 25 26 fs.file-max = 6815744 27 vm.swappiness = 0 28 29 kernel.sem = 250 32000 100 128 30 kernel.shmmni = 4096 31 kernel.ctrl-alt-del = 1 5. MySQL 5.6.10 數據庫自身設置 01 ＃ 以下針對 24G 內存服務器設置 my.cnf 02 03 # For advice on how to change settings please see 04 # http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html 05 06 [mysqld] 07 08 # Remove leading # and set to the amount of RAM for the most important data 09 # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%. 10 11 socket = /tmp/mysql.sock 12 13 max_connections = 1024 14 max_connect_errors = 10000 15 max_allowed_packet = 16M 16 17 skip-name-resolve 18 lower_case_table_names = 1 19 20 thread_cache = 128 21 table_open_cache = 1024 22 23 query_cache_type = 1 24 query_cache_size = 128M 25 26 join_buffer_size = 8M 27 sort_buffer_size = 2M 28 read_buffer_size = 2M 29 read_rnd_buffer_size = 2M 30 31 max_heap_table_size = 128M 32 tmp_table_size = 128M 33 tmpdir = /dev/shm 34 35 binlog_cache_size = 12M 36 max_binlog_size = 512M 37 expire_logs_days = 3 38 39 innodb_buffer_pool_size = 16G 40 innodb_use_sys_malloc = 1 41 42 # Set .._log_file_size to 25 % of buffer pool size 43 innodb_log_file_size = 128M 44 innodb_log_buffer_size = 32M 45 innodb_flush_log_at_trx_commit = 2 46 innodb_flush_method = O_DIRECT 47 innodb_lock_wait_timeout = 50 48 49 # Remove leading # to turn on a very important data integrity option: logging 50 # changes to the binary log between backups. 51 # log_bin 52 53 # These are commonly set, remove the # and set as required. 54 # basedir = ..... 55 # datadir = ..... 56 # port = ..... 57 # server_id = ..... 58 # socket = ..... 59 60 # Remove leading # to set options mainly useful for reporting servers. 61 # The server defaults are faster for transactions and fast SELECTs. 62 # Adjust sizes as needed, experiment to find the optimal values. 63 # join_buffer_size = 128M 64 # sort_buffer_size = 2M 65 # read_rnd_buffer_size = 2M 66 67 # slow_query_log = 1 68 # slow_query_log_file = slow.log 69 # long_query_time = 1 70 # log_queries_not_using_indexes 71 72 # log-bin = mysql-bin 73 # server-id = 1 74 # innodb_flush_log_at_trx_commit = 1 75 # sync_binlog = 1 76 77 sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 附上 MySQL 5.6.10 編譯參數: 1 tar zxvf mysql-5.6.10.tar.gz 2 cd mysql-5.6.10 3 cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DWITH_INNOBASE_STORAGE_ENGINE=1 -DMYSQL_TCP_PORT=3306 -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DWITH_DEBUG=0 -DCURSES_LIBRARY=/usr/lib64/libncurses.so -DCURSES_INCLUDE_PATH=/usr/include 4 make 5 make install
