Bug #5608 Wrong default value for variable max_join_size
Submitted: 16 Sep 2004 12:16 Modified: 9 Mar 2005 14:39
Reporter: [ name withheld ] Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: General Severity:S4 (Feature request)
Version:4.0.21 OS:Solaris (Solaris 2.8 SPARC 64)
Assigned to: Assigned Account CPU Architecture:Any
Triage: Triaged: D5 (Feature request)

[16 Sep 2004 12:16] [ name withheld ]
Description:
Used Binary:
mysql-standard-4.0.21-sun-solaris2.8-sparc-64bit.tar.gz

The default values for max_join_size and myisam_max_sort_file_size are very high.

SHOW VARIABLES:
max_join_size 18446744073709551615
myisam_max_sort_file_size 9223372036854775807

SELECT @@max_join_size:
-1

If the variables were set by hand in my.cnf, SHOW VARIABLES and SELECT @@max_join_size shows the defined values correctly.

How to repeat:
Install mysql-standard-4.0.21-sun-solaris2.8-sparc-64bit.tar.gz

Enter SHOW VARIABLES in mysql console

Check the default value of  max_join_size and myisam_max_sort_file_size

Set the variables in my.cnf by hand and check again
[7 Oct 2004 22:16] Matthew Lord
Hi,

Thanks for your bug report!

Those are the default values for all of our 64 bit binaries.  Do you think the default is just too 
large?  I can make this a feature request.  What would you like to see the defaults be?

Best Regards
[8 Oct 2004 7:45] [ name withheld ]
These variables are the only two which are different to the standard values used in the documentation. So I think it should be better to set these to the standard too:

max_join_size  4294967295
myisam_max_sort_file_size  2147483647

If someone needs higher values, he can do this in my.cnf.

Thanks

Martin
[19 May 2008 15:20] Matthew Lord
The very large values are also used in our 32 bit binaries now:
mysql> \s
--------------
mysql  Ver 14.12 Distrib 5.0.56, for redhat-linux-gnu (i686) using readline 5.1
...

mysql> show global variables like "max_join_size";
+---------------+----------------------+
| Variable_name | Value                |
+---------------+----------------------+
| max_join_size | 18446744073709551615 | 
+---------------+----------------------+
1 row in set (0.00 sec)
[19 Oct 2008 13:43] Jason Pickard
I believe that this bug is causing our mySql server to start spinning out of control and causing our web application server to crash under heavy loads.  Before changing the value in my.cnf, mysql would pretty much always be the top running process when you watched it using top, basically it and vmware server would go back and forth.  Now it runs for maybe 3 refresh cycles and goes away like it should.  I have this feeling that the large selects with joins that we have going on in the web application that is running there was gradually starting to eat up all the available memory on the server and then the kernel would basically lock up.