热门标签 | HotTags
当前位置:  开发笔记 > 编程语言 > 正文

初识MySQL5.6新功能、参数

摘要:继上一篇的文章初识MySQL5.5新功能、参数之后,现在MySQL5.6针对MySQL5.5各个方面又提升了很多,特别在性能和一些新参数上面,现在看看大致提升了哪

摘要:

      继上一篇的文章 初识 MySQL 5.5 新功能、参数 之后,现在MySQL5.6 针对 MySQL5.5 各个方面又提升了很多,特别在性能和一些新参数上面,现在看看大致提升了哪些方面(后续不定时更新)。

一:性能、功能上的提升。

在线DDL即 online DDL,日常的增删字段和索引都不会出现问题,但还是有很多操作不支持完全的在线DDL,包括增加一个全文索引,修改列的数据类型,删除一个主键,修改表的字符集等,其中主键可以通过自己指定的方式进行操作,操作方式有2种:algorithm=inplace|copy 。也可以看官方的例子

dba@192.168.200.59 : dchat_main 05:44:54>alter table messages_tt add primary key(m_id),algorithm=inplace;
Query OK, 0 rows affected (26.59 sec)
Records: 0  Duplicates: 0  Warnings: 0

dba@192.168.200.59 : dchat_main 05:45:53>alter table messages_tt drop primary key;
Query OK, 684076 rows affected (16.46 sec)
Records: 684076  Duplicates: 0  Warnings: 0

dba@192.168.200.59 : dchat_main 05:46:20>alter table messages_tt add primary key(m_id),algorithm=copy; Query OK, 684076 rows affected (17.54 sec)
Records: 684076  Duplicates: 0  Warnings: 0

dba@192.168.200.59 : dchat_main 05:46:48>alter table messages_tt drop primary key;
Query OK, 684076 rows affected (16.73 sec)
Records: 684076  Duplicates: 0  Warnings: 0

dba@192.168.200.59 : dchat_main 05:48:19>alter table messages_tt add primary key(m_id);默认,和第一次inplace效果一样
Query OK, 0 rows affected (26.31 sec)
Records: 0  Duplicates: 0  Warnings: 0

下面是MySQL官方的测试例子:

mysql> CREATE TABLE add_pk_via_inplace (c1 INT, c2 VARCHAR(10), c3 DATETIME);
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO add_pk_via_inplace VALUES (1,'a','2014-11-03 11:01:37'),(NULL,NULL,NULL);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM add_pk_via_inplace;
+------+------+---------------------+
| c1   | c2   | c3                  |
+------+------+---------------------+
|    1 | a    | 2014-11-03 11:01:37 |
| NULL | NULL | NULL                |
+------+------+---------------------+
2 rows in set (0.00 sec)

mysql> SET sql_mode = '';
Query OK, 0 rows affected (0.00 sec)

mysql> ALTER TABLE add_pk_via_inplace ADD PRIMARY KEY (c1,c2,c3), ALGORITHM=INPLACE;
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: cannot silently convert NULL 
values, as required in this SQL_MODE. Try ALGORITHM=COPY.

mysql> SET sql_mode ='strict_trans_tables';
Query OK, 0 rows affected (0.00 sec)

mysql> ALTER TABLE add_pk_via_inplace ADD PRIMARY KEY (c1,c2,c3), ALGORITHM=INPLACE;
ERROR 1138 (22004): Invalid use of NULL value
mysql> DELETE FROM add_pk_via_inplace WHERE c1 IS NULL OR c2 IS NULL OR c3 IS NULL;
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM add_pk_via_inplace;
+------+------+---------------------+
| c1   | c2   | c3                  |
+------+------+---------------------+
|    1 | a    | 2014-11-03 11:01:37 |
+------+------+---------------------+
1 row in set (0.00 sec)

mysql> ALTER TABLE add_pk_via_inplace ADD PRIMARY KEY (c1,c2,c3), ALGORITHM=INPLACE;
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0
View Code

从自己的例子看出,在处理主键时,没有指定ALGORITHM子句,MySQL会自动选择任意算法(如果都支持,则ALGORITHM=INPLACE优先)。所以在执行的时候可以不用显性的加上"algorithm"

在官方的例子看出,对于主键列为null时,alter table … add primary key 只在sql_mode 包含srict_trans_table 或strict_all_tables标志下才支持ALGORITHM=INPLACE,否则,强制指定ALGORITHM=INPLACE,会出现错误信息:

ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: cannot silently convert NULL values, as required in this SQL_MODE. Try ALGORITHM=COPY.

