What is the recommended maximum value for join_buffer_size in mysql?

Question

I try to optimize my time data in accordance with 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 the general case. It depends on the profile of your requests. Check the manual, find out their purpose and, as they put it, beautifully:

( sort_buffer_size )

Installing more than globally required will slow down most queries that are sorted. It is best to increase it as a session setting and only for sessions requiring a larger size. Linux has thresholds of 256 KB and 2 MB, where large values ​​can significantly slow down memory allocation (...). Experiment to find the best value for your workload.

( join_buffer_size )

There is no benefit of setting a buffer larger than that required to store each corresponding line, and all connections allocate at least the minimum size, so be careful when setting this variable to a global value. It’s better to keep the global settings small and change the larger settings only in sessions that make large joins. Memory allocation time can lead to a significant decrease in performance if the global size is larger than necessary for most requests that use it.