Performance Schema tables stats

PREVIOUS POST
NEXT POST

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.

PREVIOUS POST
NEXT POST

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

Leave a Reply

Your email address will not be published. Required fields are marked *