[MySQL FAQ]系列 — profiling中要关注哪些信息

利用MySQL的PROFILE功能,我们可以很方便的查看一个SQL具体的执行代价是怎样的,尤其是可以分析它的最大瓶颈在哪里。目前PROFILE功能可提供除了内存以外的其他资源消耗统计,例如CPU、I/O、CONTEXT、SWAP等。

PROFILE功能只能在SESSION级别使用,还做不到像SQL Server那样可以全局开启,收集一段时间后再关闭,这点有待改进。关于PROFILE的具体用法大家可以查看手册 13.7.5.31 SHOW PROFILE Syntax,这里不细说。

大部分情况下,PROFILE的结果我们主要关注两列:StatusDuration,前者表示的是PROFILE里的状态,它和PROCESSLIST的状态基本是一致的,后者是该状态的耗时。因此,我们最主要的是关注处于哪个状态耗时最久,这些状态中,哪些可以进一步优化。

和我们之前的一个分享 [MySQL FAQ]系列 — processlist中哪些状态要引起关注 类似,PROFILE中,下面几种状态是要尤其关注的,而且大多数通过创建合适的索引就可以完成优化。

 

Status 建议
System lock 确认是由于哪个锁引起的,通常是因为MySQL或InnoDB内核级的锁引起的建议:如果耗时较大再关注即可,一般情况下都还好
Sending data 从server端发送数据到客户端,也有可能是接收存储引擎层返回的数据,再发送给客户端,数据量很大时尤其经常能看见
备注:Sending Data不是网络发送,是从硬盘读取,发送到网络是Writing to net
建议:通过索引或加上LIMIT,减少需要扫描并且发送给客户端的数据量
Sorting result 正在对结果进行排序,类似Creating sort index,不过是正常表,而不是在内存表中进行排序
建议:创建适当的索引
Table lock 表级锁,没什么好说的,要么是因为MyISAM引擎表级锁,要么是其他情况显式锁表
create sort index 当前的SELECT中需要用到临时表在进行ORDER BY排序
建议:创建适当的索引
checking query cache for querychecking privileges on cachedsending cached result to clien

storing result in query cache

和query cache相关的状态,已经多次强烈建议关闭

更多状态请移步之前的分享文章 [MySQL FAQ]系列 — processlist中哪些状态要引起关注 以及官方文档 8.14.2 General Thread States,如果有未涉及想了解的状态,也请在评论区给我留言,谢谢。

 

MySQL 5.7版本新特性连载(一)

本文将和大家一起分享下5.7的新特性,不过我们要先从即将被删除的特性以及建议不再使用的特性说起。根据这些情况,我们在新版本及以后的版本中,应该不再使用,避免未来产生兼容性问题。

本文是基于MySQL-5.7.7-rc版本,未来可能 还会发生更多变化。

1、即将删除的特性
1.1、InnoDB monitoring features,详见:WL#7377(访问地址:http://dev.mysql.com/worklog/task/?id=7377,下面的其他WL,可以自行替换)
【建议】可以动态修改 innodb_status_output、innodb_status_output_locks 两个参数的值打印相关信息,或者直接查看INFORMATION_SCHEMA下的相关表。

1.2、old-password,4.1之前的就密码认证模式已经禁用,old_passwords参数不可用,WL#8006
【建议】尽快升级旧密码串,同时升级MySQL版本,不要告诉我,你还在用4.1甚至更早的版本。

1.3、部分SQL语法不可用
1.3.1、ALTER TABLE … IGNORE。
1.3.2、INSERT DELAY特性,但保留这个语法。WL#6073
1.3.3、ERROR_FOR_DIVISION_BY_ZERO, NO_ZERO_DATE, NO_ZERO_IN_DATE SQL MODES 等几个SQL MODE合并到STRICT中。不过可能会导致replication失败,所以还在考虑中。WL#7467
1.3.4、不再支持YEAR(2),建议尽快升级成YEAR(4)。WL#6263
【建议】尽可能使用标准SQL语法,不再使用MySQL特有的,或者不是那么严格要求的语法,避免以后版本升级遇到更多麻烦。

1.4、一些参数不可用
1.4.1、不再支持一些指令的简短写法,必须要求写全了,例如mysqldump –compr表示 mysqldump –compress,以后必须将整个参数写完整。WL#6978
1.4.2、删除timed_mutexes。WL#7436
1.4.3、不能再禁用InnoDB引擎,因为系统表也都改成InnoDB了。WL#7976
1.4.4、性能提升有限,删除innodb_use_sys_malloc、innodb_additional_mem_pool_size。WL#7628
1.4.5、意义不大,删除innodb_mirrored_log_groups。WL#6808
1.4.6、已经有新的系统参数代替了,删除innodb_file_io_threads。WL#7149
1.4.7、删除系统参数storage_engine,改用default_storage_engine。WL#7148
1.4.8、删除mysql_upgrade中的–basedir和–datadir系统参数。WL#7010

1.5、一些客户端工具
mysqlaccess、mysql_convert_table_format、mysql_fix_extensions、mysql_find_rows.sh、mysql_setpermission、msql2mysql、mysqlbug、mysql_zap and mysql_waitpid、mysqlhotcopy将不再使用。
【建议】没什么好说的,顺应潮流跟上新版本吧,该放弃的就放弃,不要抱残守缺了,这些工具也基本上都用不上的。

下一期,我们讲讲5.7中不再建议使用的特性, 也就是未来可能会被删除的特性。

