| Bug #110421 | MySQL server not honoring connection_memory_limit for user created temp tables | ||
|---|---|---|---|
| Submitted: | 18 Mar 2023 4:02 | Modified: | 26 Mar 2023 8:13 |
| Reporter: | Pranay Motupalli | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Security: Privileges | Severity: | S3 (Non-critical) |
| Version: | 8.0.32 | OS: | Linux |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | MYSQL SERVER, temp tables | ||
[18 Mar 2023 4:04]
Pranay Motupalli
mysql> show variables like '%connection_mem%';
+-----------------------------------+----------------------+
| Variable_name | Value |
+-----------------------------------+----------------------+
| connection_memory_chunk_size | 8912 |
| connection_memory_limit | 50000000 |
| global_connection_memory_limit | 18446744073709551615 |
| global_connection_memory_tracking | ON |
+-----------------------------------+----------------------+
4 rows in set (0.00 sec)
mysql> create temporary table test.mem_tab(id bigint(20),notes varchar(200))engine=MEMORY;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> insert into test.mem_tab select * from test.mid_tab limit 10000;
Query OK, 10000 rows affected (0.01 sec)
Records: 10000 Duplicates: 0 Warnings: 0
mysql> select * from sys.memory_by_thread_by_current_bytes where thread_id=107\G
*************************** 1. row ***************************
thread_id: 107
user: test@localhost
current_count_used: 147
current_allocated: 9.17 MiB
current_avg_alloc: 63.87 KiB
current_max_alloc: 7.96 MiB
total_allocated: 9.43 MiB
1 row in set (0.00 sec)
mysql> insert into test.mem_tab select * from test.mid_tab limit 10000;
Query OK, 10000 rows affected (0.02 sec)
Records: 10000 Duplicates: 0 Warnings: 0
mysql> select * from sys.memory_by_thread_by_current_bytes where thread_id=107\G
*************************** 1. row ***************************
thread_id: 107
user: test@localhost
current_count_used: 211
current_allocated: 17.00 MiB
current_avg_alloc: 82.52 KiB
current_max_alloc: 15.79 MiB
total_allocated: 17.44 MiB
1 row in set (0.00 sec)
mysql> insert into test.mem_tab select * from test.mid_tab limit 10000;
Query OK, 10000 rows affected (0.01 sec)
Records: 10000 Duplicates: 0 Warnings: 0
mysql> select * from sys.memory_by_thread_by_current_bytes where thread_id=107\G
*************************** 1. row ***************************
thread_id: 107
user: test@localhost
current_count_used: 275
current_allocated: 24.84 MiB
current_avg_alloc: 92.49 KiB
current_max_alloc: 23.62 MiB
total_allocated: 25.44 MiB
1 row in set (0.00 sec)
mysql> insert into test.mem_tab select * from test.mid_tab limit 10000;
Query OK, 10000 rows affected (0.02 sec)
Records: 10000 Duplicates: 0 Warnings: 0
mysql> select * from sys.memory_by_thread_by_current_bytes where thread_id=107\G
*************************** 1. row ***************************
thread_id: 107
user: test@localhost
current_count_used: 339
current_allocated: 32.67 MiB
current_avg_alloc: 98.69 KiB
current_max_alloc: 31.46 MiB
total_allocated: 33.45 MiB
1 row in set (0.00 sec)
mysql> insert into test.mem_tab select * from test.mid_tab limit 10000;
Query OK, 10000 rows affected (0.01 sec)
Records: 10000 Duplicates: 0 Warnings: 0
mysql> select * from sys.memory_by_thread_by_current_bytes where thread_id=107\G
*************************** 1. row ***************************
thread_id: 107
user: test@localhost
current_count_used: 403
current_allocated: 40.51 MiB
current_avg_alloc: 102.93 KiB
current_max_alloc: 39.29 MiB
total_allocated: 41.45 MiB
1 row in set (0.01 sec)
mysql> insert into test.mem_tab select * from test.mid_tab limit 10000;
Query OK, 10000 rows affected (0.02 sec)
Records: 10000 Duplicates: 0 Warnings: 0
mysql> select * from sys.memory_by_thread_by_current_bytes where thread_id=107\G
*************************** 1. row ***************************
thread_id: 107
user: test@localhost
current_count_used: 467
current_allocated: 48.34 MiB
current_avg_alloc: 106.00 KiB
current_max_alloc: 47.13 MiB
total_allocated: 49.46 MiB
1 row in set (0.00 sec)
mysql> insert into test.mem_tab select * from test.mid_tab limit 10000;
Query OK, 10000 rows affected (0.02 sec)
Records: 10000 Duplicates: 0 Warnings: 0
mysql> select * from sys.memory_by_thread_by_current_bytes where thread_id=107\G
*************************** 1. row ***************************
thread_id: 107
user: test@localhost
current_count_used: 531
current_allocated: 56.18 MiB
current_avg_alloc: 108.33 KiB
current_max_alloc: 54.96 MiB
total_allocated: 57.46 MiB
1 row in set (0.00 sec)
mysql> insert into test.mem_tab select * from test.mid_tab limit 10000;
Query OK, 10000 rows affected (0.02 sec)
Records: 10000 Duplicates: 0 Warnings: 0
mysql> select * from sys.memory_by_thread_by_current_bytes where thread_id=107\G
*************************** 1. row ***************************
thread_id: 107
user: test@localhost
current_count_used: 595
current_allocated: 64.01 MiB
current_avg_alloc: 110.16 KiB
current_max_alloc: 62.80 MiB
total_allocated: 65.47 MiB
1 row in set (0.00 sec)
mysql> insert into test.mem_tab select * from test.mid_tab limit 10000;
Query OK, 10000 rows affected (0.02 sec)
Records: 10000 Duplicates: 0 Warnings: 0
mysql> select * from sys.memory_by_thread_by_current_bytes where thread_id=107\G
*************************** 1. row ***************************
thread_id: 107
user: test@localhost
current_count_used: 659
current_allocated: 71.84 MiB
current_avg_alloc: 111.64 KiB
current_max_alloc: 70.63 MiB
total_allocated: 73.48 MiB
1 row in set (0.00 sec)
mysql> insert into test.mem_tab select * from test.mid_tab limit 10000;
Query OK, 10000 rows affected (0.02 sec)
Records: 10000 Duplicates: 0 Warnings: 0
mysql> select * from sys.memory_by_thread_by_current_bytes where thread_id=107\G
*************************** 1. row ***************************
thread_id: 107
user: test@localhost
current_count_used: 724
current_allocated: 79.80 MiB
current_avg_alloc: 112.87 KiB
current_max_alloc: 78.59 MiB
total_allocated: 81.60 MiB
1 row in set (0.00 sec)
mysql> insert into test.mem_tab select * from test.mid_tab limit 10000;
Query OK, 10000 rows affected (0.02 sec)
Records: 10000 Duplicates: 0 Warnings: 0
mysql> select * from sys.memory_by_thread_by_current_bytes where thread_id=107\G
*************************** 1. row ***************************
thread_id: 107
user: test@localhost
current_count_used: 788
current_allocated: 87.64 MiB
current_avg_alloc: 113.88 KiB
current_max_alloc: 86.42 MiB
total_allocated: 89.61 MiB
1 row in set (0.01 sec)
mysql> show variables like '%connection_mem%';
+-----------------------------------+----------------------+
| Variable_name | Value |
+-----------------------------------+----------------------+
| connection_memory_chunk_size | 8912 |
| connection_memory_limit | 50000000 |
| global_connection_memory_limit | 18446744073709551615 |
| global_connection_memory_tracking | ON |
+-----------------------------------+----------------------+
4 rows in set (0.01 sec)
[24 Mar 2023 7:05]
huahua xu
Hi, Pranay Motupalli:
The heap storage engine does not enable collecting the memory consumed by threads. For its PSI_memory_key:
```
static PSI_memory_info all_heap_memory[] = {
{&hp_key_memory_HP_SHARE, "HP_SHARE", 0, 0, PSI_DOCUMENT_ME},
{&hp_key_memory_HP_INFO, "HP_INFO", 0, 0, PSI_DOCUMENT_ME},
{&hp_key_memory_HP_PTRS, "HP_PTRS", 0, 0, PSI_DOCUMENT_ME},
{&hp_key_memory_HP_KEYDEF, "HP_KEYDEF", 0, 0, PSI_DOCUMENT_ME}};
```
They do not allow PFS to limit memory usage by the flag `PSI_FLAG_MEM_COLLECT`.
[24 Mar 2023 16:34]
Pranay Motupalli
Thanks for your response. If that is an expected behavior, please document the list of memory allocation actions that are not counted towards this memory limit. I don't see this clause mentioned anywhere in the documentation.
[26 Mar 2023 8:13]
MySQL Verification Team
Hello Pranay, Thank you for the report and feedback. Leaving the category as is for now. regards, Umesh
Description: When an user created temptable with storage engine=Memory is created on user connection with connection_memory limit enforced, engine allows memory allocation on a user thread beyond the connection_memory limit. How to repeat: [ec2-user@ip-172-31-54-9 ~]$ /home/ec2-user/opt/mysql/8.0.32/bin/mysql -utest -p -P8032 -h127.0.0.1 Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 59 Server version: 8.0.32 MySQL Community Server - GPL Copyright (c) 2000, 2023, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show variables like '%connection_mem%'; +-----------------------------------+----------------------+ | Variable_name | Value | +-----------------------------------+----------------------+ | connection_memory_chunk_size | 8912 | | connection_memory_limit | 50000000 | | global_connection_memory_limit | 18446744073709551615 | | global_connection_memory_tracking | ON | +-----------------------------------+----------------------+ 4 rows in set (0.01 sec) mysql [localhost:8032] {msandbox} ((none)) > show variables like '%max_heap%'; +---------------------+----------------------+ | Variable_name | Value | +---------------------+----------------------+ | max_heap_table_size | 18446744073709550592 | +---------------------+----------------------+ 1 row in set (0.00 sec) mysql> show grants; +----------------------------------------------------------------------------+ | Grants for test@% | +----------------------------------------------------------------------------+ | GRANT SELECT, INSERT, CREATE, CREATE TEMPORARY TABLES ON *.* TO `test`@`%` | +----------------------------------------------------------------------------+ 1 row in set (0.00 sec) ### create seed to fill into temp table mysql> create table test.mid_tab(id bigint(20),notes varchar(200)); Query OK, 0 rows affected, 1 warning (0.02 sec) mysql> insert into test.mid_tab values(1,'fwaefawews'); Query OK, 1 row affected (0.01 sec) mysql> insert into test.mid_tab select * from test.mid_tab; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> insert into test.mid_tab select * from test.mid_tab; Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> insert into test.mid_tab select * from test.mid_tab; Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> insert into test.mid_tab select * from test.mid_tab; Query OK, 8 rows affected (0.00 sec) Records: 8 Duplicates: 0 Warnings: 0 mysql> insert into test.mid_tab select * from test.mid_tab; Query OK, 16 rows affected (0.00 sec) Records: 16 Duplicates: 0 Warnings: 0 mysql> insert into test.mid_tab select * from test.mid_tab; Query OK, 32 rows affected (0.00 sec) Records: 32 Duplicates: 0 Warnings: 0 mysql> insert into test.mid_tab select * from test.mid_tab; Query OK, 64 rows affected (0.01 sec) Records: 64 Duplicates: 0 Warnings: 0 mysql> insert into test.mid_tab select * from test.mid_tab; Query OK, 128 rows affected (0.01 sec) Records: 128 Duplicates: 0 Warnings: 0 mysql> insert into test.mid_tab select * from test.mid_tab; Query OK, 256 rows affected (0.01 sec) Records: 256 Duplicates: 0 Warnings: 0 mysql> insert into test.mid_tab select * from test.mid_tab; Query OK, 512 rows affected (0.01 sec) Records: 512 Duplicates: 0 Warnings: 0 mysql> insert into test.mid_tab select * from test.mid_tab; Query OK, 1024 rows affected (0.01 sec) Records: 1024 Duplicates: 0 Warnings: 0 mysql> insert into test.mid_tab select * from test.mid_tab; Query OK, 2048 rows affected (0.02 sec) Records: 2048 Duplicates: 0 Warnings: 0 mysql> insert into test.mid_tab select * from test.mid_tab; Query OK, 4096 rows affected (0.04 sec) Records: 4096 Duplicates: 0 Warnings: 0 mysql> exit Bye ### Create temp table and insert the values from above seed into temp table [ec2-user@ip-172-31-54-9 ~]$ /home/ec2-user/opt/mysql/8.0.32/bin/mysql -utest -p -P8032 -h127.0.0.1 Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 68 Server version: 8.0.32 MySQL Community Server - GPL Copyright (c) 2000, 2023, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show variables like '%connection_mem%'; +-----------------------------------+----------------------+ | Variable_name | Value | +-----------------------------------+----------------------+ | connection_memory_chunk_size | 8912 | | connection_memory_limit | 50000000 | | global_connection_memory_limit | 18446744073709551615 | | global_connection_memory_tracking | OFF | +-----------------------------------+----------------------+ 4 rows in set (0.00 sec) mysql> create temporary table test.mem_tab(id bigint(20),notes varchar(200))engine=MEMORY; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> select * from sys.memory_by_thread_by_current_bytes where thread_id=105; Empty set (0.00 sec) mysql> select * from sys.memory_by_thread_by_current_bytes where thread_id=106\G *************************** 1. row *************************** thread_id: 106 user: test@localhost current_count_used: 79 current_allocated: 1.23 MiB current_avg_alloc: 15.92 KiB current_max_alloc: 1.00 MiB total_allocated: 1.57 MiB 1 row in set (0.00 sec) mysql> insert into test.mem_tab select * from test.mid_tab limit 10000; Query OK, 10000 rows affected (0.02 sec) Records: 10000 Duplicates: 0 Warnings: 0 mysql> select * from sys.memory_by_thread_by_current_bytes where thread_id=106\G *************************** 1. row *************************** thread_id: 106 user: test@localhost current_count_used: 147 current_allocated: 9.17 MiB current_avg_alloc: 63.87 KiB current_max_alloc: 7.96 MiB total_allocated: 9.70 MiB 1 row in set (0.00 sec) mysql> insert into test.mem_tab select * from test.mid_tab limit 10000; Query OK, 10000 rows affected (0.01 sec) Records: 10000 Duplicates: 0 Warnings: 0 mysql> select * from sys.memory_by_thread_by_current_bytes where thread_id=106\G *************************** 1. row *************************** thread_id: 106 user: test@localhost current_count_used: 211 current_allocated: 17.00 MiB current_avg_alloc: 82.52 KiB current_max_alloc: 15.79 MiB total_allocated: 17.71 MiB 1 row in set (0.00 sec) mysql> insert into test.mem_tab select * from test.mid_tab limit 10000; Query OK, 10000 rows affected (0.02 sec) Records: 10000 Duplicates: 0 Warnings: 0 mysql> select * from sys.memory_by_thread_by_current_bytes where thread_id=106\G *************************** 1. row *************************** thread_id: 106 user: test@localhost current_count_used: 275 current_allocated: 24.84 MiB current_avg_alloc: 92.49 KiB current_max_alloc: 23.62 MiB total_allocated: 25.71 MiB 1 row in set (0.00 sec) mysql> insert into test.mem_tab select * from test.mid_tab limit 10000; Query OK, 10000 rows affected (0.01 sec) Records: 10000 Duplicates: 0 Warnings: 0 mysql> select * from sys.memory_by_thread_by_current_bytes where thread_id=106\G *************************** 1. row *************************** thread_id: 106 user: test@localhost current_count_used: 339 current_allocated: 32.67 MiB current_avg_alloc: 98.69 KiB current_max_alloc: 31.46 MiB total_allocated: 33.72 MiB 1 row in set (0.00 sec) mysql> insert into test.mem_tab select * from test.mid_tab limit 10000; Query OK, 10000 rows affected (0.01 sec) Records: 10000 Duplicates: 0 Warnings: 0 mysql> select * from sys.memory_by_thread_by_current_bytes where thread_id=106\G *************************** 1. row *************************** thread_id: 106 user: test@localhost current_count_used: 403 current_allocated: 40.51 MiB current_avg_alloc: 102.93 KiB current_max_alloc: 39.29 MiB total_allocated: 41.72 MiB 1 row in set (0.00 sec) mysql> insert into test.mem_tab select * from test.mid_tab limit 10000; Query OK, 10000 rows affected (0.02 sec) Records: 10000 Duplicates: 0 Warnings: 0 mysql> select * from sys.memory_by_thread_by_current_bytes where thread_id=106\G *************************** 1. row *************************** thread_id: 106 user: test@localhost current_count_used: 467 current_allocated: 48.34 MiB current_avg_alloc: 106.00 KiB current_max_alloc: 47.13 MiB total_allocated: 49.73 MiB 1 row in set (0.00 sec) mysql> insert into test.mem_tab select * from test.mid_tab limit 10000; Query OK, 10000 rows affected (0.02 sec) Records: 10000 Duplicates: 0 Warnings: 0 mysql> select * from sys.memory_by_thread_by_current_bytes where thread_id=106\G *************************** 1. row *************************** thread_id: 106 user: test@localhost current_count_used: 531 current_allocated: 56.18 MiB current_avg_alloc: 108.33 KiB current_max_alloc: 54.96 MiB total_allocated: 57.73 MiB 1 row in set (0.00 sec) mysql> insert into test.mem_tab select * from test.mid_tab limit 10000; Query OK, 10000 rows affected (0.02 sec) Records: 10000 Duplicates: 0 Warnings: 0 mysql> select * from sys.memory_by_thread_by_current_bytes where thread_id=106\G *************************** 1. row *************************** thread_id: 106 user: test@localhost current_count_used: 595 current_allocated: 64.01 MiB current_avg_alloc: 110.16 KiB current_max_alloc: 62.80 MiB total_allocated: 65.74 MiB 1 row in set (0.00 sec) mysql> insert into test.mem_tab select * from test.mid_tab limit 10000; Query OK, 10000 rows affected (0.02 sec) Records: 10000 Duplicates: 0 Warnings: 0 mysql> select * from sys.memory_by_thread_by_current_bytes where thread_id=106\G *************************** 1. row *************************** thread_id: 106 user: test@localhost current_count_used: 659 current_allocated: 71.84 MiB current_avg_alloc: 111.64 KiB current_max_alloc: 70.63 MiB total_allocated: 73.75 MiB 1 row in set (0.00 sec) mysql> insert into test.mem_tab select * from test.mid_tab limit 10000; Query OK, 10000 rows affected (0.02 sec) Records: 10000 Duplicates: 0 Warnings: 0 mysql> select * from sys.memory_by_thread_by_current_bytes where thread_id=106\G *************************** 1. row *************************** thread_id: 106 user: test@localhost current_count_used: 724 current_allocated: 79.80 MiB current_avg_alloc: 112.87 KiB current_max_alloc: 78.59 MiB total_allocated: 81.87 MiB 1 row in set (0.01 sec) mysql> insert into test.mem_tab select * from test.mid_tab limit 10000; Query OK, 10000 rows affected (0.02 sec) Records: 10000 Duplicates: 0 Warnings: 0 mysql> select * from sys.memory_by_thread_by_current_bytes where thread_id=107\G *************************** 1. row *************************** thread_id: 107 user: test@localhost current_count_used: 788 current_allocated: 87.64 MiB. <----- where the limit is 50 MB current_avg_alloc: 113.88 KiB current_max_alloc: 86.42 MiB total_allocated: 89.61 MiB 1 row in set (0.01 sec) Suggested fix: Enforce the limit for user created temp table as well.