若不指ALGORITHM=INPLACE,会复制表,并且把null 改成not null。

当主键列为not null的时候则一切正常。

注意采用algorithm=inplace新建主键,虽然避免表的复制,但数据需要重新进行重组的。ALGORITHM=copy则把表拷贝到临时表,然后把临时表的数据插入到新表。

对于alter table … drop primary key 只能采用ALGORITHM=copy,如果强制采用ALGORITHM=inplace会出现错误。

总之在DDL发起和完成之前(altering table 阶段),允许在这期间DML、查询的并发。但DDL在完成时,还是需要获取表的元数据锁(MDL)。因为online DDL在 preparing for alter table和committing alter table to storage engine这两个阶段需要获取表的排斥锁(时间比较短)。

原理,5.6是通过怎么样的方法来做到online ddl 的呢?在5.6之前我们进行online ddl是需要通过percona tool创建的触发器实现。这里有个参数:

innodb_online_alter_log_max_size:

该参数是MySQL5.6.6新加入的一个参数,用以指定对InnoDB表做在线DDL操作时所使用的临时日志文件的最大大小(以字节为单位,默认128M)。在创建索引或者ALTER表时会使用该临时文件。该日志文件记录了DDL操作期间插入、更新、删除的数据。每次扩展innodb_sort_buffer_size的大小直至达到innodb_online_alter_log_max_size指定的最大值。若日志的大小超出此上限则ALTER表的操作会失败,当前所有未提交的DML操作会回滚。但如果日志文件太大会可能会导致DDL操作最后锁定表(Waiting for table metadata lock)的时间更长(锁定表,应用日志到表上)。

② Purge Thread:回收UNDO。

通过参数innodb_purge_threads来开启,5.5之后的参数,从Master Thread独立出来,回收无用的UNDO页。在MySQL 5.5之前是在Master Thread中完成的。5.5之后,独立到了单独的线程中完成,减轻了Master Thread线程的工作。提升CPU的使用率和存储引擎的性能。在5.5中只能也只有1可以设置。5.6可以设置大于1。另一个参数:innodb_purge_batch_size 来控制每次回收UNDO页的数量,在5.5之前默认是20(写死),5.5之后可以根据情况调整该参数,默认300。

③ Page Clean Thread:刷写脏页。

脏页的刷写线程。5.6里开始支持从Master Thread里面独立出来。减轻了Master Thread 的工作,和对用户查询的阻塞。进一步提高Innodb 存储引擎的性能和并发。5.7可以设置多个线程来刷写脏页。

 插入缓冲(Insert Buffer/Change Buffer):提升插入性能。

InnoDB为了避免更新数据时更新索引损失太多性能,使用了这种称为Insert Buffer的方法来缓冲索引更新。关于插入缓冲的概念可以查找资料,也可以看这里。原先插入缓冲最大使用空间为1/2的缓冲池大小,5.6之后可以控制插入缓冲的大小了,通过参数:innodb_change_buffer_max_size,默认是BP的1/4。

 刷新邻接页:刷写一个脏页时,会检测该页所在的区(extent:64页,1M)的其他页是否也有脏页,有则一起刷写。5.6通过参数:innodb_flush_neighbors 参数控制。机械磁盘建议开启,固态硬盘建议设置为0,即关闭。

复制方面的提升:包括GTID、binlog的记录(基于行的复制)、延迟复制、多线程复制、远程备份binlog等。

数据库预热的提升:从之前读表的预热升级到buffer pool的预热。

优化器的增强:包括ICP、MRR、BAK等。

InnoDB全文检索的支持:InnoDB对fulltext的支持,提高了MySQL的并发性。

 死锁检测增强:5.6 中引入参数innodb_print_all_deadlocks,这个参数是全局设置的, 可以把所有的死锁状况打印到error日志中。

 Undo Log 从系统表空间分离。innodb_undo_directory、innodb_undo_tablespaces、inodb_undo_logs。

Explain的增强。可以对insert、update、delete 进行explain。

优化器跟踪。set optimizer_trace='enabled=on'开后,执行sql,再通过select * from information_schema.optimizer_trace\G; 查看。

⑭ Innodb_page_size的增强。可以自行的修改页大小,在数据库创建的时候就先设置好。

infomation_schema的增强。information_schema中增加了一些统计表。

⑯ thread pool的增强。降低了数据库连接的不必要开销,提高了并发处理能力。

...

