Bug #18875 Default value of tmp_table_size is meaningless
Submitted: 7 Apr 2006 5:00 Modified: 26 Sep 2006 19:52
Reporter: Jeremy Cole (Basic Quality Contributor) (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.1/5.0/5.1 OS:n/a
Assigned to: Chad MILLER CPU Architecture:Any

[7 Apr 2006 5:00] Jeremy Cole
Description:
The default value of tmp_table_size (32M) is meaningless, as it is less than max_heap_table_size (16M), which then becomes the real maximum for an in-memory temporary table.

How to repeat:
mysql> select @@global.tmp_table_size, @@global.max_heap_table_size \G
*************************** 1. row ***************************
     @@global.tmp_table_size: 33554432
@@global.max_heap_table_size: 16777216

Suggested fix:
Decrease the default of tmp_table_size to 16M, or increase the default of max_heap_table_size to 32M.
[7 Apr 2006 5:27] Jeremy Cole
Further, it would make sense to emit a warning (to the error file) when setting tmp_table_size higher than max_heap_table_size, and clip the value of tmp_table_size to that of max_heap_table_size.  When setting it via the SET command it makes sense to do the same and emit the warning for SHOW WARNINGS instead.
[7 Apr 2006 18:30] MySQL Verification Team
Thank you for the bug report.
[11 Aug 2006 13:25] Chad MILLER
I'm wary of changing the default value of either for stable releases.  This bug isn't causing faults, and if anyone is explicitly setting one of the variables then a change in default for the /other/ variable might cause failures.

For stable releases, this is a documentation problem.  I will lower the default tmp_table_size in a development version, 5.1 .

Documenters:  Please make it explicit that where tmp_table_size is used, it is maximally bounded by max_heap_table_size .
[11 Aug 2006 13:48] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/10312

ChangeSet@1.2272, 2006-08-11 09:49:01-04:00, cmiller@zippy.cornsilk.net +1 -0
  Bug#18875: Default value of tmp_table_size is meaningless
  
  It makes no sense to have a default tmp_table_size larger than the 
  max_heap_table_size .  In usage, the tmp is ever limited to the max value, 
  so I lowered the default tmp to the default max value.
  
  A great idea would be to emit a warning when the tmp_table_size is set to 
  greater than max_heap_table_size .
[12 Aug 2006 12:11] Chad MILLER
This is in the Maint team-tree and should be available in 5.1.12-beta .
[13 Sep 2006 8:05] Timothy Smith
Merged up to 5.1.12
[26 Sep 2006 19:52] Paul DuBois
Noted in 5.1.12 changelog.