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.
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;
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;
The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:
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) BBB WHERE ENGINE = 'InnoDB' ) AA) A