MySQL Bugs: #75689: Out of sort memory on ORDER BY(UPDATEXML(....))

Bug #75689	Out of sort memory on ORDER BY(UPDATEXML(....))
Submitted:	30 Jan 2015 4:43	Modified:	13 Nov 2015 4:23
Reporter:	Roel Van de Paar	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Errors	Severity:	S2 (Serious)
Version:	5.1, 5.5, 5.6, 5.7	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
mysql> SELECT 1 FROM t1 ORDER BY(UPDATEXML(a,'1','1'));
ERROR 1038 (HY001): Out of sort memory, consider increasing server sort buffer size

2015-01-30T04:40:05.412054Z 1 [Note] Sort aborted, host: localhost, user: root, thread: 1, error: Out of sort memory, consider increasing server sort buffer size, query: SELECT 1 FROM t1 ORDER BY(UPDATEXML(a,'1','1'))

How to repeat:
DROP DATABASE test;CREATE DATABASE test;USE test;
CREATE TABLE t1(a INT,b POINT NOT NULL,KEY(a));
SET SESSION TRANSACTION READ ONLY,ISOLATION LEVEL REPEATABLE READ;
SET @@session.max_sort_length=10737418241;
SELECT 1 FROM t1 ORDER BY(UPDATEXML(a,'1','1'));

Hello Roel,

Thank you for the report.
Imho this is after Shane's FR Bug #36022 - Per change log entry "for queries that were aborted due to sort problem, the server wrote the message Sort aborted to the error log. Now the server writes more information to provide a more specific message".

Thanks,
Umesh

The question is; technically speaking is it supposed to run out of memory for this particular case? Or, should it be covered by another error? Is it sensible?

Something weird going on.   Take a look at this testcase:

---
\r
drop table if exists t1;
create table t1(a int)engine=innodb;
set session max_sort_length=1024*32;
select @@session.max_sort_length;
select 1 from t1 order by(updatexml(a,'1','1'));
select version();
-----

mysql> drop table if exists t1;
Query OK, 0 rows affected (0.01 sec)

mysql> create table t1(a int)engine=innodb;
Query OK, 0 rows affected (0.02 sec)

mysql> set session max_sort_length=1024*1024;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@session.max_sort_length;
+---------------------------+
| @@session.max_sort_length |
+---------------------------+
|                   1048576 |
+---------------------------+
1 row in set (0.00 sec)

mysql> select 1 from t1 order by(updatexml(a,'1','1'));
ERROR 1038 (HY001): Out of sort memory, consider increasing server sort buffer size
mysql> select version();
+-------------+
| version()   |
+-------------+
| 5.7.9-debug |
+-------------+
1 row in set (0.00 sec)

Thanks Shane!

Please see
http://bugs.mysql.com/bug.php?id=74907

We have to allocate the sort buffer up-front,
and we cannot know the actual size of the rows to be sorted,
nor the actual number of rows to be sorted.

We need to have space for at least 15 rows in the sort buffer,
because that's the max number of chunks we use during merge sort.

With a huge value for max_sort_length, you need at least 15
times that number for sort buffer size.

Hi Tor, 

Thanks! I have some questions (numbered to make it easy to reference);

1) "You do not have access to bug #74907." - so cannot see what it says
2) You mention that the sort buffer needs to be allocated upfront, but isn't it allocated already - i.e. should the max_sort_length not fail (given it's a known set of bytes)?
3) https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_max_sort_lengt...
   Says "The server does not use max_sort_length for integer, decimal, floating-point, and temporal data types."  
   In this case, there is not even any data type being used (select 1).
4) You mention that "we cannot know the actual size of the rows to be sorted, nor the actual number of rows to be sorted."
   and "We need to have space for at least 15 rows in the sort buffer, because that's the max number of chunks we use during merge sort."
   And in the manual I read;
   "The server uses only the first max_sort_length bytes of each [data] value and ignores the rest."
   So does it matter what length the rows are? Or are you saying that the length of rows is that which goes into the sort buffer? Still, how could it be applicable here?
   Also, Shane saw the issue even with 1024*32, so using the 15 it  1024*32*15 = 491520 = 480KB whereas the default for sort_buffer_size is 1MB (though I don't know if Shane reduced the size?)
5) You mention "With a huge value for max_sort_length, you need at least 15 times that number for sort buffer size."
   So, https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_sort_buffer_s...
   gives the formula to estimate memory consumption, but nowhere does it mention max_sort_length?

Btw, the manual on innodb_sort_buffer_size says "Specifies the size of sort buffers used for sorting data during creation of an InnoDB index." and "This sort area is only used for merge sorts during index creation, not during later index maintenance operations. Buffers are deallocated when index creation completes." leading one to believe it's like it says: it's only used for (real) index creations. Then later on it mentions a "typical sort operation" - Should we log a separate bug to explain that the sort buffer is also used elsewhere?

1) Oops, sorry for referring to a non-public bug.
The fix was to always allocate space for at least 15 rows.

2) If you set max_sort_length to such a big number,
you need to adjust the sort buffer size accordingly

3) You are ordering on UPDATEXML(a,'1','1')
In general filesort has no way of knowing the length of the result of a string
function, so it needs to allocate max_sort_length (plus a little overhead)
bytes for each row to sort. Maybe the entire sort operation could
have been optimized away for this particular query (since you select 1)
but it is not.

4) The keys in the sort buffer will be whatever you have in your
order by clause. Shanes numbers confirm my explanation.

5) I will ask our docs people to re-check our manuals.

6) sort_buffer_size is used by filesort, innodb sorting is not relevant here.

Cool, thanks for the info.