[MySQL学习] MySQL 5.6 improvement for troubleshooting

本文基于Sveta(Oracle的Principle Technical Support Engineer )的博文”My eighteen MySQL 5.6 favorite troubleshooting improvements”,原文地址如下:https://blogs.oracle.com/svetasmirnova/entry/my_18_mysql_5_6

原文针对每个点介绍的比较粗略,这里会对内容做一些扩展,也是我看这篇博客时的笔记,聚合了查阅的相关资料

 

1.对UPDATE/INSERT/DELETE进行EXPLAIN

在5.5及之前的版本中,只能对SELECT进行explain,输出查询计划,通常的做法是将DML转换为SELECT,但优化器在对DML和查询,可能做不同的优化。

 

简单的测试表sbtest,例如:

mysql> explain delete from sbtest1 where k = 100;

+—-+————-+———+——-+—————+——+———+——+——+————-+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+—-+————-+———+——-+—————+——+———+——+——+————-+

| 1 | SIMPLE | sbtest1 | range | PRIMARY,k | k | 4 | NULL | 1 | Using where |

+—-+————-+———+——-+—————+——+———+——+——+————-+

1 row in set (0.00 sec)

 

不过尝试了下,explain extended对于DML不记录warning,而对于SELECT,可以从warning中查看到具体的查询计划信息     

 

2. INFORMATION_SCHEMA.OPTIMIZER_TRACE表(后续扩展研究)

这是5.6新增的表,用于记录最近的几次查询计划数,比起5.5,这其中记录的信息更加具体,不过也复杂很多,不是很好读懂,官方提供的文档在此:

http://dev.mysql.com/doc/internals/en/optimizer-tracing.html

表结构如下:

Query 查询的SQL
TRACE 查询计划路径,格式为json
MISSING_BYTES_BEYOND_MAX_MEM_SIZE 由于超出optimizer_trace_max_mem_size限制,导致的截断字节数
INSUFFICIENT_PRIVILEGES 某些情况下,用户执行的SQL引用了SQL SECURITY DEFINER试图或者存储过程,可能在某些对象上没有权限,将trace置为空,并将该列设置为1

 

每个线程单独保存各自的查询路径数据,从这个表中也只能获得各自的数据。

 

默认情况下,这个特性是关闭的,我们可以通过如下打开:

SET optimizer_trace=”enabled=on”;

 

optimizer_trace有两个字段:

“enabled=on,one_line=off” ,可以通过set 进行字符串更新,前者表示打开optimizer_trace,后者表示打印的查询计划是否以一行显示,还是以json树的形式显示

 

我们可以在session级别来设这这个参数。

 

默认optimizer_trace_limit值为1,因此只会保存一条记录。这个设置需要重连session才能生效,另外一个变量optimizer_trace_offset通常与之配合使用,默认值为-1

例如,offset=-1, limit=1将显示最近一次trace

offset=-2,limit=1将显示最近的前一个trace。

offset=-5,limit=5 将最近的5次trace打印出来

 

总的来说:

当offset大于0时,则会显示老的从offset开始的limit个trace,也就是说,新的trace没有记下来。

当offset小于0时,则会显示最新的-offset开始的limit个trace,也就是说,只显示新的trace

 

注意重设变量会导致trace被清空

另外由于trace数据是存储在内存中的,因此还需要设置optimizer_trace_max_mem_size来限制内存的使用量,否则意外的设置可能导致内存爆掉。这是session级别,不应该设置的过大

 

optimizer_trace_limit和optimizer_trace_offset也影响占用内存大小,但不应该超过OPTIMIZER_TRACE_MAX_MEM_SIZE

 

另外,还有个参数optimizer_trace_features,可以控制打印到查询计划树的项,暂不展开描述

 

从optimizer_trace表打印的信息来看,即使是一条简单的select语句,也会打印出非常庞大的树形结构,通过set @@end_markers_in_json=ON可以使其更便于阅读。

 

官方示例

 

 

针对OPTIMIZER_TRACE的开销,DimitriK大神有做过测试,链接如下:

http://dimitrik.free.fr/blog/archives/2012/01/mysql-performance-overhead-of-optimizer-tracing-in-mysql-56.html

根据其测试,当打开optimizer trace时,约有不到10%的性能下降,打开innodb_stats_persistent时,几乎没有退化(未去证实)

 

3.以JSON模式输出explain

执行格式为 EXPLAIN FORMAT=JSON [query]

 

示例1示例2

 

4.更多的information_schema表

 

INNODB_METRICS表包含了很多跟Innodb相关的计数器,包含相当多可以用于诊断的信息,目前约有207个计数器(MySQL5.6.9)

