Search code examples
mysqlinnodb

can some one please explain innodb buffer pool behavior....?

i have my innodb_buffer_pool_size =1G innodb_old_blocks_time = 5000 innodb_old_blocks_pct = 37

initially the innodb engine's status

BUFFER POOL AND MEMORY
Total memory allocated 1098907648; in additional pool allocated 0
Dictionary memory allocated 58256
Buffer pool size   65536
Free buffers       64932
Database pages     603
Old database pages 242
Modified db pages  0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 603, created 0, written 2
6.00 reads/s, 0.00 creates/s, 0.50 writes/s
Buffer pool hit rate 995 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 603, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]

after

select count(*) from tenMillion;


BUFFER POOL AND MEMORY
Total memory allocated 1098907648; in additional pool allocated 0
Dictionary memory allocated 404498
Buffer pool size   65536
Free buffers       873
Database pages     64660
Old database pages 23888
Modified db pages  3
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 64660, created 0, written 20
1085.69 reads/s, 0.00 creates/s, 0.31 writes/s
Buffer pool hit rate 955 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 1081.34/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 64660, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]

is it the correct behavior..? because i am expecting it to occupy only the 37% of database pages as the whole cache should be in old blocks

how would the innodb free buffer be filled initially(after restart of the server) when i have innodb _old_blocks_time as 5000 milli seconds and the table size is more than 37(my innodb_old_blocks_pct) percentage curious to know if only the 37 pct would be filled or since there is no young ones and no need for eviction too so if it would be filled more than 37 pct of my innodb buffer pool?

please explain

Solution

  • Before the days of "old_blocks" and "37%", this would happen:

    1. You would be happily trekking along, with 1GB of stuff cached in your buffer_pool, when your co-worker does an innocent COUNT(*) on a big table of a few gigabytes.
    2. Poof! All your queries now run slower -- because his query zapped all the cached blocks from the buffer_pool. (They would gradually come back.)

    Now,

    1. You are happily trekking ...
    2. The COUNT(*) comes along, but his "table scan" is confined to 37% of the buffer pool.
    3. (If someone happens to touch some of that table, the touched blocks will be promoted from "old", thereby moving to the other 63%.)
    4. Your queries continue to run nicely, because you still have most of what had been cached.
    5. Another COUNT(*) comes along -- well, it will mess around in the 37%, still not interfering (much) with your "normal" queries.

    Note: Buffer pool hit rate 995 / 1000 -- this dropped from 99.5% (extremely good) to 95.5% (still very good).

    Old database pages 23888 (out of 65536) is confirming what I said about being confined to the 37%.

    Here is what was said in the release noted in late 2009 for 5.1.41 and 5.5.0:

    The InnoDB buffer pool is divided into two sublists: A new sublist containing blocks that are heavily used by queries, and an old sublist containing less-used blocks and from which candidates for eviction are taken. In the default operation of the buffer pool, a block when read in is loaded at the midpoint and then moved immediately to the head of the new sublist as soon as an access occurs. In the case of a table scan (such as performed for a mysqldump operation), each block read by the scan ends up moving to the head of the new sublist because multiple rows are accessed from each block. This occurs even for a one-time scan, where the blocks are not otherwise used by other queries. Blocks may also be loaded by the read-ahead background thread and then moved to the head of the new sublist by a single access. These effects can be disadvantageous because they push blocks that are in heavy use by other queries out of the new sublist to the old sublist where they become subject to eviction.

    InnoDB now provides two system variables that enable LRU algorithm tuning:

    innodb_old_blocks_pct

Specifies the approximate percentage of the buffer pool used for the old block sublist. The range of values is 5 to 95. The default

    value is 37 (that is, 3/8 of the pool).

    innodb_old_blocks_time

Specifies how long in milliseconds (ms) a block inserted into the old sublist must stay there after its first access before it can be

    moved to the new sublist. The default value is 0: A block inserted into the old sublist moves immediately to the new sublist the first time it is accessed, no matter how soon after insertion the access occurs. If the value is greater than 0, blocks remain in the old sublist until an access occurs at least that many ms after the first access. For example, a value of 1000 causes blocks to stay in the old sublist for 1 second after the first access before they become eligible to move to the new sublist.

    See The InnoDB Buffer Pool. (Bug #45015)