mysql忧化参数

深碍√TFBOYSˉ_ 2021-12-16 20:13 214阅读 0赞

转自 https://blog.51cto.com/tongcheng/1710265  
以下参数是在mysql-5.6.27中使用,可能mysql版本不同使用方法不一样  
1.线程参数  
innodb\_read\_io\_threads=6 --innodb存储引擎读线程数,供客户端读取数据,默认值是4  
innodb\_read\_io\_threads=7 --innodb存储引擎写线程数,供客户端写入数据,默认值是4  
innodb\_purge\_threads=2 --innodb存储引擎回收purge页线程,清空脏数据,默认值是1  
innodb\_lru\_scan\_depth=1024 --控制LRU的可用页的数量,默认值是1024  
innodb\_max\_dirty\_pages\_pct=75 --innodb缓冲池中的脏页大于75%时,checkpoint将一部分的脏页强制刷新到磁盘,一般是100个脏页  
innodb\_io\_capacity=200 --磁盘IO在吞吐量,当数据量很大时默认刷新100个脏页,合并20个插入缓冲时可能忙不过来,此值可以刷新200个脏页,合并200 X 5%=100个插入缓冲,默认值是200  
innodb\_adaptive\_flushing=on --自动刷新脏页,当innodb\_max\_dirty\_pages\_pct值小于75时也可以刷新脏页  
innodb\_purge\_batch\_size=300 --每次回收undo页的数量,默认值是300个页  
innodb\_max\_purge\_lag = 0 --控制history list的长度,history list是根据事物提交的顺序将undo log进行链接。  
binlog\_max\_flush\_queue\_time = 0 --用来控制Flush阶段中的等待时间,也就是事物与事物之间的等待时间。  
innodb\_change\_buffering=all --插入缓冲只缓冲哪些语句,选项值有inserts,updates,deletes,purges,all,none等.none表示都不进行缓冲,all表示都进行缓冲.默认值是all  
innodb\_change\_buffer\_max\_size=25 --插入缓冲大小占整个缓冲池的25%内存大小,最大值是50  
innodb\_adaptive\_hash\_index=on --启用自适应哈稀索引(AHI),哈稀索引是一次读取数据,只能等值使用  
innodb\_page\_size=16KB --innodb存储引擎默认页(块)是16KB,可以自己调整,默认区大小是1M  
innodb\_file\_format=Antelope --字段的文件格式(文件格式有Compact,Redudant,Antelope,Compressed,Dynamic)  
innodb\_file\_format\_check=ON --检查字段是否支持的文件格式  
lower\_case\_table\_names=1 --mysql数据库对表名是否区分大小写,1 表示不区分大小写,0 表示区分大小写  
auto-rehash --命令补全功能,只补全库名,表名,字段名.(是mysql客房端参数,定义在\[mysql\]下面)  
foreign\_key\_checks=0 --关闭外键检查,关闭后导入导出数据的时间变快  
old\_alter\_table=off --创建和删除索引的算法,COPY 表示创建临时表,INPLACE 不需要创建临时表,off默认是INPLACE  
innodb\_online\_alter\_log\_max\_size=128M --记录在创建和删除索引时,将DML语句写入缓存日志,如果有大量的写事物可调大参数  
innodb\_stats\_sample\_pages=8 --统计Caridinality(索引散列)时每次采样页的数量,默认值是8  
innodb\_stats\_method=nulls\_equal --如何对待索引页的null值记录,nulls\_equal NULL视为相等的记录,nulls\_unequal NULL视为相等记录或NULL忽略记录,nulls\_ignored 忽略null值记录.  
innodb\_stats\_persistent=ON --是否将命令aalyze table计算到的Cardinality值存放到磁盘上  
innodb\_stats\_on\_metadata=OFF --命令show table status和show index是否重新需要计算索引到Cardinality值  
innodb\_stats\_persistent\_sample\_pages=20 --命令analyze table更新Cardinality值时每次采样页的数量,默认值是20  
innodb\_stats\_transient\_sample\_pages=8 --每次采样的数量,默认值是8  
innodb\_ft\_aux\_table=test/fts\_a --将test库fts\_a表的全文索引信息记录到表中,表名information\_schema.INNODB\_FT\_INDEX\_TABLE记录分词信息  
innodb\_ft\_cache\_size=31M --全文索引缓存的大小,默认是32M  
innodb\_optimize\_fulltext\_only=ON --启用全文索引忧化,删除多余的文档  
innodb\_ft\_num\_word\_optimize=2000 --每次删除分的数量,默认是2000  
innodb\_ft\_enable\_stopword=ON --启用不需要分词的参数  
innodb\_ft\_server\_stopword\_table=test/fts\_a --对不需要分词的word写入列表中,对应的分词在information\_scema.INNODB\_FT\_DEFAULT\_STOPWORD表中  
innodb\_ft\_min\_token\_size=3 --分词word中最小的长度,默认是3  
innodb\_ft\_max\_token\_size=84 --分词word中最大的长度,默认是84  
innodb\_autoinc\_lock\_mode=1 --控制自增长模式,默认值是1  
innodb\_lock\_wait\_timeout=50 --锁等待默认是50秒(适用于innodb存储引擎)  
innodb\_rollback\_on\_timeout=off --锁表后数据是否回滚,默认不回滚  
table\_lock\_wait\_timeout=50 --表锁等待(myisam存储引擎)  
slave-skip-errors=错误值 --在主从有错误时可以跳过指定的错误代码值

