Mythbusters: How to configure InnoDB buffer pool on large MySQL servers

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.

Jim Parks (not verified)

Thu, 2014-09-11 13:04

Please consider that any buffer pool allocated beyond 1.6 times the size of InnoDB data and indexes combined will be simply wasted. Also, large buffer pools should be spread across multiple buffer pool instances (innodb_buffer_pool_instances).

So, if you have 256 GB of RAM and 30 GB of InnoDB data + indexes, then you should consider setting the following:

innodb_buffer_pool_size=48G
innodb_buffer_pool_instances=48

That would give you 48 1GB individual buffer pool instances, allowing for more concurrent access.

In this case, assigning more that 48 GB of buffer pool will not improve performance, as 48 GB is sufficient to maintain all InnoDB data in RAM all the time without paging between the buffer pool and the main system cache.

This article is a must-read for anyone managing large MySQL servers. The author's methodical approach and real-world data analysis provide valuable insights into optimizing memory allocation for InnoDB buffer pools. The detailed breakdown of memory consumption and the debunking of common myths make this post highly informative and relevant for database administrators.

Add new comment

The content of this field is kept private and will not be shown publicly. Cookie & Privacy Policy
  • No HTML tags allowed.
  • External and mailto links in content links have an icon.
  • Lines and paragraphs break automatically.
  • Web page addresses and email addresses turn into links automatically.
  • Use [fn]...[/fn] (or <fn>...</fn>) to insert automatically numbered footnotes.
  • Each email address will be obfuscated in a human readable fashion or, if JavaScript is enabled, replaced with a spam resistent clickable link. Email addresses will get the default web form unless specified. If replacement text (a persons name) is required a webform is also required. Separate each part with the "|" pipe symbol. Replace spaces in names with "_".
About the bookAbout this siteAcademicAccordAmazonBeginnersBooksBuildBotBusiness modelsbzrCassandraCloudcloud computingclsCommunitycommunityleadershipsummitConsistencycoodiaryCopyrightCreative CommonscssDatabasesdataminingDatastaxDevOpsDistributed ConsensusDrizzleDrupalEconomyelectronEthicsEurovisionFacebookFrosconFunnyGaleraGISgithubGnomeGovernanceHandlerSocketHigh AvailabilityimpressionistimpressjsInkscapeInternetJavaScriptjsonKDEKubuntuLicensingLinuxMaidanMaker cultureMariaDBmarkdownMEAN stackMepSQLMicrosoftMobileMongoDBMontyProgramMusicMySQLMySQL ClusterNerdsNodeNoSQLodbaOpen ContentOpen SourceOpenSQLCampOracleOSConPAMPPatentsPerconaperformancePersonalPhilosophyPHPPiratesPlanetDrupalPoliticsPostgreSQLPresalespresentationsPress releasesProgrammingRed HatReplicationSeveralninesSillySkySQLSolonStartupsSunSybaseSymbiansysbenchtalksTechnicalTechnologyThe making ofTransactionsTungstenTwitterUbuntuvolcanoWeb2.0WikipediaWork from HomexmlYouTube

Search

Recent blog posts

Recent comments