List:Internals« Previous MessageNext Message »
From:Steve Hardy Date:February 8 2011 9:49am
Subject:add more innodb bufferpool usage statistics
View as plain text  
Hi guys

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 table, eg:

index_name | pages read | pages flushed | pages removed | read_count_at_removal |
write_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.
Thread
add more innodb bufferpool usage statisticsSteve Hardy8 Feb
  • Re: add more innodb bufferpool usage statisticsMARK CALLAGHAN8 Feb
Re: add more innodb bufferpool usage statisticsJimmy Yang8 Feb
  • Re: add more innodb bufferpool usage statisticsKristian Nielsen8 Feb
    • Re: add more innodb bufferpool usage statisticsMARK CALLAGHAN11 Feb