I've found that a large production DB experiences a severe degradation in performance, after a restart. It makes sense that this is because after a restart, the innoDB buffer pool isn't yet warmed up - so a high proportion of requests require a disk access.
I want to observe and measure warm-up time in my (lab) DB, and I've been trying, using
sysbench oltp_read_write, with
This table size yields a database of about 50G. I have 128G memory on the machine I'm using, and innodb configured with
innodb_buffer_pool_size = 80G. So the entire table should eventually be handled within the buffer pool after 'warm-up' is completed. I'm using MySQL v5.7
I also have configured
innodb_buffer_pool_dump_pct = 1 so that I can easily reproduce an un-warmed-up buffer pool, simply by stopping & restarting mysqld.
I run a series of short tests, observing after each test:
Innodb_buffer_pool_bytes_dataas a measure of the degree of 'warmed-up-ness' of the buffer pool.
- d buffer pool hit rate - calculated as as
b / (b + c)
- I expect that
Innodb_buffer_pool_bytes_datashould increase after each test, eventually levelling off as it approaches 50GB. It does. This shows that the buffer pool is indeed warming up.
- I expect that the buffer pool hit rate would also increase after each test, approximately in proportion with the value of 'a'. But this isn't what I see, and I can't understand why not. What I see instead is that after 60s of testing, the Pool seems to be loaded with about 25% of the content of the DB, but 92% of requests are already being handled by the buffer pool.
At first, I thought that maybe sysbench isn't generating a truly random set of requests. This can be varied by
--rand-type=uniform instead of the default which is
--rand-type=special. I tried using 'gaussian' or 'uniform' [not really random at all]. The results with 'gaussian' were much the same as with 'special'. The results with 'uniform' were different in that the entire DB was loaded into buffer pool more quickly, but the same in that the hit rate was already over 90% even when only 25% of the data was loaded into the buffer pool.
So my question is: Why is it that innoDB is satisfying >95% of requests through the buffer pool, without going to disk - even though the reported buffer pool size shows that it only contains 25% of the data in the database?