MySQL Bugs: #5608: Wrong default value for variable max_join

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

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

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

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

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)

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.