在开发
MySQL时,我真的很想能够启动一个分析器.我发现
SQLyog是查询分析器的一个很好的替代品,但是没有找到像sql分析器那样工作的工具.
对于没有看过微软SQL Profiler的MysqL用户,这里有截图
在我以前的工作中,我们有一个工具trumped SQL Profiler甚至给了我们堆栈跟踪
有没有人知道任何像我提到的与MysqL一起使用的工具.
(仅供参考,我可以让Altiris Profiler与MysqL合作,但它将涉及运行Windows,而不是真正的赛门铁克sku,因此授权非常棘手)
MysqL从未提出查询性能分析.既然MysqL已经被Oracle摒弃了,我知道情况会继续如此.
但是,所有希望都不会丢失.
自2007年以来,Percona为Developer和DBA想要的所有东西提供了一些绝对出色的工具,包括Query Profiling.
Percona的第一套工具,即MAATKIT,为严肃的MysqL用户创造了一个领域. It features many things,如：
>查询分析
>复制心跳
>复制从属管理
>表校验和和同步
Percona最近将MAATKIT分解为更新的工具集,known today as Percona Toolkit.这些工具通过扩展严肃的MysqL用户的活动领域而在MAATKIT停止的地方获得了包括以下内容：
>外键错误检查
>在线模式更改
>视觉解释计划
>等等……
回到原始问题,用于查询分析的工具是
> pt-query-advisor
> pt-query-digest
> mk-query-profiler(适用于MAATKIT用户)
> mk-query-digest(适用于MAATKIT用户)
以下是使用以下工具之一可以获得的丰富信息的示例：
我帮助客户实现mk-query-digest每20分钟报告20个性能最差的查询. I got the idea from this YouTube video.客户端会将任何错误查询的输出移动到memcached,从而降低了查询对数据库造成损失的可能性.
这是我调用mk-query-digest的脚本(仅检查进程列表)
#!/bin/sh
RUNFILE=/tmp/QueriesAreBeingDigested.txt
if [ -f ${RUNFILE} ] ; then exit ; fi
MKDQ=/usr/local/sbin/mk-query-digest
RUNTIME=${1}
copIES_TO_KEEP=${2}
DBVIP=${3}
WHICH=/usr/bin/which
DATE=`${WHICH} date`
ECHO=`${WHICH} echo`
HEAD=`${WHICH} head`
TAIL=`${WHICH} tail`
AWK=`${WHICH} awk`
SED=`${WHICH} sed`
CAT=`${WHICH} cat`
WC=`${WHICH} wc`
RM=`${WHICH} rm | ${TAIL} -1 | ${AWK} '{print $1}'`
LS=`${WHICH} ls | ${TAIL} -1 | ${AWK} '{print $1}'`
HAS_THE_DBVIP=`/sbin/ip addr show | grep "scope global secondary" | grep -c "${DBVIP}"`
if [ ${HAS_THE_DBVIP} -eq 1 ] ; then exit ; fi
DT=`${DATE} +"%Y%m%d_%H%M%s"`
UNIQUETAG=`${ECHO} ${SSH_CLIENT}_${SSH_CONNECTION}_${DT} | ${SED} 's/\./ /g' | ${SED} 's/ //g'`
cd /root/QueryDigest
OUTFILE=QP_${DT}.txt
HOSTADDR=${DBVIP}
${MKDQ} --processlist h=${HOSTADDR},u=queryprofiler,p=queryprofiler --run-time=${RUNTIME} > ${OUTFILE}
#
# Rotate out Old copies
#
QPFILES=QPFiles.txt
QPFILES2ZAP=QPFiles2Zap.txt
${LS} QP_[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]_[0-9][0-9][0-9][0-9][0-9][0-9].txt > ${QPFILES}
LINECOUNT=`${WC} -l < ${QPFILES}`
if [ ${LINECOUNT} -gt ${copIES_TO_KEEP} ]
then
(( DIFF = LINECOUNT - copIES_TO_KEEP ))
${HEAD} -${DIFF} < ${QPFILES} > ${QPFILES2ZAP}
for QPFILetoZAP in `${CAT} ${QPFILES2ZAP}`
do
${RM} ${QPFILetoZAP}
done
fi
rm -f ${QPFILES2ZAP}
rm -f ${QPFILES}
rm -f ${RUNFILE}
这是我使用mk-query-digest连接到MysqL的用户
GRANT PROCESS ON *.* TO 'queryprofiler'@'%' IDENTIFIED BY 'queryprofiler';
这是我每隔20分钟(不到10秒)运行的crontab,保留最后144份(这是48小时的分析)
*/20 * * * * /root/QueryDigest/ExecQueryDigest.sh 1190s 144 10.1.1.8
令人难以置信的部分：mk-query-digest的输出
这是2011-12-28 11:20:00运行1190秒(20分钟少于10秒)的个人资料
最后22行
# Rank Query ID Response time Calls R/Call Item # ==== ================== ================ ======= ========== ==== # 1 0x5E994008E9543B29 40.3255 11.2% 101 0.399263 SELECT schedule_occurrence schedule_eventschedule schedule_event schedule_eventtype schedule_event schedule_eventtype schedule_occurrence.start # 2 0x392F6DA628C7FEBD 33.9181 9.4% 17 1.995184 SELECT mt_entry mt_objecttag # 3 0x6C6318E56E149036 26.4695 7.3% 102 0.259505 SELECT schedule_occurrence schedule_eventschedule schedule_event schedule_eventtype schedule_event schedule_eventtype schedule_occurrence.start # 4 0x00F66961DAE6FFB2 25.5472 7.1% 55 0.464495 SELECT mt_entry mt_placement mt_category # 5 0x99E13015BFF1E75E 22.3618 6.2% 199 0.112371 SELECT mt_entry mt_objecttag # 6 0x84DD09F0FC444677 22.3516 6.2% 39 0.573118 SELECT mt_entry # 7 0x440EBDBCEDB88725 21.1817 5.9% 36 0.588380 SELECT mt_entry # 8 0x8D258C584B858811 17.2402 4.8% 37 0.465951 SELECT mt_entry mt_placement mt_category # 9 0x4E2CB0F4CAFD1400 16.9768 4.7% 40 0.424419 SELECT mt_entry mt_placement mt_category # 10 0x377E0D0898266FDD 16.6979 4.6% 150 0.111319 SELECT polls_pollquestion mt_category # 11 0x3B9686D98BB8E054 16.2089 4.5% 32 0.506529 SELECT mt_entry mt_objecttag mt_tag # 12 0x97F670B604A85608 15.6158 4.3% 34 0.459287 SELECT mt_entry mt_placement mt_category # 13 0x3F5557DA231225EB 14.4309 4.0% 36 0.400859 SELECT mt_entry mt_placement mt_category # 14 0x191D660A10738896 13.1220 3.6% 31 0.423290 SELECT mt_entry mt_placement mt_category # 15 0xF88F7421DD88036D 12.1261 3.4% 61 0.198788 SELECT mt_entry mt_blog mt_objecttag mt_tag mt_author # 16 0xA909BF76E7051792 10.3971 2.9% 53 0.196172 SELECT mt_entry mt_objecttag mt_tag # 17 0x3D42D07A335ED983 9.1424 2.5% 20 0.457121 SELECT mt_entry mt_placement mt_category # 18 0x59F43B57DD43F2BD 9.0533 2.5% 21 0.431111 SELECT mt_entry mt_placement mt_category # 19 0x7961BD4C76277EB7 8.5564 2.4% 47 0.182052 INSERT UNION UPDATE UNION mt_session # 20 0x173EB4903F3B6DAC 8.5394 2.4% 22 0.388153 SELECT mt_entry mt_placement mt_category
请注意,这是基于查询响应时间除以调用查询的次数的20个性能最差的查询的列表.
查看查询ID#1,即0x5E994008E9543B29,我们在输出文件中找到该查询ID,这里是该特定查询的报告：
# Query 1: 0.09 QPS,0.03x concurrency,ID 0x5E994008E9543B29 at byte 0 __ # This item is included in the report because it matches --limit. # pct total min max avg 95% stddev median # Count 4 101 # Exec time 7 40s 303ms 1s 399ms 992ms 198ms 293ms # Lock time 0 0 0 0 0 0 0 0 # Users 1 mt # Hosts 101 10.64.95.73:33750 (1),10.64.95.73:34452 (1),10.64.95.73:38440 (1)... 97 more # Databases 1 mt1 # Time range 1325089201 to 1325090385 # bytes 0 273.60k 2.71k 2.71k 2.71k 2.62k 0 2.62k # id 4 765.11M 7.57M 7.58M 7.58M 7.29M 0.12 7.29M # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms # 100ms ################################################################ # 1s ###### # 10s+ # Tables # SHOW TABLE STATUS FROM `mt1` LIKE 'schedule_occurrence'\G # SHOW CREATE TABLE `mt1`.`schedule_occurrence`\G # SHOW TABLE STATUS FROM `mt1` LIKE 'schedule_eventschedule'\G # SHOW CREATE TABLE `mt1`.`schedule_eventschedule`\G # SHOW TABLE STATUS FROM `mt1` LIKE 'schedule_event'\G # SHOW CREATE TABLE `mt1`.`schedule_event`\G # SHOW TABLE STATUS FROM `mt1` LIKE 'schedule_eventtype'\G # SHOW CREATE TABLE `mt1`.`schedule_eventtype`\G # SHOW TABLE STATUS FROM `schedule_occurrence` LIKE 'start'\G # SHOW CREATE TABLE `schedule_occurrence`.`start`\G # EXPLAIN SELECT `schedule_occurrence`.`id`,`schedule_occurrence`.`schedule_id`,`schedule_occurrence`.`event_id`,`schedule_occurrence`.`start`,`schedule_occurrence`.`end`,`schedule_occurrence`.`cancelled`,`schedule_occurrence`.`original_start`,`schedule_occurrence`.`original_end`,`schedule_occurrence`.`all_day`,`schedule_occurrence`.`ongoing`,`schedule_occurrence`.`featured`,`schedule_eventschedule`.`id`,`schedule_eventschedule`.`event_id`,`schedule_eventschedule`.`start`,`schedule_eventschedule`.`end`,`schedule_eventschedule`.`all_day`,`schedule_eventschedule`.`ongoing`,`schedule_eventschedule`.`min_date_calculated`,`schedule_eventschedule`.`max_date_calculated`,`schedule_eventschedule`.`rule`,`schedule_eventschedule`.`end_recurring_period`,`schedule_eventschedule`.`textual_description`,`schedule_event`.`id`,`schedule_event`.`title`,`schedule_event`.`slug`,`schedule_event`.`description`,`schedule_event`.`host_id`,`schedule_event`.`cost`,`schedule_event`.`age_restrictions`,`schedule_event`.`more_info`,`schedule_event`.`photo_id`,`schedule_event`.`contact_email`,`schedule_event`.`event_type_id`,`schedule_event`.`featured`,`schedule_event`.`staff_pick`,`schedule_event`.`futuremost`,`schedule_event`.`creator_id`,`schedule_event`.`created_on`,`schedule_event`.`allow_comments`,`schedule_event`.`mt_entry`,`schedule_eventtype`.`id`,`schedule_eventtype`.`parent_id`,`schedule_eventtype`.`name`,`schedule_eventtype`.`slug`,`schedule_eventtype`.`lft`,`schedule_eventtype`.`rght`,`schedule_eventtype`.`tree_id`,`schedule_eventtype`.`level`,T5.`id`,T5.`title`,T5.`slug`,T5.`description`,T5.`host_id`,T5.`cost`,T5.`age_restrictions`,T5.`more_info`,T5.`photo_id`,T5.`contact_email`,T5.`event_type_id`,T5.`featured`,T5.`staff_pick`,T5.`futuremost`,T5.`creator_id`,T5.`created_on`,T5.`allow_comments`,T5.`mt_entry`,T6.`id`,T6.`parent_id`,T6.`name`,T6.`slug`,T6.`lft`,T6.`rght`,T6.`tree_id`,T6.`level` FROM `schedule_occurrence` INNER JOIN `schedule_eventschedule` ON (`schedule_occurrence`.`schedule_id` = `schedule_eventschedule`.`id`) INNER JOIN `schedule_event` ON (`schedule_eventschedule`.`event_id` = `schedule_event`.`id`) INNER JOIN `schedule_eventtype` ON (`schedule_event`.`event_type_id` = `schedule_eventtype`.`id`) INNER JOIN `schedule_event` T5 ON (`schedule_occurrence`.`event_id` = T5.`id`) INNER JOIN `schedule_eventtype` T6 ON (T5.`event_type_id` = T6.`id`) WHERE (EXTRACT(MONTH FROM `schedule_occurrence`.`start`) = 8 AND EXTRACT(DAY FROM `schedule_occurrence`.`start`) = 6 AND `schedule_occurrence`.`start` BETWEEN '2011-01-01 00:00:00' and '2011-12-31 23:59:59.99') ORDER BY `schedule_occurrence`.`ongoing` ASC,`schedule_occurrence`.`all_day` DESC,`schedule_occurrence`.`start` ASC\G
尽管直方图是基于文本的,但它可以准确显示查询的整体性能,有时会超过1秒,大部分时间在0.01到0.1秒之间.从这里开始,可以通过重构查询,将查询结果放入memcached,添加缺失或覆盖索引等来进行性能调整.
结论
恕我直言如果Percona曾将探查器工具放入Windows GUI,它很容易与微软的sql Server Profiler相媲美.
防守休息!!!