[MDEV-24015] SQL Error (1038): Out of sort memory when enough memory for the sort buffer is provided Created: 2020-10-23  Updated: 2021-04-26  Resolved: 2020-10-28

Status: Closed
Project: MariaDB Server
Component/s: Configuration, Optimizer, Storage Engine - InnoDB
Affects Version/s: 10.5.6
Fix Version/s: 10.5.7

Type: Bug Priority: Blocker
Reporter: Andrej Piatnica Assignee: Varun Gupta (Inactive)
Resolution: Fixed Votes: 0
Labels: innodb, optimizer
Environment:

Windows Server 2012R2 verson 6.3; Xeon E3-1225@3.2Ghz; 16GB ram

Attachments: File settings-10.3.13     File settings-10.5.6    

 Description   

Decided to upgrade older 10.3.13 with the currently latest version 10.5.6.

One of my sql selects started throwing SQL Error (1038): Out of sort memory.

Versions are running on the same machine (sharing the same port, so just one instance is running at a time). Did mysqldump to have identical conditions. Using default settings (attached system variables for both versions).

Need to set sort_buffer_size to ~120MB to make script working again while older one works well with default 2MB.

I managed to simlify the script while still keeping an error (I understand it does not makes sense now, its really a shortened version so I could share something with you):

DROP TABLE IF EXISTS test_products;
 
CREATE TABLE test_products (
 
    product_name VARCHAR(7),
 
    id_country INT,
 
    PRIMARY KEY (product_name, id_country)
 
) COLLATE='utf8_general_ci' ENGINE=INNODB;
 
 
 
DELIMITER //
 
FOR i IN 1..3700 DO INSERT INTO test_products (product_name, id_country) VALUES ('product', i); END FOR; //
 
DELIMITER ;
 
 
 
SELECT p.product_name, p.id_country, hierarchy.hierarchy
 
FROM test_products p
 
LEFT JOIN (
 
    SELECT p2.product_name, p2.id_country, h.hierarchy, MAX(test) AS test
 
    FROM test_products p2
 
    LEFT JOIN (
 
        SELECT product_name, id_country, '_exactly_' AS hierarchy, 1 AS test -- removing one single character from string '_exactly_' will stop producing error
 
        FROM test_products
 
    ) h ON h.product_name = p2.product_name AND h.id_country = p2.id_country
 
   GROUP BY product_name, id_country, h.hierarchy
 
) hierarchy ON hierarchy.product_name = p.product_name AND hierarchy.id_country = p.id_country

Is this behaviour correct?
Is increasing sort_buffer_size a recommended way to make the script working again?
Thank you

 Comments   
Comment by Varun Gupta (Inactive) [ 2020-10-26 ]

The patch is present in the branch 10.5-varun

Comment by Sergei Petrunia [ 2020-10-27 ]

Review https://lists.launchpad.net/maria-developers/msg12451.html

Comment by Rick James [ 2021-04-24 ]

I do not accept the answer of "change a setting because of an optimizer improvement".

https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-20.html –

"One effect of this enhancement is that it is now possible for Out of memory errors to occur when trying to sort rows containing very large (multi-megabtye) JSON or GEOMETRY column values if the sort buffers are of insufficient size; this can be compensated for in the usual fashion by increasing the value of the sort_buffer_size system variable. (Bug #30400985, Bug #30804356)"

This error seems to be caused by an "improvement" in the Optimizer. The "right" way to deal with the error is to do one of these:

  • Use statistics on the table to conservatively estimate that the JSON (etc) could be too big to avoid the error, then use the old sorting method. MariaDB might have better stats (histogram) than Oracle?
    Or
  • Catch the "out of memory" and revert to the old way.

One reverts unnecessarily in some cases (where the stats are not precise enough); the other has to undo some work (making it slower).

Here is another test case: https://stackoverflow.com/questions/67242726/mysql-8-order-by-created-at-out-of-sort-memory-error-1038

See also https://bugs.mysql.com/bug.php?id=103225

Comment by Sergei Golubchik [ 2021-04-26 ]

rjasdfiii, I don't understand. Are you complaining about what MySQL developers did? Then it seems to be a wrong bug tracker for that. Or are you saying that we should not do the same? We don't, didn't — this bug was fixed in 10.5.7 with the commit db56f9b852, at no point in time the message was "change a setting because of an optimizer improvement" (and it wouldn't have helped anyway).

Comment by Rick James [ 2021-04-26 ]

Sergei - My apologies if I misspoke.

I understood that Oracle and MariaDB sometimes "share" code or fixes. When I saw the MySQL regression, I went to MariaDB to see if the "improvement" had been also revise the optimizer), I wanted you to do a better job than that. I can't tell from this workflow whether the problem was really introduced, nor whether it was "fixed".

Comment by Sergei Golubchik [ 2021-04-26 ]

No problem. Don't worry, this time it was our fix. The problem was really fixed and a new test will ensure it won't happen again.

Generated at Thu Feb 08 09:26:48 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.