I have been doing some work on optimizing (minimizing) the size of the 'working set' in
our application (Zarafa). In practice this involved running a load simulation, and then
looking at the distribution of pages in the buffer pool using
information_schema.innodb_buffer_pool_pages_index. This is a rather indirect way of
checking how effectively my buffer pool is being used - the most common 'problem' was that
tables were being accessed so randomly, that whole pages were being read into the buffer
pool for just one (small) record, while a small change of the primary key often made the
data 'clump together' more, making me use say 10x less buffer pool pages for that table,
effectively enlarging my buffer pool space. Optimizations here have decreased disk I/O
activity by a factor of 5 in some cases now (the changes were rather major though).
I was thinking whether we could measure this more directly. One way would be to track the
number of record reads and writes per page, and record that number at the moment that the
page is removed from the buffer pool. So if the page is removed and the read or write
count is 1, then that's bad, if it's more like the average number of records per page for
the table, or higher, then that's good. I guess you could even count the number of unique
records read, but that would be rather intensive I guess.
What do you think ? I currently don't have an idea of the overhead needed to do this, but
it could be a simple on/off switch. The data could be output in another information_schema
index_name | pages read | pages flushed | pages removed | read_count_at_removal |
Another approach would be to add two columns to innodb_buffer_pool_pages_index to expose
the number of record reads/writes for each page in the buffer pool.