What tools exist to profile MySQL, like how MSSQL 2000+ does with the SQL Profiler?
I'd want to trace things like SQL statements executed, execution times, execution plan, etc.
Add a comment
|
9 Answers
Look into enabling the Query Log and the Slow Query Log.
answered May 2, 2009 at 15:15
if you have query logging turned on on your production/test environment [ which is not necessary the case ] you can use mk-query-digest from maatkit toolkit. it'll help you to determine which queries are most frequent/longest taking etc.
Another commercial option is MySQL Query Analyzer which is part of the MySQL Enterprise Monitor. I've found it to be moderately useful in helping to profile oddball queries to figure out ways to improve their performance.
You can also check out MySQLTuner
Here is an good article about the profiler of MySQL. Although take a look at the explain statement.
I've used several scripts and other tools which are all great, but i found Jet Profiler really good at giving real time monitoring and visualization of what's going on and how things are changing. The full version costs money but the restricted free version is also useful and gives you a good feel for what the full one can do.
I use this little script. It's always been useful to me, although not anything official.
See: https://sites.google.com/site/basicsqlmanagment/ Works for me, is not a proxy profiler
-
1Welcome to Server Fault! Generally we like answers on the site to be able to stand on their own - Links are great, but if that link ever breaks the answer should have enough information to still be helpful. Please consider editing your answer to include more detail. See the FAQ for more info.– slmJul 16, 2013 at 15:01
I highly recommend the following
- pt-query-digest
- mk-query-digest (outdated but still works)
From the old MAATKIT Documentation
Column Meaning
============ ==========================================================
Rank The query's rank within the entire set of queries analyzed
Query ID The query's fingerprint
Response time The total response time, and percentage of overall total
Calls The number of times this query was executed
R/Call The mean response time per execution
Apdx The Apdex score; see --apdex-threshold for details
V/M The Variance-to-mean ratio of response time
EXPLAIN If --explain was specified, a sparkline; see --explain
Item The distilled query
In the DBA StackExchange I answered MySQL general query log performance effects. In my old post, I suggested using mk-query-digest instead of the general log or slow log. From that post, here is sample output of the query profiling done by mk-query-digest:
# Rank Query ID Response time Calls R/Call Item
# ==== ================== ================ ======= ========== ====
# 1 0x812D15015AD29D33 336.3867 68.5% 910 0.369656 SELECT mt_entry mt_placement mt_category
# 2 0x99E13015BFF1E75E 25.3594 5.2% 210 0.120759 SELECT mt_entry mt_objecttag
# 3 0x5E994008E9543B29 16.1608 3.3% 46 0.351321 SELECT schedule_occurrence schedule_eventschedule schedule_event schedule_eventtype schedule_event schedule_eventtype schedule_occurrence.start
# 4 0x84DD09F0FC444677 13.3070 2.7% 23 0.578567 SELECT mt_entry
# 5 0x377E0D0898266FDD 12.0870 2.5% 116 0.104199 SELECT polls_pollquestion mt_category
# 6 0x440EBDBCEDB88725 11.5159 2.3% 21 0.548376 SELECT mt_entry
# 7 0x1DC2DFD6B658021F 10.3653 2.1% 54 0.191949 SELECT mt_entry mt_placement mt_category
# 8 0x6C6318E56E149036 8.8294 1.8% 44 0.200667 SELECT schedule_occurrence schedule_eventschedule schedule_event schedule_eventtype schedule_event schedule_eventtype schedule_occurrence.start
# 9 0x392F6DA628C7FEBD 8.5243 1.7% 9 0.947143 SELECT mt_entry mt_objecttag
# 10 0x7DD2B294CFF96961 7.3753 1.5% 70 0.105362 SELECT polls_pollresponse
# 11 0x9B9092194D3910E6 5.8124 1.2% 57 0.101973 SELECT content_specialitem content_basecontentitem advertising_product organizations_neworg content_basecontentitem_item_attributes
# 12 0xA909BF76E7051792 5.6005 1.1% 55 0.101828 SELECT mt_entry mt_objecttag mt_tag
# 13 0xEBE07AC48DB8923E 5.5195 1.1% 54 0.102213 SELECT rssfeeds_contentfeeditem
# 14 0x3E52CF0261A7C3FF 4.4676 0.9% 44 0.101536 SELECT schedule_occurrence schedule_occurrence.start
# 15 0x9D0BCD3F6731195B 4.2804 0.9% 41 0.104401 SELECT mt_entry mt_placement mt_category
# 16 0x7961BD4C76277EB7 4.0143 0.8% 18 0.223014 INSERT UNION UPDATE UNION mt_session
# 17 0xD2F486BA41E7A623 3.1448 0.6% 21 0.149754 SELECT mt_entry mt_placement mt_category mt_objecttag mt_tag
# 18 0x3B9686D98BB8E054 2.9577 0.6% 11 0.268885 SELECT mt_entry mt_objecttag mt_tag
# 19 0xBB2443BF48638319 2.7239 0.6% 9 0.302660 SELECT rssfeeds_contentfeeditem
# 20 0x3D533D57D8B466CC 2.4209 0.5% 15 0.161391 SELECT mt_entry mt_placement mt_category
Above this output are histograms of these 20 top worst-performing queries
Example of the first entry's histogram
# Query 1: 0.77 QPS, 0.28x concurrency, ID 0x812D15015AD29D33 at byte 0 __
# This item is included in the report because it matches --limit.
# pct total min max avg 95% stddev median
# Count 36 910
# Exec time 58 336s 101ms 2s 370ms 992ms 230ms 393ms
# Lock time 0 0 0 0 0 0 0 0
# Users 1 mt
# Hosts 905 10.64.95.74:54707 (2), 10.64.95.74:56133 (2), 10.64.95.80:33862 (2)... 901 more
# Databases 1 mt1
# Time range 1321642802 to 1321643988
# bytes 1 1.11M 1.22k 1.41k 1.25k 1.26k 25.66 1.20k
# id 36 9.87G 11.10M 11.11M 11.11M 10.76M 0.12 10.76M
# Query_time distribution
# 1us
# 10us
# 100us
# 1ms
# 10ms
# 100ms ################################################################
# 1s ###
# 10s+
# Tables
# SHOW TABLE STATUS FROM `mt1` LIKE 'mt_entry'\G
# SHOW CREATE TABLE `mt1`.`mt_entry`\G
# SHOW TABLE STATUS FROM `mt1` LIKE 'mt_placement'\G
# SHOW CREATE TABLE `mt1`.`mt_placement`\G
# SHOW TABLE STATUS FROM `mt1` LIKE 'mt_category'\G
# SHOW CREATE TABLE `mt1`.`mt_category`\G
# EXPLAIN
SELECT `mt_entry`.`entry_id`, `mt_entry`.`entry_allow_comments`, `mt_entry`.`entry_allow_pings`, `mt_entry`.`entry_atom_id`, `mt_entry`.`entry_author_id`, `mt_entry`.`entry_authored_on`, `mt_entry`.`entry_basename`, `mt_entry`.`entry_blog_id`, `mt_entry`.`entry_category_id`, `mt_entry`.`entry_class`, `mt_entry`.`entry_comment_count`, `mt_entry`.`entry_convert_breaks`, `mt_entry`.`entry_created_by`, `mt_entry`.`entry_created_on`, `mt_entry`.`entry_excerpt`, `mt_entry`.`entry_keywords`, `mt_entry`.`entry_modified_by`, `mt_entry`.`entry_modified_on`, `mt_entry`.`entry_ping_count`, `mt_entry`.`entry_pinged_urls`, `mt_entry`.`entry_status`, `mt_entry`.`entry_tangent_cache`, `mt_entry`.`entry_template_id`, `mt_entry`.`entry_text`, `mt_entry`.`entry_text_more`, `mt_entry`.`entry_title`, `mt_entry`.`entry_to_ping_urls`, `mt_entry`.`entry_week_number` FROM `mt_entry` INNER JOIN `mt_placement` ON (`mt_entry`.`entry_id` = `mt_placement`.`placement_entry_id`) INNER JOIN `mt_category` ON (`mt_placement`.`placement_category_id` = `mt_category`.`category_id`) WHERE (`mt_entry`.`entry_status` = 2 AND `mt_category`.`category_basename` IN ('business_review' /*... omitted 3 items ...*/ ) AND NOT (`mt_entry`.`entry_id` IN (53441))) ORDER BY `mt_entry`.`entry_authored_on` DESC LIMIT 4\G
answered Jul 16, 2013 at 17:17
