Author: erol

Posted on

How to get recommended key_buffer_size in MySQL

Execute following command in mysql shell by connecting with your username and password:

mysql> SELECT CONCAT(ROUND(KBS/POWER(1024,
 IF(PowerOf1024<0,0,IF(PowerOf1024>3,0,PowerOf1024)))+0.4999),
 SUBSTR(' KMG',IF(PowerOf1024<0,0,
 IF(PowerOf1024>3,0,PowerOf1024))+1,1))
 recommended_key_buffer_size FROM
 (SELECT LEAST(POWER(2,32),KBS1) KBS
 FROM (SELECT SUM(index_length) KBS1
 FROM information_schema.tables
 WHERE engine='MyISAM' AND
 table_schema NOT IN ('information_schema','mysql')) AA ) A,
 (SELECT 3 PowerOf1024) B;

Response will be something like this:

+-----------------------------+
| recommended_key_buffer_size |
+-----------------------------+
| 1G |
+-----------------------------+
1 row in set (0.08 sec)

When I used this calculation for my Raspberry Pi 2 running MySQL result was 0G, so you need to modify the query to show either MB or KB values by using:

For key_buffer_size recommended size in MB:

SELECT CONCAT(ROUND(KBS/POWER(1024, IF(PowerOf1024<0,0,IF(PowerOf1024>3,0,PowerOf1024)))+0.4999), SUBSTR(' KMG',IF(PowerOf1024<0,0, IF(PowerOf1024>3,0,PowerOf1024))+1,1)) recommended_key_buffer_size FROM (SELECT LEAST(POWER(2,32),KBS1) KBS FROM (SELECT SUM(index_length) KBS1 FROM information_schema.tables WHERE engine='MyISAM' AND table_schema NOT IN ('information_schema','mysql')) AA ) A, (SELECT 2 PowerOf1024) B;

Result:

+-----------------------------+
| recommended_key_buffer_size |
+-----------------------------+
| 1M |
+-----------------------------+
1 row in set (0.44 sec)

For key_buffer_size recommended size in KB:

SELECT CONCAT(ROUND(KBS/POWER(1024, IF(PowerOf1024<0,0,IF(PowerOf1024>3,0,PowerOf1024)))+0.4999), SUBSTR(' KMG',IF(PowerOf1024<0,0, IF(PowerOf1024>3,0,PowerOf1024))+1,1)) recommended_key_buffer_size FROM (SELECT LEAST(POWER(2,32),KBS1) KBS FROM (SELECT SUM(index_length) KBS1 FROM information_schema.tables WHERE engine='MyISAM' AND table_schema NOT IN ('information_schema','mysql')) AA ) A, (SELECT 1 PowerOf1024) B;

and result is:

+-----------------------------+
| recommended_key_buffer_size |
+-----------------------------+
| 23K |
+-----------------------------+
1 row in set (0.44 sec)