MySQL Bugs: #21727: sort_buffer_size has an extremely negative impact on a query as it increases

Bug #21727	sort_buffer_size has an extremely negative impact on a query as it increases
Submitted:	18 Aug 2006 20:53	Modified:	28 Nov 2006 20:28
Reporter:	Harrison Fisk	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S5 (Performance)
Version:	5.0.24	OS:	Linux (Ubuntu 6.06)
Assigned to:	Igor Babaev	CPU Architecture:	Any
Tags:	backport_050030SP1 performance

Description:
A query of the type:

SELECT SQL_NO_CACHE a, b, (SELECT x FROM t2 WHERE y=b ORDER BY z DESC LIMIT 1) c FROM t1;

will get much slower as your sort_buffer_size is increased.  In fact, to make the query perform faster, you need to set sort_buffer_size to 32k (the smallest it can be set to).

This is pretty much the opposite of any other case I have seen.

For example:

mysql> SET session sort_buffer_size = 0;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT SQL_NO_CACHE a, b, (SELECT x FROM t2 WHERE y=b ORDER BY z DESC LIMIT 1) c FROM t1;
10000 rows in set (25.79 sec)

mysql> SET session sort_buffer_size = 8 * 1024 * 1024;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT SQL_NO_CACHE a, b, (SELECT x FROM t2 WHERE y=b ORDER BY z DESC LIMIT 1) c FROM t1;
10000 rows in set (1 min 47.55 sec)

How to repeat:
I have created the following SQL script which can create tables and populate them to demostrate the issue:

DELIMITER ;

DROP TABLE IF EXISTS t2;
DROP TABLE IF EXISTS t1;

CREATE TABLE t1 (
        a INT,
        b INT auto_increment,
        PRIMARY KEY (b)
) ENGINE=InnoDB;

CREATE TABLE t2 (
        x INT auto_increment,
        y INT,
        z INT,
        PRIMARY KEY (x),
        FOREIGN KEY (y) references t1 (b)
) ENGINE=InnoDB;

DROP PROCEDURE IF EXISTS pop_tables;

DELIMITER $$
CREATE PROCEDURE pop_tables (IN rows INT, IN number_of_b_per_a FLOAT)
BEGIN
        DECLARE id, rand_num, m, n INT DEFAULT 0;

        START TRANSACTION;
	
        WHILE m < rows DO
                SET rand_num = FLOOR(rand() * 1000);
                INSERT INTO t1 (a) values (rand_num);
                /* workaround for bug # 21726 */
                SET id = (SELECT max(b) FROM t1);
                SET n = 0;
                WHILE n < number_of_b_per_a DO
                        SET rand_num = FLOOR(rand() * 1000);
                        INSERT INTO t2 (y, z) VALUES (id, rand_num);
                        SET n = n +1;
                END WHILE;
                SET m = m +1;
        END WHILE;
        COMMIT;

END$$
DELIMITER ;

/* this will take a while, took a minute on my system, 10000 rows in t1 1000000 rows in t2*/
call pop_tables(10000,100);

/* fast vrooooom */
SET session sort_buffer_size = 0;
SELECT SQL_NO_CACHE a, b, (SELECT x FROM t2 WHERE y=b ORDER BY z DESC LIMIT 1) c FROM t1;

/* not so fast, slooooow */
SET session sort_buffer_size = 8 * 1024 * 1024;
SELECT SQL_NO_CACHE a, b, (SELECT x FROM t2 WHERE y=b ORDER BY z DESC LIMIT 1) c FROM t1;

Suggested fix:
From strace, I seem to see a lot of memory allocations and deallocations.  Not sure if the sort_buffer_size is being reallocated many times or what the case it, but it should be avoided if possible if that is the case.

In this particular case there is a work-around to make the query use an index for the sorting instead, but that wouldn't be possible in all cases.

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/14588

ChangeSet@1.2307, 2006-10-30 20:14:43-08:00, igor@rurik.mysql.com +10 -0
  Fixed bug #21727.
  This is a performance issue for queries with subqueries evaluation
  of which requires filesort.
  Allocation of memory for the sort buffer at each evaluation of a
  subquery may take a significant amount of time if the buffer is rather big.
  With the fix we allocate the buffer at the first evaluation of the
  subquery and reuse it at each subsequent evaluation.

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/14644

ChangeSet@1.2307, 2006-10-31 17:31:56-08:00, igor@rurik.mysql.com +12 -0
  Fixed bug #21727.
  This is a performance issue for queries with subqueries evaluation
  of which requires filesort.
  Allocation of memory for the sort buffer at each evaluation of a
  subquery may take a significant amount of time if the buffer is rather big.
  With the fix we allocate the buffer at the first evaluation of the
  subquery and reuse it at each subsequent evaluation.

Pushed in 5.0.32/5.1.14-beta

Noted in 5.0.32, 5.1.14 changelogs.

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/17323

ChangeSet@1.2312, 2006-12-22 14:52:20+03:00, igor@mysql.com +12 -0
  [PATCH] bk commit - 5.0 tree (igor:1.2307) BUG#21727

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/17329

ChangeSet@1.2312, 2006-12-22 16:13:39+03:00, igor@mysql.com +12 -0
  [PATCH] bk commit - 5.0 tree (igor:1.2307) BUG#21727