写这篇博文主要就是为了针对参数的说明和设置,现在我们来看看MySQL server 参数和Inndob参数的介绍。

二,新参数说明和设置,这里说下5.6比较重要的参数,以及5.5到5.6默认值有过变化的参数。

MySQL Server参数:

1,optimizer_switch:优化器选项。

Variable_name: optimizer_switch
        Value: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on

关于优化器的改进可以参考下面这些文章:

ICP  :http://blog.itpub.net/22664653/viewspace-1678779/

MRR:http://blog.itpub.net/22664653/viewspace-1673682/

BKA:http://blog.itpub.net/22664653/viewspace-1715511/

BNL:http://blog.itpub.net/22664653/viewspace-1692317/

2,thread_handling(Thread pool)线程调度方式相关参数,默认one-thread-per-connection。详细说明可以看percona的官方文档。

MySQL常用(目前线上使用)的线程调度方式是one-thread-per-connection(每连接一个线程),server为每一个连接创建一个线程来服务,连接断开后,这个线程进入thread_cache或者直接退出(取决于thread_cache设置及系统当前已经cache的线程数目)。比较适合活跃的长连接的应用场景,而在大量短连接或者高并发情况下,one-thread-per-connection需要创建/调度大量的线程,产生较高的的context-switch代价,从而使得系统性能下降。Threadpool是提供一种线程代理的模型执行每个连接的语句。而MySQL内部维护一个可能接受的线程总数,减少线程太多在CPU切换等方面的压力和开销。通过thread_handling=pool-of-threads开启thread pool功能:threadpool中worker线程处理单位为一个sql,而不是one-thread-per-connection对应的一个连接;当worker线程处理完A连接发送来的一个sql后,A连接没有立刻发送第二条sql,worker线程会去服务其它连接发送来的sql,因此worker线程工作效率更高,系统需要的线程数也更少。启用Thread Pool后,想要终止某个查询的话,要这么写KILL QUERY connection_id,而不是写成 KILL connection_id,否则就会导致整个连接被KILL。 

threadpool相关参数
root@(none) 05:33:27>show global variables like '%thread_pool%';
+-------------------------------+--------------+
| Variable_name                 | Value        |
+-------------------------------+--------------+
| thread_pool_high_prio_mode    | transactions |
| thread_pool_high_prio_tickets | 4294967295   |
| thread_pool_idle_timeout      | 60           |
| thread_pool_max_threads       | 100000       |
| thread_pool_oversubscribe     | 3            |
| thread_pool_size              | 24           |
| thread_pool_stall_limit       | 500          |
+-------------------------------+--------------+
7 rows in set (0.00 sec)
thread_pool_high_prio_mode
有三个取值:transactions / statements / none
transactions(default): 使用优先队列和普通队列,对于事务已经开启的statement,放到优先队列中,否则放到普通队列中
statements:只使用优先队列
none: 只是用普通队列,本质上和statements相同,都是只是用一个队列

thread_pool_high_prio_tickets
取值0~4294967295,当开启了优先队列模式后(thread_pool_high_prio_mode=transactions),每个连接最多允许thread_pool_high_prio_tickets次被放到优先队列中,之后放到普通队列中,默认为4294967295

thread_pool_idle_timeout
worker线程最大空闲时间,单位为秒,超过限制后会退出,默认60

thread_pool_max_threads
threadpool中最大线程数目,所有group中worker线程总数超过该限制后不能继续创建更多线程,默认100000

thread_pool_oversubscribe
一个group中线程数过载限制,当一个group中线程数超过次限制后,继续创建worker线程会被延迟,默认3

thread_pool_size

threadpool中group数量,默认为cpu核心数,server启动时自动计算

thread_pool_stall_limit
timer线程检测间隔,单位为毫秒,默认500

关于具体的信息可以参考下面的文章:

http://www.mysqlsupport.cn/percona-thread-pool/

http://www.gpfeng.com/?p=540&utm_source=tuicool&utm_medium=referral

http://imysql.cn/2014/07/02/percona-thread-pool-benchmark-testing.shtml

3,...

 

InnoDB 参数:

1,binlog_checksum:全局动态变量。5.6.2引入,5.6.5之前默认是NONE,5.6.6之后默认是CRC32。

开启该参数,这个变量将导致主为每个二进制日志的事件进行写校验,提高了安全性。关闭该参数的时候,是通过二进制日志事件的长度来验证的。需要注意的是:因为5.6.2之前没有这个参数,要是主从版本不一致(M(5.6.6),S(5.5.x))会导致复制出错,需要显性的在高版本上添加:binlog_checksum=NONE;或则执行:set global binlog_checksum = none。

