mysql性能监控工具
参考文档:
http://www.linuxidc.com/Linux/2012-09/70459.htm
1.记录慢查询SQL
#配置开启 (linux)修改my.cnf: log-slow-queries=/var/log/mysql/slowquery.log (指定日志文件存放位置,可以为空,系统会给一个缺省的文件host_name-slow.log) long_query_time=2 (记录超过的时间,默认为10s) log-queries-not-using-indexes (log下来没有使用索引的query,可以根据情况决定是否开启) log-long-format (如果设置了,所有没有使用索引的查询也将被记录) (windows)修改my.ini: log-slow-queries=D:\mysql\log\mysqlslowquery.log long_query_time=2 ... (其他参数如上) #查看方式 使用mysql自带命令mysqldumpslow查看,常用命令: -s ORDER what to sort by (t, at, l, al, r, aretc), 'at’ is default -t NUM just show the top n queries -g PATTERN grep: only consider stmts that includethis string eg: s,是order的顺序,说明写的不够详细,俺用下来,包括看了代码,主要有 c,t,l,r和ac,at,al,ar,分别是按照query次数,时间,lock的时间和返回的记录数来排序,前面加了a的时倒序 -t,是top n的意思,即为返回前面多少条的数据 -g,后边可以写一个正则匹配模式,大小写不敏感的 mysqldumpslow -s c -t 20 host-slow.log mysqldumpslow -s r -t 20 host-slow.log 上述命令可以看出访问次数最多的20个sql语句和返回记录集最多的20个sql。 mysqldumpslow -t 10 -s t -g “left join” host-slow.log这个是按照时间返回前10条里面含有左连接的sql语句。
2.explain (sql执行计划解释命令)
EXPAIN [SQL]
3.profile
mysql> select @@profiling; +-------------+ | @@profiling | +-------------+ | 0 | +-------------+ 1 row in set mysql> SET profiling = 1; Query OK, 0 rows affected mysql> select @@profiling; +-------------+ | @@profiling | +-------------+ | 1 | +-------------+ 1 row in set mysql> SELECT * FROM t_im_tokens WHERE profileid IN (SELECT profileid FROM t_enduser_customer_service); +-----------+----------------------------------+ | profileid | token | +-----------+----------------------------------+ | 10000152 | a2898efe07380a813efa53e0fd4b3697 | | 10000153 | b37122b662a6a6f7ad0a16e1a03f7f01 | | 10000190 | 7235940f4438dbceeaf288540ba68f19 | | 10000302 | 7ccb852b19562fc296f82f95f8e0d9ef | | 10000363 | a672510b73a34a3d1d6227a53086b248 | | 10000387 | 209a5085acbcd3969a98668d62fbd1a3 | | 10000550 | 52324116187ce5b558b8a082762b657b | | 10000556 | d6b61c83675b373f5b7887c088e6806e | +-----------+----------------------------------+ 8 rows in set mysql> show profiles; +----------+------------+-------------------------------------------------------------------------------------------------+ | Query_ID | Duration | Query | +----------+------------+-------------------------------------------------------------------------------------------------+ | 1 | 0.00018075 | select @@profiling | | 2 | 0.000613 | SELECT * FROM t_im_tokens WHERE profileid IN (SELECT profileid FROM t_enduser_customer_service) | +----------+------------+-------------------------------------------------------------------------------------------------+ 2 rows in set mysql> show profile for query 2; +----------------------+----------+ | Status | Duration | +----------------------+----------+ | starting | 6.3E-5 | | checking permissions | 5E-6 | | checking permissions | 4E-6 | | Opening tables | 2E-5 | | init | 2.1E-5 | | System lock | 7E-6 | | optimizing | 2E-5 | | statistics | 0.000236 | | preparing | 1.7E-5 | | executing | 3E-6 | | Sending data | 0.000129 | | end | 5E-6 | | removing tmp table | 8E-6 | | end | 3E-6 | | query end | 5E-6 | | closing tables | 8E-6 | | freeing items | 3.7E-5 | | cleaning up | 2.5E-5 | +----------------------+----------+ 18 rows in set mysql>