How to calculate innodb_buffer_pool_size value

Run to the following command to start calculation:

SELECT CEILING(Total_InnoDB_Bytes*1.6/POWER(1024,3)) RIBPS FROM
(SELECT SUM(data_length+index_length) Total_InnoDB_Bytes
FROM information_schema.tables WHERE engine='InnoDB') A;

Example:

mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 60295069
Server version: 10.3.34-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

MariaDB [(none)]> SELECT CEILING(Total_InnoDB_Bytes*1.6/POWER(1024,3)) RIBPS FROM
-> (SELECT SUM(data_length+index_length) Total_InnoDB_Bytes
-> FROM information_schema.tables WHERE engine=’InnoDB’) A;
+——-+
| RIBPS |
+——-+
| 61 |
+——-+
1 row in set, 28 warnings (6 min 58.342 sec)

So innodb_buffer_pool_size should be 61Gb

Update your /etc/my.cnf with the following:

innodb_buffer_pool_size=61Gb

 

Source: https://dba.stackexchange.com/questions/27328/how-large-should-be-mysql-innodb-buffer-pool-size

 

Similar Posts:

Leave a Reply

Your email address will not be published.