patternsqlModerate
How much memory do I need for innodb buffer pool?
Viewed 0 times
muchneedinnodbformemoryhowpoolbuffer
Problem
I read here that
You need buffer pool a bit (say 10%) larger than your data (total size
of Innodb TableSpaces)
On the other hand I've read elswher that innodb_buffer_pool_size must be up to %80 of the memory. So I'm really confused how should I choose the best size for the pool. My database size is about 6GB and my total memory 64GB. Also I'm wondering if I increase the buffer pool size, I should shrink the number of maximum connections to make room for extra buffer, or these parameters are independent. Thanks
You need buffer pool a bit (say 10%) larger than your data (total size
of Innodb TableSpaces)
On the other hand I've read elswher that innodb_buffer_pool_size must be up to %80 of the memory. So I'm really confused how should I choose the best size for the pool. My database size is about 6GB and my total memory 64GB. Also I'm wondering if I increase the buffer pool size, I should shrink the number of maximum connections to make room for extra buffer, or these parameters are independent. Thanks
Solution
If you go strictly by that rule of accommodating an addition 10%, here is my suggestion:
This will produce exactly what you need to set innodb_buffer_pool_size in
Recently, I answered another question like this in the DBA StackExchange.
SELECT CONCAT(CEILING(RIBPS/POWER(1024,pw)),SUBSTR(' KMGT',pw+1,1))
Recommended_InnoDB_Buffer_Pool_Size FROM
(
SELECT RIBPS,FLOOR(LOG(RIBPS)/LOG(1024)) pw
FROM
(
SELECT SUM(data_length+index_length)*1.1*growth RIBPS
FROM information_schema.tables AAA,
(SELECT 1 growth) BBB
WHERE ENGINE='InnoDB'
) AA
) A;This will produce exactly what you need to set innodb_buffer_pool_size in
/etc/my.cnf. If you want to account for 25% increase in data and indexes over time, please change (SELECT 1 growth) BBB to (SELECT 1.25 growth) BBBRecently, I answered another question like this in the DBA StackExchange.
Code Snippets
SELECT CONCAT(CEILING(RIBPS/POWER(1024,pw)),SUBSTR(' KMGT',pw+1,1))
Recommended_InnoDB_Buffer_Pool_Size FROM
(
SELECT RIBPS,FLOOR(LOG(RIBPS)/LOG(1024)) pw
FROM
(
SELECT SUM(data_length+index_length)*1.1*growth RIBPS
FROM information_schema.tables AAA,
(SELECT 1 growth) BBB
WHERE ENGINE='InnoDB'
) AA
) A;Context
StackExchange Database Administrators Q#27471, answer score: 12
Revisions (0)
No revisions yet.