1

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.)

1
  • Additional DB information request, please. OS, Version? # cores, any SSD or NVME devices on MySQL Host server? Post TEXT data on justpaste.it and share the links. From your SSH login root, Text results of: A) SELECT COUNT(*) FROM information_schema.tables; B) SHOW GLOBAL STATUS; after minimum 24 hours UPTIME C) SHOW GLOBAL VARIABLES; D) SHOW FULL PROCESSLIST; E) STATUS; not SHOW STATUS, just STATUS; G) SHOW ENGINE INNODB STATUS; for server workload tuning analysis to provide suggestions. Jan 23 at 1:18

1 Answer 1

2

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.

1
  • Another thing in the buffer_pool is the "change buffer", where INDEX updates are staged.
    – Rick James
    Jan 24 at 1:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.