Mythbusters: How to configure InnoDB buffer poll on large MySQL servers Yesterday I wrote about the dangers in using top on systems with 100+ GB of RAM, not to mention future systems with 1+ TB. A related topic is, how should I configure MySQL on such a large system? There is a classic rule of thumb that on a dedicated MySQL server one should allocate 80% of memory to the InnoDB buffer pool. On a 128GB system that is 102.4 GB. This means that I would leave 25.6 GB of RAM "unused". So surely on these large systems, this old piece of advice cannot hold anymore. If the database was previously running on a server that in total had less than that altogether, it seems wrong to leave so much memory just unused. Let's label the old rule of thumb tentatively a "myth" and ask mythbusters to figure out a new MySQL configuration for us...

So the rationale for challenging the old advice is the assumption that the buffer pool is the main variable consumer of memory in MySQL. There are of course other data strutures that will consume memory too, but we are suspecting the amount of memory consumed on top of the buffer pool is more or less constant overhead. For instance, each connection and executing query will consume some amount of memory, but I don't have more connections here than I had on the much smaller server, so they should consume a constant amount of memory. This is the output of a system with innodb_buffer_pool_size set to 100GB: mysql> show engine innodb status\G
*************************** 1. row ***************************
Type: InnoDB Name: Status:
===================================== 110530 8:10:10 INNODB MONITOR OUTPUT =====================================
... ---------------------- BUFFER POOL AND MEMORY ----------------------
Total memory allocated 109890764800; in additional pool allocated 0 Dictionary memory allocated 851714 Buffer pool size 6553596 Free buffers 0 Database pages 5195872 Old database pages 1917929 Modified db pages 1763 ... 109890764800 bytes is 102.3 GB, so InnoDB consumes 2.3 GB more than the buffer pool. And if you remember from the top output yesterday, MySQL itself consumes in total 109-110 GB, so the MySQL layer consumes an additional 6.7 to 7.7 GB. There are a lot of queries with many JOINs (like an 11-way JOIN) so there are many temporary tables created per second, but if I did the math correctly, that would at most only consume 1-2 GB. (I know that the temporary tables are always smaller than tmp_table_size.) So we learn here that actually MySQL and InnoDB do consume several GB on top of what you configure for the InnoDB buffer pool. We still have some 15 GB unused, but as a rough rule of thumb the old advice is still good also on large systems and configuring the buffer pool at 80% of your total available memory is still relatively good advice. Confirmed would be the official Mythbusters judgement here. In fact, the fine manual says: "InnoDB reserves additional memory for buffers and control structures, so that the total allocated space is approximately 10% greater than the specified size." In my observation, this is not true for InnoDB, but MySQL as a whole indeed consumed 9-10 % on top of the buffer pool size. When I get a TB level system it will be interesting to see if the overhead is still the same, it would be 90 GB then.