September 20, 2014

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

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.

About Vadim Tkachenko

Vadim leads Percona's development group, which produces Percona Clould Tools, the Percona Server, Percona XraDB Cluster and Percona XtraBackup. He is an expert in solid-state storage, and has helped many hardware and software providers succeed in the MySQL market.

Comments

  1. Mark Leith says:

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

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

Speak Your Mind

*