EverSQL Database Performance Knowledge Base
Figuring out right value for innodb_buffer_pool_size in MySQL 5.7+
Database type:
I am trying to understand how can I evaluate any scope of improvement in the value of innodb_buffer_pool_size in MySQL irrespective of the hardware I am using. I am running into two most common approaches suggested in the community: a. Start with a value >= ~5-10% of the current data + index_data size b. Set it to ~75-80% of the total available RAM.
I am not sure what should be the basis on which I should decide which of these directions to take ?
I went through the below articles: Set to 75% of RAM where it says use Query 1 to find the total data size and allot 75% of that size if it exceeds the available RAM size. On the other hand, this article says that use Query 2 below to accommodate additional ~5% growth and assign that value.
How are these values different ? Is it that Query 1 is accounting only for the current data size in the buffer pool and query 2 the size of the entire database(data+index) ?I am also not following what is the '1.1' thing that is being introduced in Query 2.
Query 1:
SET @IBPSize = (SELECT VARIABLE_VALUE FROM information_schema.global_status WHERE VARIABLE_NAME = 'Innodb_page_size'); -- SELECT @IBPSize;
SET @IBPDataPages = (SELECT VARIABLE_VALUE FROM information_schema.global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_pages_data'); -- SELECT @IBPDataPages;
SET @IBPSize = concat(ROUND(@IBPSize * @IBPDataPages / (1024*1024*1024) * 1.05, 2), ' GB' );
SELECT @IBPSize;
Query 2:
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.05 growth group by growth) BBB
WHERE ENGINE='InnoDB'
) AA
) A;