专注,勤学,慎思。戒骄戒躁,谦虚谨慎

just do it

导航

统计

MySQL相关参数总结

保留个原文链接,避免被爬虫爬了过去,以便后续更正补充:https://www.cnblogs.com/wy123/p/11273023.html

MySQL参数繁多,是一个需要根据具体业务、软硬件环境、负载压力、性能需求、数据异常的容忍程度等等信息综合考量的结果,不是一成不变的(当然,某些参数保持默认值就够了)。
想管好数据库,必须理解数据库的一些配置选项,以及其背景因素。
陆陆续续收集整理了好多MySQL相关的参数,总是觉得串不起来,总结归类之后,立马就清晰了很多,提到一个参数,首先会往分门别类,类别是什么,作用是什么,有哪些相关的知识点,需要注意什么。
这里根据个人的思路,用xmind做了一个归类总结以及说明,后续会修正,补充、增加。

对于这些参数的理解与说明,来自于网络、书籍以及自己的理解整理而成,不一定完全正确,随时补充、更正、纠错,这里参考了许多博客,并有大段的引用解释,详细情况会一一注明

 
General参数
 
server_id = XXX
In MySQL 5.7, the --server-id option must be specified if binary logging is enabled, otherwise the server is not allowed to start.
mysql同步的数据中是包含server_id的,用于标识该语句最初是从哪个server写入的,因此server_id一定要有的,根据需求修改,不能重复
 
user = mysql
Mysql使用的用户
 
datadir = /usr/local/mysql57/data
数据文件路径
 
tmpdir = /usr/local/mysql57/tmp
临时文件路径
 
character-set-server = utf8mb4
默认server字符集
 
collation-server = utf8mb4_general_ci
字符序,某种字符集下,也即character-set-server已知的情况下,存在多种排序规则,指定其中一种排序规则,其实叫做排序规则更容易理解。
 
port = 3306
数据库服务端口号
 
default_storage_engine =InnoDB
默认为InnoDB引擎
 
socket = /usr/local/mysql57/mysql.Sock
本机连接至MySQL服务可以使用sock方式连接,实际上是MySQL服务的进程Id
 
pid-file = /usr/local/mysql57/data/mysql.pid
Mysql的进程文存放位置
 
log_error = /usr/local/mysql57_data/mysql3306/log/mysql-error.log
启动&错误日志信息

log_error_verbosity = 0|1|2
log_error_verbosity 为0, 表示不记录告警信息。
log_error_verbosity 为1, 表示告警信息写入错误日志。
log_error_verbosity 大于1, 表示各类告警信息,例如有关网络故障的信息和重新连接信息写入错误日志。参考:https://www.cnblogs.com/kerrycode/p/8973285.html

slow_query_log = on
慢查询日志开关
 
long_query_time = n
界定慢查询阈值,单位秒
 
log_output = file|table
慢查询输出位置,文件或者表,如果是文件的话,目标为slow_query_log_file ,如果是表的话,存储在mysql.slowlog
 
slow_query_log_file = /usr/local/mysql57_data/mysql3306/log/slow_query_log .log
慢查询日志文件
 
log_queries_not_using_indexes
捕获没有用到索引的查询,同样会记录到慢查询的目标中
 
log_bin_trust_function_creators = ON
存储过程或者函数需要标记是否修改数据,参考https://www.cnblogs.com/kerrycode/p/7641835.html
 
performance_schema = ON
启用performance_schema
 
######################################################################################
binlog相关参数
 
binlog_format = row|statment|mixed
  Row level
  日志中会记录每一行数据被修改的情况,然后在slave端对相同的数据进行修改。
  优点:能清楚的记录每一行数据修改的细节
  缺点:数据量太大
  Statement level(默认)
  每一条被修改数据的sql都会记录到master的bin-log中,slave在复制的时候sql进程会解析成和原来master端执行过的相同的sql再次执行
  优点:解决了 Row level下的缺点,不需要记录每一行的数据变化,减少bin-log日志量,节约磁盘IO,提高新能
  缺点:容易出现主从复制不一致
  Mixed(混合模式)
  结合了Row level和Statement level的优点,不建议使用

