join_buffer_size >= 4 M is not advised?

I get this message from MysqlTunner.pl:

join_buffer_size >= 4 M This is not advised

On the other hand, I read in Debian's my.cnf guide about jont_buffer_size that:

This buffer is used for the optimization of full JOINs (JOINs without indexes). Such JOINs are very bad for performance in most cases anyway, but setting this variable to a large value reduces the performance impact. See the "Select_full_join" status variable for a count of full JOINs. Allocated per thread if full join is found

So I'm wondering which one should I believe? Currently I've set join_buffer_size = 64M as part of efforts to cope with scalability problem of a high-traffic site whose queries are not particularly optimized. I appreciate your hints on this.

Solution 1:

join_buffer_size = 64MB is kinda crazy, that's +64MB of allocated to each new thread.

The size of the buffer that is used for plain index scans, range index scans, and joins that do not use indexes and thus perform full table scans. Normally, the best way to get fast joins is to add indexes. Increase the value of join_buffer_size to get a faster full join when adding indexes is not possible.

I'd say, you should reduce join_buffer_size to a value between 128K and 256K while adding indexes to your tables and using the memory you just saved to increase key_buffer_size > +10x.

More memory doesn't always translate to more speed, common examples: sort_buffer_size, read_buffer_size, read_rnd_buffer_size and table_open_cache. Google it.

Solution 2:

They both seem to be saying the same thing to me. They are both telling you that FULL JOINS are BAD.

• Mysqltuner is pointing out that something about your system is bad, in this case having to a have large join buffer is a sign that you have something bad about your database.
• The documentation is telling you that it is bad, but if you cannot change your code, then adding more memory will let you accept the badness.

Did you check the Select_full_join variable? Are you actually seeing this counter increase? Are you sure fixing the code or yelling at the people responsible for fixing it is not an option?

Solution 3:

Do not Increase Per-Connection Buffers!

Not all buffers in my.cnf are allocated only once for the server instance. Some buffers are allocated for each connection. Please see more information at https://haydenjames.io/my-cnf-tuning-avoid-this-common-pitfall/ :

Quote:

Buffers such as join_buffer_size, sort_buffer_size, read_buffer_size and read_rnd_buffer_size are allocated per connection. Therefore a setting of read_buffer_size=1M and max_connections=150 is asking MySQL to allocate – from startup – 1MB per connection x 150 connections. For more than a decade the default remains at 128K. Increasing the default is not only a waste of server memory, but often does not help performance. In nearly all cases its best to use the defaults by removing or commenting out these four buffer config lines. For a more gradual approach, reduce them in half to free up wasted RAM, keep reducing them towards default values over time. I’ve actually seen improved throughput by reducing these buffers. But there’s really no performance gains from increasing these buffers except in cases of very high traffic and/or other special circumstances. Avoid arbitrarily increasing these!

The Speed of Memory Access

Contrary to the common logic, memory access is not O(1).

The more RAM you have, the slower is the access to any data in this RAM.

So, using less RAM may provide faster access to the RAM - this is a general rule, not only applies to MySQL. Please see The Myth of RAM - why a random memory read is O(√N)

Now let us get back to the MySQL join_buffer_size.

Tuning MySQL join_buffer_size

The join_buffer_size is allocated for each full join between two tables. From MySQL’s documentation the join_buffer_size is described as: “The minimum size of the buffer that is used for plain index scans, range index scans, and joins that do not use indexes and thus perform full table scans.” It goes on to say: “Memory allocation time can cause substantial performance drops if the global size is larger than needed by most queries that use it.” The join buffer is allocated to cache table rows when the join can’t use an index. If your database(s) suffer from many joins performed without indexes it cannot be solved by just increasing join_buffer_size. The problem is “joins performed without indexes” and thus the solution for faster joins is to add indexes.

Change the MySQL config to log queries without indexes, so you will be able to find such queries and add indexes and/or modify the application that sends generates such ill queries. You should enable "log-queries-not-using-indexes" Then look for non-indexed joins in the slow query log.

Solution 4:

I dont think so. its likely mysqltuner suggest you configuration based as configuration you might need. if I run mysqltuner on DB server. here it is recommendation that I get:

Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    join_buffer_size (> 64.0M, or always use indexes with joins)
    innodb_buffer_pool_size (>= 54G) if possible.
    innodb_log_file_size should be (=2G) if possible, so InnoDB total log files size equals to 25% of buffer pool size.

So I think it's not always bad size on another server and needs.