join_buffer_size >= 4 M is not advised?

Question

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.

Answer 1

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?

Answer 2

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.