我在日志中没有发现任何慢查询，尝试了不同的 my.cnf 配置。看不出问题出在哪里。8 CPU 服务器，32GB RAM，固态硬盘，专用 mysql 服务器。所有 CPU > 100%，启动缓慢，前 15-30 分钟运行正常，然后就变得很疯狂。无缘无故的高负载。
升级后我执行了 mysql_upgrade，一切看起来都很好。现在运行的是 8.0.13，几乎所有表都是 MyISAM
[my.cnf] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock log-error=/var/log/mysqld.log default-authentication-plugin=mysql_native_password sql-mode="" character-set-server=utf8 default-storage-engine = MyISAM wait-timeout=300 long_query_time = 1 slow-query-log = 1 slow-query-log-file = /var/lib/mysql/mysql-slow.log long-query-time = 1 skip_name_resolve = 1 general_log = 0 query_prealloc_size=32K # from default to avoid RAM acquisition all day query_alloc_block_size=32K # from default to manage larger blocks innodb_buffer_pool_size=10G # from 128M to reduce innodb_buffer_pool_reads innodb_lru_scan_depth=128 # from 1024 per REFMAN when ibp_instances=8 expire_logs_days=5 # from 0 for limited historical error,general,slow logs slow_query_log=ON # from OFF to allow awareness of ~ 2000 in 76 days, for improvement of queries thread_cache_size=100 # from 12 to reduce threads_created 353K max_connections=200 # from 400 for max_used_connections 56 x ~ 4 innodb_io_capacity=1000 # from 200 to open the door wider innodb_change_buffer_max_size=10 # from 25% to reduce set aside for CHGS, etc max_write_lock_count=16 # from HUGE number to allow RD after nn lcks join_buffer_size=128M # from 4096 for join capacity increase sort_buffer_size=2M # from 256K to reduce sort_merge_passes ~ 1m updatable_views_with_limit=NO # from YES to reduce handler_external_lock cnt open_files_limit = 32000 table_open_cache = 2000 read_rnd_buffer_size = 2M key-buffer-size=64M
SHOW VARIABLES LIKE '%buffer%' bulk_insert_buffer_size 8388608 innodb_buffer_pool_chunk_size 134217728
innodb_buffer_pool_dump_at_shutdown ON
innodb_buffer_pool_dump_now OFF innodb_buffer_pool_dump_pct 25
innodb_buffer_pool_filename ib_buffer_pool
innodb_buffer_pool_instances 8
innodb_buffer_pool_load_abort OFF innodb_buffer_pool_load_at_startup ON
innodb_buffer_pool_load_now OFF innodb_buffer_pool_size 10737418240 innodb_change_buffer_max_size 10
innodb_change_buffering all innodb_log_buffer_size 16777216
innodb_sort_buffer_size 1048576 join_buffer_size 134217728
key_buffer_size 67108864
myisam_sort_buffer_size 8388608 net_buffer_length 16384
preload_buffer_size 32768
read_buffer_size 131072
read_rnd_buffer_size 2097152 sort_buffer_size 2097152 sql_buffer_result OFF
答案1
针对 my.cnf [mysqld] 部分的建议
key_cache_age_threshold=7200 # from 300 seconds to reduce key_reads
因为您的大多数表都是 MyISAM，以提高实例的性能。