Bug #86258 ERROR 1038 (HY001): Out of sort memory where it does not seem to make sense
Submitted: 10 May 2017 5:59 Modified: 5 Jan 2018 5:44
Reporter: Roel Van de Paar Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Errors Severity:S2 (Serious)
Version:5.7.17 OS:Any
Assigned to: CPU Architecture:Any

[10 May 2017 5:59] Roel Van de Paar
Description:
mysql> SET @@session.max_length_for_sort_data=10737418241;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> SET big_tables=1;
Query OK, 0 rows affected (0.00 sec)

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

mysql> SELECT LOGFILE_GROUP_NAME,FILE_NAME,TOTAL_EXTENTS,INITIAL_SIZE,ENGINE FROM INFORMATION_SCHEMA.FILES ORDER BY FILE_NAME;
ERROR 1038 (HY001): Out of sort memory, consider increasing server sort buffer size
mysql> SET SESSION sort_buffer_size=100 * 1024;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT LOGFILE_GROUP_NAME,FILE_NAME,TOTAL_EXTENTS,INITIAL_SIZE,ENGINE FROM INFORMATION_SCHEMA.FILES ORDER BY FILE_NAME;
ERROR 1038 (HY001): Out of sort memory, consider increasing server sort buffer size
mysql> SET SESSION sort_buffer_size=1024 * 1024;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT LOGFILE_GROUP_NAME,FILE_NAME,TOTAL_EXTENTS,INITIAL_SIZE,ENGINE FROM INFORMATION_SCHEMA.FILES ORDER BY FILE_NAME;
+--------------------+---------------------------------------+---------------+--------------+--------+
| LOGFILE_GROUP_NAME | FILE_NAME                             | TOTAL_EXTENTS | INITIAL_SIZE | ENGINE |
+--------------------+---------------------------------------+---------------+--------------+--------+
| NULL               | ./ibdata1                             |            12 |     12582912 | InnoDB |
| NULL               | ./ibtmp1                              |            12 |     12582912 | InnoDB |
| NULL               | ./mysql/engine_cost.ibd               |             0 |            0 | InnoDB |
| NULL               | ./mysql/gtid_executed.ibd             |             0 |            0 | InnoDB |
| NULL               | ./mysql/help_category.ibd             |             0 |            0 | InnoDB |
| NULL               | ./mysql/help_keyword.ibd              |             0 |            0 | InnoDB |
| NULL               | ./mysql/help_relation.ibd             |             0 |            0 | InnoDB |
| NULL               | ./mysql/help_topic.ibd                |             0 |            0 | InnoDB |
| NULL               | ./mysql/innodb_index_stats.ibd        |             0 |            0 | InnoDB |
| NULL               | ./mysql/innodb_table_stats.ibd        |             0 |            0 | InnoDB |
| NULL               | ./mysql/plugin.ibd                    |             0 |            0 | InnoDB |
| NULL               | ./mysql/servers.ibd                   |             0 |            0 | InnoDB |
| NULL               | ./mysql/server_cost.ibd               |             0 |            0 | InnoDB |
| NULL               | ./mysql/slave_master_info.ibd         |             0 |            0 | InnoDB |
| NULL               | ./mysql/slave_relay_log_info.ibd      |             0 |            0 | InnoDB |
| NULL               | ./mysql/slave_worker_info.ibd         |             0 |            0 | InnoDB |
| NULL               | ./mysql/time_zone.ibd                 |             0 |            0 | InnoDB |
| NULL               | ./mysql/time_zone_leap_second.ibd     |             0 |            0 | InnoDB |
| NULL               | ./mysql/time_zone_name.ibd            |             0 |            0 | InnoDB |
| NULL               | ./mysql/time_zone_transition.ibd      |             0 |            0 | InnoDB |
| NULL               | ./mysql/time_zone_transition_type.ibd |             0 |            0 | InnoDB |
| NULL               | ./sys/sys_config.ibd                  |             0 |            0 | InnoDB |
+--------------------+---------------------------------------+---------------+--------------+--------+
22 rows in set (0.00 sec)

