Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question

2 Answers 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?

share|improve this answer
    
Well, select_full_join = 0. At the moment I can not fix the code or yell at someone. So my best bet is to make squeeze the best possible performance out of mysql. Thanks –  alfish Jun 17 '12 at 12:35

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.

share|improve this answer
1  
> join_buffer_size = 64MB is kinda crazy, that's +64MB of allocated to > each new thread. Actually that is not entirely correct. The join buffer size is NOT allocated on each thread. It's only allocated on EACH full table join. So whenever a full table join is performed, mysql will allocate the "join_buffer_size". Also note that if you join several tables without indexes in the same query (same thread), mysql will allocate "join_buffer_size" for each joined table. Apart from that I agree with Alix Axels comments. –  user152467 Jan 8 '13 at 9:39

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.