EmergencyEMERGENCY? Get 24/7 Help Now!

How well does your table fit in the InnoDB buffer pool in MySQL 5.6+?

 | December 29, 2014 |  Posted In: InnoDB, Insight for DBAs, MySQL, Percona Server for MySQL

PREVIOUS POST
NEXT POST

Some time ago, Peter Zaitsev posted a blog titled “How well does your table fits in innodb buffer pool?” He used some special INFORMATION_SCHEMA tables developed for Percona Server 5.1 to report how much of each InnoDB table and index resides in your buffer pool.

As Peter pointed out, you can use this view into the buffer pool to watch a buffer pool warm up with pages as you run queries. You can also use it for capacity planning. If you expect some tables need to be fully loaded in the buffer pool to be used efficiently, but the buffer pool isn’t large enough to hold them, then it’s time to increase the size of the buffer pool.

The problem, however, was that system tables change from version to version. Specifically, the INNODB_BUFFER_POOL_PAGES_INDEX table no longer exists in Percona Server 5.6, and the INNODB_INDEX_STATS table changed some column names in Percona Server 5.5.8, and the table no longer exists in Percona Server 5.6. So many of the comments on Peter’s blog rightly pointed out that the example query didn’t work on subsequent versions of Percona Server. And MySQL Community Edition at the time didn’t have the feature at all. They asked for an update to the blog post.

So here’s an updated, simplified query to report the content of your buffer pool, tested on the most recent versions.

Percona Server 5.1 and 5.5:

MySQL 5.6 and 5.7 (this also works on Percona Server 5.6):

In both cases, the output looks something like the following (if I have read from a single table called test.foo):

Unfortunately, the INFORMATION_SCHEMA tables report total size of secondary indexes for a table, but not the size of each index individually. Therefore this query shows the percent of index only for the primary index (which is also the clustered index, i.e. the table itself), and then all other secondary indexes grouped together.

PERFORMANCE_SCHEMA solution

The PERFORMANCE_SCHEMA also includes some information about the contents of the buffer pool. The MySQL SYS Schema makes it easy to query this. But this view doesn’t calculate the percentage of each table in the buffer pool, nor the percentage of the buffer pool occupied by each table.

PREVIOUS POST
NEXT POST
Bill Karwin

Bill Karwin has been a software professional for over 20 years. He's helped thousands of developers with SQL technology. Bill authored the book "SQL Antipatterns," collecting frequent blunders and showing better solutions.

2 Comments

  • Have you checked any differences in resources/time/blocking operations? That would be a great information to decide which option to choose. I found the custom query solution too costly in a busy server with thousands of tables, so I couldn’t risk to compare.

    Great work, anyway!

  • It’s worth noting that the “hard coded” page sizes above mean the percentages well not work correctly with compressed tables (which have differing sizes, even in the buffer pool). The views in the sys schema do account for that though.

    I’ll look in to adding percentages too (depending on performance).

    As Jaime notes, beware on large buffer pools, the I_S tables materialize the whole result set for each individual page pre aggregation, locking the buf_pool mutex and reading 10,000 pages in a loop, which can affect user performance..

Leave a Reply