2,innodb_autoextend_increment:全局动态变量。5.6.6之后默认大小是64M,之前是8M。

该参数是在当共享表空间满了的时候自动扩展的一个大小,如果表是独享表空间(innodb_file_per_table),该变量不会影响其创建大小。

3,innodb_buffer_pool_instances:全局变量。 5.6.6之后默认改成了8,之前是1。

该参数来增加InnoDB_Buffer_Pool实例的个数(BP>1G),并使用哈希函数将读取缓存的数据页随机分配到一个缓冲池里面。每个缓冲区实例分别管理着自己的free list、flush list和LRU。提升了buffer pool的利用率。要是单个InnoDB_Buffer_Pool缓冲池实例,当达到好几十GB时,如果某个线程正在更新缓冲池,将会造成其他线程必须等待的瓶颈。

4,innodb_concurrency_tickets:全局动态变量。5.6.6之后默认5000,之前是500。

该参数意义是同一时刻,能访问InnoDB引擎数据的线程数,当访问InnoDB引擎数据的线程数达到设置的上线,线程将会被放到队列中,等待其他线程释放ticket。

5,innodb_old_blocks_time:全局动态变量。5.6.6之后默认是1000,之前是0。单位是毫秒。

该参数表示等到该时间后,再读取该页则会进入到new端,有效的避免了对于上述SQL对BP的污染。默认是0,单位是毫秒。如设置为1000则表示:读到该页到midpoint的位置,要再等1秒之后读取该页才能进入new列表。而0则表示读取到该页则会直接被放入到new列表。具体的可以看这里。和innodb_old_blocks_pct配合使用,该参数默认是37(3/8),即BP的3/8处。

6,innodb_stats_on_metadata:全局动态变量。5.6.6之后默认是OFF,之前是ON。

该参数的作用是查询 information_schema 元数据库里的表时,Innodb 还会随机提取其他数据库每个表索引页的部分数据。当你的表很大,并且数量很多时,耗费的时间就会很长,很多不访问的数据也会进入到Innodb_Buffer_Pool 缓冲池里,那么就会把缓冲池所污染。并且 ANALYZE TABLE和 SHOW TABLE STATUS 语句也会造成 Innodb 随机提取数据。可以动态关闭 innodb_stats_on_metadata,默认是开启的,建议关闭

7,innodb_adaptive_flushing_lwm:全局动态变量。5.6.6支持,默认值得10。

该参数表示redo log(ib_logfile)的一个最低容量限制百分比,默认为10,范围是[0,70]。当没有达到这个值时,page cleaner线程不会根据redo来判断是否刷脏页,超过则通过参数innodb_adaptive_flushing来刷写。

8,innodb_buffer_pool_dump_at_shutdown:全局动态变量,默认关闭。

该参数表示当数据库关闭时,是否把BP里的数据导出到文件里,建议开启。如果一台高负荷的机器重启后,buffer pool中的热数据被丢失,此时就会重新从磁盘加载到Buffer_Pool缓冲池里,这样当高峰期间,性能就会变得很差,连接数就会很高,应用的性能也受到影响

9,innodb_buffer_pool_dump_now:全局动态变量,默认关闭。

该参数表示采用手工方式把热数据dump到本地磁盘文件。可以在数据库运行时执行。

10,innodb_buffer_pool_filename:全局变量,默认:ib_buffer_pool

该参数控制BP导出的文件名。

11,innodb_buffer_pool_load_abort:全局动态变量,默认OFF。

该参数表示中止缓冲池加载操作。

12,innodb_buffer_pool_load_at_startup:全局变量,默认OFF。

该参数表示在数据库启动时,把dump出来的数据加载到内存,建议开启。

13,innodb_buffer_pool_load_now:全局变量,默认OFF。

该参数表示在数据库运行时,把dump出来的数据加载到内存。

14,innodb_change_buffer_max_size:全局动态变量,默认是25,单位百分比。

该参数表示InnoDB为了避免更新数据时更新索引损失太多性能,使用了这种称为Insert Buffer的方法来缓冲索引更新。关于插入缓冲的概念可以查找资料,也可以看这里。原先插入缓冲最大使用空间为1/2的缓冲池大小,5.6之后可以控制插入缓冲的大小了,默认是BP的1/4。

15,innodb_disable_sort_file_cache:全局动态变量,默认OFF。

