Bug #15088 No status variable exists to test join_buffer_size efficientcy
Submitted: 21 Nov 2005 4:06 Modified: 15 Dec 2010 11:51
Reporter: Matthew Montgomery Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S4 (Feature request)
Version:5.0 OS:Any (All)
Assigned to: Geir Høydalsvik CPU Architecture:Any

[21 Nov 2005 4:06] Matthew Montgomery
Description:
Hello,

As I understand it; when a sort operation, that can't use an index, has more than 'sort_buffer_size' amount of data to chew on it will split the data into multiple smaller sort jobs and merge those back together at the end.  These merge passes will cause the 'Sort_merge_passes' variable to be incremented.  This allows us to know when 'sort_buffer_size' is too small and should be increased.  I assume that join operations will do a symilar thing when it has more rows that will fit into 'join_buffer_size'.  However there is no status variable that gets updated when this senario occours.  Thus, we don't have an easy way to tell when join_buffer_size is too small.

How to repeat:
mysql> show status like '%join%';

Suggested fix:
I'd like to see a variable like "Join_merge_passes" added to future versions of mysqld.
[16 May 2007 1:27] MySQL Verification Team
With the added explanation of Sergey Petrunia's blog -- http://s.petrunia.net/blog/?p=18 It is obvious now that "Join_merg_passes" variables doesn't make sense.

Still there is no status variable that indicates when join_buffer_size is exceeded or is configured in a wasteful manner.