mysql 8.0.31 for centos 7.9优化（2）

1.关闭透明大页

临时生效：

[root@mysql1 data]# echo never > /sys/kernel/mm/transparent_hugepage/enabled
[root@mysql1 data]# echo never > /sys/kernel/mm/transparent_hugepage/defrag
[root@mysql1 data]# cat /sys/kernel/mm/transparent_hugepage/enabled
always madvise [never]
[root@mysql1 data]# cat /sys/kernel/mm/transparent_hugepage/defrag
always madvise [never]

永久生效：

vi /etc/rc.d/rc.local添加：
if test -f /sys/kernel/mm/transparent_hugepage/enabled; then
   echo never > /sys/kernel/mm/transparent_hugepage/enabled
fi
if test -f /sys/kernel/mm/transparent_hugepage/defrag; then
   echo never > /sys/kernel/mm/transparent_hugepage/defrag
fi

赋予执行权限：
chmod +x /etc/rc.d/rc.local

2.启用大页内存

[root@mysql1 data]# cat /proc/meminfo | grep -i huge
AnonHugePages:    350208 kB
HugePages_Total:       0
HugePages_Free:        0
HugePages_Rsvd:        0
HugePages_Surp:        0
Hugepagesize:       2048 kB
[root@mysql1 data]# id mysql
uid=1000(mysql) gid=1000(mysql) groups=1000(mysql)
[root@mysql1 data]# sysctl -p
net.ipv4.ip_forward = 0
net.ipv4.conf.default.rp_filter = 1
net.ipv4.conf.default.accept_source_route = 0
kernel.sysrq = 0
kernel.core_uses_pid = 1
net.ipv4.tcp_syncookies = 1
kernel.msgmnb = 65536
kernel.msgmax = 65536
kernel.shmmax = 68719476736 #比物理内存小一点即可
fs.aio-max-nr = 3145728
fs.file-max = 6815744
kernel.shmall = 1073741824
kernel.shmmni = 4096
kernel.sem = 300 307200 100 1024
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 4194304
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
vm.nr_hugepages = 1024 #分配2G
vm.hugetlb_shm_group = 1000

[root@mysql1 data]# cat /proc/meminfo | grep -i huge
AnonHugePages:    350208 kB
HugePages_Total:    1024
HugePages_Free:     1024
HugePages_Rsvd:        0
HugePages_Surp:        0
Hugepagesize:       2048 kB

[root@mysql1 data]# cat /etc/security/limits.conf 
mysql            hard    memlock         unlimited
mysql            soft    memlock         unlimited

[root@mysql1 data]# vi /etc/my.cnf
[mysqld]
innodb_buffer_pool_size=2G #注意这个大小建议为50%-70%的物理内存即可，总之建议给操作系统留出30%以上的剩余，并且小于vm.nr_hugepages的设置
large-pages
[root@mysql1 data]# service mysql restart

[root@mysql1 data]# cat /proc/meminfo | grep -i huge
AnonHugePages:      6144 kB
HugePages_Total:    1024
HugePages_Free:      984
HugePages_Rsvd:      950
HugePages_Surp:        0
Hugepagesize:       2048 kB
[root@mysql1 data]# free -m
              total        used        free      shared  buff/cache   available
Mem:           3789        2719         147           8         922         831
Swap:          3275           0        3275