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:

[[email protected] ~]# 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.

Did this answer your question?