How to change join_buffer_size in MySQL

I will give an example of changing join_buffer_size in MySQL.
join_buffer_size determines the size of the buffer for index scanning and joins without indexes. I recommend adding indexes or slightly increasing join_buffer_size (but in this case a full table scan will be performed).

First, connect to the MySQL server and see the current value (by default 262144 = 256K):

mysql -u root -p
show variables like "join_buffer_size%";
show global variables like 'join_buffer_size';
show global status like '%Select_full_join%';

You can change the value without restarting the MySQL server as follows (for example, up to 524288 = 512K):

SET GLOBAL join_buffer_size=524288;

To prevent the change from being reset after restarting the MySQL server, open the configuration file:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

And indicate:

join_buffer_size=524288

Restart the MySQL server and make sure that the parameter is not reset:

sudo /etc/init.d/mysql restart

See also my article:
How to enable Slow Query Log in MySQL

Leave a comment

Leave a Reply