【歪门邪道】小内存Mysql优化

目前网站运行于搬瓦工的 3c2g 服务器上，今天去后台一瞧，Mysql 竟然吃了 1.5GB 的内存，以至于美国本地访问网站静态资源只要 150ms，而访问网站查询文章内容要 2.9s，这怎么行，遂开始优化。

调整配置的过程比较繁琐，而且也没啥可写的，简而言之就是找到对应版本的 Mysql 的模板配置文件，然后根据官方文档去看每一个参数的作用，然后调参数，部分参数可以参照「适用于 2G 内存的 MySQL 性能调优 my.cnf 详解【转】」这篇文章来调。其实这篇文章写的已经很详细了，我查过官方文档之后需要调的参数这篇文章里基本上都涉及到了，不过有一说一，官方文档写的是真的碎。这里唯独要注意的就是文章中有关 query_cache 部分，也就是查询缓存的配置，在我使用的 Mysql8 已经废弃了，不要复制粘贴进去了，不然 Mysql 会起不来，这里要尤为注意。

调整好的 Mysql 配置如下：

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

[mysqld]
bind-address = 127.0.0.1
port         = 3306
socket       = /tmp/mysql.sock
datadir      = /usr/local/mysql/var
server-id    = 1

skip-external-locking
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'

max_connections      = 500
max_connect_errors   = 100
open_files_limit     = 65535
table_open_cache     = 256
max_allowed_packet   = 4M
max_heap_table_size  = 8M
tmp_table_size       = 16M
read_buffer_size     = 768K
read_rnd_buffer_size = 512K
sort_buffer_size     = 768K
join_buffer_size     = 768K
thread_cache_size    = 8

key_buffer_size   = 8M
net_buffer_length = 8K

log-bin                    = mysql-bin
binlog_format              = mixed
binlog_cache_size          = 1M
binlog_expire_logs_seconds = 864000

performance_schema              = off
#performance_schema_max_table_instances = 1000
explicit_defaults_for_timestamp = true
default_authentication_plugin   = mysql_native_password
early-plugin-load               = ""

default_storage_engine         = InnoDB
innodb_data_home_dir           = /usr/local/mysql/var
innodb_data_file_path          = ibdata1:10M:autoextend
innodb_log_group_home_dir      = /usr/local/mysql/var
innodb_buffer_pool_size        = 128M
innodb_purge_threads           = 1
innodb_flush_log_at_trx_commit = 2
innodb_file_per_table          = 1
innodb_log_file_size           = 32M
innodb_log_buffer_size         = 2M
innodb_log_files_in_group      = 3
innodb_max_dirty_pages_pct     = 90
innodb_lock_wait_timeout       = 120

myisam_sort_buffer_size   = 8M
myisam_max_sort_file_size = 10G 
myisam_repair_threads     = 1

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[myisamchk]
key_buffer_size  = 8M
sort_buffer_size = 768K
read_buffer      = 768K
write_buffer     = 2M

[mysqlhotcopy]
interactive-timeout