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.
About Vadim Tkachenko
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.
Vadim,
In this case you have both enabled global instrumentation and timing it, what if you disable timing (TIMED=OFF) ?