mysqlguy.net

Letters to the Editor (1)

I recently got this email from a reader on my site and since I haven't posted for a while, I thought it might be a good discussion: 


Jay,

I've been reading your site and had a question that you might have good insight for.  

I'm working on a high-traffic website that includes forums.  All the code is custom PHP, including the forums, simply because the PHP BB software out there doesn't scale as large and as well as I wanted.  I've implemented heavy memcached usage, distributed databases, etc. to handle any level of growth we may hit.

The scoop on Innodb reads

I spent some time going through the source the other day in order to try to understand the difference between these SHOW STATUS variables in 5.x:


Innodb_buffer_pool_read_requests
Innodb_buffer_pool_reads
Innodb_pages_read

Here's what I discovered that I'm not sure is 100% right, but I suspect is at least on the right track:

Innodb_buffer_pool_read_requests:  requests to get something from the buffer pool.  This isn't really that great of a mystery.  I suspect these are 'direct' read requests, meaning those caused directly from a query of some kind.  

MyQ Gadgets 0.0.9 released

A new version of myq_gadgets has been released and you can download it here.


Changelog since the last released version here:
Version 0.0.9 -
    - Documented new reports in README
    - Updated 'thds' column in the 'cttf' report to read 'crtd' instead.
Version 0.0.8 -
    - Added a bunch more innodb reports, looking for the good stuff.
Version 0.0.7 -
    - Added an 'innodb' mode to myq_status to support 5.0+ STATUS variables.
      'myq_innodb_status' continues to handle SHOW INNODB STATUS output,
      mostly for 4.1 and earlier.
    - Beefed up the format_memory function to use recursion to determine the
      appropriate output multiplier (tera, giga, mega, kilo, etc.)


STATUS variables: the difference between innodb_pages_read and innodb_buffer_pool_reads

I haven't gotten any responses to my question from Friday, so I figured I'd post a more direct question:


The two variables 'innodb_pages_read' and 'innodb_buffer_pool_reads' are different.  'Innodb_pages_read' seems consistently higher than the buffer_pool_reads.  Certainly the buffer pool reads 'pages', but what else would be counted as a page read?


On the flip side, 'innodb_pages_written' and 'innodb_buffer_pool_pages_flushed' seem consistent.  

Anyone have any clues?

It's all about the metrics, but what do they mean?

I'm working on an update to my myq_gadgets package for 5.1, particularly the innodb stats tracker.  I'm liking the additional stats that are in SHOW STATUS now for Innodb, particularly because I don't need to parse SHOW INNODB STATUS anymore.  I'm in love with being able to compare logical to physical I/Os for the buffer pool.  


I'm a bit confused, however, as some of my numbers don't quite line up.  Here's what I've got so far:

            | Innodb Engine       Buffer Pool                        Data                     Log       Lock
Time         read  ins  upd  del  new read %phy wrte %phy %dirt wait read      wrte      fsyc wrte fsyc wait time
02/22-10:06  3.8k    0    0    0    0 9.8k  259 15.0  0.1   0.0    0  351 6.6M  0.9 2.6K  0.9  0.7  0.8    0    0
02/22-10:07  5.6k    0    0    0    0  14k  344 21.8  0.1   0.0    0  461 8.7M  1.3 3.9K  1.3  1.0  1.1    0    0

About Me

Jay Janssen
Yahoo!, Inc.
jayj at yahoo dash inc dot com
MySQL
High Availability
Global Load Balancing
Failover
View Jay Janssen on Twitter  View Jay Janssen's LinkedIn profile View Jay Janssen's Facebook profile