bin_log_cache_size
对于事务性的操作,是要事物完成的时候写入二进制日志,事物提交之前,执行的写入性操作会被缓存起来,直到整个事物完成,mysqld进程会将整个事物写入二进制日志。
当事物开始的时候,会按照binlog_cache_size系统变量指定的值分配内容空间,如果指定的binlog_cache_size缓存空间不够,执行的事务性操作回滚并提示失败
默认是32kb

 
max_bin_log_cache_size     
在32位的系统中是4G,64位的是16P(可以认为是也即为无穷大),max_binlog_cache_size语binlog_cache_size的区别在于前者是实例级别的cache,后者是Session级别的cache,如果并发量很大,就需要考虑将max_binlog_cache_size设置的稍微大一些。
 
log_bin
路径和binlog的前缀名称
 
expire_logs_days
binlog 过期清理时间阈值,单位为天,默认值为0,也即不过期,需要手动配置过期时间
 
sync_binlog = 0|1|n
二进制日志记录可以使同步的,也即事物提交之后就写入二进制日志,也可以是异步的,由操作系统的磁盘缓存觉得什么时候写入磁盘。
由参数sync_binlog= n来控制,设置sync_binlog = 1的话,表示最高安全级别的写入(但也不能保证不丢失任何事物日志),相当于是一种安全写入模式,不过对性能有一定的影响。
 
GTID相关参数
  gtid_mode = on|off
  gtid模式的开关,默认是关闭的
 
  enforce_gtid_consistency = 1
  强事物一致性,开启之后事物中不能创建临时表
 
  binlog_gtid_simple_recovery = 1

5.7.6以后默认是开启,开启之后,以更优化的方式从binlog中读取GTID

1. 这个变量用于在MySQL重启或启动的时候寻找GTIDs过程中,控制binlog 如何遍历的算法?
2. 当binlog_gtid_simple_recovery=FALSE 时:
为了初始化 gtid_executed,算法是: 从newest_binlog -> oldest_binlog 方向遍历读取,如果发现有Previous_gtids_log_event , 那么就停止遍历
为了初始化 gtid_purged,算法是: 从oldest_binlog -> newest_binlog 方向遍历读取, 如果发现有Previous_gtids_log_event(not empty)或者 至少有一个Gtid_log_event的文件,那么就停止遍历
3. 当binlog_gtid_simple_recovery=TRUE 时:
为了初始化 gtid_executed , 算法是: 只需要读取newest_binlog
为了初始化 gtid_purged, 算法是: 只需要读取oldest_binlog
4. 当设置binlog_gtid_simple_recovery=TRUE , 如果MySQL版本低于5.7.7 , 可能会有gitd计算出错的可能,具体参考官方文档详细描述
  • 在线GTID升级的时候,binlog_gtid_simple_recovery = TRUE 必须打开,否则在binlog 删除的时候,会发生阻塞状况
  • 在线GTID升级的时候,尽量将非GTID的binlog备份好,然后删除掉,以免出现莫名其妙的错误
binlog_ignore相关参数
  
  binlog-ignore-db = db1|db2
  binlog会忽略配置的数据库
  replicate_wild_do_table = table_m|table_n
  replicate-wild-ignore-table  = table_x|table_y
  对于binlog-ignore-db种,强制记录表级别的binlog
 
 
######################################################################################
InnoDB相关参数
 
innodb_flush_log_at_trx_commit = 0|1|2
MySQL最经典的参数之一
如果innodb_flush_log_at_trx_commit设置为0,log buffer将每秒一次地写入log file中,并且log file的flush(刷到磁盘)操作同时进行.该模式下,在事务提交的时候,不会主动触发写入磁盘的操作。
如果innodb_flush_log_at_trx_commit设置为1,每次事务提交时MySQL都会把log buffer的数据写入log file,并且flush(刷到磁盘)中去.
如果innodb_flush_log_at_trx_commit设置为2,每次事务提交时MySQL都会把log buffer的数据写入log file.但是flush(刷到磁盘)操作并不会同时进行。该模式下,MySQL会每秒执行一次 flush(刷到磁盘)操作。
 
