MySQL 5.6.40大内存场景配置优化及query_cache_size设置咨询

问题1：调整后的配置是否合理、是否为最优配置？ #

• 现有调整整体合理性尚可，但不属于最优配置，可优化点如下：
  1. key_buffer_size：你的库以MyISAM引擎为主，该参数用于缓存MyISAM表的索引数据，目前256M设置偏保守，可统计所有MyISAM表的索引总大小，调整为总索引大小的80%左右（最多不超过MySQL可用内存的25%），能大幅提升索引查询性能。
  2. innodb_buffer_pool_instances：该参数要求每个实例至少分配1G内存才会产生收益，你当前innodb_buffer_pool_size仅为2G，设置为2个实例即可，设置为10个反而会带来额外的管理开销，浪费CPU资源。
  3. thread_cache_size：目前8的设置偏保守，32核CPU、最大连接300的场景下可以调整为32，提升线程复用率，减少频繁创建销毁线程的CPU开销。
  4. join_buffer_size临时调整为2M的方案可行，但必须尽快补全关联索引，否则当连接数打满300时，最坏情况会额外占用300M内存，且无索引关联本身的CPU开销始终存在。
• max_connections调整为300的设置符合你的业务峰值需求，合理性无问题。

问题2：大内存场景下，query_cache_size设置过大有什么影响，是否需要调整？ #

• 过大query_cache_size的负面影响非常明显：
  1. MySQL查询缓存使用全局锁保护，每次表有更新/写入操作时，需要遍历所有缓存条目，失效所有和该表相关的缓存，query_cache_size越大，遍历耗时越长，锁占用时间越久，CPU开销越高，这正好匹配你批量导入、统计计算时性能下降、CPU飙升的现象。
  2. 过大的缓存会存储很多低频次的查询结果，反而挤占高频查询的缓存空间，降低缓存的有效利用率。
• 调整建议：即使当前命中率有79.5%，也建议将query_cache_size降低到64M~128M范围，调整后观察命中率变化，如果命中率下降幅度低于5%，可以继续降低甚至直接关闭查询缓存（MySQL 8.0版本已经完全移除查询缓存功能，本身就是不推荐使用的特性）。query_cache_limit4M的设置不用调整，除非你有高频大结果集的查询需要缓存。

问题3：为什么运行过程中有时仅出现CPU负载升高，内存占用却无明显变化？ #

你遇到的CPU高、内存稳的场景，基本都是以下原因导致：

1. 批量导入、统计计算属于CPU密集型操作：排序、分组聚合、无索引关联查询、索引重建等操作本身就会消耗大量CPU资源，且这些操作需要用到的内存缓冲区（sort_buffer、join_buffer、read_buffer等）已经提前分配完成，不会再额外申请新的内存，所以内存占用保持稳定。
2. 查询缓存锁争抢：批量写入时频繁失效查询缓存的全局锁争抢，会消耗大量CPU资源，但不会产生额外的内存占用。
3. 全表扫描/过滤操作：没有索引的查询需要遍历全表数据，在已加载到内存的数据集里做条件过滤，只会消耗CPU做计算，不会额外申请内存。

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