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
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?
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.
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
Look at the SHOW GLOBAL VARIABLES LIKE 'innodb_buffer_pool%' particularly:
Innodb_buffer_pool_pages_free any non-trivial number would indicated you've made it too bigInnodb_buffer_pool_read_requests vs Innodb_buffer_pool_reads if this ratio is quite low that indicates a good size.Innodb_buffer_pool_pages_dirty a high value compared to the total pool size could indicate the flushing isn't occurring often enough.Look at the other values and consult your manual on their meaning.
Tuning to your actual data usage is always better than rough magnitudes.