2.缓冲池参数  
innodb\_buffer\_pool\_size=2G --innodb存储引擎缓冲池包括数据页,索引页,插入缓冲,自适应哈稀索引,数据字典,锁信息.myisam只缓存索引文件.默认值134217728字节(128M).  
innodb\_buffer\_pool\_instances=8 --innodb的缓冲池个数,与innodb\_buffer\_pool\_size配合使用  
innodb\_old\_blocks\_pct=37 --使用LRU(最近最少算法)算法计算缓冲池,缓冲池前面63%为最活跃数据,63%以后为历史数据,最活跃数据命中率高.  
innodb\_old\_blocks\_time=100 --数据被读后100s才加入到最活跃缓冲池中  
innodb\_log\_buffer\_size=8M --重做日志缓冲大小,不宜设置太大,master thread是每秒同步到磁盘,事物提交同步到磁盘,重做日志空间剩余1/2也同步到磁盘,默认为8M  
innodb\_use\_native\_aio=on --开启异步io,默认是开启  
innodb\_flush\_neighbors=1 --开启刷新邻近接页,刷新相邻的脏页,默认开启

3.数据恢复  
innodb\_fast\_shutdown=1 --0表示关闭mysql时完成所有的full purge和merge insterbuffer操作.1表示将缓冲池刷新到磁盘,2表示将所有操作写入日志,下次启动时进行恢复  
innodb\_force\_recover=1 --1表示忽略检查corrupt页,将所有脏页同步到磁盘,2表示阻止主线程运行,3表示不执行事物回滚操作,4表示不执行缓冲插入操作,5表示不查看重做日志,6表示不执行前滚操作  
innodb\_undo\_directory = . --回滚段存放的目录  
innodb\_undo\_logs = 128 --回滚段的数量  
innodb\_undo\_tablespaces = 0 --构成回滚段的文件数量  
completion\_type=NO\_CHAIN --不使用链事物(0 commit与commit work是等价的,1 commit work与commit and chain是等价的,事物提交后自动启动相同隔离级别的事物.2 commit work与commit and release等价,事物提交后自动断开服务器)