How to repeat:
SET @@session.max_length_for_sort_data=10737418241;
SET big_tables=1;
SET SESSION sort_buffer_size=32 * 1024;
SELECT LOGFILE_GROUP_NAME,FILE_NAME,TOTAL_EXTENTS,INITIAL_SIZE,ENGINE FROM INFORMATION_SCHEMA.FILES ORDER BY FILE_NAME;  #Fails 
SET SESSION sort_buffer_size=100 * 1024;
SELECT LOGFILE_GROUP_NAME,FILE_NAME,TOTAL_EXTENTS,INITIAL_SIZE,ENGINE FROM INFORMATION_SCHEMA.FILES ORDER BY FILE_NAME;  #Fails
SET SESSION sort_buffer_size=1024 * 1024;
SELECT LOGFILE_GROUP_NAME,FILE_NAME,TOTAL_EXTENTS,INITIAL_SIZE,ENGINE FROM INFORMATION_SCHEMA.FILES ORDER BY FILE_NAME;  # Works
[10 May 2017 11:17] MySQL Verification Team
Thank you for the bug report.
[12 Jul 2017 14:11] Tor Didriksen
Posted by developer:
 
with a large value for max_length_for_sort_data you are asking filesort to always add all result columns to the data to be sorted. Each row in the sort buffer will be something like:

<FILE_NAME as key><LOGFILE_GROUP_NAME as data><FILE_NAME as data><TOTAL_EXTENTS as data><INITIAL_SIZE as data>,<ENGINE as data>

Plus a few extra bytes.

Looking at the data to be sorted:

mysql> SELECT LOGFILE_GROUP_NAME,FILE_NAME,TOTAL_EXTENTS,INITIAL_SIZE,ENGINE
    -> FROM INFORMATION_SCHEMA.FILES ORDER BY FILE_NAME;
Field   1:  `LOGFILE_GROUP_NAME`
Catalog:    `def`
Database:   `information_schema`
Table:      `FILES`
Org_table:  `FILES`
Type:       VAR_STRING
Collation:  latin1_swedish_ci (8)
Length:     64
Max_length: 0
Decimals:   0
Flags:      

Field   2:  `FILE_NAME`
Catalog:    `def`
Database:   `information_schema`
Table:      `FILES`
Org_table:  `FILES`
Type:       VAR_STRING
Collation:  latin1_swedish_ci (8)
Length:     4000
Max_length: 37
Decimals:   0
Flags:      

Field   3:  `TOTAL_EXTENTS`
Catalog:    `def`
Database:   `information_schema`
Table:      `FILES`
Org_table:  `FILES`
Type:       LONGLONG
Collation:  binary (63)
Length:     4
Max_length: 2
Decimals:   0
Flags:      NUM 

Field   4:  `INITIAL_SIZE`
Catalog:    `def`
Database:   `information_schema`
Table:      `FILES`
Org_table:  `FILES`
Type:       LONGLONG
Collation:  binary (63)
Length:     21
Max_length: 8
Decimals:   0
Flags:      UNSIGNED NUM 

Field   5:  `ENGINE`
Catalog:    `def`
Database:   `information_schema`
Table:      `FILES`
Org_table:  `FILES`
Type:       VAR_STRING
Collation:  latin1_swedish_ci (8)
Length:     64
Max_length: 6
Decimals:   0
Flags:      NOT_NULL 

We see that FILE_NAME has length 4000 bytes, which is multiplied by two (both in key, and as data)

The data can be packed at runtime, if names are shorter, but when allocating the buffer up-front, we are pessimistic, we need space for the full row, and we need space for at least 15 rows, in case we need to spill to disk, and then merge sorted chunks later.

The error message should have been a bit more helpful explaining all this.

If you look at the optimizer trace, you can see things like

  "steps": [
    {
      "creating_tmp_table": {
        "tmp_table_info": {
          "row_length": 14468,
          "key_length": 0,
          "unique_constraint": false,
          "location": "disk (InnoDB)",
          "record_format": "packed"
        }
      }
    },

you have some potentially large rows there, and this is the input to filesort.
[22 Dec 2017 8:30] Erlend Dahl
Closing, based on Tor's comments.
[5 Jan 2018 5:44] Roel Van de Paar
Thank you for the input Tor!