List:Internals« Previous MessageNext Message »
From:MARK CALLAGHAN Date:February 8 2011 4:14pm
Subject:Re: add more innodb bufferpool usage statistics
View as plain text  
We have similar performance problems. The overhead for the counters (8
or 16 extra bytes per page) is not significant given the current
overhead of more than 1100 bytes (see
https://www.facebook.com/notes/mysql-at-facebook/revisiting-the-innodb-memory-overhead/491430345932).
However, there will be extra work for frequently read or written pages
to maintain the counters. I have considered making such a change, but
it might only get enabled in special debug builds.

On Tue, Feb 8, 2011 at 1:49 AM, Steve Hardy <s.hardy@stripped> wrote:
> 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.
>
> --
> MySQL Internals Mailing List
> For list archives: http://lists.mysql.com/internals
> To unsubscribe:    http://lists.mysql.com/internals?unsub=1
>
>



-- 
Mark Callaghan
mdcallag@stripped
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