4.日志  
slow\_query\_log=on --开启慢查询日志,记录查询较慢的sql语句  
slow\_query\_log\_file='文件路径' --存放慢查询文件的路径  
long\_query\_time=2 --慢查询的时间,默认是10秒  
log\_queries\_not\_using\_indexes=off --记录在查询中没有使用索引的查询,默认不使用索引  
log\_throttle\_queries\_not\_using\_indexes=0 --记录多少行慢查询,0表示无限制  
log\_output=FILE --指定慢查询日志输出是文件还是表(TABLE)  
log\_bin=master\_bin.log --开启二进制日志  
log\_bin\_index=master\_index.log --指定二进日志文件的索引文件  
max\_binlog\_size=1G --每个二进日志的大小,默认值是1G  
binlog\_cache\_size=8MB --二进日志缓存,默认大小32KB  
max\_binlog\_cache\_size=128MB --最大二进制日志缓存(show status like'%Binlog\_cache\_use%' 使用缓存写二进的次数,show status like'%Binlog\_cache\_disk\_use%' 使用临时文件写二进日志的次数)  
sync\_binlog=1 --事物提交同步到磁盘  
binlog\_do\_db=数据库 --需要同步的数据库(指定数据库写入二进日志)  
binlog-ignore-db=数据库 --不需要同步的数据库(忽略数据库写入二进日志)  
binlog\_format=STATEMENT --二进制日志输出格式,默认是语句模式,还有混合模式(MITEX)和行模式(ROW)  
binlog\_max\_flush\_queue\_time=0 --控制Flush阶段中等待的时间,默认是0  
innodb\_support\_xa=ON --分布式事务,如果事物未提交发生宕机事物进行回滚  
log\_slave\_updates=ON --将主数据库的二进日志同步到从数据库的二进制日志中  
innodb\_log\_file\_size=2G --重做日志文件的大小,默认4GB  
innodb\_log\_files\_in\_group=3 --重做日志文件组的数量,默认是2  
innodb\_mirrored\_log\_groups=1 --重做日志文件的镜像,默认值是1,表示没有镜像  
innodb\_log\_group\_home\_dir=./ --重做日志文件的路径,./表示在数据目录下  
innodb\_flush\_log\_at\_trx\_commit=1 --事物提交时将重做日志写入到磁盘,0 表示事物提交时不将重做日志写入到磁盘,等master线程每秒刷新,2表示将重做日志异步到磁盘  
completion\_type=0 --值是0表示事物提交进行下一个事物,值是1就是链事物,当一个事物完成性必须提交,值是2就是事物提交自动会断开与服务器的连接.

4.mysql 5.6新特性  
slave\_parallel\_workers = 4 --开启并行复制,减少复制的延迟,增加slave快速读取中继日志的内容  
gtid\_mode = on --从 5.6 开始mysql 增加了gtid(Global Transaction Identifiers),开启了这个参数,就在change master to 的时候就无需再指定 MASTER\_LOG\_FILE 和 MASTER\_LOG\_POS,而只需要增加auto\_master\_postition =1 就行了,如开启这个参数需要相应增加--log-slave-updates --enforce-gtid-consistency 这2个参数  
延时复制方案:  
如果你想slave 延时复制的话，可以把slave 停掉之后，用命令 change master to master\_delay=n n 为你想要延时的时间

5.二次写(doublewrite由两部分组成,一部分是内存中的doublewrite buffer，大小为2MB,另一部分是物理磁盘上的共享表空间连续的128个页,大小也是2MB)  
mysql> show global status like '%innodb\_db%';  
\+----------------------------+-------+  
| Variable\_name | Value |  
\+----------------------------+-------+  
| Innodb\_dblwr\_pages\_written | 1 | --一共写了多少个页  
| Innodb\_dblwr\_writes | 1 | --写了多少个页,如果(总数:写入)小于64:1证明写入负载不大  
\+----------------------------+-------+  
2 rows in set (0.00 sec)  
mysql>

转载于:https://www.cnblogs.com/ritchy/p/10944800.html