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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 |
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
|
1 2 3 4 5 6 7 8 9 10 11 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 |
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:
|
1 2 3 4 5 6 7 8 |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 |
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 the Author
Vadim TkachenkoVadim Tkachenko co-founded Percona in 2006 and serves as its Chief Technology Officer. Vadim leads Percona Labs, which focuses on technology research and performance evaluations of Percona’s and third-party products. Vadim’s expertise in LAMP performance and multi-threaded programming help optimize MySQL and InnoDB internals to take full advantage of modern hardware. He also co-authored the book High Performance MySQL: Optimization, Backups, and Replication 3rd Edition.
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) ?