MySQL Bugs: #105004: mysql 8.0 parser consuming more memory than 5.7 for similar query

Bug #105004	mysql 8.0 parser consuming more memory than 5.7 for similar query
Submitted:	22 Sep 2021 10:20	Modified:	23 Apr 8:39
Reporter:	Chelluru Vidyadhar	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Parser	Severity:	S2 (Serious)
Version:	8.0.26	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	regression

Description:
While running a large query with many values inside "IN"/"NOT IN" clause, we have noticed that the query causing OOM issue on 8.0. However the same query running as expected in 5.7 versions. Upon further verification using performance_schema memory instruments, we noticed that the amount of memory consumption on mysql 8.0 for same query is far higher than that of 5.7 version. 

We have considered to adjust the value of "parser_max_mem_size" to 10G in both 5.7 and 8.0. We have noticed that the query completed as expected in 5.7 and its consuming approx 5.14 GiB of memory. However the same query failed with error in 8.0 version.

Example:
[root@testbox3 ~]# /home/mysql/mysql8026/bin/mysql -u root -ptemp12345 -S /tmp/mysql8026.sock < query2.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 3170 (HY000) at line 1: Memory capacity of 10737418240 bytes for 'parser_max_mem_size' exceeded. Parser bailed out for this query.
[root@testbox3 ~]#

Configuration:

[mysql@testbox3 ~]$ 
[mysql@testbox3 ~]$ cat mysql5735/my.cnf
[mysqld]
basedir=/home/mysql/mysql5735
datadir=/home/mysql/mysql5735/data
port=5735
socket=/tmp/mysql5735.sock
log-bin
server-id=5735
max_allowed_packet = '1073741824'
innodb_strict_mode = '0'
max_length_for_sort_data = '1024'
performance_schema = '1'

[mysql@testbox3 ~]$ 
[mysql@testbox3 ~]$ cat mysql8026/my.cnf
[mysqld]
basedir=/home/mysql/mysql8026
datadir=/home/mysql/mysql8026/data
port=8026
socket=/tmp/mysql8026.sock
log-bin
server-id=8026
max_allowed_packet = '1073741824'
innodb_strict_mode = '0'
max_length_for_sort_data = '1024'
temptable_max_ram = '8388608'
performance_schema = '1'

[mysql@testbox3 ~]$

5.7.35 (query2)

mysql> select * from sys.memory_by_user_by_current_bytes;
+------------+--------------------+-------------------+-------------------+-------------------+-----------------+
| user       | current_count_used | current_allocated | current_avg_alloc | current_max_alloc | total_allocated |
+------------+--------------------+-------------------+-------------------+-------------------+-----------------+
| mysql5734  |               2006 | 5.14 GiB          | 2.62 MiB          | 4.82 GiB          | 11.85 GiB       |
| background |                  0 | 0 bytes           | 0 bytes           | 0 bytes           | 2.06 KiB        |
+------------+--------------------+-------------------+-------------------+-------------------+-----------------+

8.0.25 (query2)

mysql> select * from sys.memory_by_user_by_current_bytes;
+-----------------+--------------------+-------------------+-------------------+-------------------+-----------------+
| user            | current_count_used | current_allocated | current_avg_alloc | current_max_alloc | total_allocated |
+-----------------+--------------------+-------------------+-------------------+-------------------+-----------------+
| mysql8025       |                 72 | 50.94 GiB         | 724.54 MiB        | 50.63 GiB         | 88.89 GiB       |
| background      |               8990 | 1.87 MiB          |  217 bytes        | 508.70 KiB        | 64.41 MiB       |
+-----------------+--------------------+-------------------+-------------------+-------------------+-----------------+

mysql> select * from performance_schema.memory_summary_by_user_by_event_name where CURRENT_NUMBER_OF_BYTES_USED>0 order by CURRENT_NUMBER_OF_BYTES_USED desc;
+-----------------+-------------------------------------------------+-------------+------------+---------------------------+--------------------------+----------------+--------------------+-----------------+--------------------------+------------------------------+---------------------------+
| USER            | EVENT_NAME                                      | COUNT_ALLOC | COUNT_FREE | SUM_NUMBER_OF_BYTES_ALLOC | SUM_NUMBER_OF_BYTES_FREE | LOW_COUNT_USED | CURRENT_COUNT_USED | HIGH_COUNT_USED | LOW_NUMBER_OF_BYTES_USED | CURRENT_NUMBER_OF_BYTES_USED | HIGH_NUMBER_OF_BYTES_USED |
+-----------------+-------------------------------------------------+-------------+------------+---------------------------+--------------------------+----------------+--------------------+-----------------+--------------------------+------------------------------+---------------------------+
| mysql8025       | memory/sql/THD::main_mem_root                   |          37 |          1 |               36467380536 |                     8200 |              0 |                 36 |              36 |                        0 |                  36467372336 |               36467372336 |
| mysql8025       | memory/sql/NET::buff                            |          22 |         21 |                3864273050 |               3523231891 |              0 |                  1 |               2 |                        0 |                    341041159 |                 676585486 |
| NULL            | memory/sql/dd::objects                          |       13799 |      12076 |                   4281216 |                  3760304 |              0 |               1723 |            4295 |                        0 |                       520912 |                   1226336 |
| NULL            | memory/innodb/memory                            |        2444 |       2198 |                   7145920 |                  6678864 |              0 |                246 |             253 |                        0 |                       467056 |                    523224 |
| NULL            | memory/sql/sp_head::main_mem_root               |          21 |          0 |                    317880 |                        0 |              0 |                 21 |              21 |                        0 |                       317880 |                    317880 |
.
.
+-----------------+-------------------------------------------------+-------------+------------+---------------------------+--------------------------+----------------+--------------------+-----------------+--------------------------+------------------------------+---------------------------+