通过选项innodb_monitor_enable、innodb_monitor_disable、innodb_monitor_reset来调整每个计数器,例如想开启某个计数器,就执行

set global innodb_monitor_enable = “dml_%”  //可以用匹配符来做计数器名

也可以直接用”%”来代替所有的计数器

set global innodb_monitor_reset_all = ‘%’;

 

INNODB_SYS_%包含了Innodb数据词典等信息,例如表,外键,列等。。。

mysql> show tables like ‘INNODB_SYS_%’;

+———————————————+

| Tables_in_information_schema (INNODB_SYS_%) |

+———————————————+

| INNODB_SYS_DATAFILES |

| INNODB_SYS_TABLESTATS |

| INNODB_SYS_INDEXES |

| INNODB_SYS_TABLES |

| INNODB_SYS_FIELDS |

| INNODB_SYS_TABLESPACES |

| INNODB_SYS_FOREIGN_COLS |

| INNODB_SYS_COLUMNS |

| INNODB_SYS_FOREIGN |

+———————————————+

 

 

INNODB_BUFFER_POOL_STATS包含了每个buffer pool实例的信息。

 

另外还有其他,例如更多的压缩表信息展示。。。。

 

 

5.将所有的死锁信息全写入错误日志中

控制选项:innodb_print_all_deadlocks

 

 

6.物化Innodb表的统计信息

控制选项:innodb_stats_persistent

当开启该选项后,就会将表的统计信息记录到ibdata中,只有手动执行ANALYZE TABLE才会对其进行更新。

 

 

7. Innodb只读事务(需要跟进)

官方博客介绍

默认开启事务是READ WRITE,可以在开启事务时指定: START TRANSACTION READ ONLY;

如果以autocommit运行select,则视其为READ ONLY

根据官方描述,只读事务减少了创建read view的开销,因为这是个全局锁竞争的热点。

后面再深入研究其具体实现。

 

 

8.支持buffer pool数据转储,这可以减少重启预热bP的时间,Percona5.5就已经支持了,不过用的很少

有两个参数来控制这个行为:

 

转储文件名为ib_buffer_pool, 转储的文件中只记录了space id和page no,这些信息从 innodb_buffer_page_lru中获得

 

设置参数innodb_buffer_pool_dump_now 为ON ,可以立刻开始一次转储

还有一个参数innodb_buffer_pool_dump_at_shutdown 用于控制在shutdown时转储

 

相应的也有两个参数来控制将转储文件中记录的page读入bp

innodb_buffer_pool_load_now

innodb_buffer_pool_load_at_startup

可以通过innodb_buffer_pool_filename来指定转储和导入的文件名,默认文件名为ib_buffer_pool

也可以通过参数 innodb_buffer_pool_load_abort来中断load page的过程

 

Innodb_buffer_pool_dump_status上次转储时间点

Innodb_buffer_pool_load_status上次导入Page的时间点

 

 

9.  多线程复制(包括其他一些复制的改进)

没什么好说的,众望所归

 

 

10.在备库上延迟更新,这可以避免在有误操作时的补救措施

 

11.row模式复制时,不记录全部数据前镜像(减少网络传输)

通过参数binlog_row_image来控制,这还是比较有用的,因为就算现在5.5及之前的版本,如果存在主键时,也只用到前镜像的主键值,前镜像其他列的值并不做判断。

设置为full,跟之前版本行为相同

设置为minimal,只在前镜像记录那些可以标记一条记录的列,例如主键值;只记录后镜像中修改过的列

设置为noblob,在没有blob/text类型列时,行为和all相同,当blob列不作为标示列或被修改的列时,就不在binlog中记录。

 

 

12.GET DIAGNOSTICS  语句

 

 

13.更好的处理错误或warning信息

 

和存储过程中获取错误/警告信息有关。

具体查阅 here and here

 

 

Performance Schema也引入了巨大的改进,例如这篇博客,作者根据Performance Schema进行性能瓶颈挖掘,一步步定位到问题

以下为Performance Schema的一些新特性

14.可以观察某些特定表的IO操作;

15,可以观察某些特定SQL事件(events_statements_*

16,events_stages_*表

17,可以对PS信息进行聚合,例如根据用户名,host等,由于PS也存储了历史信息,可以聚合这些信息做性能分析

18,新的host_cache表,cache的域名被保存在内存中,这样就无需查询DNS服务器,之前版本这些信息对用户是不可见的。

 

其他还有许多相关的Performance Schema表信息,详细见官方文档 

 

原创文章,转载请注明: 转载自Simple Life

本文链接地址: [MySQL学习] MySQL 5.6 improvement for troubleshooting

文章的脚注信息由WordPress的wp-posturl插件自动生成

Leave a Comment

Current month ye@r day *