Mysql 5.7 常用配置

MySQL 5.7.20 常用配置

连接相关

max_connections

客户端最大并发连接限制数
根据 Threads_connected 和 Max_used_connections 来调整
默认值：151

back_log

MySQL 暂存连接数，短时间得到大量连接时，能够被暂时存到堆栈的连接数
不能超过系统设置：/proc/sys/net/ipv4/tcp_max_syn_backlog，详见 Unix listen() backlog 参数
默认值：50 + (max_connections / 5)，不超过 900

interactive_timeout

交互连接(interactive connection)超时。
称对 mysql_real_connect() 使用 CLIENT_INTERACTIVE 选项的客户端为一个交互客户端(interactive client)
默认值：28800

缓存相关

query_cache_size

查询结果缓存大小

query_cache_type：开关
query_cache_limit：单个查询结果缓存大小
query_cache_min_res_unit：缓存分配空间最小单位，Qcache_free_blocks 过大时表示缓存中碎片过多，需要降低 query_cache_min_res_unit

查询缓存碎片率 = Qcache_free_blocks / Qcache_total_blocks * 100%
查询缓存命中率 = (Qcache_hits – Qcache_inserts) / Qcache_hits * 100%
查询缓存利用率 = (query_cache_size – Qcache_free_memory) / query_cache_size * 100%

如果查询缓存碎片率超过20%，可以用 FLUSH QUERY CACHE 整理缓存碎片，或者试试减小 query_cache_min_res_unit，如果你的查询都是小数据量的话。查询缓存利用率在25%以下的话说明query_cache_size设置的过大，可适当减小；查询缓存利用率在80％以上而且 Qcache_lowmem_prunes > 50的话说明 query_cache_size可能有点小，要不就是碎片太多。
默认值：1048576

sort_buffer_size

每一个需要排序的会话(session)将分配该大小缓存
该缓存线程独占
Sort_merge_passes：排序算法合并数，过大时可以增加 sort_buffer_size
max_sort_length：排序关键字用到的前该字节数据，增加该值时可能需要同时增加 sort_buffer_size
默认值：262144

join_buffer_size

当join是全表扫描而没有使用索引时，将用到该缓存
该缓存线程独占
默认值：262144

table_open_cache

所有线程打开的表数，增大该值会增大mysql使用的fd数
根据 Opened_tables 的大小来调整该参数
默认值：2000

thread_cache_size

缓存线程数，复用线程，当线程创建频繁时可提高该参数
根据参数 Connections 和 Threads_created 来调整
默认值：8 + (max_connections / 100)

wait_timeout

服务端连接超时
默认值：28800

INNODB 相关

innodb_buffer_pool_size

innodb缓存区大小，缓存表、索引及其他辅助内容
对于单独的MySQL数据库服务器，建议将该值设为内存的 50%

innodb_flush_log_at_trx_commit

innodb 将 log buffer 中的数据写入日志文件并flush磁盘的时间点
0：当事务提交时，不做日志写入操作，而是每秒钟将 log buffer 中的数据写入日志文件并flush磁盘一次
1：每秒钟或是每次事物的提交都会引起日志文件写入、flush磁盘的操作，确保了事务的ACID
2；每次事务提交引起写入日志文件的动作，但每秒钟完成一次flush磁盘操作
该参数对写入速度影响极大，如果业务场景允许事务丢失，可以设置成0或2

innodb_log_buffer_size

innodb log 缓存大小
默认值：16777216

innodb_additional_mem_pool_size

innodb 存储 data dictionary 类型数据及其他内部数据结构的缓存，与表的数量、复杂程度有关
分配过小时会有警告日志
默认值：8388608

innodb_thread_concurrency

innodb 线程并发数
官方建议：

• 如果一个工作负载中，并发用户线程的数量小于64，建议设置 innodb_thread_concurrency=0
• 如果工作负载一直较为严重甚至偶尔达到顶峰，建议先设置innodb_thread_concurrency=128，并通过不断的降低这个参数，96, 80, 64等等，直到发现能够提供最佳性能的线程数，例如，假设系统通常有40到50个用户，但定期的数量增加至60，70，甚至200。你会发现，性能在80个并发用户设置时表现稳定，如果高于这个数，性能反而下降。在这种情况下，建议设置innodb_thread_concurrency参数为80，以避免影响性能
• 如果你不希望InnoDB使用的虚拟CPU数量比用户线程使用的虚拟CPU更多（比如20个虚拟CPU），建议通过设置innodb_thread_concurrency 参数为这个值（也可能更低，这取决于性能体现），如果你的目标是将MySQL与其他应用隔离，你可以考虑绑定mysqld进程到专有的虚拟CPU。但是需 要注意的是，这种绑定，在myslqd进程一直不是很忙的情况下，可能会导致非最优的硬件使用率。在这种情况下，你可能会设置mysqld进程绑定的虚拟 CPU，允许其他应用程序使用虚拟CPU的一部分或全部
• 在某些情况下，最佳的innodb_thread_concurrency参数设置可以比虚拟CPU的数量小
• 定期检测和分析系统，负载量、用户数或者工作环境的改变可能都需要对innodb_thread_concurrency参数的设置进行调整

默认值：0 (不限制)