1

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 --table-size=20000000.

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:

  • a Innodb_buffer_pool_bytes_data as a measure of the degree of 'warmed-up-ness' of the buffer pool.
  • b Innodb_buffer_pool_read_requests
  • c Innodb_buffer_pool_reads
  • d buffer pool hit rate - calculated as as b / (b + c)

Results:

  • I expect that Innodb_buffer_pool_bytes_data should 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.

excerpt of observed stats, demonstrating the results described above

At first, I thought that maybe sysbench isn't generating a truly random set of requests. This can be varied by --rand-type=gaussian or --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?

  • Interesting question +1 - but why are you testing with only one table? I presume that you have more than this number of tables in production? I'm not that familiar with Sysbench, but is there another cache that you're missing? – Vérace Jun 5 at 14:39
  • Thanks for your reply @Vérace. a) Testing with only one table, because that's sysbench's default (only?) behaviour. I should investigate using more than one. b) Since posting the question, I've made many changes to /etc/my.cnf and this has removed a bottleneck in my system. I'm now seeing the effect of buffer pool warm-up, taking approx 25 minutes to reach full steam [performance now far exceeds what I was seeing previously] I'll update when I figure out which config changes are the relevant ones. I'm still mystified by the values I calculated for 'buffer pool hit rate'. – Joe Mag Fhionnghaile Jun 5 at 15:17
  • These ratios can be deceptive - high/low is not important if the app is performing well! Googling will bring you plenty of pages where they're discussed but it's very difficult to make sense of any of it! Anyway, best of luck with your testing - and p.s. welcome to the forum! :-) Scottish name? – Vérace Jun 5 at 16:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.