[Solved] Figuring out right value for innodb_buffer_pool_size in MySQL 5.7+

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;

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Avoid Subqueries (query line: 12): We advise against using subqueries as they are not optimized well by the optimizer. Therefore, it's recommended to join a newly created temporary table that holds the data, which also includes the relevant search index.
  2. Avoid Subqueries (query line: 16): We advise against using subqueries as they are not optimized well by the optimizer. Therefore, it's recommended to join a newly created temporary table that holds the data, which also includes the relevant search index.
The optimized query:
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

Related Articles



* original question posted on StackOverflow here.