Evening all,

I have a modest 6GB database running in MySQL 8.0.x.. my dedicated server is only running 4 cores and 8GB RAM and it doesn't really have any major running issues.

after learning some very basic info about using the pool buffer last tomcat app upgrade and I after an initial stab i made a 1GB pool of 8 instances of the default 128m chunks.

my buffer is normally 78% full on these settings and i get a few read spikes and on a 15 minute delta I'm getting a 0.5 page read a second that is not in the buffer. i have the normal 4 read and 4 write Innodb threads (I would say my Db has 2/3 select heavy)

right to the actually question, i think i actually have another 1Gb that I can assign to my pool but i'm not sure which way to double it up.

Is it better to have bigger chunks (8/256Mb) or is it more beneficial to have more instances (16/128Mb)

Is there a query that can better indicate if I need more instances to hold more buffer, as my buffer still has some spare overhead, I assume i don't need bigger chunks? again its not running slow just want to make sure all is running as good as needed so I can leave it for a while.

Apologise if I'mm getting this completely wrong

  • If you consult the manual, it'll tell you that you should "specify a combination of innodb_buffer_pool_instances and innodb_buffer_pool_size so that each buffer pool instance is at least 1GB".
    – mustaccio
    Oct 2 at 14:15
  • You have not mentioned where your data is hosted. SHOW GLOBAL STATUS LIKE '%flush%'; every hour to get a sense of frequency of log flushing activity when you are curious. Some AWS flushing is on 5 miinute intervals, others have been observed on 15 minute intervals. If AWS, what is your instance type/model? Oct 5 at 13:56

The size of the buffer_pool is important. It should be set to about 70% of available RAM. If only MySQL is running, set it to 6G on your 8G machine.

innodb_buffer_pool_size is the main tunable in MySQL. It should be bigger than the amount of data + indexes that you have, but not so much that swapping occurs.

Are you saying that the buffer_pool is 78% full? What STATUS value is providing that info. Offhand, the number sounds fine. It may grow to somewhere around 91-95%; this, too, is normal.

The fact that "1GB" did not seem to fill up is also normal. It just means that you have not yet touched all of the 6G of data that you have. If you leave it set at 1G and continue to access more of the data, then it will act as it should -- by caching "least recently used blocks" and doing I/O as needed to process queries.

"Instances" should be no more than 16, and should be buffer_pool_size / 1G. But the setting has only a minor impact on performance. There is no STATUS to provide a clearer choice.

"Chunks" is relevant for one obscure operation; don't worry about it. Again, no useful setting.

"4 read and 4 write threads" -- another thing not to worry about (yet).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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