EmergencyEMERGENCY? Get 24/7 Help Now!

Performance Schema tables stats


Posted on:

|

By:


PREVIOUS POST
NEXT POST
Share Button

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.

Share Button
PREVIOUS POST
NEXT POST


Vadim Tkachenko

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



Categories:
MySQL


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

    Reply

  • Peter Zaitsev

    Vadim,

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

    Reply

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.

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