1

We have a MySQL 5.7 AWS RDS instance running on a db.m4.large instance. The basic resources for that instance type are:

  1. 7.5gb RAM
  2. 2 VCPU

RDS defaults to a calculation that allocates 75% of RAM to innodb_buffer_pool_size.

{DBInstanceClassMemory*3/4}

This ends up being a 6gb allocation:

mysql> SELECT @@innodb_buffer_pool_size/1024/1024/1024;
+------------------------------------------+
| @@innodb_buffer_pool_size/1024/1024/1024 |
+------------------------------------------+
|                           6.000000000000 |
+------------------------------------------+

With MySQL 5.7 if you do not set the number of buffer pool instances, then MySQL now defaults to 8 pools. However the MySQL 5.7 manual states this when discussing buffer pool instances:

The total size you specify is divided among all the buffer pools. For best efficiency, specify a combination of innodb_buffer_pool_instances and innodb_buffer_pool_size so that each buffer pool instance is at least 1GB.

For this reason, I plan on reducing the innodb_buffer_pool_instances to a number <= 6.

Here are my questions:

  1. Is there some correlation to be made between the number of CPU's and the buffer_pool_instances setting?
  2. Would it be better in this scenario to go with 4,5, or 6 buffer_pool_instances, and if so, what is the reasoning behind that recommendation? My simplistic response would be to change the setting to 6 buffer pool instances to satisfy the "at least 1GB" recommendation, but I would be interested to hear from anyone who has looked into this further, or done benchmarking of this setting whether that be under RDS or not.
Improve this question

1 Answer 1

Reset to default
1

Short Answer: Not a problem.

Long Answer:

The "instances" feature is new (as of 5.5.4) and exists to speed up certain things when the system is running at peak load. But even then, it does not help the performance more than a few percent. For lightly loaded servers, the improvement is probably not measurable.

What about "8"? I have yet to hear of anyone getting in trouble when each instance is less than 1GB. I suspect it is "advice", not "a hard and fast rule" to make each instance at least 1GB. In fact, glancing at a few dozen servers, I see that almost half of them violate that. I see one machine with 10 instances for only 2GB of buffer_pool.

I would trust RDS to provide reasonable settings. If their settings are bad, they will be bad for all their clients. Imagine how much hassling RDS would get!

Improve this answer
10
  • I appreciate your response, and you make some good points. WIth that said, the lack of settings/defaults is not the same as proactive settings that are beneficial to some customers and perhaps not to others. Per the MySQL manual, an individual buffer pool should be 1gb. I have reasons to trust this recommendation, and certainly a server with 2gb and 10 instances violates their documentation. Since the pools only have benefits when there is contention of the IO read/write threads, I don't think there is a one size fits all answer, nor do I think this is a micro optimization.
    – gview
    Oct 17, 2018 at 1:17
  • 1
    I gathered this, back when "instances" were introduced: Multiple connections may try to simultaneously lock the buffer pool. The locks are brief, still, there is some contention under high traffic. The "instances" let separate connections lock different instances (in most cases).
    – Rick James
    Oct 17, 2018 at 16:48
  • @RickJames what about number of cpus vs buffer pool instances? node 0 cpus: 16 node 0 size: 128664 MB, node 0 free: 37904 MB, node distances: node 0, 0: 10 Data base 500Gb, inodb buffer poll 110Gb. Should I set buffer pool instances to 64, actually 16 (same as cores) thanks
    – Horaciux
    Feb 28, 2020 at 20:36
  • One more detail to last comment, storage is SSD. Should I change innodb_read_io_threads and innodb_write_io_threads according to CPUs? MySQL 5.6
    – Horaciux
    Feb 28, 2020 at 20:58
  • @Horaciux - The number of pool_instances has only a small impact on performance. And probably it has even less impact on a lightly loaded MySQL. Is your system close to melting down?
    – Rick James
    Feb 28, 2020 at 21:19

Your Answer

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

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