File IO statistics
./test/warehouse.ibd 10 -- read: 4 requests, 4 pages, 0.00 secs, 0.72 msecs/r, write: 3 requests, 3 pages, 0.00 secs, 1.43 msecs/r
./ibdata1 0 -- read: 30 requests, 203 pages, 0.03 secs, 0.99 msecs/r, write: 124 requests, 3020 pages, 0.74 secs, 5.93 msecs/r
./test/orders.ibd 29 -- read: 8490 requests, 10033 pages, 8.48 secs, 1.00 msecs/r, write: 6754 requests, 12728 pages, 34.27 secs, 5
.07 msecs/r
./test/customer.ibd 28 -- read: 33901 requests, 34226 pages, 32.05 secs, 0.95 msecs/r, write: 11224 requests, 11850 pages, 43.17 se
cs, 3.85 msecs/r
./test/stock.ibd 27 -- read: 151957 requests, 176913 pages, 256.89 secs, 1.69 msecs/r, write: 41475 requests, 52199 pages, 220.43 s
ecs, 5.31 msecs/r
./test/order_line.ibd 25 -- read: 14239 requests, 14876 pages, 13.10 secs, 0.92 msecs/r, write: 11610 requests, 38413 pages, 45.01
secs, 3.88 msecs/r
./test/new_orders.ibd 22 -- read: 2023 requests, 2316 pages, 1.80 secs, 0.89 msecs/r, write: 1213 requests, 7004 pages, 7.58 secs,
6.25 msecs/r
./test/history.ibd 21 -- read: 5740 requests, 7711 pages, 5.64 secs, 0.98 msecs/r, write: 4938 requests, 22754 pages, 27.97 secs, 5
.66 msecs/r
./test/district.ibd 18 -- read: 15 requests, 15 pages, 0.01 secs, 0.78 msecs/r, write: 8 requests, 31 pages, 0.02 secs, 3.02 msecs/
r
./test/item.ibd 16 -- read: 757 requests, 904 pages, 0.67 secs, 0.89 msecs/r, write: 0 requests, 0 pages, 0.00 secs, 0.00 msecs/r
./ib_logfile0 4294967280 -- read: 6 requests, 9 pages, 0.00 secs, 0.02 msecs/r, write: 25630 requests, 25877 pages, 0.56 secs, 0.02
msecs/r
Friday, May 22, 2009
A good reason to use inodb_file_per_table -- per-table IO statistics
I added support for per-tablespace IO statistics to InnoDB. This also provides per-table IO statistics when you innodb_file_per_table is used. The stats are listed in SHOW INNODB STATUS and the text below is output when tpcc-mysql is run -- pardon the formatting. The code should appear at code.google.com real soon now.
Subscribe to:
Post Comments (Atom)
Posts
Cool!
ReplyDeleteThat did not take very long to get in:)
Thanks, Mark. I look forward to this getting into other builds as well (hi, Percona).
ReplyDeleteJeremy
I think Percona did something to measure IO perf stats in Innodb, but I never looked at it. Perhaps this is redundant. They should clarify. I wanted to measure stats per table, but by the time the requests reach the IO layer, they are identified by filename and tablespace and my brief search of the code wasn't able to find an easy way to map things back to the table name. Does anyone know how to do that?
ReplyDeleteThis is great. :)
ReplyDeleteShame it's not in an I_S table though - SHOW INNODB STATUS is overloaded already..
Percona did "INFORMATION_SCHEMA.INNODB_IO_PATTERN", which is somewhat the same - it just loads the table/index name from the data dictionary (which requires the tables to be loaded in to it at that point), i.e:
+ if (dict_sys != NULL) {
+ dulint id;
+ id.high = 0;
+ id.low = io_counter->index_id;
+ index = dict_index_find_on_id_low(id);
+ } else {
+ index = NULL;
+ }
+
+ table->field[0]->store(io_counter->space);
+ table->field[1]->store(io_counter->offset);
+ table->field[2]->store(io_counter->index_id);
+ if (index != NULL) {
+ table->field[3]->store(index->table_name,strlen(index->table_name),system_charset_info);
+ table->field[4]->store(index->name,strlen(index->name),system_charset_info);
+ } else {
+ table->field[3]->store("",0,system_charset_info);
+ table->field[4]->store("",0,system_charset_info);
+ }
http://www.percona.com/mysql/5.0.77-b13/patches/innodb_io_pattern.patch
PERFORMANCE_SCHEMA should do this too, as and when InnoDB gets instrumented..
We don't use the IS (yet). This data would be much more useful there.
ReplyDelete