Bug #39401 large sort_buffer_size slows down sorts of small data
Submitted: 11 Sep 2008 15:53 Modified: 16 Sep 2008 12:41
Reporter: Todd Lipcon Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server Severity:S5 (Performance)
Version:5.0 OS:Linux
Assigned to:

[11 Sep 2008 15:53] Todd Lipcon
Description:
Setting sort_buffer_size to be fairly large (e.g 64M) slows down sorts even when the size of the data to be sorted is exceedingly small. This only seems to occur when the source table is large, regardless of the number of rows actually being sorted.

How to repeat:
mysql> select count(*) from Song;
+----------+
| count(*) |
+----------+
|   >1M ROWS BIG TABLE HERE [hidden] | 
+----------+
1 row in set (0.14 sec)

mysql> optimize table Song;
+------------------------+----------+----------+----------+
| Table                  | Op       | Msg_type | Msg_text |
+------------------------+----------+----------+----------+
| amiest_production.Song | optimize | status   | OK       | 
+------------------------+----------+----------+----------+
1 row in set (59.99 sec)

mysql> select SQL_NO_CACHE id from Song where album_id = 1805 order by amie_index desc;
+------+
| id   |
+------+
| 9820 | 
| 9816 | 
| 9818 | 
| 9822 | 
| 9817 | 
| 9821 | 
| 9819 | 
| 9823 | 
| 9813 | 
| 9815 | 
| 9814 | 
| 9812 | 
+------+
12 rows in set (0.01 sec)   <--- consistently 0.01 or 0.02sec

mysql> create table test like Song;
Query OK, 0 rows affected (0.10 sec)

mysql> explain select SQL_NO_CACHE id from Song where album_id = 1805 order by amie_index desc;
+----+-------------+-------+------+---------------+----------+---------+-------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key      | key_len | ref   | rows | Extra                       |
+----+-------------+-------+------+---------------+----------+---------+-------+------+-----------------------------+
|  1 | SIMPLE      | Song  | ref  | album_id      | album_id | 4       | const |   12 | Using where; Using filesort | 
+----+-------------+-------+------+---------------+----------+---------+-------+------+-----------------------------+
1 row in set (0.00 sec)

mysql> insert into test select * from Song where id < 10000;
mysql> select SQL_NO_CACHE id from test where album_id = 1805 order by amie_index desc;
+------+
| id   |
+------+
| 9820 | 
| 9816 | 
| 9818 | 
| 9822 | 
| 9817 | 
| 9821 | 
| 9819 | 
| 9823 | 
| 9813 | 
| 9815 | 
| 9814 | 
| 9812 | 
+------+
12 rows in set (0.00 sec)  <-- consistently 0.00 sec

Using the community SHOW PROFILE patch shows the extra time is all occurring inside filesort.

After SET SESSION sort_buffer_size = 256*1024; all queries take <10ms consistently.

Tested on 5.0.62, 5.0.67, 5.0.68 from bzr.

Suggested fix:
sort_buffer_size should be a maximum - if the amount of data being sorted is less than sort_buffer_size, then increasing sort_buffer_size should have no effect.
[16 Sep 2008 12:41] Sergei Golubchik
a duplicate of bug#37359