key_buffer_size optimisation in MySQL

269ac00f765508cb74b25d006dbf5bb2

Written by Dennis Nind

Last published at: February 8th, 2019

The most common way to calculate the ideal value for the  key_buffer_size is to make it 'not higher' than the MYI files. Regarding the calculations:

[root@golf ~]# find /var/lib/mysql/ -name *.MYI -ls | awk '{s = s + $7} END {print s/1048576 " MB"}'
1638.17 MB

Based on the above results, a good value to be set would be around 1.5G and can be set by doing the following...

mysql> SET GLOBAL key_buffer_size=1.5G

You can also define the key_buffer_size directly inside the my.cnf file so that the values remain following a service restart.