innodb_doublewrite = 0|1
物理级保护数据安全,在将内存中的脏页写入磁盘之前,先将内存中的脏页复制到内存的doublewrite buffer中(2MB),然后将doublewrite buffer的数据写入共享表空间的页中(128个page,2MB大小),然后再写磁盘。

innodb_flush_method = fdatasync|O_DSYNC|O_DIRECT
控制着innodb数据文件及redo log的打开、刷写模式,参考:https://blog.csdn.net/smooth00/article/details/72725941
redo LOG BUFFER 和redo LOG 以及数据文件的不同刷新模式
O_DIRECT绕过操作系统缓存,直接从innodb BUFFER写磁盘,减少磁盘IO和内存的使用,会最小化缓冲对io的影响

 

innodb_file_per_table = 1
共享表空间或者独立表空间,1表示每个表对应一个(组)物理文件。默认值。
 
 
innodb_buffer相关参数   
innodb_buffer_pool_size
buffer pool 物理内存的70%~80%,其实这个说法是一个很粗的表述,如果是128GB的物理内存,纯DB服务器,配置为80%,则还剩下大概25GB左右,其实是一个很大的浪费。
参考这里:https://scalegrid.io/blog/calculating-innodb-buffer-pool-size-for-your-mysql-server/,觉得这里的计算方法更为合理。
innodb_buffer_pool_instances
当innodb_buffer_pool_size不大于1GB的时候,默认是1,大于1GB的时候,默认是8,也就是有8个缓冲池实例。
MySQL允许有多个缓冲池实例,每个页根据哈希值平均分配到不同的缓冲池实例,这样做可以减少数据库内部的资源竞争,增加数据库的并发能力。
innodb_additional_mem_pool_size
该参数用来存储数据字典和其他内部数据结构。表越多,需要在这里分配的内存越多,
如果InnoDB用光了和这个池的内存,InnoDB开始从操作系统分配内存,并且往MySQL错误日志中写警告信息,默认值是8MB,当发现
错误日志中有相关的警告信息时,就应该适当地增加该参数的大小,一般设置为16MB即可。
 
innodb_data相关参数
 
innodb_data_home_dir
这是InnoDB表的目录共用设置。如果没有在 my.cnf 进行设置,InnoDB 将使用MySQL的 datadir 目录为缺省目录
 
innodb_data_file_path
单独指定数据文件的路径与大小。数据文件的完整路径由 innodb_data_home_dir 与这里所设定值的组合。
 
innodb_log相关参数,更确切地说是redo log:
       innodb_log_group_home_dir
    InnoDB 日志文件的路径。默认是当前实例的数据目录
 
innodb_log_files_in_group
日志组中的日志文件数目,默认是的文件个数为2,也就是两个文件(ib_logfile0和ib_logfile1)。InnoDB 以环型方式(circular fashion)写入文件。
 
innodb_log_file_size

默认是48MB,在频繁的数据写入的实例中为了防止redolog频繁在两个文件中间切换,应该配置为一个较大的值,比如500MB

 
innodb_log_buffer_size
默认值是16MB,未提交事务的缓冲区大小,如果单个事物和事物并发量不大,可以保留默认值。
 
 
innodb_thread相关参数:
Innodb_thread_concurrency 
   InnoDB最大线程数
 
  innodb_thread_sleep_delay
  调整当 并发 thread 到达 innodb_thread_concurrency时需要sleep的时间
 
  innodb_concurrency_tickets
 
  innodb_commit_concurrency
 
     backend Thread相关参数:
     innodb_max_dirty_pages_pct 
       innodb_purge_threads 
       innodb_flush_neighbors 
       innodb_fast_shutdown 
 
innodb_file:
innodb_file_format = Antelope|Barracuda
innodb_file_format_check
innodb_file_format_max
 
