mysql 优化

##计算shmall和shmmax值
memTotal=$(grep MemTotal /proc/meminfo | awk '{print $2}')
totalMemory=$((memTotal / 2048))
shmall=$((memTotal / 4))
if [ $shmall -lt 2097152 ]; then
shmall=2097152
fi
shmmax=$((memTotal * 1024 - 1))
if [ "$shmmax" -lt 4294967295 ]; then
shmmax=4294967295
fi
echo $shmall
echo $shmmax

修改内核参数

fs.file-max = 6815744
kernel.sem = 250 60000 100 240
kernel.shmmni = 4096
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 16777216
net.core.rmem_max = 16777216
net.core.wmem_max = 16777216
net.core.wmem_default = 16777216
fs.aio-max-nr = 6194304
vm.dirty_ratio=20
vm.dirty_background_ratio=3
vm.dirty_writeback_centisecs=100
vm.dirty_expire_centisecs=500
vm.swappiness=10
vm.min_free_kbytes=2097152
net.core.netdev_max_backlog = 30000
net.core.netdev_budget = 600
vm.nr_hugepages =500
net.ipv4.conf.all.rp_filter = 2
net.ipv4.conf.default.rp_filter = 2
net.ipv4.ipfrag_time = 120
net.ipv4.ipfrag_low_thresh = 41943040
net.ipv4.ipfrag_high_thresh = 40894464
net.ipv4.ipfrag_max_dist = 1024
kernel.panic_on_oops=1

sysctl  -p 内核参数生效

资源限制参数

* soft nproc 655360
* hard nproc 655360
* soft nofile 655360
* hard nofile 655360
* soft core -1
* hard core -1
* soft memlock -1
* hard memlock -1

mysql innodb启动大页

[mysqld]
large-pages

重启mysql

/etc/init.d/mysqld stop

/etc/init.d/mysqld start

[root@mysql01 ~]# grep Huge /proc/meminfo
AnonHugePages: 0 kB
ShmemHugePages: 0 kB
HugePages_Total: 512
HugePages_Free: 499
HugePages_Rsvd: 53
HugePages_Surp: 0
Hugepagesize: 2048 kB

开启大页内存的好处：

1.减少内存置换

2.减少TLB miss次数

3.减少swap

Innodb存储引擎的缓存命中率计算

（1- Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests） *100 %，如果缓存命中率过低，则我们需要考虑扩充内存的大小或者是innodb_buffer_pool_size的值。

线程优化

Threads_created > thread_cache_size  ,加大thread_cache_size