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: | |
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
[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!