I'm working on an update to my myq_gadgets package for 5.1, particularly the innodb stats tracker. I'm liking the additional stats that are in SHOW STATUS now for Innodb, particularly because I don't need to parse SHOW INNODB STATUS anymore. I'm in love with being able to compare logical to physical I/Os for the buffer pool.
I'm a bit confused, however, as some of my numbers don't quite line up. Here's what I've got so far:
| Innodb Engine Buffer Pool Data Log Lock
Time read ins upd del new read %phy wrte %phy %dirt wait read wrte fsyc wrte fsyc wait time
02/22-10:06 3.8k 0 0 0 0 9.8k 259 15.0 0.1 0.0 0 351 6.6M 0.9 2.6K 0.9 0.7 0.8 0 0
02/22-10:07 5.6k 0 0 0 0 14k 344 21.8 0.1 0.0 0 461 8.7M 1.3 3.9K 1.3 1.0 1.1 0 0
02/22-10:08 5.6k 0 0 0 0 14k 337 21.9 0.1 0.0 0 457 8.6M 1.3 3.9K 1.3 1.0 1.1 0 0
02/22-10:09 5.7k 0 0 0 0 14k 321 22.1 0.1 0.0 0 436 8.2M 1.3 3.9K 1.3 1.0 1.1 0 0
02/22-10:10 5.7k 0 0 0 0 14k 307 22.2 0.1 0.0 0 405 7.5M 1.3 3.9K 1.3 1.0 1.1 0 0
02/22-10:11 2.3k 0 0 0 0 7.1k 882 8.9 0.1 0.0 0 938 16M 1.2 3.2K 1.2 1.0 1.0 0 0
02/22-10:12 1.4k 0 0 0 0 5.5k 1.1k 5.6 0.1 0.0 0 1.1k 18M 1.2 3.7K 1.2 0.9 1.0 0 0(sorry for the line wrapping, widen your browser if you can)
So the %phy (physical) is not actually the percentage (for debugging), but it's the number I would divide into the previous column (the logical) to get the percentage. Let me elaborate.
For buffer pool reads, I use Innodb_buffer_pool_read_requests. For the physical reads, I use Innodb_buffer_pool_reads. I'm assuming these values are in 'pages', and not 'rows', since I'm reading many more pages than I'm actually doing looking at rows (compare to the first column: Innodb_rows_read).
Now, compare the physical reads (using Innodb buffer_pool_reads) to the Data reads. This uses Innodb_data_reads, which I'm assuming is physical read requests to the disk (most likely for a page). They're in the same ballpark, but I'm seeing a lot more Data reads than buffer page reads, can someone help explain that to me?
Similarly, for buffer writes, I'm comparing Innodb_buffer_pool_write_requests (logical) to Innodb_buffer_pool_pages_flushed (physical, I think). Comparing the physical to the data writes (Innodb_data_writes), I'm seeing more data writes than buffer pool writes. Same question.
Also, what's interesting here is that I'm seeing buffer 'writes' but I'm only doing reads on the database. I don't think this is the adaptive index, as I'm not seeing any dirty pages at all.
Regardless, once I understand this, this is a much clearer picture of what's going on inside Innodb. Thanks Heikki!
Trackback URL for this post:
http://mysqlguy.net/trackback/14
