In server optimization, various rules of thumb have guided professionals in making informed decisions about resource allocation. One such directive is the 80% rule, which suggests allocating 80% of a server’s RAM to InnoDB’s buffer pool, leaving the remaining 20% for other operations. Initially conceived as a general guideline to get servers up and running quickly, the rule is often adopted in situations with limited knowledge about the system workload. Let’s scrutinize the nuances of this rule to understand its implications and efficacy in modern server environments.
Breaking Down the 80% Rule
To better understand, we may consider scenarios where the total server RAM varies significantly. Here’s how the rule plays out with different RAM sizes:
|
1 2 3 4 5 6 7 8 9 10 11 |
| Total Server RAM | Buffer pool with 80% rule | Remaining RAM | |------------------|---------------------------|---------------| | 1G | 800MB | 200MB | | 8G | 6G | 2G | | 16G | 13G | 3G | | 32G | 26G | 6G | | 64G | 51G | 13G | | 128G | 102G | 26G | | 256G | 205G | 51G | | 512G | 409G | 103G | | 1024G | 819G | 205G | |
The Disproportionate Growth Dilemma
For the 80% rule to stand its ground, it necessitates a proportionate increase in workload memory consumption relative to the buffer pool size. In reality, this is rarely the case. A server with 1TB of RAM might not require a gargantuan 205G to manage connections and queries — a threshold that MySQL might find overwhelming to handle regarding active connections and queries.
The 20% Tax on Resources
It brings us to an essential question: does it make sense to adhere to a rule that essentially levies a 20% “tax” on your server resources, particularly when you have invested a substantial amount of dollars in procuring a high-performance server?
There’s no denying that setting aside a generous portion of RAM for unforeseen spikes in workload can be a prudent strategy. Yet, the arbitrary adherence to the 80% rule may result in underutilization of resources, leading to economic inefficiencies.
Towards a More Tailored Approach
A deeper understanding of how the buffer pool performs can be gleaned through constant monitoring of several parameters in MySQL’s InnoDB status reports. Let’s look at some key indicators that help in assessing the efficiency of the buffer pool:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 52747567104; in additional pool allocated 0 Total memory allocated by read views 6696 Internal hash tables (constant factor + variable factor) Adaptive hash index 1264159888 (815987752 + 448172136) Page hash 3188312 (buffer pool 0 only) Dictionary cache 218963057 (203998672 + 14964385) File system 1623040 (812272 + 810768) Lock system 127518328 (127499608 + 18720) Recovery system 0 (0 + 0) Dictionary memory allocated 14964385 Buffer pool size 3145712 Buffer pool size, bytes 51539345408 Free buffers 16385 — IF NEAR 0 - BUFFER POOL IS FULL Database pages 3101973 Old database pages 1144736 Modified db pages 0 Pending reads 0 — IF CONSISTENTLY GREATER THAN 0 - BUFFER MAY BE TOO SMALL Pending writes: LRU 0, flush list 0, single page 0 — IF CONSISTENTLY GREATER THAN 0 - BUFFER MAY BE TOO SMALL OR DISK IS SLOW FOR AMOUNT OF WRITES YOU HAVE Pages made young 6420249, not young 2000664705 0.13 youngs/s, 0.00 non-youngs/s Pages read 21996954, created 7754528, written 10606607 — IF written CONSISTENTLY GREATER THAN read - BUFFER IS TOO SMALL 0.00 reads/s, 0.00 creates/s, 0.33 writes/s Buffer pool hit rate 1000 / 1000, young-making rate 25 / 1000 not 0 / 1000 — IF hit rate CONSISTENTLY NOT 1000 / 1000 - BUFFER IS TOO SMALL, IF young-making rate CONSISTENTLY NOT 0 / 1000 - BUFFER IS TOO SMALL Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s — IF evicted without access CONSISTENTLY GREATER THAN 0 - BUFFER IS TOO SMALL LRU len: 3101973, unzip_LRU len: 0 I/O sum[720]:cur[0], unzip sum[0]:cur[0] |
Conclusion
While the 80% rule offers a simplified starting point for server RAM allocation, its practicality diminishes as we scale up the server resources. It calls for reconsidering this rule of thumb, steering towards strategies grounded in the demands and characteristics of your server workloads.