Common optimization methods and parameter settings for MySQL Databases

MySQL database common tuning methods and parameter settings databases are IO-intensive applications. A Raid card with a Cache is more effective than most of the following configurations! Www.2cto.com 1. disable SELinux 1vim/etc/selinux/config and change SELINUX = enforcing to SELINUX = disabled 2. change IO Schedule. 1 echo deadline>/sys/block/sda/queue/scheduler 3 is not required for SSD hard disks. change ulimit 1vim/etc/security/limits. conf2 3 * soft nofile 655354 * hard nofile 655355 root soft nofile 655356 root hard nofile 65535 4. change the Kernel Parameter 01vim/etc/sysctl. conf02 03net. core. netdev_max_backlog = 300004net. core. optmem_max = 2048005net. core. rmem_default = 838860806net. core. rmem_max = 838860807net. core. wmem_default = 104857608net. core. wmem_max = 104857609 10net. ipv4.tcp _ mem = 786432 1048576 157286411net. ipv4.tcp _ rmem = 32768 4194304 838860812net. ipv4.tcp _ wmem = 8192 4194304 838860813 14net. ipv4.tcp _ max_syn_backlog = 204815 16net. ipv4.tcp _ retries2 = 517net. ipv4.tcp _ fin_timeout = 3018 19net. ipv4.tcp _ keepalive_time = 360020net. ipv4.tcp _ keepalive_intvl = 3021net. ipv4.tcp _ keepalive_probes = 922 23net. ipv4.tcp _ max_tw_buckets = 600024net. ipv4.ip _ local_port_range = 10240 6100025 26fs. file-max = 681574427vm. swappiness = 028 29kernel. sem = 250 32000 100 12830kernel. shmmni = 409631kernel. ctrl-alt-del = 1 5. mySQL 5.6.10 database settings 01 # The following settings for 24 GB memory servers my. cnf02 03 # For advice on how to change settings please see04 # http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html05 06 [mysqld] 07 08 # Remove leading # and set to the amount of RAM for the most important data09 # cache in MySQL. start at 70% of total RAM for dedicated server, else 10%. 10 11 socket =/tmp/mysql. sock12 13max_connections = requests = 16M16 requests = 119 20thread_cache = 12821table_open_cache = 102422 requests = 128M25 requests = 2M30 requests = 128M33tmpdir =/dev/shm34 35binlog_cache_size = 12M36max_binlog_size = 512M37expire_logs_days = 338 39innodb_buffer_pool_size = 16G40innodb_use_sys_malloc = 141 42 # Set .. _ log_file_size to 25% of buffer pool size43innodb_log_file_size = bytes = 246innodb_flush_method = bytes = 5048 49 # Remove leading # to turn on a very important data integrity option: logging50 # changes to the binary log between backups.51 # log_bin52 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 = 128M64 # sort_buffer_size = 2M65 # bytes = 2M66 67 # slow_query_log = 168 # slow_query_log_file = slow. log69 # long_query_time = 170 # container 72 # log-bin = mysql-bin73 # server-id = 174 # Container = 175 # sync_binlog = 176 77sql_mode = NO_ENGINE_SUBSTITUTION, STRICT_TRANS_TABLES attaches MySQL 5.6.10 compilation parameters: 1tar zxvf mysql-5.6.10.tar.gz2cd mysql-5.6.103cmake. -DCMAKE_INSTALL_PREFIX =/usr/local/mysql-connector = 1-connector = 3306-DDEFAULT_CHARSET = utf8-DDEFAULT_COLLATION = signature-DWITH_DEBUG = 0-DCURSES_LIBRARY =/usr/lib64/libncurses. so-DCURSES_INCLUDE_PATH =/usr/include4make5make install