Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We have a dedicated DB box with with 8 GB of RAM and running Redhat linux 64 bit OS. Our MySQL Data + Index size almost 7.8 GB. About setting innodb_buffer_pool_size, I have read 2 suggestions

  • Set it to 80% of RAM available on a dedicated DB server.
  • Set at-least 10% above the size of Data + Indexes.

We cannot satisfy the second condition in my case. So will it have negative effect on Database performance?

Will having innodb_buffer_pool_size of like 6GB affect the performance?

share|improve this question

2 Answers 2

I think 80% is a little bit too much, I suggest 60%. Otherwise you'll risk swapping.

If you're data and indexes do not fit into ram (innodb_buffer_pool_size) you'll experience more disk reads. Which of course is slower than reading from RAM, especially when you have slow disks.

Monitoring disks (IOPS) can be done using iostat or pt-diskstats.

share|improve this answer

The variable innodb_buffer_pool_size Set the amount of memory allocated to both InnoDB data and index buffer cache. If the server requests data available in the cache, the data can be processed right away. Otherwise, the operating system will request that the data be loaded from the disk into the buffer. It is important to set this value as high as possible to use the more efficient innodb data and index buffer cache instead of operating system buffer.

It is better to set innodb_buffer_pool_size to 50 - 80% of physical memory.

Please have a look at post on Choosing innodb_buffer_pool_size.

Also have a look at some other links on optimizing buffer pool

  1. How do you tune MySQL for a heavy InnoDB workload?

  2. Optimizing InnoDB default settings

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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