Thread 1 (Thread 0x400022045060 (LWP 4226)):
#0  0x0000000000f05684 in mem_root_deque<Item*>::add_block_back() ()
#1  0x00000000010dda28 in mem_root_deque<Item*>::push_back(Item* const&) ()
#2  0x00000000010f4f8c in MYSQLparse(THD*, Parse_tree_root**) ()
#3  0x0000000000f1a0bc in THD::sql_parser() ()
#4  0x0000000000f9d600 in parse_sql(THD*, Parser_state*, Object_creation_ctx*) ()
#5  0x0000000000fa2794 in dispatch_sql_command(THD*, Parser_state*) ()
#6  0x0000000000fa429c in dispatch_command(THD*, COM_DATA const*, enum_server_command) ()
#7  0x0000000000fa535c in do_command(THD*) ()
#8  0x00000000010bbc48 in handle_connection ()
#9  0x00000000025582bc in pfs_spawn_thread ()
#10 0x00004000114842ac in start_thread () from /lib64/libpthread.so.0
#11 0x0000400011679e5c in thread_start () from /lib64/libc.so.6

How to repeat:
We can repo the issue by executing query with huge number of values (10M or more) in it. Here, 10M values occupies around 50G memory and lead to OOM on the instance. The overall consumption of memory on 5.7 is very efficient compared to 8.0

Import required data: 
tar -zxf data.tar.gz
mysql -u root -p -e "create database large charset utf8"
mysql -u root -p large < t1.sql

Run queries:
mysql -u root -p < query1.sql
mysql -u root -p < query2.sql

Monitor memory usage from different session: (8.0 uses 10x more memory than 5.7 for same query)
select * from sys.memory_by_user_by_current_bytes;
select * from performance_schema.memory_summary_by_user_by_event_name where CURRENT_NUMBER_OF_BYTES_USED>0 order by CURRENT_NUMBER_OF_BYTES_USED desc;

This file contains compressed queries files and schema file

Attachment: data.tar.gz (application/x-gzip, text), 2.72 MiB.

Hello Chelluru,

Thank you for the report and feedback.
Verified as described.

regards,
Umesh

- Didn't observe any OOM(was on bigger box)
- 5.7.35

mysql> select * from sys.memory_by_user_by_current_bytes;
+------------+--------------------+-------------------+-------------------+-------------------+-----------------+
| user       | current_count_used | current_allocated | current_avg_alloc | current_max_alloc | total_allocated |
+------------+--------------------+-------------------+-------------------+-------------------+-----------------+
| root       |               3448 | 7.71 GiB          | 2.29 MiB          | 7.23 GiB          | 11.86 GiB       |
| background |                  0 | 0 bytes           | 0 bytes           | 0 bytes           | 3.36 KiB        |
+------------+--------------------+-------------------+-------------------+-------------------+-----------------+
2 rows in set (0.01 sec)

- 8.0.26

mysql> select * from sys.memory_by_user_by_current_bytes;
+-----------------+--------------------+-------------------+-------------------+-------------------+-----------------+
| user            | current_count_used | current_allocated | current_avg_alloc | current_max_alloc | total_allocated |
+-----------------+--------------------+-------------------+-------------------+-------------------+-----------------+
| root            |                422 | 75.94 GiB         | 184.27 MiB        | 75.62 GiB         | 275.42 GiB      |
| background      |              11236 | 1.44 MiB          |  134 bytes        | 413.56 KiB        | 198.80 MiB      |
| event_scheduler |                  3 | 16.18 KiB         | 5.39 KiB          | 16.01 KiB         | 16.18 KiB       |
+-----------------+--------------------+-------------------+-------------------+-------------------+-----------------+
3 rows in set (0.00 sec)

Is there any progress on getting this resolved? This is a major blocker for our upgrade from 5.7, and it looks like it recently affected GitHub too: https://github.blog/2023-12-07-upgrading-github-com-to-mysql-8-0/#queries-would-pass-ci-bu....

This is a MAJOR issue for us and is resulting in frequent RDS server crashing and complete outages. AWS has confirmed that this is a known issue affecting multiple customers running MYSQL (not just us).

Refactoring all IN statements to avoid this is not a feasible workaround.

Posted by developer:
 
Added the following note to the MySQL Server 8.0.46, 8.4.9, and 9.7.0 release notes:		

The SQL parser consumed a large amount of memory when parsing very large
queries with many large IN clauses.

As of this release, the SQL parser's memory management has been improved.