May 20, 2012

Performance Schema tables stats

May 2, 2011 By 2 Comments

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 

mysql> select * from table_io_waits_summary_by_table where OBJECT_NAME='sbtest55' G
*************************** 1. row ***************************
     OBJECT_TYPE: TABLE
   OBJECT_SCHEMA: sbtest
     OBJECT_NAME: sbtest55
      COUNT_STAR: 1806932
  SUM_TIMER_WAIT: 23771749557590
  MIN_TIMER_WAIT: 83266
  AVG_TIMER_WAIT: 13155501
  MAX_TIMER_WAIT: 84087466520
      COUNT_READ: 1789764
  SUM_TIMER_READ: 17671510276755
  MIN_TIMER_READ: 83266
  AVG_TIMER_READ: 9873345
  MAX_TIMER_READ: 84087466520
     COUNT_WRITE: 17168
 SUM_TIMER_WRITE: 6100239280835
 MIN_TIMER_WRITE: 7587746
 AVG_TIMER_WRITE: 355326061
 MAX_TIMER_WRITE: 78153671549
     COUNT_FETCH: 1789764
 SUM_TIMER_FETCH: 17671510276755
 MIN_TIMER_FETCH: 83266
 AVG_TIMER_FETCH: 9873345
 MAX_TIMER_FETCH: 84087466520
    COUNT_INSERT: 4292
SUM_TIMER_INSERT: 1343751174852
MIN_TIMER_INSERT: 31913539
AVG_TIMER_INSERT: 313082268
MAX_TIMER_INSERT: 57121140547
    COUNT_UPDATE: 8584
SUM_TIMER_UPDATE: 3453842535354
MIN_TIMER_UPDATE: 7587746
AVG_TIMER_UPDATE: 402357649
MAX_TIMER_UPDATE: 78153671549
    COUNT_DELETE: 4292
SUM_TIMER_DELETE: 1302645570629
MIN_TIMER_DELETE: 18127219
AVG_TIMER_DELETE: 303505097
MAX_TIMER_DELETE: 62494969560

Or using this data we can TOP 5 accessed tables via

mysql> select OBJECT_NAME,COUNT_STAR from table_io_waits_summary_by_table order by COUNT_STAR DESC LIMIT 5;
+-------------+------------+
| OBJECT_NAME | COUNT_STAR |
+-------------+------------+
| sbtest97    |    1854084 |
| sbtest66    |    1837665 |
| sbtest56    |    1834297 |
| sbtest44    |    1829666 |
| sbtest47    |    1825035 |
+-------------+------------+
5 rows in set (0.01 sec)

2. There is table with statistic per index: 

mysql> select * from table_io_waits_summary_by_index_usage where OBJECT_NAME='sbtest55'G
*************************** 1. row ***************************
     OBJECT_TYPE: TABLE
   OBJECT_SCHEMA: sbtest
     OBJECT_NAME: sbtest55
      INDEX_NAME: PRIMARY
      COUNT_STAR: 1789764
  SUM_TIMER_WAIT: 17671510276755
  MIN_TIMER_WAIT: 83266
  AVG_TIMER_WAIT: 9873345
  MAX_TIMER_WAIT: 84087466520
      COUNT_READ: 1789764
  SUM_TIMER_READ: 17671510276755
  MIN_TIMER_READ: 83266
  AVG_TIMER_READ: 9873345
  MAX_TIMER_READ: 84087466520
     COUNT_WRITE: 0
 SUM_TIMER_WRITE: 0
 MIN_TIMER_WRITE: 0
 AVG_TIMER_WRITE: 0
 MAX_TIMER_WRITE: 0
     COUNT_FETCH: 1789764
 SUM_TIMER_FETCH: 17671510276755
 MIN_TIMER_FETCH: 83266
 AVG_TIMER_FETCH: 9873345
 MAX_TIMER_FETCH: 84087466520
    COUNT_INSERT: 0
SUM_TIMER_INSERT: 0
MIN_TIMER_INSERT: 0
AVG_TIMER_INSERT: 0
MAX_TIMER_INSERT: 0
    COUNT_UPDATE: 0
SUM_TIMER_UPDATE: 0
MIN_TIMER_UPDATE: 0
AVG_TIMER_UPDATE: 0
MAX_TIMER_UPDATE: 0
    COUNT_DELETE: 0
