MySQL database common tuning methods and parameter settings, mysql Tuning

MySQL database common tuning methods and parameter settings, mysql Tuning

1. Disable SELinux

Vim/etc/selinux/config change SELINUX = enforcing to SELINUX = disabled

2. Change IO Schedule, no need to change the SSD Hard Disk

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

3. Change ulimit

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

4. Change Kernel Parameters

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 database settings

# The following settings apply to the 24 GB memory server my. cnf # For advice on how to change settings please see # 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 = Connections = bytes = 1thread_cache = 128table_open_cache = bytes = 1query_cache_size = bytes = 128Mtmp_table_size = 128 Mtmpdir =/dev/queues = bytes = 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 = bytes = 2innodb_flush_method = bytes = 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 = 128 M # sort_buffer_size = 2 M # read_rnd_buffer_size = 2 M # 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 # region = 1 # sync_binlog = 1sql_mode = NO_ENGINE_SUBSTITUTION, STRICT_TRANS_TABLES

Attached MySQL 5.6.10 compilation parameters:

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