Common tuning method and parameter setting of MySQL database

1. Turn off SELinux

Vim/etc/selinux/config Change selinux=enforcing to selinux=disabled

2. Change IO Schedule, no need to change for SSD drive

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. Changing 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 = 32000 = 128kernel.shmmni = 4096kernel.ctrl-alt-del = 1

5. MySQL 5.6.10 Database Self-setting

# The following for 24G memory server settings my.cnf# for advice The 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 M Ysql. Start at 70% of all 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 = 5 12mexpire_logs_days = 3innodb_buffer_pool_size = 16ginnodb_use_sys_malloc = # Set. _log_file_size to% 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 in a very important data integrity option:logging# changes to the binary log between backups.# log_bin# These is 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 is Faster for transactions and fast selects.# Adjust sizes as needed, experiment to find the optimal values.# join_buffer_si Ze = 128m# sort_buffer_size = 2m# read_rnd_buffer_size = 2M # slow_query_log = # slow_query_log_file = slow.log# Long_que Ry_time = # log_queries_not_using_indexes# Log-bin = mysql-bin# Server-id = # innodb_flush_log_at_trx_commit = # Sync_b Inlog = 1sql_mode=no_engine_substitution,strict_trans_tables

Attach MySQL 5.6.10 Compile 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

Common tuning method and parameter setting of MySQL database