SUM_TIMER_DELETE: 0
MIN_TIMER_DELETE: 0
AVG_TIMER_DELETE: 0
MAX_TIMER_DELETE: 0
*************************** 3. row ***************************
     OBJECT_TYPE: TABLE
   OBJECT_SCHEMA: sbtest
     OBJECT_NAME: sbtest55
      INDEX_NAME: NULL
      COUNT_STAR: 17168
  SUM_TIMER_WAIT: 6100239280835
  MIN_TIMER_WAIT: 7587746
  AVG_TIMER_WAIT: 355326061
  MAX_TIMER_WAIT: 78153671549
      COUNT_READ: 0
  SUM_TIMER_READ: 0
  MIN_TIMER_READ: 0
  AVG_TIMER_READ: 0
  MAX_TIMER_READ: 0
     COUNT_WRITE: 17168
 SUM_TIMER_WRITE: 6100239280835
 MIN_TIMER_WRITE: 7587746
 AVG_TIMER_WRITE: 355326061
 MAX_TIMER_WRITE: 78153671549
     COUNT_FETCH: 0
 SUM_TIMER_FETCH: 0
 MIN_TIMER_FETCH: 0
 AVG_TIMER_FETCH: 0
 MAX_TIMER_FETCH: 0
    COUNT_INSERT: 4292
SUM_TIMER_INSERT: 1343751174852
MIN_TIMER_INSERT: 31913539
AVG_TIMER_INSERT: 313082268
MAX_TIMER_INSERT: 57121140547
    COUNT_UPDATE: 8584
SUM_TIMER_UPDATE: 3453842535354
MIN_TIMER_UPDATE: 7587746
AVG_TIMER_UPDATE: 402357649
MAX_TIMER_UPDATE: 78153671549
    COUNT_DELETE: 4292
SUM_TIMER_DELETE: 1302645570629
MIN_TIMER_DELETE: 18127219
AVG_TIMER_DELETE: 303505097
MAX_TIMER_DELETE: 62494969560
3 rows in set (0.03 sec)

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:

select  * from file_summary_by_instance where FILE_NAME like '%sbtest55%' limit 5G
*************************** 1. row ***************************
                FILE_NAME: /data/tachion/sb/sbtest/sbtest55.ibd
               EVENT_NAME: wait/io/file/innodb/innodb_data_file
               COUNT_READ: 22071
              COUNT_WRITE: 19916
 SUM_NUMBER_OF_BYTES_READ: 361611264
SUM_NUMBER_OF_BYTES_WRITE: 326303744

or we can get top tables that required read IO 

mysql> select  * from file_summary_by_instance order by COUNT_READ desc limit 6G
*************************** 1. row ***************************
                FILE_NAME: /data/tachion/sb/ibdata1
               EVENT_NAME: wait/io/file/innodb/innodb_data_file
               COUNT_READ: 118218
              COUNT_WRITE: 849692
 SUM_NUMBER_OF_BYTES_READ: 1936883712
SUM_NUMBER_OF_BYTES_WRITE: 103557693440
*************************** 2. row ***************************
                FILE_NAME: /data/tachion/sb/sbtest/sbtest95.ibd
               EVENT_NAME: wait/io/file/innodb/innodb_data_file
               COUNT_READ: 22947
              COUNT_WRITE: 20646
 SUM_NUMBER_OF_BYTES_READ: 375963648
SUM_NUMBER_OF_BYTES_WRITE: 338264064
*************************** 3. row ***************************
                FILE_NAME: /data/tachion/sb/sbtest/sbtest53.ibd
               EVENT_NAME: wait/io/file/innodb/innodb_data_file
               COUNT_READ: 22617
              COUNT_WRITE: 20282
 SUM_NUMBER_OF_BYTES_READ: 370556928
SUM_NUMBER_OF_BYTES_WRITE: 332300288
*************************** 4. row ***************************
                FILE_NAME: /data/tachion/sb/sbtest/sbtest92.ibd
               EVENT_NAME: wait/io/file/innodb/innodb_data_file
               COUNT_READ: 22507
              COUNT_WRITE: 19160
 SUM_NUMBER_OF_BYTES_READ: 368754688
SUM_NUMBER_OF_BYTES_WRITE: 313917440
*************************** 5. row ***************************
                FILE_NAME: /data/tachion/sb/sbtest/sbtest35.ibd
               EVENT_NAME: wait/io/file/innodb/innodb_data_file
               COUNT_READ: 22406
              COUNT_WRITE: 20938
 SUM_NUMBER_OF_BYTES_READ: 367099904
SUM_NUMBER_OF_BYTES_WRITE: 343048192
*************************** 6. row ***************************
                FILE_NAME: /data/tachion/sb/sbtest/sbtest97.ibd
               EVENT_NAME: wait/io/file/innodb/innodb_data_file
               COUNT_READ: 22378
              COUNT_WRITE: 20512
 SUM_NUMBER_OF_BYTES_READ: 366641152
SUM_NUMBER_OF_BYTES_WRITE: 336068608
6 rows in set (0.00 sec)

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.

Filed Under: MySQL
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:
    May 2, 2011 at 12:00 am

    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:
    May 2, 2011 at 12:00 am

    Vadim,

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

Speak Your Mind

*