April 17, 2014

Performance Schema tables stats

My previous benchmark on Performance Schema was mainly in memory workload and against single tables.
Now after adding multi-tables support to sysbench, it is interesting to see what statistic we can get from workload that produces some disk IO.

So let’s run sysbench against 100 tables, each 5000000 rows (~1.2G ) and buffer pool 30G.

The scripts and results are on Benchmark Wiki.

If we look on performance overhead it appears rather big in read-only benchmark, and it is well explained in Dimitri’s post, so let’s keep this question aside and wait on further 5.6 releases with fixes.

Now I am going to post some statistics we are able to get from performance schema tables.

1. table_io_waits_summary_by_table

Or using this data we can TOP 5 accessed tables via

2. There is table with statistic per index:

Interesting that UPDATE/DELETE operations are not counted in INDEX_NAME: PRIMARY,
the documentation says: “Inserts are counted against INDEX_NAME = NULL”, but
it does not mention UPDATEs and DELETEs.

3. Beside logical access to tables, we can see physical IO to files:

or we can get top tables that required read IO

Interesting that top tables that required IO are not the same that most accessed.

I am looking to run the same benchmark in coming 5.6 releases when performance overhead is fixed.

About Vadim Tkachenko

Vadim leads Percona's development group, which produces the Percona Server 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:

    Hey Vadim,

    “Interesting that UPDATE/DELETE operations are not counted in INDEX_NAME: PRIMARY,
    the documentation says: “Inserts are counted against INDEX_NAME = NULL”, but
    it does not mention UPDATEs and DELETEs.”

    As I mentioned in my similar post on monitoring table stats (http://www.markleith.co.uk/?p=611) – DELETE and UPDATE were undefined in the worklog, and were being counted against the “null row”. I opened http://bugs.mysql.com/bug.php?id=60905, which has already been resolved. In future releases it should be as you expected.

  2. Peter Zaitsev says:

    Vadim,

    In this case you have both enabled global instrumentation and timing it, what if you disable timing (TIMED=OFF) ?

Speak Your Mind

*