Hey there, let's tackle your MySQL 8.0 performance slump after upgrading from 5.7. Those wild CPU spikes and increasing latency on repeated queries are frustrating, but we can work through this by tweaking configurations and checking key system behaviors. Here's my step-by-step breakdown:

Your hardware has 16GB RAM and 4 cores, so let's align your InnoDB settings to match:

• innodb_buffer_pool_size: You've set this to 4G, which is way too low for 16GB RAM. Aim for 8G-12G (50-75% of available memory) – this is the single most impactful setting for InnoDB performance, as it caches table data and indexes. A small buffer pool forces frequent disk I/O, which directly spikes CPU usage.
• innodb_log_file_size: 16M is tiny. The general rule is 25% of the buffer pool size, so if you bump the pool to 8G, set this to 2G. Small log files cause frequent checkpointing, which eats up CPU cycles.
• innodb_flush_neighbors: You have this set to 2, which is optimized for traditional HDDs. If you're using an SSD (most modern setups do), change this to 0 – SSDs don't benefit from adjacent block flushes, and this setting will only add unnecessary overhead.
• innodb_thread_concurrency: 64 is way too high for a 4-core CPU. In MySQL 8.0, you can set this to 0 (let MySQL auto-manage thread concurrency) or cap it at 8-12 (2x your core count). Too many threads cause constant context switching, which kills CPU efficiency.

• key_buffer_size: You've allocated 2G to this, but your default engine is InnoDB. This parameter is for MyISAM indexes – drop it to 64M or 128M to free up memory for InnoDB's buffer pool.
• Session-level buffers: sort_buffer_size=4M, read_buffer_size=2M are overly large. These are per-connection settings, and with max_connections=400, you could be wasting up to 400*(4+2) = 2.4G of memory. Reduce them to 1M for sort_buffer_size and 128K for read_buffer_size to avoid memory contention that spills over to CPU.

The "repeated query getting slower" symptom points to potential execution plan degradation or stale statistics:

• Update table statistics with ANALYZE TABLE your_table_name; for all frequently queried tables. MySQL 8.0's optimizer relies on up-to-date stats to choose the best indexes.
• Use EXPLAIN ANALYZE on the problematic query to check if it's suddenly using a bad index, doing full table scans, or encountering unexpected joins. Compare this to the execution plan you had in 5.7 – sometimes 8.0's optimizer makes different choices that need tuning (like forcing an index with FORCE INDEX if needed).
• Enable the slow query log by uncommenting these lines in your my.ini:

  slow_query_log = ON
  slow_query_log_file = "c:/wamp64/logs/slow_query.log"
  long_query_time = 1
  

  This will capture queries taking over 1 second, so you can identify which ones are driving CPU usage.

• sql-mode: You have this set to an empty string. For stability and performance, set it to:

  sql-mode="STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION"
  

  This prevents implicit data type conversions that can lead to inefficient query execution.
• innodb_use_native_aio: If you're running on Windows, native AIO can sometimes cause issues. Try setting this to false to see if it reduces CPU spikes – Windows' AIO implementation isn't as robust as Linux's for MySQL.

From your global status data, keep an eye on these metrics to validate changes:

• Threads_running: If this stays consistently above your core count (4), your thread concurrency setting is still too high.
• InnoDB_buffer_pool_reads: A high number here means your buffer pool is still too small – you're reading from disk instead of memory.
• InnoDB_log_waits: If this is non-zero, your log files are still too small, causing waits during log flushes.

Start with the InnoDB buffer pool and log file size changes first – those will likely give you the biggest immediate improvement. Then work through the other tweaks and monitor your CPU usage and query latency over time.

内容的提问来源于stack exchange，提问作者UI YT