InnoDB IO相关参数
     innodb_read_io_threads
     在Linux平台上就可以根据CPU核数来更改相应的参数值了,默认是4。
 
     innodb_write_io_threads
     同innodb_read_io_threads

   innodb_io_capacity
   按照该值的百分比来控制刷新到磁盘页的数量
   1. 在合并插入缓冲时, 合并插入缓冲的数量是该值的5%
   2. 在从缓冲中刷新脏页时, 刷新的脏页数量等于该值.
   若用户使用了ssd类的磁盘或做了磁盘阵列, 可将该值适当调大.

 
 
innodb other parameter
    innodb_lock_wait_timeout
    innodb_lock_wait_timeout指的是事务等待获取资源等待的最长时间,超过这个时间还未分配到资源则会返回应用失败;参数的时间单位是秒,最小可设置为1s
 
    innodb_print_all_deadlocks = 0|1
    是否打开记录死锁日志到error log中,如果要分析死锁,设置为1,即打开    
 
    innodb_strict_mode
    MySQL5.7 默认打开,严格语法检查,有错误直接抛出,而不是给出警告。
 
 
######################################################################################
Cache 相关参数
  read_buffer_size
  该参数用于表的顺序扫描,表示每个线程分配的缓冲区大小。比如,在进行全表扫描时,MySQL会按照数据的存储顺序依次读取数据块,     
    每次读取的数据块首先会暂存在read_buffer_size中,当buffer的空间被写满或者全部数据读取结束后,再讲buffer的数据返回给上层调用 者,以提高效率。
  默认为128kb,这个参数不要设置过大,一般在128~256即可。
    
  sort_buffer_size
     在表进行order by和group by排序操作时,由于排序字段没有索引,会出现using filesort,为了提高性能,可用此参数增加每个线程分配
     的缓冲区大小。默认为2MB。这个参数不要设置的过大,一般在128~256kb即可。另外,一般出现using filesort的时候,要通过增加索引来解决。
    
  join_buffer_size
     表进行连接操作时,如果关联的字段没有索引,会出现using join buffer,为了提高性能,可用此参数增加每个线程分配的缓冲区大小。
  默认是128kb,这个参数不要设置的过大,一般在128~256kb即可,一般出现using join buffer的时候,要通过增加索引来解决。
  
  read_rnd_buffer_size
  该参数用于表的随机读取,表示每个线程分配的缓冲区大小。
  两个表join的时候,加入连接字段为非聚集索引,并不是每次通过辅助索引读取到数据就回表去取记录,而是将其rowid给缓存起来,
  然后对 rowid进行排序后,再去访问记录,这样就能将随机I/O转化为顺序I/O,从而大幅地提升性能。
   tmp_table_size:
     global级别内部内存临时表的最大值,如果必须使用临时表 且同时执行大量sql 生成大量临时表时适当增加 tmp_table_size
     如果生成的临时表数据量大于 tmp_table_size 则会将临时表存储与磁盘而不是内存,默认是128MB
     可以通过 Created_tmp_disk_tables 和 Created_tmp_tables 状态来分析是否需要增加tmp_table_size
      
  max_heap_table_size
       同tmp_table_size 它规定了内部内存临时表的最大值,每个线程都要分配。(实际起限制作用的是tmp_table_size和max_heap_table_size的最小值。)
   
  thread_cache_size:
     线程池缓存大小 ( 当客户端断开连接后 将当前线程缓存起来 当在接到新的连接请求时快速响应 无需创建新的线程 )
 
     connect相关参数