该参数开启表示操作系统不对merge-sort的临时文件cache,使用O_DIRECT。

16,innodb_flush_log_at_timeout:全局动态变量,默认1,范围是1~2700。

该参数表示自定义刷新日志时间,每隔这么多秒刷一次日志,只有在innodb_flush_log_at_trx_commit=2时才生效。大致的原因是:

1.INNODB REDO日志:InnoDB为了保证日志的刷写的高效,使用了内存的log buffer。
由于InnoDB大部分情况下使用的是文件系统,(linux文件系统本身也是有buffer的)而不是直接使用物理块设备,这样的话就有两种丢失日志的可能性:日志保存在log_buffer中,机器挂了,对应的事务数据就丢失了;日志从log buffer刷到了linux文件系统的buffer,机器挂掉了,对应的事务数据就丢失了。
2.InnoDB有一个参数用于设置这两个缓存的刷新: innodb_flush_log_at_trx_commit。而 innodb_flush_log_at_trx_commit  有三个值:0/1/2,默认是1。而innodb_flush_log_at_timeout 定义了每次日志刷新的时间,与  innodb_flush_log_at_trx_commit 配合使用:
innodb_flush_log_at_trx_commit=1,表示在每次事务提交的时候,都把log buffer刷到文件系统中(os buffer)去,并且调用文件系统的“flush”操作将缓存刷新到磁盘上去。
innodb_flush_log_at_trx_commit=0,表示每隔一秒把log buffer刷到文件系统中(os buffer)去,并且调用文件系统的“flush”操作将缓存刷新到磁盘上去。
innodb_flush_log_at_trx_commit=2,表示在每次事务提交的时候会把log buffer刷到文件系统中(os buffer)去,但是每隔一秒调用文件系统(os buffer)的“flush”操作将缓存刷新到磁盘上去。如果只是MySQL数据库挂掉了,由于文件系统没有问题,那么对应的事务数据并没有丢失。只有在数据库所在的主机操作系统损坏或者突然掉电的情况下,数据库的事务数据可能丢失1秒之类的事务数据。这样的好处,减少了事务数据丢失的概率,而对底层硬件的IO要求也没有那么高(log buffer写到文件系统中,一般只是从log buffer的内存转移的文件系统的内存缓存中,对底层IO没有压力)。MySQL 5.6.6以后,这个“1秒”的刷新还可以用innodb_flush_log_at_timeout来控制刷新间隔。

具体的原因可以看这里和这里。

17,innodb_flush_log_at_trx_commit:动态变量,默认是1。

该参数表示的是日志刷写机制,可选值有0,1,2。具体意思见16。

18,innodb_flush_method:全局变量。默认为NULL。

该参数表示刷写的模式,在5.6.7之后多了一个值:O_DIRECT_NO_FSYNC,具体说明见这里。

19,innodb_flush_neighbors:全局动态变量,默认是1。

该参数表示刷新邻接页,当刷写一个脏页时,会检测该页在BP里所在的区(extent:64页,1M)的其他页是否也有脏页,有则一起刷写。5.6通过参数:innodb_flush_neighbors 参数控制。机械磁盘建议开启,固态硬盘建议设置为0,即关闭。要不是随机IO多则建议开启,充分利用顺序IO去写数据。

20,innodb_flushing_avg_loops:全局动态变量,默认是30,范围是1-1000。

该参数表示控制adaptive flush对工作负载变化的响应速度。在这么多次loop内,innodb会保持上次的刷新状态快照不变,增加这个值有助于刷新操作更加平稳,而减小这个值有助于对工作负载的变化更快的调整adaptive flush,不过,如果设置的过小的话,在突然增大/减小的工作的负载中,容易引起性能尖峰。

21,innodb_force_load_corrupted全局变量。默认关闭

该参数表示当innodb表损坏时开启数据库导入表,排查故障,修复数据时不可能访问, 当故障排除后,关闭此设置回关闭并重新启动服务器。可以和innodb_force_recovery关联。

22,Innodb FullText:关于InnoDB的全文索引的说明见官方文档,使用方法见:之前介绍的文章。

23,innodb_large_prefix:全局动态变量,默认关闭。

该参数表示当innodb为字段创建索引时,限制的字节长度。关闭时,字节长度大于767则会报warnings。开启时,则会报错,创建不成功。

zjy@192.168.200.59 : test 11:39:29>create table ttt(id int,name varchar(1024)) default charset utf8;
Query OK, 0 rows affected (0.00 sec)

