MySQL 数据库常见调优方法及参数设置

1. 关闭 SELinux

vim /etc/selinux/config 更改 SELINUX=enforcing 为　SELINUX=disabled

2. 更改 IO Schedule,　对于 SSD 硬盘无需更改

echo deadline > /sys/block/sda/queue/scheduler

3. 更改 ulimit

vim /etc/security/limits.conf*        soft  nofile     65535*        hard  nofile     65535root      soft  nofile     65535root      hard  nofile     65535

4. 更改内核参数

vim /etc/sysctl.confnet.core.netdev_max_backlog = 3000net.core.optmem_max = 20480net.core.rmem_default = 8388608net.core.rmem_max = 8388608net.core.wmem_default = 1048576net.core.wmem_max = 1048576net.ipv4.tcp_mem = 786432 1048576 1572864net.ipv4.tcp_rmem = 32768 4194304 8388608net.ipv4.tcp_wmem = 8192 4194304 8388608net.ipv4.tcp_max_syn_backlog = 2048net.ipv4.tcp_retries2 = 5net.ipv4.tcp_fin_timeout = 30net.ipv4.tcp_keepalive_time = 3600net.ipv4.tcp_keepalive_intvl = 30net.ipv4.tcp_keepalive_probes = 9net.ipv4.tcp_max_tw_buckets = 6000net.ipv4.ip_local_port_range = 10240 61000fs.file-max = 6815744vm.swappiness = 0kernel.sem = 250 32000 100 128kernel.shmmni = 4096kernel.ctrl-alt-del = 1

5. MySQL 5.6.10 数据库自身设置

＃ 以下针对 24G 内存服务器设置 my.cnf# For advice on how to change settings please see# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html[mysqld]# Remove leading # and set to the amount of RAM for the most important data# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.socket = /tmp/mysql.sockmax_connections = 1024max_connect_errors = 10000max_allowed_packet = 16Mskip-name-resolvelower_case_table_names = 1thread_cache = 128table_open_cache = 1024query_cache_type = 1query_cache_size = 128Mjoin_buffer_size = 8Msort_buffer_size = 2Mread_buffer_size = 2Mread_rnd_buffer_size = 2Mmax_heap_table_size = 128Mtmp_table_size = 128Mtmpdir = /dev/shmbinlog_cache_size = 12Mmax_binlog_size = 512Mexpire_logs_days = 3innodb_buffer_pool_size = 16Ginnodb_use_sys_malloc = 1# Set .._log_file_size to 25 % of buffer pool sizeinnodb_log_file_size = 128Minnodb_log_buffer_size = 32Minnodb_flush_log_at_trx_commit = 2innodb_flush_method = O_DIRECTinnodb_lock_wait_timeout = 50# Remove leading # to turn on a very important data integrity option: logging# changes to the binary log between backups.# log_bin# These are commonly set, remove the # and set as required.# basedir = .....# datadir = .....# port = .....# server_id = .....# socket = .....# Remove leading # to set options mainly useful for reporting servers.# The server defaults are faster for transactions and fast SELECTs.# Adjust sizes as needed, experiment to find the optimal values.# join_buffer_size = 128M# sort_buffer_size = 2M# read_rnd_buffer_size = 2M # slow_query_log = 1# slow_query_log_file = slow.log# long_query_time = 1# log_queries_not_using_indexes# log-bin = mysql-bin# server-id = 1# innodb_flush_log_at_trx_commit = 1# sync_binlog = 1sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

附上 MySQL 5.6.10 编译参数:

tar zxvf mysql-5.6.10.tar.gzcd mysql-5.6.10cmake . -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/includemakemake install

原标题：MySQL 数据库常见调优方法及参数设置

关键词：MYSQL