max_connections
最大连接数
wait_timeout
服务器关闭非交互连接之前等待活动的秒数。
Interactive_timeout
服务器关闭交互式连接前等待活动的秒数。交互式客户端定义为在mysql_real_connect()中使用CLIENT_INTERACTIVE选项的客户端。
connect_timeout和max_connect_errors 参考:https://www.cnblogs.com/kerrycode/p/8405862.html
connect_timeout
MySQL服务端进程mysqld等待连接建立完成的时间,单位为秒。如果超过connect_timeout时间范围内,仍然无法完成协议握手话,MySQL客户端会收到异常,
异常消息类似于: Lost connection to MySQL server at 'XXX', system error: errno,该变量默认是10秒。
max_connect_errors = N
默认值是100,不是防止暴力破解超过N次后禁止连接的,而是计算协议握手错误次数之后禁用主机,并且仅用于通过验证的主机(HOST_VALIDATED = YES)。
为了防止网络中断造成的连接失败,从而造成客户端无法连接,相反,这个值需要配置成一个较大的值,比如10000甚至更多。
     相关信息记录在select * from performance_schema.host_cache;使用 flush hosts;清理。
 
     table_definition_cache
  table_definition_cache,该参数值的代表MySQL可以缓存的表定义的数量。和前面的table cache不同的是,表定义的缓存占用空间很小,
        而且不需要使用文件描述符,也就是只要打开.frm文件,缓存表定义,然后就可以关闭.frm文件。
   
  table_open_cache
     table_open_cache指定表高速缓存的大小。每当MySQL访问一个表时,如果在表缓冲区中还有空间,该表就被打开并放入其中,这样可以更快地访问表内容。
     通过检查峰值时间的状态值Open_tables和  Opened_tables,可以决定是否需要增加table_open_cache的值。
     如果你发现open_tables等于table_open_cache,并且opened_tables在不断增长,那么你就需要增加table_open_cache的值了(上述状态值可:SHOW STATUS LIKE ‘Open%tables’获得)。
 
     query_cache
     查询缓存,不建议使用
          query-cache-type:
    是否打开查询缓存
          query-cache-size:
    查询缓存的大小
    
  open_files_limit:
    open_files_limit = table_open_cache*2 + innodb表
 
######################################################################################
 
 
Master-Slave主从复制相关参数
 
半同步Master端参数:
 
rpl_semi_sync_master_enabled
on 开启半同步复制
 
rpl_semi_sync_master_timeout
参数控制,单位是毫秒,默认为10000,即10s,master等待slave超时时间,超时之后关闭半同步,转为异步复制

rpl_semi_sync_master_wait_no_slave
ON默认值,当状态变量Rpl_semi_sync_master_clients中的值小于rpl_semi_sync_master_wait_for_slave_count时,Rpl_semi_sync_master_status依旧显示为ON。
OFF当状态变量Rpl_semi_sync_master_clients中的值于rpl_semi_sync_master_wait_for_slave_count时,Rpl_semi_sync_master_status立即显示为OFF,即异步复制。
说得直白一点,如果我的架构是1主2从,2个从都采用了半同步复制,且设置的是rpl_semi_sync_master_wait_for_slave_count=2,如果其中一个挂掉了,
对于rpl_semi_sync_master_wait_no_slave设置为ON的情况,此时显示的仍然是半同步复制,如果rpl_semi_sync_master_wait_no_slave设置为OFF,则会立刻变成异步复制。

rpl_semi_sync_master_wait_point=wait_after_commit|wait_after_sync
参考:https://mp.weixin.qq.com/s/fvvEn6nSYzQs9NCa1eCOIQ
wait_after_commit:半同步;wait_after_sync:增强(无损)半同步
为什么要增强的半同步复制?因为传统的半同步复制有潜在问题
wait_after_commit模式:主上客户端发出提交指令,事务提交到了存储引擎后,等待从传递过来ack,再向前端返回成功的状态。
与无损复制的区别就是:如果在主上这个事务已经提交到了存储引擎,而正在等待从的ack过程中---这个时候发生creash,则主上这个事务其实已经认为commit了,而从还没commit,
在切换到从后,就会回滚最后的这个事务,这个时候主从的时候其实就不一致了
after_commit在主机事务提交后将日志传送到从机,after_sync是先传再提交

