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;
