List:Internals« Previous MessageNext Message »
From:Jimmy Yang Date:February 8 2011 4:37pm
Subject:Re: add more innodb bufferpool usage statistics
View as plain text  
Steve & Mark, we were discussing this in our InnoDB internal list as well

Counters can be added to buf_page_t recording each buffer page latching activity 
so as to record the page R/W access, and reset in each buffer flush. The 
result can be displayed through information schema innodb_buffer_pages
and innodb_buffer_stats. As mentioned by Mark earlier, since this could
be performance sensitive, it will be enabled only in special debug builds.


----- mdcallag@stripped wrote:

> From: mdcallag@stripped
> To: s.hardy@stripped, domas.mituzas@stripped
> Cc: internals@stripped
> Sent: Tuesday, February 8, 2011 8:15:11 AM GMT -08:00 US/Canada Pacific
> Subject: Re: add more innodb bufferpool usage statistics
> 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
> 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:
> > To unsubscribe:  
> >
> >
> -- 
> Mark Callaghan
> mdcallag@stripped
> --
> MySQL Internals Mailing List
> For list archives:
> To unsubscribe:   
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