MySQL某些场景下的参数设置（更新中...）

一、超时相关

1、会话连接

• connect_timeout

MySQL Client端尝试与Server端建立链接时，Server返回握手协议错误等待的最大时间，默认10秒。

• interactive_timeout

Server端在关闭正在交互活跃链接前等待的最大时限，默认28800秒(8h)。

• wait_timeout

Server端关闭非交互连接前等待的最大时限。默认28800秒。

• net_read_timeout

MySQL Server端等待从Client端读取数据的最大时限，默认30秒。

• net_write_timeout

MySQL Server端等待从Client端写入数据的最大时限，默认30秒。

• slave_net_timeout

主从架构中，slave节点从复制连结等待读取数据的最大时限，默认3600秒。

2、锁等待超时相关

• innodb_lock_wait_timeout

InnoDB事务执行期间因无法获取到行锁资源而等待的时间，默认50秒。客户端可以设置session级别的行锁超时时间。

• innodb_rollback_on_timeout

当SQL执行发生行锁超时时，MySQL选择回滚事务中最后一条超时语句还是回滚整个事务，默认为OFF。

• lock_wait_timeout

会话等待元数据锁或者表锁的最大时限，默认31536000秒(1年)。

二、内存相关

1、共享内存

• innodb_buffer_pool_size

该部分缓存是 Innodb 引擎最重要的缓存区域，是通过内存来弥补物理数据文件的重要手段。其中主要包含数据页、索引页、undo 页、insert buffer、自适应哈希索引、锁信息以及数据字典等信息。在进行 SQL 读和写的操作时，首先并不是对物理数据文件操作，而是先对 buffer_pool 进行操作，然后再通过 checkpoint 等机制写回数据文件。该空间的优点是可以提升数据库的性能、加快 SQL 运行速度，缺点是故障恢复速度较慢。

• innodb_log_buffer_size

该部分主要存放redo log 的信息。InnoDB 会首先将 redo log 写在redo log buffer中，然后按照一定频率将其刷新回重做日志文件中。该空间不需要太大，因为一般情况下该部分缓存会以较快频率刷新至 redo log（Master Thread 会每秒刷新、事务提交时会刷新、其空间少于 1/2 时同样会刷新）。

• innodb_additional_mem_pool_size

用来保存数据字典信息和其他内部数据结构的内存池的大小，默认值为8M。数据库中的表数量越多，参数值应该越大，如果 InnoDB 用完了内存池中的内存，就会从操作系统中分配内存，同时在 error log 中打入报警信息。

• key_buffer_size

主要用来存放 MyISAM 表的索引信息，MyISAM 表不同于 InnoDB 表，其缓存的索引缓存是放在 key_buffer 中的，而数据缓存则存储于操作系统的内存中。MySQL 8.0之前系统表都是 MyISAM 引擎的，因此该参数应该分配一定空间。

• query_cache_size & query_cache_type

该部分是对查询结果做缓存以减少解析 SQL 和执行 SQL 的花销。主要适合于读多写少的应用场景，因为它是按照 SQL 语句的 hash 值进行缓存的，当表数据发生变化后即失效。query_cache_type指定是否开启查询缓存

0：缓存禁用
1：缓存所有的结果
2：只缓存在select语句中通过SQL_CACHE指定需要缓存的查询

2、私有内存

• read_buffer_size & read_rnd_buffer_size

分别存放了对顺序和随机扫描（例如按照排序的顺序访问）的缓存，当 thread 进行顺序或随机扫描数据时会首先扫描该 buffer 空间以避免更多的物理读。

• sort_buffer_size

需要执行 order by 和 group by 的会话都会分配 sort_buffer，用于存储排序的中间结果，若存储量大于 sort_buffer_size，则会在磁盘生成临时表以完成操作。若观察到状态参数Sort_merge_passes比较大，可以考虑增加该参数值大小

• join_buffer_size

MySQL多表关联使用 nest loop算法时会使用到join buffer。处理逻辑是驱动表的一行和非驱动表联合查找，这时就可以将非驱动表放入 join_buffer，不需要访问拥有并发保护机制的 buffer_pool。

• binlog_cache_size & max_binlog_cache_size

binlog_cache_size用来缓存一个事务未提交前的binlog日志信息，若binlog_cache_size空间已满，会将这部分数据转储至binlog临时文件，binlog临时文件的最大限制由max_binlog_cache_size控制（超过该大小会报错ERROR 1197 (HY000)），等到事务 commit 时将其所有的binlog日志信息刷回磁盘上的binlog文件以持久化。

• tmp_table_size

该参数是指用户内存临时表的大小，如果该 thread 创建的临时表超过它设置的大小会把临时表转换为磁盘 临时表（引擎由default_tmp_storage_engine控制）。

• innodb_sort_buffer_size

在创建InnoDB索引时对数据排序的排序缓冲区的大小，默认1M。利用这块内存把数据读进来进行内部排序然后写入磁盘。这个参数只会在创建索引的过程中被使用，不会用在后面的维护操作；在索引创建完毕后innodb_sort_buffer会被释放。
这个值也控制了在执行online DDL期间DML产生的临时日志文件。

• myisam_sort_buffer_size

三、并发相关

1、MySQL线程并发控制

• innodb_thread_concurrency

并发线程数限制，默认为0，表示没有并发线程数限制，所有请求都会直接请求线程执行。当 innodb_thread_concurrency 设置为0时，则innodb_thread_sleep_delay的设置将会被忽略，不起作用。如果数据库没出现性能问题时，使用默认值即可。

