EmergencyEMERGENCY? Get 24/7 Help Now!

MySQL performance optimization: Don’t guess! Measure with Percona Cloud Tools

 | January 29, 2014 |  Posted In: MySQL, Percona Cloud Tools

PREVIOUS POST
NEXT POST

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:

metrics-sm

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.

PREVIOUS POST
NEXT POST
Vadim Tkachenko

Vadim 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. Percona Labs designs no-gimmick tests of hardware, filesystems, storage engines, and databases that surpass the standard performance and functionality scenario benchmarks. Vadim’s expertise in LAMP performance and multi-threaded programming help optimize MySQL and InnoDB internals to take full advantage of modern hardware. Oracle Corporation and its predecessors have incorporated Vadim’s source code patches into the mainstream MySQL and InnoDB products. He also co-authored the book High Performance MySQL: Optimization, Backups, and Replication 3rd Edition.

4 Comments

  • 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.

Leave a Reply

 
 

Percona’s widely read Percona Data Performance blog highlights our expertise in enterprise-class software, support, consulting and managed services solutions for both MySQL® and MongoDB® across traditional and cloud-based platforms. The decades of experience represented by our consultants is found daily in numerous and relevant blog posts.

Besides specific database help, the blog also provides notices on upcoming events and webinars.
Want to get weekly updates listing the latest blog posts? Subscribe to our blog now! Submit your email address below and we’ll send you an update every Friday at 1pm ET.

No, thank you. Please do not ask me again.