zjy@192.168.200.59 : test 11:39:46>alter table ttt add index idx_name(name);
Query OK, 0 rows affected, 1 warning (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 1

zjy@192.168.200.59 : test 11:39:58>show warnings;                                                                                                                                    +---------+------+---------------------------------------------------------+
| Level   | Code | Message                                                 |
+---------+------+---------------------------------------------------------+
| Warning | 1071 | Specified key was too long; max key length is 767 bytes |
+---------+------+---------------------------------------------------------+
1 row in set (0.01 sec)

zjy@192.168.200.59 : test 11:40:00>show create table ttt\G;
*************************** 1. row ***************************
       Table: ttt
Create Table: CREATE TABLE `ttt` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(1024) DEFAULT NULL,
  KEY `idx_name` (`name`(255))
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.01 sec)

zjy@192.168.200.59 : test 11:40:26>alter table ttt add address varchar(1024);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

dba@192.168.200.59 : test 11:39:03>set global innodb_large_prefix = 1;
Query OK, 0 rows affected (0.00 sec)

zjy@192.168.200.59 : test 11:40:33>alter table ttt add index idx_address(address);
ERROR 1709 (HY000): Index column size too large. The maximum column size is 767 bytes.

dba@192.168.200.59 : test 11:41:10>set global innodb_large_prefix = 0;
Query OK, 0 rows affected (0.01 sec)

