In our practice we often see that MySQL performance optimization is done in a sort of “black magic” way. A common opinion is: “If there is a performance problem – then it must be a database issue, because what else could it be? And if this is a database issue, then it must involve IO problems because the reason for a slow database is always a slow IO…” Following this logic might actually give a result, but achieving a fully successful resolution would require magic.
At Percona we use a different approach. Performance optimization should not be based on guesses, but exact measurements. In application to databases, I described previously, we use queries as units of work. And a proper measurement of these units is the first important task in performance optimization.
Let me list the metrics of what our Percona Cloud Tools provides:
- Query count – How many times query was executed
- Query_time – Total time that MySQL spent on query execution
- Lock_time – Time spent in waiting on Table level locks
- Rows_sent – How many rows query returned to application
- Rows_examined – How many rows MySQL actually had to read
In my previous post I indicated that Rows_sent/Rows_examined ratio is one of interest for OLTP workloads.
And the following metrics are available only for Percona Server, and not because we crippled our tools, but because MySQL simply does not provide them. It is worth reminding that one of main goals in making Percona Server was to provide diagnostics and transparency.
Percona Server metrics:
- Rows_affected
- Merge_passes
- InnoDB_IO_r_bytes
- InnoDB_IO_r_ops
- InnoDB_IO_r_wait
- InnoDB_pages_distinct
- InnoDB_queue_wait
- InnoDB_rec_lock_wait
- Query_length
- Bytes_sent
- Tmp_tables
- Tmp_disk_tables
- Tmp_table_sizes
Meaning of all these metrics are available from our docs page
Now, often it is not enough to provide a single value for a given metric, so the following stats are available:
Total, Average, Minimum, Median, 95%, Maximum and Stddev.
Add for each of these trending graphs and you will see a matrix on your query:
Having all of these comprehensive metrics about your queries, you can now make an intelligent decision on how to approach performance optimization of your database.
You can do it all by yourself today with the free Beta of Percona Cloud Tools.
I understand that all of this information might be too much too figure out at first, but as with any tool – it takes skills and some experience to analyze and apply metrics properly. To help to bootstrap with Percona Cloud Tools, I will be running a webinar “Analyze MySQL Query Performance with Percona Cloud Tools” on Feb-12, 2014; if you register and install the Percona Cloud Tools agent you may win a free consulting hour from me during which I will examined your queries and provide an advice for optimization. See all conditions there.
About the Author
Vadim TkachenkoVadim Tkachenko co-founded Percona in 2006 and serves as its Chief Technology Officer. Vadim leads Percona Labs, which focuses on technology research and performance evaluations of Percona’s and third-party products.Vadim’s expertise in LAMP performance and multi-threaded programming help optimize MySQL and InnoDB internals to take full advantage of modern hardware.He also co-authored the book High Performance MySQL: Optimization, Backups, and Replication 3rd Edition.
The following of the list you state are not in MySQL:
Rows_affected
Merge_passes
Tmp_tables
Tmp_disk_tables
Tmp_table_sizes
Are all available in 5.6, with Performance Schema statement instrumentation (and more):
mysql> select * from performance_schema.events_statements_summary_by_digest where digest_text like ‘%insert_dupes%’\G
*************************** 1. row ***************************
SCHEMA_NAME: NULL
DIGEST: 960c2553083521e22dc8ab21c1dcfd33
DIGEST_TEXT: INSERT INTO test . insert_dupes ( i , j ) VALUES (…) /* , … */ ON DUPLICATE KEY UPDATE j = VALUES ( j )
COUNT_STAR: 1
SUM_TIMER_WAIT: 521433675
MIN_TIMER_WAIT: 521433675
AVG_TIMER_WAIT: 521433675
MAX_TIMER_WAIT: 521433675
SUM_LOCK_TIME: 0
SUM_ERRORS: 0
SUM_WARNINGS: 0
SUM_ROWS_AFFECTED: 4
SUM_ROWS_SENT: 0
SUM_ROWS_EXAMINED: 0
SUM_CREATED_TMP_DISK_TABLES: 0
SUM_CREATED_TMP_TABLES: 0
SUM_SELECT_FULL_JOIN: 0
SUM_SELECT_FULL_RANGE_JOIN: 0
SUM_SELECT_RANGE: 0
SUM_SELECT_RANGE_CHECK: 0
SUM_SELECT_SCAN: 0
SUM_SORT_MERGE_PASSES: 0
SUM_SORT_RANGE: 0
SUM_SORT_ROWS: 0
SUM_SORT_SCAN: 0
SUM_NO_INDEX_USED: 0
SUM_NO_GOOD_INDEX_USED: 0
FIRST_SEEN: 2014-01-29 15:51:54
LAST_SEEN: 2014-01-29 15:51:54
It’s just not available in the slow query log (inaccurate to say MySQL does not provide them).
Mark,
Vadim talks about what is supported by the tool of course. We will be looking to support Performance Schema data source as well in the future version though there are couple of reasons it has not been high on the roadmap
1) MySQL 5.6 adoption is still not that high and upgrade has not be “drop in” experience for many, yet changing to Percona Server 5.5 which has these feature is easy.
2) The overhead of the performance_schema with digest enabled can be quite high, with little control over limited it. Percona Server supports sampling with slow query log so we can keep overhead very low.
I also wish we would get some sort of top wait information as tied to the statement. The amount of time waiting on IO from Innodb tables is very valuable in practice yet statement digest table does not seems to provide this data.
Curious what you charge for an hour of consultation like that.
Ohh, is Percona Cloud Tools gone already 🙁