Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-24015

SQL Error (1038): Out of sort memory when enough memory for the sort buffer is provided

Details

    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

      Attachments

        Activity

          Ascending order - Click to sort in descending order
          varun Varun Gupta (Inactive) added a comment -

          The patch is present in the branch 10.5-varun

          varun Varun Gupta (Inactive) added a comment - The patch is present in the branch 10.5-varun
          psergei Sergei Petrunia added a comment -

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

          psergei Sergei Petrunia added a comment - Review https://lists.launchpad.net/maria-developers/msg12451.html
          rjasdfiii Rick James added a comment -

          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

          rjasdfiii Rick James added a comment - 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
          serg Sergei Golubchik added a comment -

          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).

          serg Sergei Golubchik added a comment - 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).
          rjasdfiii Rick James added a comment -

          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".

          rjasdfiii Rick James added a comment - 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".
          serg Sergei Golubchik added a comment -

          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.

          serg Sergei Golubchik added a comment - 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.

          People

            varun Varun Gupta (Inactive)
            andrej8080 Andrej Piatnica
            Votes:
            0 Vote for this issue
            Watchers:
            5 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.