zjy@192.168.200.59 : test 11:40:57>alter table ttt add index idx_address(address);
Query OK, 0 rows affected, 1 warning (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 1
View Code 

24,innodb_locks_unsafe_for_binlog全局变量,默认OFF。

该参数表示innodb锁和二进制的安全问题,当ON的时候可以避免gap lock的问题,但是会引起binlog写入顺序出问题,导致主从数据不一致。建议关闭。

25,innodb_max_purge_lag:全局变量,默认0。

该参数表示是当purge操作滞后是否要延迟insert、delete、update的操作,延迟操作时间有innodb_max_purge_lag_delay控制,单位毫秒。默认不延迟,建议不延迟。

26,innodb_monitor_*相关参数:innodb_monitor_disable、innodb_monitor_enable、innodb_monitor_reset、innodb_monitor_reset_all。

这些参数表示InnoDB计数器的统计,这些都记录在INFORMATION_SCHEMA.INNODB_METRICS中体现,后面会另起一篇文章介绍这些参数。

27,innodb_online_alter_log_max_size:全局动态变量,默认128M。

该参数具体的说明和使用上面已经说明。

28,innodb_optimize_fulltext_only:全局动态变量,默认关闭。

该参数表示改变OPTIMIZE TABLE语句操作Innodb表的方法,对含有全文索引的innodb表的维护操作期间,临时启用该变量。默认情况, OPTIMIZE TABLE 会对有聚集索引表进行重新整理数据。当启用这选项,OPTIMIZE TABLE跳过表数据重新整理,仅对有全文索引的新增、更新和删除的数据进行处理。

29,innodb_page_size:全局变量,默认16K。

该参数表示InnoDB页的大小,默认16K,可以设置的值有4K、8K、16K。在数据库创建的时候就先设置好,后续不能修改。

30,innodb_print_all_deadlocks:全局动态变量,默认OFF。

该参数表示死锁检测,开启可以把所有的死锁状况打印到error日志中。否则,使用 SHOW ENGINE INNODB STATUS命令,只能看到最后一条死锁信息。InnoDB死锁会自动回滚一个小事务,可以使用这个选项来排除为什么发生死锁。

31,innodb_purge_threads:全局变量,默认是1。5.6之前最大也只能设置1,5.6.5之后可以设置大于1。

该参数表示回收UNDO,5.5之后的参数,从Master Thread独立出来,回收无用的UNDO页。在MySQL 5.5之前是在Master Thread中完成的。5.5之后,独立到了单独的线程中完成,减轻了Master Thread线程的工作,提升CPU的使用率和存储引擎的性能。和参数innodb_purge_batch_size配合使用,该参数控制每次回收UNDO页的数量,在5.5之前默认是20(写死),5.5之后可以根据情况调整该参数,默认300。

32,innodb_read_ahead_threshold:全局变量,默认56,最大64。单位是页。

该参数控制InnoDB预读的灵敏度(阙值),如果Innodb至少从一个extent(64页)顺序读取innodb_read_ahead_threshold页,则innodb将会异步的将下一个extent读取到buffer pool中。可以监控这些预读的情况:

在监控innodb的预读时候,我们可以通过show innodb status中的 Pages read ahead和evicted without access 两个值来观察预读的情况:
Pages read ahead 1.00/s, evicted without access 9.99/s.
得到的值表示每秒读入和读出的pages

或者通过两个状态值:

Innodb_buffer_pool_read_ahead和Innodb_buffer_pool_read_ahead_evicted.

Innodb_buffer_pool_read_ahead:表示通过预读请求到buffer pool的pages;

Innodb_buffer_pool_read_ahead_evicted:表示由于请求到buffer pool中没有被访问,而驱逐出buffer pool的pages;

> show global status like '%read_ahead%';
+---------------------------------------+-------+
| Variable_name                         | Value |
+---------------------------------------+-------+
| Innodb_buffer_pool_read_ahead_rnd     | 0     |
| Innodb_buffer_pool_read_ahead         | 18497 |
| Innodb_buffer_pool_read_ahead_evicted | 0     |
+---------------------------------------+-------+
View Code

具体的说明见:http://hidba.org/?p=398

33,innodb_read_only:全局变量,5.6.7开始支持,默认OFF。

该参数表示以只读模式启动服务。用于分发数据库应用或数据设置为只读的。也可以用于数据仓库,多个实例之间共享同一数据目录,具体可以查看官网说明。

34,innodb_replication_delay:全局动态变量,默认0,单位毫秒。

该参数表示当到达innodb_thread_concurrency限制,在一个从服务延迟复制线程(毫秒),一般为了提高并发和cpu的利用率innodb_thread_concurrency设置为0,不做限制,所以该参数没什么意义。

35,innodb_rollback_on_timeout:全局变量,默认OFF。

该参数表示当发生超时的时候,关闭参数时InnoDB仅回滚超时事务的最后一条语句,如果开启则一个事务超时引起Innodb中止和回滚整个事务。可以看这个例子。

36,innodb_sort_buffer_size:全局变量,5.6.4进入,默认1M,可选范围是64K到64M。

该参数表示InnoDB索引创造期间排序数据,仅用于在创建索引期间合并排序,而不是用于后续索引维护操作。在ALTER TABLE或CREATE TABLE 创建一个索引期间,分配3个缓冲区。每个缓冲区的大小都使用该选项。这些缓冲区在创建索引完成时将其收回。这个选项的也控制online ddl临时日志文件扩展大小,该日志文件记录在线DDL期间并发DML操作的数据。

37,innodb_stats_on_metadata:全局动态变量,默认OFF。

该参数表示查询 information_schema 元数据库里的表时,Innodb 还会随机提取其他数据库每个表索引页的部分数据,当你的表很大,并且数量很多时,耗费的时间就会很长,很多不访问的数据也会进入到Innodb_Buffer_Pool 缓冲池里,那么就会把缓冲池所污染。并且 ANALYZE TABLE和 SHOW TABLE STATUS 语句也会造成 Innodb 随机提取数据。5.6.6之后默认是OFF,之前默认是ON,建议关闭。

38,innodb_undo_directory:全局变量,5.6.3开始支持。把undo log分离到独立的表空间,并放到单独的文件目录下,这给我们部署不同IO类型的文件位置带来便利,对于并发写入型负载,我们可以把undo文件部署到单独的高速存储设备上。具体可以查看这篇文章。

该参数表示当开启独立undo表空间,指定undo文件存放的目录。即InnoDB为undo日志创建单独的表空间文件的相对或绝对路径。将日志存储在不同的存储设备。结合innodb_undo_logs 和 innodb_undo_tablespaces 一起使用。这个变量决定在系统表空间之外的undo日志的存储布局。默认.表示和其他日志在同一个目录。

39,innodb_undo_logs:全局动态变量,默认128

该参数表示回滚段的个数(早期版本的命名为innodb_rollback_segments ),该变量可以动态调整,但是物理上的回滚段不会减少,只是会控制用到的回滚段的个数。

40,innodb_undo_tablespaces:全局变量,默认0

该参数表示用于设定创建的undo表空间的个数,在Install db时初始化后,就再也不能被改动了。默认值为0,表示不独立设置undo的tablespace,默认记录到ibdata中;否则,则在undo目录下创建这么多个undo文件,例如假定设置该值为16,那么就会创建命名为undo001~undo016的undo tablespace文件,每个文件的默认大小为10M。

41,innodb_lru_scan_depth:全局动态变量,默认1024

该参数会影响page cleaner线程每次刷脏页的数量,这是一个每1秒 loop一次的线程。当系统的IO比较空闲的时候,可以适当将这个参数设大,当IO吃紧时,需要适当减小。IO能力强的可以适当调大具体,可以查看这篇文章。

42,table_open_cache_instances:全局动态变量,默认1

对table cache进行划分,减少table cache的锁竞争。

43,metadata_locks_hash_instances:全局动态变量,默认8

对server层的metalock hash进行划分。

44, metadata_locks_cache_size:全局动态变量,默认1024

metadata lock cache的大小,这是总的大小,可以适当调大来提升并发度。

45,log_throttle_queries_not_using_indexes:全局动态变量,默认0

当打开log_queries_not_using_indexes  时,该变量用于限制每分钟写入slow log的日志条数。

46,default_tmp_storage_engine:动态变量,默认InnoDB

用于控制在创建临时表时使用的存储引擎,默认为innodb。

47,explicit_defaults_for_timestamp:默认0

影响timestamp类型column的行为,具体见文档中的参数说明或则上一篇文章说明。

48,ignore_db_dirs:

用于控制是否忽略DATA目录下的db目录,多个用多行分开,如:

ignore_db_dir = undolog
ignore_db_dir = tokudb_data
ignore_db_dir = tokudb_log
dba@192.168.121.58 : (none) 02:06:52>show variables like 'ignore_db_dirs';
+----------------+--------------------------------+
| Variable_name  | Value                          |
+----------------+--------------------------------+
| ignore_db_dirs | undolog,tokudb_data,tokudb_log |
+----------------+--------------------------------+

49,eq_range_index_dive_limit:动态变量,默认10

用于优化in(),以确认是否直接使用索引统计,在where条件中列的等值条件个数小于这个值时,使用index dive来估算行数,否则使用index statistics来估算;设置为0则禁用index statistics,index dive更准确但效率低,具体说明见 文档或则文章说明。

50,复制相关的参数:

slave_parallel_workers   #备库复制worker线程数

slave_checkpoint_group    #在并发复制时总共执行这么多次事务后做一次checkpoint,更新show slave status的数据

slave_checkpoint_period    #在复制执行这么长时间后做一次checkpoint

slave_pending_jobs_size_max    #在多线程复制时,在队列中Pending的事件所占用的最大内存,默认为16M,如果内存富余,或者延迟较大时,可以适当调大;注意这个值要比主库的max_allowed_packet大

slave_allow_batching   #备库是否允许批量更新,只用于ndb cluster

slave_sql_verify_checksum   #备库SQL线程是否检查binlog的checksum

slave_rows_search_algorithms

binlog_checksum   #默认为CRC32,表示使用该算法记录binlog checksum,设置为NONE,则关闭checksum

binlog_max_flush_queue_time   #表示在做group commit之前多长时间从刷新队列读取事务。默认值为0,表示没有超时,直到队列为空为止。控制在Flush stage中的等待时间,让Flush队列在此阶段多等待一些时间来增加这一组事务队列的数量使 该队列到Sync阶段可以一次fysnc()更多的事务 

binlog_order_commits     #开启该选项时,事务提交顺序和binlog记录顺序是相同的,默认打开;设置为关闭时,事务提交顺序可能是并行的;关闭可能提升性能

binlog_row_image              #row模式下,binlog记录格式。根据选项记录相应的列:full:默认行为,记录所有的列,和MySQL5.6之前的版本一样;minimal:记录被修改的列,其他未修改的列不记录;noblob :记录所有的列,除text、blob列外。

binlog_rows_query_log_events   #行模式下,是否记录原始的query语句,可以直接不需要-vv,直接用mysqlbinlog查看。

log_bin_use_v1_row_events      #默认关闭,表示使用v2版本的binlog格式,打开的话,则使用之前版本的binlog格式

51,

...

 

相关文章:

http://mysqllover.com/?p=575

http://www.tuicool.com/articles/z2uAza

 


推荐阅读
author-avatar
守护雪天_使0062_423
这个家伙很懒,什么也没留下!
Tags | 热门标签
RankList | 热门文章
PHP1.CN | 中国最专业的PHP中文社区 | DevBox开发工具箱 | json解析格式化 |PHP资讯 | PHP教程 | 数据库技术 | 服务器技术 | 前端开发技术 | PHP框架 | 开发工具 | 在线工具
Copyright © 1998 - 2020 PHP1.CN. All Rights Reserved | 京公网安备 11010802041100号 | 京ICP备19059560号-4 | PHP1.CN 第一PHP社区 版权所有