延伸阅读:

整理的比较仓促,若有遗漏或失误,请留言回复,谢谢!

关于MySQL的方方面面大家想了解什么,可以直接留言回复,我会从中选择一些热门话题进行分享。 同时希望大家多多转发,多一些阅读量是老叶继续努力分享的绝佳助力,谢谢大家 :)

最后打个广告,运维圈人士专属铁观音茶叶微店上线了,访问:http://yuhongli.com 获得专属优惠

[MySQL FAQ]系列 — EXPLAIN结果中哪些信息要引起关注

MySQL FAQ
插图来自网络并作简单加工,如果觉得不当还请及时告知 :)

我们使用EXPLAIN解析SQL执行计划时,如果有下面几种情况,就需要特别关注下了:

首先看下 type 这列的结果,如果有类型是 ALL 时,表示预计会进行全表扫描(full table scan)。通常全表扫描的代价是比较大的,建议创建适当的索引,通过索引检索避免全表扫描。此外,全索引扫描(full index scan)的代价有时候是比全表扫描还要高的,除非是基于InnoDB表的主键索引扫描。

再来看下 Extra 列的结果,如果有出现 Using temporary 或者 Using filesort 则要多加关注:

Using temporary,表示需要创建临时表以满足需求,通常是因为GROUP BY的列没有索引,或者GROUP BY和ORDER BY的列不一样,也需要创建临时表,建议添加适当的索引。

Using filesort,表示无法利用索引完成排序,也有可能是因为多表连接时,排序字段不是驱动表中的字段,因此也没办法利用索引完成排序,建议添加适当的索引。

Using where,通常是因为全表扫描或全索引扫描时(type 列显示为 ALLindex),又加上了WHERE条件,建议添加适当的索引。

暂时想到上面几个,如果有遗漏,以后再补充。

其他状态例如:Using index、Using index condition、Using index for group-by 则都还好,不用紧张。

更多详情请看官方文档:8.8.2 EXPLAIN Output Format

[MySQL FAQ]系列 — processlist中哪些状态要引起关注

MySQL FAQ
插图来自网络并作简单加工,如果觉得不当还请及时告知 :)

一般而言,我们在processlist结果中如果经常能看到某些SQL的话,至少可以说明这些SQL的频率很高,通常需要对这些SQL进行进一步优化。

今天我们要说的是,在processlist中,看到哪些运行状态时要引起关注,主要有下面几个:

状态 建议
copy to tmp table 执行ALTER TABLE修改表结构时建议:放在凌晨执行或者采用类似pt-osc工具
Copying to tmp table 拷贝数据到内存中的临时表,常见于GROUP BY操作时建议:创建适当的索引
Copying to tmp table on disk 临时结果集太大,内存中放不下,需要将内存中的临时表拷贝到磁盘上,形成 #sql***.MYD、#sql***.MYI(在5.6及更高的版本,临时表可以改成InnoDB引擎了,可以参考选项default_tmp_storage_engine建议:创建适当的索引,并且适当加大sort_buffer_size/tmp_table_size/max_heap_table_size
Creating sort index 当前的SELECT中需要用到临时表在进行ORDER BY排序建议:创建适当的索引
Creating tmp table 创建基于内存或磁盘的临时表,当从内存转成磁盘的临时表时,状态会变成:Copying to tmp table on disk建议:创建适当的索引,或者少用UNION、视图(VIEW)、子查询(SUBQUERY)之类的,确实需要用到临时表的时候,可以在session级临时适当调大 tmp_table_size/max_heap_table_size 的值
Reading from net 表示server端正通过网络读取客户端发送过来的请求建议:减小客户端发送数据包大小,提高网络带宽/质量
Sending data 从server端发送数据到客户端,也有可能是接收存储引擎层返回的数据,再发送给客户端,数据量很大时尤其经常能看见备注:Sending Data不是网络发送,是从硬盘读取,发送到网络是Writing to net

建议:通过索引或加上LIMIT,减少需要扫描并且发送给客户端的数据量

Sorting result 正在对结果进行排序,类似Creating sort index,不过是正常表,而不是在内存表中进行排序建议:创建适当的索引
statistics 进行数据统计以便解析执行计划,如果状态比较经常出现,有可能是磁盘IO性能很差建议:查看当前io性能状态,例如iowait
Waiting for global read lock FLUSH TABLES WITH READ LOCK整等待全局读锁建议:不要对线上业务数据库加上全局读锁,通常是备份引起,可以放在业务低谷期间执行或者放在slave服务器上执行备份
Waiting for tables,Waiting for table flush FLUSH TABLES, ALTER TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE, OPTIMIZE TABLE等需要刷新表结构并重新打开建议:不要对线上业务数据库执行这些操作,可以放在业务低谷期间执行
Waiting for lock_type lock 等待各种类型的锁:• Waiting for event metadata lock• Waiting for global read lock• Waiting for schema metadata lock• Waiting for stored function metadata lock• Waiting for stored procedure metadata lock• Waiting for table level lock• Waiting for table metadata lock• Waiting for trigger metadata lock建议:比较常见的是上面提到的global read lock以及table metadata lock,建议不要对线上业务数据库执行这些操作,可以放在业务低谷期间执行。如果是table level lock,通常是因为还在使用MyISAM引擎表,赶紧转投InnoDB引擎吧,别再老顽固了

更多详情可参考官方手册:8.14.2 General Thread States