MySQL 8.0.26 CPU占用达300%-400% 优化无效问题求助

问题描述 #

您可查看我的进程截图：
htop进程列表

我使用的是Ubuntu Server 20.04操作系统，所用VPS配置如下：

• 4 vCPU核心
• 8 GB内存
• 50 GB NVMe固态硬盘

以下附上我的mysqld.cnf配置文件内容：

#
# The MySQL database server configuration file.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

[mysqld]
#sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
#
# * Basic Settings
#
user        = mysql
# pid-file  = /var/run/mysqld/mysqld.pid
# socket    = /var/run/mysqld/mysqld.sock
# port      = 3306
# datadir   = /var/lib/mysql


# If MySQL is running as a replication slave, this should be changed.
# tmpdir        = /tmp
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address        = 127.0.0.1
mysqlx-bind-address = 127.0.0.1
#
# * Fine Tuning
#
key_buffer_size     = 1536M
# max_allowed_packet    = 64M
# thread_stack      = 256K

# thread_cache_size       = -1

# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover-options  = BACKUP

# max_connections        = 151

# table_open_cache       = 4000

#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
#
# Log all queries
# Be aware that this log type is a performance killer.
# general_log_file        = /var/log/mysql/query.log
# general_log             = 1
#
# Error log - should be very few entries.
#
log_error = /var/log/mysql/error.log
#
# Here you can see queries with especially long duration
slow_query_log      = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 5
log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
# server-id     = 1
# log_bin           = /var/log/mysql/mysql-bin.log
# binlog_expire_logs_seconds    = 2592000
max_binlog_size   = 100M
# binlog_do_db      = include_database_name
# binlog_ignore_db  = include_database_name

我已尝试调大key_buffer_size参数，也通过开启慢查询日志优化了大量慢查询语句，但MySQL的CPU占用率仍然居高不下，请问是否有开发者遇到过同类问题，该如何排查解决？

以下是我的InnoDB引擎配置参数查询结果：

