MySQL join_buffer_size, sort_buffer_size, read_buffer_size and read_rnd_buffer_size

join_buffer_size, 

sort_buffer_size, 

read_buffer_size 

read_rnd_buffer_size

These four buffers are allocated per connection. So think twice before alerting.

For example, if you set join buffer size=2 M and max connections=800, MySQL will allocate an extra 2 M per connection (2 M x 800). The same is true for the remaining three buffers. Again, everything is per-connection.

In almost all circumstances, keeping the defaults by eliminating or commenting out these four config lines is the best option. As the number of connections grows, queries that require more space than is available owing to increasing buffer settings may be pushed to disc. This causes a bottleneck in your database server and significantly slows it down.

join_buffer_size

For each entire join between two tables, the join buffer size is allocated. "The minimum size of the buffer that is utilised for simple index scans, range index scans, and joins that do not use indexes and hence execute complete table scans," according to MySQL's documentation.

"Memory allocation time might cause severe performance decreases if the global size is larger than needed by most queries that use it," MySQL's literature continues. When a join can't use an index, the join buffer is used to cache table rows.

If your database suffers from many joins performed without indexes, it cannot be solved by increasing join_buffer_size. The problem is “joins performed without indexes.” Thus, the solution for faster joins is to add indexes. 

If we give join_buffer_size = 64MB its will be crazy. Its mean is 64MB of memory allocated to each new thread. Oh!  

sort_buffer_size

Unless you have data indicating otherwise, you should avoid arbitrarily increasing the sort_buffer_size as well. Memory here is also assigned per connection! MySQL’s documentation warns: “On Linux, there are thresholds of 256KB and 2MB where larger values may significantly slow down memory allocation, so you should consider staying below one of those values.” Avoid increasing sort_buffer_size above 2M since there is a performance penalty that will eliminate benefits.

read_buffer_size

read_buffer_size Applies generally to MyISAM only and does not affect InnoDB. Consider converting your MySQL tables to InnoDB storage engine before increasing this buffer. InnoDB is the default storage engine of MySQL 5.7 and MySQL 8.0. InnoDB features rollback and crash-recovery capabilities to protect data.

read_rnd_buffer_size

read_rnd_buffer_size variable is also used mainly for MyISAM reads from tables. Also, consider InnoDB or MariaDB’s Aria storage engines. For the past decade, the default values of these buffers have remained the same.

key_buffer_size

The value of key_buffer_size is the size of the buffer used with indexes. The larger the buffer, the faster the SQL command will finish and a result will be returned. The rule-of-thumb is to set the key_buffer_size to at least a quarter, but no more than half, of the total amount of memory on the server. Ideally, it will be large enough to contain all the indexes (the total size of all .MYI files on the server).

innodb_sort_buffer_size

innodb_sort_buffer_size specifies the size of sort buffers used for sorting data during creation of an InnoDB index.

While sort_buffer_size is just a per-connection variable and do not belongs to any specific storage engine.