之前的一个博客运行于搬瓦工的 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 = 1000
max_connect_errors = 4000
open_files_limit = 65535
table_open_cache = 128
max_allowed_packet = 4M
max_heap_table_size = 8M
tmp_table_size = 16M
read_buffer_size = 4M
read_rnd_buffer_size = 8M
sort_buffer_size = 8M
join_buffer_size = 8M
thread_cache_size = 16
key_buffer_size = 16M
net_buffer_length = 32K
log-bin = mysql-bin
binlog_format = mixed
binlog_cache_size = 1M
binlog_expire_logs_seconds = 864000
performance_schema = 0
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_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 = 16M
sort_buffer_size = 4M
read_buffer = 4M
write_buffer = 4M
[mysqlhotcopy]
interactive-timeout
与之前的配置对比一下:
从效果上来看美国本地访问要 1.4s,速度虽然还是感觉有点慢,但是从实际体验来看,还不错,算是没白弄。以及今天又看了一下 VPS 的价格,真是水涨船高,这博客都快开不起了。
如若转载,请注明出处:https://www.ozabc.com/jianzhan/13471.html