What is the recommended max value for join_buffer_size in mysql?

Question

I am traying to optimize my temporary data following the recommendation given in the answer to this question: Mysql tmp_table_size max_heap_table_size

What is the recommended value for join_buffer_size and sort_buffer_size in mysql?

My actual values are:

join_buffer_size: 128 Kio;
sort buffer size: 512 Kio;

Answer 1

It is impossible to answer your question in a general case. It really depends on the profile of your queries. Check the manual, learn their purpose, and as they put it nicely:

(sort_buffer_size)

Setting it larger than required globally will slow down most queries that sort. It is best to increase it as a session setting, and only for the sessions that need a larger size. On Linux, there are thresholds of 256KB and 2MB where larger values may significantly slow down memory allocation (...). Experiment to find the best value for your workload.

(join_buffer_size)

There is no gain from setting the buffer larger than required to hold each matching row, and all joins allocate at least the minimum size, so use caution in setting this variable to a large value globally. It is better to keep the global setting small and change to a larger setting only in sessions that are doing large joins. Memory allocation time can cause substantial performance drops if the global size is larger than needed by most queries that use it.