Trying to understand the buffer pool usage - actual usage greatly exceeds the total from

Question

I am reviewing the buffer pool size and usage on a setup that was recently moved to a machine with more RAM (new machine has 256 GiB). MySQL server version is 5.7.42.

The total data size in the DB (from the query below) comes back as just over 33 GiB (NB: one of the tables was pruned quite aggressively in recent days, after which an OPTIMIZE was run on that table):

SELECT Sum(data_length + index_length) / Power(2, 30) totsizGB
FROM   information_schema.tables
WHERE  engine = 'InnoDB'
       AND table_schema = 'mydb';

While tinkering, I bumped innodb_buffer_pool_size to 46 GiB, with my thinking being that this ought to be more than big enough to have all data + indices fitting into the buffer pool. However, a day later, I can see that the usage has reached over 99%. This looks like a big clue that I'm missing something...

When I try to see which tables are consuming pages in the buffer pool, using the "InnoDB Buffer Stats by Table" function within MySQL Workbench (v8.0.12), I get a few hundred entries; when I sum all the "Allocated" column I get a grand total of 27 GiB. I can also see that "Rows Cached" for some of the large tables is much less than the total number of rows they contain, so the buffer pool is full without even fitting in all of the data (!?). The query used by MySQL Workbench for the buffer stats by table is just

SELECT *
FROM   sys.`x$innodb_buffer_stats_by_table` 

Not sure what can cause such a dramatic mismatch between the actual usage and the reported usage, so I'd grateful for tips about where to look next.

(Of course, even if the reported buffer stats are not trustworthy, the other massive red flag with regard to my understanding of what's going on is that the buffer pool greatly exceeds the total DB size and yet it's almost 100% full.)

Answer 1

I think you are targeting the wrong metric: "to have all data + indices fitting into the buffer pool" isn't productive. A more useful metric would be the buffer pool hit ratio.

Over-allocating the buffer pool, while not necessarily having positive effect on performance, comes at the expense of other memory consumers: sort and join buffers, communication buffers, in-memory temporary tables etc., which can have far worse performance impact.

---

While calculating the expected data size, you don't seem to take into account temporary tables, as well as other things that can be stored in the buffer pool. The system catalog tables, as well as performance_schema, also occupy space in memory that you don't account for.