rpl_stop_slave_timeout ???
控制stop slave 命令的执行时间
控制stop slave 的执行时间,在重放一个大的事务的时候,突然执行stop slave,命令 stop slave会执行很久,这个时候可能产生死锁或阻塞,严重影响性能,mysql
5.6可以通过rpl_stop_slave_timeout参数控制stop slave 的执行时间

 
######################################################################################
MySQL主从复制Slave端参数
 
  timeout参数
  slave_net_timeout       
  connect_retry/master_connection_retry
  master_Retry_Count
  这几个参数用一句话来解释:
       备库过了slave_net_timeout秒之后,还没有收到主库来的数据,它就会开始第一次重试。
       然后每过 connect_retry/master_connection_retry秒后,备库会再次尝试重连主库。直到重试了 master_retry_count 次,它才会放弃重试。
 
  performance and replication carsh safety相关参数:
       
   master_info_repository = file|table
        master_info持久化方式
  
  sync_master_info = N
  每N个事件写入一次表/文件
  
  relay_log_info_repository = file|table
  relay_info持久化方式
  
  sync_relay_log_info = N
每N个事件写入一次表/文件
relay log信息如果配置为非Table模式,写事物和写文件(将已经应用的日志位置写入文件)是无法保持一致的
MySQL 5.6版本通过将复制信息存放到表中来解决此问题.通过配置两个参数 relay_log_info_repository=TABLE,master_info_repository=TABLE,
relay log info 会存放到 mysql.slave_relay_log_info表中,
master info 会存放mysql.slave_master_info表中。就是把SQL线程执行事务和更新mysql.slave_replay_log_info的语句看成一个事务处理,这样就会一直同步的.
           
半同步复制Slave端相关参数
  rpl-semi-sync-slave-enabled = on
       Slave开启半同步
 
       rpl_semi_sync_master_trace_level
       用于开启半同步复制模式时的调试级别,默认是32
 
       rpl_semi_sync_slave_trace_level
       用于开启半同步复制模式时的调试级别,默认是32
 
parallel replication多线程复制
       slave_parallel_workers = N
       slave上多个线程回放master上的binlog
 
       slave_parallel_type = DATABASE | LOGICAL_CLOCK
       DATABASE:默认值,基于库的并行复制方式
       LOGICAL_CLOCK:基于组提交的并行复制方式
 
  slave_preserve_commit_order =0| 1
当slave_preserve_commit_order=0时
没有办法保证顺序,在恢复的过程中会有问题,到时候你怎么start slave 呢?
start slave until SQL_AFTER_MTS_GAPS ; reset slave
Master执行顺序: last_committed=0,sequence_number=1,2,3,4  
slave执行顺序: 有可能就是 last_committed=0,sequence_number=1,4,3,2
当slave_preserve_commit_order=1时
后一个sequence_number提交的时候,会等待前一个sequence_number完成。
Waiting for preceding transaction to commit
 
  Slave Relay log

max_relay_log_size
标记relay log 允许的最大值,如果该值为0,则默认值为max_binlog_size(1G);如果不为0,则max_relay_log_size则为最大的relay_log文件大小;

relay_log
定义relay_log的位置和名称,如果值为空,则默认位置在数据文件的目录,文件名为host_name-relay-bin.nnnnnn(By default, relay log file names have the form host_name-relay-bin.nnnnnn in the data directory);

relay_log_index
同relay_log,定义relay_log的位置和名称;

relay_log_info_repository = table|file
relay_log_info_file

设置relay-log.info的位置和名称(relay-log.info记录MASTER的binary_log的恢复位置和relay_log的位置),也可以配置记录到mysql库中的slave_relay_log_info表中;
relay-log-recovery = 1
这个参数的作用是:当slave从库宕机后,假如relay-log损坏了,导致一部分中继日志没有处理,则自动放弃所有未执行的relay-log,并且重新从
master上获取日志,这样就保证了relay-log的完整性。默认情况下该功能是关闭的,将relay_log_recovery的值设置为 1时,可在slave从库上开启该功能,建议开启。
relay-log-purge
是否自动清空不再需要中继日志时。默认值为1(启用)

 
  slave冲突解决模式
         slave_exec_mode = strict|idempotent|smart
            

 

posted on 2019-07-30 23:27  MSSQL123  阅读(...)  评论(...编辑  收藏