mysql> show variables like 'innodb%';
+------------------------------------------+------------------------+
| Variable_name                            | Value                  |
+------------------------------------------+------------------------+
| innodb_adaptive_flushing                 | ON                     |
| innodb_adaptive_flushing_lwm             | 10                     |
| innodb_adaptive_hash_index               | ON                     |
| innodb_adaptive_hash_index_parts         | 8                      |
| innodb_adaptive_max_sleep_delay          | 150000                 |
| innodb_api_bk_commit_interval            | 5                      |
| innodb_api_disable_rowlock               | OFF                    |
| innodb_api_enable_binlog                 | OFF                    |
| innodb_api_enable_mdl                    | OFF                    |
| innodb_api_trx_level                     | 0                      |
| innodb_autoextend_increment              | 64                     |
| innodb_autoinc_lock_mode                 | 2                      |
| 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_in_core_file          | ON                     |
| innodb_buffer_pool_instances             | 1                      |
| innodb_buffer_pool_load_abort            | OFF                    |
| innodb_buffer_pool_load_at_startup       | ON                     |
| innodb_buffer_pool_load_now              | OFF                    |
| innodb_buffer_pool_size                  | 134217728              |
| innodb_change_buffer_max_size            | 25                     |
| innodb_change_buffering                  | all                    |
| innodb_checksum_algorithm                | crc32                  |
| innodb_cmp_per_index_enabled             | OFF                    |
| innodb_commit_concurrency                | 0                      |
| innodb_compression_failure_threshold_pct | 5                      |
| innodb_compression_level                 | 6                      |
| innodb_compression_pad_pct_max           | 50                     |
| innodb_concurrency_tickets               | 5000                   |
| innodb_data_file_path                    | ibdata1:12M:autoextend |
| innodb_data_home_dir                     |                        |
| innodb_deadlock_detect                   | ON                     |
| innodb_dedicated_server                  | OFF                    |
| innodb_default_row_format                | dynamic                |
| innodb_directories                       |                        |
| innodb_disable_sort_file_cache           | OFF                    |
| innodb_doublewrite                       | ON                     |
| innodb_doublewrite_batch_size            | 0                      |
| innodb_doublewrite_dir                   |                        |
| innodb_doublewrite_files                 | 2                      |
| innodb_doublewrite_pages                 | 4                      |
| innodb_extend_and_initialize             | ON                     |
| innodb_fast_shutdown                     | 1                      |
| innodb_file_per_table                    | ON                     |
| innodb_fill_factor                       | 100                    |
| innodb_flush_log_at_timeout              | 1                      |
| innodb_flush_log_at_trx_commit           | 1                      |
| innodb_flush_method                      | fsync                  |
| innodb_flush_neighbors                   | 0                      |
| innodb_flush_sync                        | ON                     |
| innodb_flushing_avg_loops                | 30                     |
| innodb_force_load_corrupted              | OFF                    |
| innodb_force_recovery                    | 0                      |
| innodb_fsync_threshold                   | 0                      |
| innodb_ft_aux_table                      |                        |
| innodb_ft_cache_size                     | 8000000                |
| innodb_ft_enable_diag_print              | OFF                    |
| innodb_ft_enable_stopword                | ON                     |
| innodb_ft_max_token_size                 | 84                     |
| innodb_ft_min_token_size                 | 3                      |
| innodb_ft_num_word_optimize              | 2000                   |
| innodb_ft_result_cache_limit             | 2000000000             |
| innodb_ft_server_stopword_table          |                        |
| innodb_ft_sort_pll_degree                | 2                      |
| innodb_ft_total_cache_size               | 640000000              |
| innodb_ft_user_stopword_table            |                        |
| innodb_idle_flush_pct                    | 100                    |
| innodb_io_capacity                       | 200                    |
| innodb_io_capacity_max                   | 2000                   |
| innodb_lock_wait_timeout                 | 50                     |
| innodb_log_buffer_size                   | 16777216               |
| innodb_log_checksums                     | ON                     |
| innodb_log_compressed_pages              | ON                     |
| innodb_log_file_size                     | 50331648               |
| innodb_log_files_in_group                | 2                      |
| innodb_log_group_home_dir                | ./                     |
| innodb_log_spin_cpu_abs_lwm              | 80                     |
| innodb_log_spin_cpu_pct_hwm              | 50                     |
| innodb_log_wait_for_flush_spin_hwm       | 400                    |
| innodb_log_write_ahead_size              | 8192                   |
| innodb_log_writer_threads                | ON                     |
| innodb_lru_scan_depth                    | 1024                   |
| innodb_max_dirty_pages_pct               | 90.000000              |
| innodb_max_dirty_pages_pct_lwm           | 10.000000              |
| innodb_max_purge_lag                     | 0                      |
| innodb_max_purge_lag_delay               | 0                      |
| innodb_max_undo_log_size                 | 1073741824             |
| innodb_monitor_disable                   |                        |
| innodb_monitor_enable                    |                        |
| innodb_monitor_reset                     |                        |
| innodb_monitor_reset_all                 |                        |
| innodb_numa_interleave                   | OFF                    |
| innodb_old_blocks_pct                    | 37                     |
| innodb_old_blocks_time                   | 1000                   |
| innodb_online_alter_log_max_size         | 134217728              |
| innodb_open_files                        | 4000                   |
| innodb_optimize_fulltext_only            | OFF                    |
| innodb_page_cleaners                     | 1                      |
| innodb_page_size                         | 16384                  |
| innodb_parallel_read_threads             | 4                      |
| innodb_print_all_deadlocks               | OFF                    |
| innodb_print_ddl_logs                    | OFF                    |
| innodb_purge_batch_size                  | 300                    |
| innodb_purge_rseg_truncate_frequency     | 128                    |
| innodb_purge_threads                     | 4                      |
| innodb_random_read_ahead                 | OFF                    |
| innodb_read_ahead_threshold              | 56                     |
| innodb_read_io_threads                   | 4                      |
| innodb_read_only                         | OFF                    |
| innodb_redo_log_archive_dirs             |                        |
| innodb_redo_log_encrypt                  | OFF                    |
| innodb_replication_delay                 | 0                      |
| innodb_rollback_on_timeout               | OFF                    |
| innodb_rollback_segments                 | 128                    |
| innodb_segment_reserve_factor            | 12.500000              |
| innodb_sort_buffer_size                  | 1048576                |
| innodb_spin_wait_delay                   | 6                      |
| innodb_spin_wait_pause_multiplier        | 50                     |
| innodb_stats_auto_recalc                 | ON                     |
| innodb_stats_include_delete_marked       | OFF                    |
| innodb_stats_method                      | nulls_equal            |
| innodb_stats_on_metadata                 | OFF                    |
| innodb_stats_persistent                  | ON                     |
| innodb_stats_persistent_sample_pages     | 20                     |
| innodb_stats_transient_sample_pages      | 8                      |
| innodb_status_output                     | OFF                    |
| innodb_status_output_locks               | OFF                    |
| innodb_strict_mode                       | ON                     |
| innodb_sync_array_size                   | 1                      |
| innodb_sync_spin_loops                   | 30                     |
| innodb_table_locks                       | ON                     |
| innodb_temp_data_file_path               | ibtmp1:12M:autoextend  |
| innodb_temp_tablespaces_dir              | ./#innodb_temp/        |
| innodb_thread_concurrency                | 0                      |
| innodb_thread_sleep_delay                | 10000                  |
| innodb_tmpdir                            |                        |
| innodb_undo_directory                    | ./                     |
| innodb_undo_log_encrypt                  | OFF                    |
| innodb_undo_log_truncate                 | ON                     |
| innodb_undo_tablespaces                  | 2                      |
| innodb_use_fdatasync                     | OFF                    |
| innodb_use_native_aio                    | ON                     |
| innodb_validate_tablespace_paths         | ON                     |
| innodb_version                           | 8.0.26                 |
| innodb_write_io_threads                  | 4                      |
+------------------------------------------+------------------------+
148 rows in set (0.01 sec)