当innodb_thread_concurrency>0，则表示有 并发数限制，当一个新的请求发起时，会检查当前并发线程数是否达到了 innodb_thread_concurrency的限制值，如果有，则需要sleep一段时间（sleep的设置详见下一部分），然后再再次请求，如果再次请求时，当前并发数还是达到限制值，那么就会进入FIFO队列等待执行。当进入到内核执行时，会得到一个 消费凭证 ticket，则这个线程，在后面的多次进入innodb执行操作是都不需要重复上面的检查步骤，当把次数消费完，那么这个线程就会被驱逐，等待下次再次进入Innodb，再重新分配ticket。

• innodb_thread_sleep_delay

当并发线程超过innodb_thread_concurrency设置大小后，进入sleep时间，在5.6.3版本后，因为 Innodb 自动根据负载调整innodb_thread_sleep_delay参数， innodb_adaptive_max_sleep_delay 参数可限制 innodb_thread_sleep_delay的最大sleep时间（单位：微妙）

• innodb_commit_concurrency

并发提交限制，默认为0表示不做限制。大于0表示允许N个事务在同一时间点提交，N的范围是0-1000。

• innodb_concurrency_tickets

线程获取到的消费凭证数，默认5000，在该线程消费完毕之前，都可以不再进去并发限制的检查以及进入sleep等待。

如果innodb_concurrency_tickets设置小些，适用于小事物操作较多的系统，可以快速使用完线程后退出来，提供给其他请求使用；而对于大事务来说，可能会循环进入等待队列中等待执行完成，这会耗费更多时间及资源；如果innodb_concurrency_tickets设置大些，适用于大事务频繁操作的系统，这样大事务则不需要频繁进入queue等待队列，可以通过较少的请求来处理；但是对于小事务来说，则意味着他们要等待更长的时候，才能排队进入到内核执行。所以，当innodb_thread_concurrency>0时，需要上下调整 innodb_concurrency_tickets ，使其达到最佳性能。可以通过show engine innodb status 的queue查看，也可以通过INFORMATION_SCHEMA.INNODB_TRX 的TRX_CONCURRENCY_TICKETS查看消费次数情况。

四、复制相关

4.1 半同步复制

以下半同步复制参数必须在安装半同步插件以及打开半同步复制后，才可显示。

1、master相关

• rpl_semi_sync_master_enabled

控制master端是否打开半同步插件，默认OFF

• rpl_semi_sync_master_timeout

控制半同步复制退化为异步复制的时间限制。
若该时间限制下master还未接收到slave的ack，半同步复制退化为异步复制。默认10000（10s）
该参数一般会设置比较大，保证数据无损。

• rpl_semi_sync_master_trace_level：

master半同步复制调试跟踪等级，默认为32。

1 = general level (for example, time function failures)
16 = detail level (more verbose information)
32 = net wait level (more information about network waits)
64 = function level (information about function entry and exit)

• rpl_semi_sync_master_wait_for_slave_count：

master提交事务并返回客户端commit时，需要获取slave的ack的个数，默认为1。该参数同样依赖rpl_semi_sync_master_wait_no_slave的设置。

• rpl_semi_sync_master_wait_no_slave：

控制当slave个数小于rpl_semi_sync_master_wait_for_slave_count参数设置时，复制模式的行为，默认为ON。

 ON：当MySQL发现slave个数小于rpl_semi_sync_master_wait_for_slave_count时，MySQL复制模式不做任何变更，只要数据同步时，master接收到大于等于rpl_semi_sync_master_wait_for_slave_count个数的ack，MySQL就继续保持半同步复制模式；
    若master等待ack超过rpl_semi_sync_master_timeout时间限制后仍然未接收到大于等于rpl_semi_sync_master_wait_for_slave_count个数的ack，MySQL就将半同步复制退化为异步复制。 
OFF：当MySQL发现slave个数小于rpl_semi_sync_master_wait_for_slave_count时，主动将半同步复制退化为异步复制

• rpl_semi_sync_master_wait_point：

控制半同步复制下,master提交事务并返回客户端commit的时机，MySQL5.7版本下默认为after_sync。

2、slave相关

• rpl_semi_sync_slave_enabled：

控制slave端是否开启半同步复制，默认为OFF

• rpl_semi_sync_slave_trace_level：

控制slave半同步复制调试跟踪等级，默认为32。

五、其他

5.1 缓冲池相关

• innodb_buffer_pool_size
• innodb_buffer_pool_chunk_size
• innodb_buffer_pool_dump_at_shutdown
• innodb_buffer_pool_dump_now
• innodb_buffer_pool_dump_pct
• innodb_buffer_pool_filename
• innodb_buffer_pool_instances
• innodb_buffer_pool_load_abort
• innodb_buffer_pool_load_at_startup
• innodb_buffer_pool_load_now

5.2 一些文件大小限制参数

• innodb_log_file_size & innodb_log_files_in_group

文件设置大小，默认值为 48M，最大值为512G，注意最大值指的是整个 redo log系列文件之和，即（innodb_log_files_in_group * innodb_log_file_size ）不能大于最大值512G。

• max_binlog_size

指定每个binlog日志文件的最大大小，若超过该大小就创建并使用新的binlog日志文件

• innodb_max_undo_log_size
• innodb_online_alter_log_max_size
• innodb_page_size

本文地址：https://blog.csdn.net/weixin_37692493/article/details/107372602