From: MARK CALLAGHAN Date: February 8 2011 4:14pm Subject: Re: add more innodb bufferpool usage statistics List-Archive: http://lists.mysql.com/internals/38244 Message-Id: MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable 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-memo= ry-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 wrote: > Hi guys > > I have been doing some work on optimizing (minimizing) the size of the 'w= orking set' in our application (Zarafa). In practice this involved running = a load simulation, and then looking at the distribution of pages in the buf= fer pool using information_schema.innodb_buffer_pool_pages_index. This is a= rather indirect way of checking how effectively my buffer pool is being us= ed - the most common 'problem' was that tables were being accessed so rando= mly, that whole pages were being read into the buffer pool for just one (sm= all) record, while a small change of the primary key often made the data 'c= lump 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 tha= t 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 hig= her, then that's good. =A0I 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 outp= ut in another information_schema table, eg: > > index_name | pages read | pages flushed | pages removed | read_count_at_r= emoval | 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 buff= er pool. > > -- > MySQL Internals Mailing List > For list archives: http://lists.mysql.com/internals > To unsubscribe: =A0 =A0http://lists.mysql.com/internals?unsub=3Dmdcallag@= gmail.com > > --=20 Mark Callaghan mdcallag@stripped