It seems these days if anyone knows anything about tuning InnoDB, it’s that you MUST tune your innodb_buffer_pool_size to 80% of your physical memory. This is such prolific tuning advice, it seems ingrained in many a DBA’s minds. The MySQL manual to this day refers to this rule, so who can blame the DBA? The question is: does it makes sense?
What uses the memory on your server?
Before we question such advice, let’s consider what can take up RAM in a typical MySQL server in their broad categories. This list isn’t necessarily complete, but I think it outlines the large areas a MySQL server could consume memory.
- OS Usage: Kernel, running processes, filesystem cache, etc.
- MySQL fixed usage: query cache, InnoDB buffer pool size, mysqld rss, etc.
- MySQL workload based usage: connections, per-query buffers (join buffer, sort buffer, etc.)
- MySQL replication usage: binary log cache, replication connections, Galera gcache and cert index, etc.
- Any other services on the same server: Web server, caching server, cronjobs, etc.
There’s no question that for tuning InnoDB, the innodb_buffer_pool_size is the most important variable. It’s expected to occupy most of the RAM on a dedicated MySQL/Innodb server, but of course, other local services may affect how it is tuned. If it (and other memory consumption on the server) is too large, swapping can kick in and degrade your performance rapidly.
Further, the workload of the MySQL server itself may cause a lot of variation. Does the server have a lot of open connections and active query workload consuming memory? The memory consumption caused by this can be dramatically different server to server.
Finally, replication mechanisms like Galera have their own memory usage pattern and can require some adjustments to your buffer pool.
We can see clearly that the 80% rule isn’t as nuanced as reality.
A rule of thumb
However, for the sake of argument, let’s say the 80% rule is a starting point. A rule of thumb to help us get a quick tuning number to get the server running. Assuming we don’t know anything really about the workload on the system yet, but we know that the system is dedicated to InnoDB, how might our 80% rule play out?
| Total Server RAM | Buffer pool with 80% rule | Remaining RAM |
|---|---|---|
| 1G | 800MB | 200MB |
| 16G | 13G | 3G |
| 32G | 26G | 6G |
| 64G | 51G | 13G |
| 128G | 102G | 26G |
| 256G | 205G | 51G |
| 512G | 409G | 103G |
| 1024G | 819G | 205G |
At lower numbers, our 80% rule looks pretty reasonable. However, as we get into large servers, it starts to seem less sane. For the rule to hold true, it must mean that workload memory consumption increases in proportion to the needed size of the buffer pool, but that usually isn’t the case. Our server that has 1TB of RAM likely doesn’t need 205G of that to handle things like connections and queries (likely MySQL couldn’t handle that many active connections and queries anyway).
So, if you really just spent all that money on a beefy server do you really want to pay a 20% tax on that resource because of this rule of thumb?
The origins of the rule
At one of my first MySQL conferences, probably around 2006-2007 when I worked at Yahoo, I attended an InnoDB tuning talk hosted by Heikki Tuuri (the original author of InnoDB) and Peter Zaitsev. I distinctly remember asking about the 80% rule because at the time Yahoo had some beefy 64G servers and the rule wasn’t sitting right with me.
Heikki’s answer stuck with me. He said something to the effect of (not a direct quote): “Well, the server I was testing on had 1GB of RAM and 80% seemed about right”. He then, if memory serves, clarified it and said it would not apply similarly to larger servers.
How should you tune innodb_buffer_pool_size?
80% is maybe a great start and rule of thumb. You do want to be sure the server has plenty of free RAM for the OS and the usually unknown workload. However, as we can see above, the larger the server, the more likely the rule will wind up wasting RAM. I think for most people it starts and ends at the rule of thumb, mostly because changing the InnoDB buffer pool requires a restart in current releases.
So what’s a better rule of thumb? My rule is that you tune the innodb_buffer_pool_size as large as possible without using swap when the system is running the production workload. This sounds good in principle, but again, it requires a bunch of restarts and may be easier said than done.
Fortunately MySQL 5.7 and it’s online buffer pool resize feature should make this an easier principle to follow. Seeing lots of free RAM (and/or filesystem cache usage)? Turn the buffer pool up dynamically. Seeing some swap activity? Just turn it down with no restart required. In practice, I suspect there will be some performance related hiccups of using this feature, but it is at least a big step in the right direction.
More resources:
Posts
- MySQL server memory usage troubleshooting tips
- Ten MySQL performance tuning settings after installation
Webinars
Presentations
Free eBooks
- Practical MySQL Performance Tuning: section 1 – understanding query parameter tuning and MySQL optimization
- Practical MySQL Performance Tuning: section 2 – troubleshooting performance issues and optimizing MySQL
- Practical MySQL Performance Tuning: section 3 – query optimization
We are using mostly 128GB servers running CentOS (different flavors) dedicated for MySQL (Percona 5.5). We have innodb_buffer_pool_size set to 104GB on those and they also have 8GB ram disks for /tmp. They are heavily utilized, but never used any swap. Of course vm.swappiness is set to 1.
Here’s what we use on lower tier servers (128GB included for brevity), again they never used any swap and are running huge databases (2-3TB):
128GB RAM: innodb_buffer_pool_size = 104GB
64GB RAM: innodb_buffer_pool_size = 56G
32GB RAM: innodb_buffer_pool_size = 28G
This setup has been working flawlessly for us in the past 3 years.
When I first started doing MySQL performance work at my current job, the first thing I started looking into was the IO rates and caching issues. The DB config of course used an 80% rule calculation just like most “recommended” configs.
Based on the workload and way we allocate systems, I found that what we actually wanted was some reserved memory for the OS, monitoring tools, and some base memory for mysqld. After that, we could allocate a percent of the remaining ram to bufferpool
We have several classes of DB nodes. 32G, 64G, and 192G.
For the most part, we allocate (N – 7G) * .9. So for a 64G node, we end up with ~51G of memory allocated to the bufferpool.
Some higher load databases we bump that system reserved number to 10G, since they tend to handle a lot more connections, and have higher need for query processing memory.
So I think this can really depend on your specific workload and applications. I tend to be conservative for a number of reasons.
1) Larger instances are still going to take up 10+% more than the set buffer pool, largely due to internal hash tables, particularly the adaptive hash index. The adaptive hash index for some workloads can be very beneficial, but can be crippled if the buffer pool is bumped up too high.
2) workloads with higher volume are obviously going to have more connection overhead, particularly if there is a temporary build-up for whatever reason. This combined with per-connection buffer allocation, temporary tables, etc.. can chew up a lot of memory quickly.
3) your developers may decide to use the memory engine for some new application with or without your knowledge 🙁
3a) I think it’s fair to say in a memory poor situation, a mysql instance with an over-aggressive buffer pool starving the server of memory and in danger of getting killed is even worse than a system with a smaller than needed buffer pool.
3b) In normal operations, let’s say your buffer pool OK, increasing may help if your working data set is not fully in memory, however for many workloads (time dependent, action at the end of the tables), the working set is already in memory and will make no difference. The benefits may only be seen down the road as the load/working set grows.
3c) assuming this growth, your working set can sometimes be difficult to determine and track, especially with new app features your company may regularly put out, and you may have found (over the period of a couple of days) the working data set no longer fits into memory. It’s saves a lot of pain to have extra memory space available to resize immediately and be fully operational with an LRU dump/restore in 15 minutes and worry about a RAM upgrade later in a controlled situation, rather than request an emergency RAM upgrade, best case scenario a 6 hour turn around and a complete stop of at least 30 minutes, or worst case, days to find the RAM needed.
nice to see 5.7 is trying to address dynamic resizing.
On one of the 1TB machines I poke at MySQL on, 80% does seem fairly excessive… But it can also handle a lot of connections and the per thread data structures do somewhat add up, as does the RAM needed for Linux just to exist and do its thing.
Hi Jay,
Will having a huge amount of memory (such as 80 GB) allocated for the InnoDB Buffer Pool Size result in a performance problem when the cache gets invalidated? Or will any updates to a table happen directly in the buffer and then are written to the disk (instead of invalidating the cache and then recaching the table).
Thanks in advance as I can’t find any documentation on how the buffer works.
When I had a chance to setup a MYSQL server with a huge RAM allocation, It’s a usual step to calculate the 80% that you will define for the InnoDB Buffer Pool Size. However, when I realized how much huge is the remaining 20% just for the OS, I try to looks for answer if it will do harm if I increase the buffer pool to utilize a certain percentage of the 20%. Indeed, the rule of thumb is reasonable but there will always be a case that you have to break it.
Great Article!
Here is what you should do. First run this query
SELECT CEILING(Total_InnoDB_Bytes*1.6/POWER(1024,3)) RIBPS FROM
(SELECT SUM(data_length+index_length) Total_InnoDB_Bytes
FROM information_schema.tables WHERE engine=’InnoDB’) A;
+——-+
| RIBPS |
+——-+
| 8 |
+——-+
1 row in set (4.31 sec)
and set my.cnf setting like this
[mysqld]
innodb_buffer_pool_size=8G
Is it advisable to change to mariadb from sql 5.6 ?
We currently have a server with 32 gb Ram, 4 CPU. The CPU load on is always above 6.
Can someone help with this ?
Thanks
It’s difficult to say yes or no. The CPU load depends on several things and what is “6”? (6%, 60% – of all the cores?). Anyway the CPU Load could be related to intensive queries multiplied per connections, requests, (lack of) assigned memory, etc.
Mariadb ( I checked a non recent version 10.1.29) seems to be a bit faster than mysql 5.6 but it’s a personal opinion.
I’ve read a comment that suggests that mysql 8 is faster:
“MySQL 8.0 implements a completely new data dictionary … while MariaDB has only a tiny fraction … The data dictionary guarantees higher performance and better data consistency and it is fairly well integrated with the rest of the server”
Reference: https://www.percona.com/blog/2017/11/02/mysql-vs-mariadb-reality-check/
Pay attention to upgrade correctly (It’s very easy to do it) if you are goint to move from mysql to mariadb, otherwise you are going to have spikes in cpu load and instable connections with the mariadb server. I just fixed a server with such problems.
what should be the size of innodb_log_file_size for a server with 128G (102G innodb_buffer_pool_size )?
I usually read suggestions that are pointing to a value should be 1/4 of buffer pool size and in other cases I’ve seen comments that it should not be larger than 256mb.
The same goes for innodb_buffer_pool_instances. How many? I’m actually using adding 1 for each GB of innodb_buffer_pool_size.
After various tests I have a general performance degradation with 102G innodb_buffer_pool_size and any size for innodb_log_file_size (ib_logfile0 and ib_logfile1 deleted before restarting). Thank you
I think 80% RAM is a safe point we should start. Everything depends on which service you are running in your server whether is it consume too much RAM unitl 20%? If they consume too few, we can increase innodb_buffer_pool_size into a higher value.