调整数据库运行参数

By minirplus on 2019-08-03 in SSRPanel

最近发现面板经常出现节点离线的情况，主机CPU也时不时超过100，查看Longview记录，发现问题出在mysql进程上，由于安装完之后一直保持默认设置，mysql进程的内存占用一直在10%左右，推测可能是由于同时查询量过多导致MySQL内存溢出丢失连接

环境

下载mysql-tuner

wget http://mysqltuner.pl/ -O mysqltuner.pl

wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/basic_passwords.txt -O basic_passwords.txt

wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/vulnerabilities.csv -O vulnerabilities.csv

运行

1	perl mysqltuner.pl --user root --pass='replace-yourself'

建议

Variables to adjust:

query_cache_size (=0)

query_cache_type (=0)

performance_schema = ON enable PFS

innodb_buffer_pool_size (>= 1.5G) if possible.

innodb_log_file_size should be (=16M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.

innodb_buffer_pool_instances (=1)

/etc/mysql/mariadb.conf.d/50-server.cnf

# * InnoDB

# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.

# Read the manual for more InnoDB related options. There are many!

innodb_buffer_pool_size=1536M

占用约60%MEM

有待后续观察

https://dev.mysql.com/doc/refman/5.7/en/innodb-buffer-pool-resize.html

https://forums.cpanel.net/threads/database-connection-issue.652547/