More changes for performance monitoring

Avatar Category: Database | 321 days ago |
I have pushed more performance monitoring changes to the Facebook patch on Launchpad. This includes:

Support for SHOW INNODB FILE STATUS that reports IO statistics per InnoDB tablespace. When innodb_file_per_table is used, this provides statistics per table.
Counters in SHOW STATUS that report time processing SQL statements including Command_seconds for non-replication SQL, Command_slave_seconds for replication SQL and Innodb_record_in_range_seconds that measures time in InnoDB during query optimization.
Changes to ignore enforcment of innodb_thread_concurrency for the replication SQL thread. This should reduce delay but more remains to be done. When a slave is busy with many more concurrent queries than CPU cores, the replication SQL thread may still get starved from CPU cores.
Counters for flushed neighbor pages in SHOW STATUS. The counters are Innodb_buffer_pool_neighbors_flushed_from_list for neighbor pages flushed from the buffer pool flush list and Innodb_buffer_pool_neighbors_flushed_from_lru for neighbor pages flushed from the buffer pool LRU list.
Block tracing - this generates a trace file that includes the page number for every page accessed by InnoDB. It is meant for analyzing IO performance and is not to be used in production.


Sample output for SHOW INNODB FILE STATUS:

File Operation Requests Old Bytes Bytes/r Svc:secs Svc:msecs/r Svc:max_msecs Wait:secs Wait:msecs/r Wa
it:max_msecs
./ibdata1 write 28 0 15876096 567003.428571 0.042245 1.508750 4 1.926522 68.804357 14
./ib_logfile0 write 1648 0 8901120 5401.165049 0.023237 0.014100 0 0.023482 0.014249 0


I generated a block trace from one of our production servers and ran the output through an LRU simulator to estimate buffer cache miss rates by buffer cache size. I added curves for two functions that estimate the miss rate. A graph of the miss rate as a function of the size of the buffer cache is displayed below. The graph includes:

Measured - this is the result from the LRU simulator for the block trace output
D / (C * 1000) - D is the size of the database file and C is the size of the buffer cache. This turns out to be a good estimate. I am surprised because I plotted this by mistake courtesy of a typo.
(1 - C/D) / 50 - fortunately this is not a good estimate. It is based on the assumption that page accesses are uniformly distributed. If that were true, the cache would not improve performance as expected.

Comments
Any coments? Contact us.