天空 Blond - Mysql
type: channel
link: https://skyblond.info/tag/Mysql/
items: 【歪门邪道】小内存Mysql优化

【歪门邪道】小内存Mysql优化
type: item
date: 2021-01-20T17:45:00+08:00
description: 目前网站运行于搬瓦工的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] interactiv…