MySQL
数据库常见调优方法及参数设置
数据库是 IO 密集型应用, 一块带 Cache 的 Raid 卡比以下大多数配置效果都来得明显!
www.2cto.com
1. 关闭 SELinux
1
vim /etc/selinux/config 更改 SELINUX=enforcing 为 SELINUX=disabled
2. 更改 IO Schedule, 对于 SSD 硬盘无需更改
1
echo deadline > /sys/block/sda/queue/scheduler
3. 更改 ulimit
1
vim /etc/security/limits.conf
2
3
* soft nofile 65535
4
* hard nofile 65535
5
root soft nofile 65535
6
root hard nofile 65535
4. 更改内核参数
01
vim /etc/sysctl.conf
02
03
net.core.netdev_max_backlog = 3000
04
net.core.optmem_max = 20480
05
net.core.rmem_default = 8388608
06
net.core.rmem_max = 8388608
07
net.core.wmem_default = 1048576
08
net.core.wmem_max = 1048576
09
10
net.ipv4.tcp_mem = 786432 1048576 1572864
11
net.ipv4.tcp_rmem = 32768 4194304 8388608
12
net.ipv4.tcp_wmem = 8192 4194304 8388608
13
14
net.ipv4.tcp_max_syn_backlog = 2048
15
16
net.ipv4.tcp_retries2 = 5
17
net.ipv4.tcp_fin_timeout = 30
18
19
net.ipv4.tcp_keepalive_time = 3600
20
net.ipv4.tcp_keepalive_intvl = 30
21
net.ipv4.tcp_keepalive_probes = 9
22
23
net.ipv4.tcp_max_tw_buckets = 6000
24
net.ipv4.ip_local_port_range = 10240 61000
25
26
fs.file-max = 6815744
27
vm.swappiness = 0
28
29
kernel.sem = 250 32000 100 128
30
kernel.shmmni = 4096
31
kernel.ctrl-alt-del = 1
5. MySQL 5.6.10 数据库自身设置
01
＃ 以下针对 24G 内存服务器设置 my.cnf
02
# For advice on how to change settings please see
04
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.
html
05
06
[
mysqld]
07
08
# Remove leading # and set to the amount of RAM for the most important data
09
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
10
11
socket = /tmp/mysql.sock
12
13
max_connections = 1024
14
max_connect_errors = 10000
15
max_allowed_packet = 16M
16
17
skip-name-resolve
18
lower_case_table_names = 1
19
20
thread_cache = 128
21
table_open_cache = 1024
22
23
query_cache_type = 1
24
query_cache_size = 128M
25
26
join_buffer_size = 8M
27
sort_buffer_size = 2M
28
read_buffer_size = 2M
29
read_rnd_buffer_size = 2M
30
31
max_heap_table_size = 128M
32
tmp_table_size = 128M
33
tmpdir = /dev/shm
34
35
binlog_cache_size = 12M
36
max_binlog_size = 512M
37
expire_logs_days = 3
38
39
innodb_buffer_pool_size = 16G
40
innodb_use_sys_malloc = 1
41
42
# Set .._log_file_size to 25 % of buffer pool size
43
innodb_log_file_size = 128M
44
innodb_log_buffer_size = 32M
45
innodb_flush_log_at_trx_commit = 2
46
innodb_flush_method = O_DIRECT
47
innodb_lock_wait_timeout = 50
48
49
# Remove leading # to turn on a very important data integrity option: logging
50
# changes to the binary log between backups.
51
# log_bin
52
53
# These are commonly set, remove the # and set as required.
54
# basedir = .....
55
# datadir = .....
56
# port = .....
57
# server_id = .....