MySQL 5.7默认安装性能远逊于5.6，求配置排查与优化指引

Hey there, let's tackle this frustrating performance drop you're seeing after upgrading MySQL from 5.6 to 5.7. Since you've already ruled out outdated drivers, zeroing in on configuration differences is the right move—MySQL 5.7 introduced a bunch of default changes that can throw off read-heavy workloads like yours. Here's a breakdown of key my.ini settings to check and tweak, plus some extra tips to get things back on track:

1. InnoDB Buffer Pool (Critical for Read Performance) #

• MySQL 5.7's developer edition might have a smaller default innodb_buffer_pool_size compared to your old 5.6 setup. For read-heavy workloads, aim to set this to 70-80% of your available physical memory (leave enough for the OS and your Java/Spring app). For example, if you have 16GB of RAM, try innodb_buffer_pool_size=12G.
• Also check innodb_buffer_pool_instances: 5.7 defaults to 8 if the buffer pool is ≥1GB, but if you have a larger pool, increasing this (to match your CPU core count, up to 64) can reduce internal contention and speed up reads.

2. Query Cache (Deprecated but Impactful) #

• Even though the query cache is deprecated in 5.7, it’s enabled by default with a small size. For repetitive read queries, a misconfigured cache can cause more overhead than benefit:
  • If you don’t need it, set query_cache_type=0 to disable it entirely—this eliminates locking overhead that slows down read operations.
  • If you do use it, set query_cache_size to a reasonable value (64M-128M) and adjust query_cache_limit to fit your frequent result sets (avoid making it too large, as that increases lock contention).

3. InnoDB Log Settings (Affects Recovery and Write Overhead) #

• 5.7 changed default log file behavior, which can slow down database recovery and background write operations:
  • innodb_log_file_size: The default is 48M, but for large datasets, increasing this to 256M or 512M reduces checkpointing overhead (just remember to resize safely: stop MySQL, delete old ib_logfile* files, then restart).
  • innodb_log_buffer_size: Bump this to 64M or 128M to reduce disk I/O during recovery or any background writes.

4. Disk I/O Tuning (Windows-Specific) #

• Windows storage subsystems can be a bottleneck—tweak these settings to reduce unnecessary I/O:
  • innodb_flush_log_at_trx_commit: For your read-only app (except during recovery), set this to 2 instead of the default 1. This reduces disk flushes without major durability risks.
  • innodb_flush_method: Try async_unbuffered instead of the default unbuffered—this can improve I/O performance on Windows depending on your storage setup.
  • sync_binlog: If you’re not using replication, set this to 0 to disable syncing binary logs to disk on every commit, cutting down on I/O overhead.

5. Optimizer and Statistics Settings #

• 5.7 updated how query plans are generated, which can lead to slower execution for some queries:
  • Run ANALYZE TABLE on all frequently accessed tables—5.7’s auto-recalc (innodb_stats_auto_recalc=ON, default) might not have updated stats properly after the upgrade, leading to poor query plans.
  • Check optimizer_switch: If you have complex queries with derived tables, test derived_merge=off (default is on) to see if the optimizer stops making suboptimal merge decisions.

6. Connection/Thread Management #

• For your app’s connection pool, ensure these settings are aligned:
  • max_connections: Set this high enough to handle your app’s connection pool size (don’t overdo it—each connection uses memory).
  • thread_cache_size: Set to 8-16 to reduce the overhead of creating new threads, which helps with sustained read workloads.

• Optimize Database Recovery: If you’re using mysqldump to restore, add --disable-foreign-key-checks to the restore command—5.7 enables these by default, which drastically slows down restores for large datasets.
• Enable Slow Query Log: Turn on the slow query log to identify problematic queries:

  slow_query_log=1
  slow_query_log_file=mysql-slow.log
  long_query_time=1
  

  Analyze the log to see if specific queries are now using worse execution plans compared to 5.6.
• Compare 5.6 vs 5.7 Configs: Pull up your old 5.6 my.ini and compare it to 5.7’s defaults. Look for big differences in buffer pool size, log settings, or optimizer flags—these are often the culprits.

Remember to test changes incrementally: tweak one setting at a time, monitor performance, and only keep changes that improve speed. Since your app is read-only, focus on settings that boost caching and cut down on unnecessary I/O.

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