집 >데이터 베이스 >MySQL 튜토리얼 >MySQL 数据库常见调优方法及参数设置_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 65535 root soft nofile 65535 root hard nofile 65535
4. 更改内核参数
vim /etc/sysctl.conf net.core.netdev_max_backlog = 3000 net.core.optmem_max = 20480 net.core.rmem_default = 8388608 net.core.rmem_max = 8388608 net.core.wmem_default = 1048576 net.core.wmem_max = 1048576 net.ipv4.tcp_mem = 786432 1048576 1572864 net.ipv4.tcp_rmem = 32768 4194304 8388608 net.ipv4.tcp_wmem = 8192 4194304 8388608 net.ipv4.tcp_max_syn_backlog = 2048 net.ipv4.tcp_retries2 = 5 net.ipv4.tcp_fin_timeout = 30 net.ipv4.tcp_keepalive_time = 3600 net.ipv4.tcp_keepalive_intvl = 30 net.ipv4.tcp_keepalive_probes = 9 net.ipv4.tcp_max_tw_buckets = 6000 net.ipv4.ip_local_port_range = 10240 61000 fs.file-max = 6815744 vm.swappiness = 0 kernel.sem = 250 32000 100 128 kernel.shmmni = 4096 kernel.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.sock max_connections = 1024 max_connect_errors = 10000 max_allowed_packet = 16M skip-name-resolve lower_case_table_names = 1 thread_cache = 128 table_open_cache = 1024 query_cache_type = 1 query_cache_size = 128M join_buffer_size = 8M sort_buffer_size = 2M read_buffer_size = 2M read_rnd_buffer_size = 2M max_heap_table_size = 128M tmp_table_size = 128M tmpdir = /dev/shm binlog_cache_size = 12M max_binlog_size = 512M expire_logs_days = 3 innodb_buffer_pool_size = 16G innodb_use_sys_malloc = 1 # Set .._log_file_size to 25 % of buffer pool size innodb_log_file_size = 128M innodb_log_buffer_size = 32M innodb_flush_log_at_trx_commit = 2 innodb_flush_method = O_DIRECT innodb_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 = 1 sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
附上 MySQL 5.6.10 编译参数:
tar zxvf mysql-5.6.10.tar.gz
cd mysql-5.6.10
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
make
make install
以上就是MySQL 数据库常见调优方法及参数设置_MySQL的内容,更多相关内容请关注PHP中文网(www.php.cn)!