解决方案 #

你的配置存在核心参数分配错误，这是CPU占用高的主要原因：

1. 内存参数完全配反了
   你给MyISAM引擎的key_buffer_size分配了1536M内存，但InnoDB的核心缓存innodb_buffer_pool_size只有128M。如果你的业务表用的是InnoDB（99%的MySQL 8.0业务都是），key_buffer_size对InnoDB完全无效，调大它纯属浪费内存。
   Buffer Pool是InnoDB缓存表数据、索引的核心区域，设这么小会导致绝大多数查询都要直接读磁盘，CPU反复处理磁盘返回的零散数据、做大量内存置换计算，占用率自然居高不下。

2. 多个InnoDB参数和你的硬件不匹配

   • innodb_log_file_size只有48M，会导致InnoDB频繁刷写redo日志，产生大量不必要的IO和CPU开销
   • innodb_flush_method用默认的fsync，会导致操作系统和InnoDB双缓存数据，浪费内存和CPU资源
   • innodb_io_capacity设为200，是机械硬盘的参数，你用的NVMe固态IO能力远高于这个值，会导致脏页大量堆积，集中刷盘时CPU和IO都会飙高
   • innodb_buffer_pool_instances设为1，高并发下会产生严重的锁竞争，浪费CPU资源

3. 慢查询捕获阈值太高
   你把long_query_time设为5秒，很多执行时间1-2秒、但每秒调用几十上百次的高频查询不会被记录，这些查询积少成多也会占满CPU。

排查步骤

• 先执行show engine innodb status\G，查看BUFFER POOL AND MEMORY段的buffer pool命中率，如果低于99.9%，直接确认是缓存太小导致的问题
• 执行下面的SQL，从performance_schema里查实际占用CPU最高的查询，补全漏网的慢SQL：

  SELECT digest_text, count_star, sum_timer_wait/1000000000 as total_wait_ms, sum_rows_examined/sum_rows_sent as rows_scan_ratio
  FROM performance_schema.events_statements_summary_by_digest
  ORDER BY sum_timer_wait DESC LIMIT 10;
  

• 执行top -H -p $(pidof mysqld)看MySQL内部线程的CPU占比，如果是IO线程占比高就是IO/缓存配置问题，如果是用户查询线程占比高就是SQL/索引问题。

优化配置参考（适配你的4核8G NVMe VPS）

修改mysqld.cnf里的对应参数：

# 没用MyISAM的话把key_buffer_size压到64M足够
key_buffer_size = 64M
# 给InnoDB分配5G内存作为缓存，留2G给系统和其他业务
innodb_buffer_pool_size = 5G
innodb_buffer_pool_instances = 4
# 把redo日志调到1G，减少刷盘频率
innodb_log_file_size = 1G
# 用O_DIRECT跳过操作系统页缓存，避免双缓存
innodb_flush_method = O_DIRECT
# 适配NVMe固态的IO能力
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000
# 慢查询阈值降到1秒，抓高频慢SQL
long_query_time = 1

改完后重启MySQL，等5-10分钟让Buffer Pool预热完成，CPU占用会有明显下降。

内容的提问来源于stack exchange，提问作者Chirag Lukhi