宝塔面板创建的mysql内存调优

大多数人在使用宝塔的时候都会遇到资源不足的情况，由于大部分网站的数据库性能需求较小，因此可以降低数据库内存来增加可用内存。

1.首先使用top命令显示cpu内存占用情况，然后输入M切换到换内存排序的模式，如下是相关显示内容，可以看到数据库占用内存为3.3%

Tasks: 134 total, 1 running, 133 sleeping, 0 stopped, 0 zombie
%Cpu(s): 17.5 us, 15.5 sy, 0.0 ni, 45.8 id, 9.8 wa, 0.0 hi, 0.7 si, 10.8 st
KiB Mem : 1014660 total, 73624 free, 860972 used, 80064 buff/cache
KiB Swap: 1048572 total, 461588 free, 586984 used. 41120 avail Mem

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
20635 www 20 0 316376 53652 1252 S 0.0 5.3 1:45.23 php-fpm
20880 www 20 0 303860 51560 1240 S 0.0 5.1 1:43.47 php-fpm
20335 www 20 0 309552 47172 1244 S 0.0 4.6 1:45.35 php-fpm
27093 www 20 0 309044 45388 1564 S 18.6 4.5 14:55.65 php-fpm
7822 www 20 0 290804 41696 444 S 0.0 4.1 1:05.54 php-fpm
7827 www 20 0 289876 38492 440 S 0.0 3.8 1:06.45 php-fpm
7832 www 20 0 300572 38476 436 S 0.0 3.8 1:04.92 php-fpm
20088 www 20 0 305452 38348 424 S 0.0 3.8 2:25.93 php-fpm
7826 www 20 0 288152 35380 440 S 0.0 3.5 1:05.41 php-fpm
20481 www 20 0 288056 34064 436 S 0.0 3.4 2:05.35 php-fpm
20803 www 20 0 284372 33960 436 S 0.0 3.3 1:42.47 php-fpm
2007 mysql 20 0 859620 33420 960 S 1.7 3.3 46:10.76 mysqld
7828 www 20 0 292940 32940 448 S 0.0 3.2 1:04.07 php-fpm
20945 www 20 0 286944 32808 424 S 0.0 3.2 1:54.24 php-fpm
7833 www 20 0 284020 31652 432 S 0.0 3.1 1:04.04 php-fpm
21047 www 20 0 279588 30232 416 S 0.0 3.0 1:46.65 php-fpm
20501 www 20 0 277936 28748 436 S 0.0 2.8 1:39.72 php-fpm

2.查看mysql的配置文件/etc/my.cnf

[client]
#password = your_password
port = 3306
socket = /tmp/mysql.sock

[mysqld]
port = 3306
socket = /tmp/mysql.sock
datadir = /www/server/data
default_storage_engine = InnoDB
performance_schema_max_table_instances = 400
table_definition_cache = 400
skip-external-locking
key_buffer_size = 4M
max_allowed_packet = 64M
table_open_cache = 32
sort_buffer_size = 256K
net_buffer_length = 4K
read_buffer_size = 128K
read_rnd_buffer_size = 256K
myisam_sort_buffer_size = 4M
thread_cache_size = 4
query_cache_size = 4M
tmp_table_size = 8M
sql-mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

explicit_defaults_for_timestamp = true
#skip-name-resolve
max_connections = 500
max_connect_errors = 100
open_files_limit = 65535

log-bin=mysql-bin
binlog_format=mixed
server-id = 1
expire_logs_days = 10
slow_query_log=1
slow-query-log-file=/www/server/data/mysql-slow.log
long_query_time=3
#log_queries_not_using_indexes=on
early-plugin-load = ""

innodb_data_home_dir = /www/server/data
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /www/server/data
innodb_buffer_pool_size = 16M
innodb_log_file_size = 5M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
innodb_max_dirty_pages_pct = 90
innodb_read_io_threads = 1
innodb_write_io_threads = 1

[mysqldump]
quick
max_allowed_packet = 500M

[mysql]
no-auto-rehash

[myisamchk]
key_buffer_size = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

3.可修改项

innodb_buffer_pool_size = 16M

[mysqldump]上面一行可以增加如下内容

performance_schema_max_table_instances=150

table_definition_cache=150

table_open_cache=64

4.最后重启mysql

systemctl restart mysqld