Bug #105726 Creating partitioned tables will lead to a rapid increase in memory usage of my
Submitted: 27 Nov 2021 10:21 Modified: 29 Nov 2021 13:41
Reporter: Kane Raymond Email Updates:
Status: Unsupported Impact on me:
None 
Category:MySQL Server: Partitions Severity:S3 (Non-critical)
Version:8.0.19 OS:Red Hat (7.5)
Assigned to: CPU Architecture:x86

[27 Nov 2021 10:21] Kane Raymond 
Description:
Hello,everyone
when MySQL keeps creating partition tables, the memory utilization rate of mysqld process will increase rapidly, and the memory utilization rate of mysqld will not decrease after creating partition tables. This makes me very confused. After I delete the partition table of MySQL, the memory utilization of mysqld process will not decrease.Is this a MySQL bug or its own mechanism?
i provided steps to restore the situation I encountered

How to repeat:
1、please execute the SQL  i provided to generate table structure
create database if not exists test character set utf8mb4 collate utf8mb4_bin;
use test;
create table t1(
c1 varchar(100) collate utf8mb4_bin not null ,
c2 varchar(10) collate utf8mb4_bin not null ,
c3 varchar(48) collate utf8mb4_bin not null ,
c4 varchar(2) collate utf8mb4_bin not null ,
c5 varchar(8) collate utf8mb4_bin not null,
c6 varchar(6) collate utf8mb4_bin not null,
c7 varchar(6) collate utf8mb4_bin default null,
c8 varchar(1) collate utf8mb4_bin not null,
c9 varchar(20) collate utf8mb4_bin not null,
c10 varchar(100) collate utf8mb4_bin not null,
c11 varchar(200) collate utf8mb4_bin not null,
c12 varchar(60) collate utf8mb4_bin not null,
c13 varchar(80) collate utf8mb4_bin not null,
c14 varchar(20) collate utf8mb4_bin not null,
c15 varchar(50) collate utf8mb4_bin not null,
c16 varchar(90) collate utf8mb4_bin not null,
c17 varchar(40) collate utf8mb4_bin not null,
c18 varchar(30) collate utf8mb4_bin not null,
c19 varchar(60) collate utf8mb4_bin not null,
c29 varchar(60) collate utf8mb4_bin not null,
primary key(`c1`)
)Engine=Innodb default charset=utf8mb4 collate=utf8mb4_bin
partition by range columns(c1)
(
partition p20200101 values less than ('20200101'),
partition p20200102 values less than ('20200102')
);

create table t2(
c1 varchar(100) collate utf8mb4_bin not null ,
c2 varchar(10) collate utf8mb4_bin not null ,
c3 varchar(48) collate utf8mb4_bin not null ,
c4 varchar(2) collate utf8mb4_bin not null ,
c5 varchar(8) collate utf8mb4_bin not null,
c6 varchar(6) collate utf8mb4_bin not null,
c7 varchar(6) collate utf8mb4_bin default null,
c8 varchar(1) collate utf8mb4_bin not null,
c9 varchar(20) collate utf8mb4_bin not null,
c10 varchar(100) collate utf8mb4_bin not null,
c11 varchar(200) collate utf8mb4_bin not null,
c12 varchar(60) collate utf8mb4_bin not null,
c13 varchar(80) collate utf8mb4_bin not null,
c14 varchar(20) collate utf8mb4_bin not null,
c15 varchar(50) collate utf8mb4_bin not null,
c16 varchar(90) collate utf8mb4_bin not null,
c17 varchar(40) collate utf8mb4_bin not null,
c18 varchar(30) collate utf8mb4_bin not null,
c19 varchar(60) collate utf8mb4_bin not null,
c29 varchar(60) collate utf8mb4_bin not null,
primary key(`c1`)
)Engine=Innodb default charset=utf8mb4 collate=utf8mb4_bin
partition by range columns(c1)
(
partition p20200101 values less than ('20200101'),
partition p20200102 values less than ('20200102')
);

create table t3(
c1 varchar(100) collate utf8mb4_bin not null ,
c2 varchar(10) collate utf8mb4_bin not null ,
c3 varchar(48) collate utf8mb4_bin not null ,
c4 varchar(2) collate utf8mb4_bin not null ,
c5 varchar(8) collate utf8mb4_bin not null,
c6 varchar(6) collate utf8mb4_bin not null,
c7 varchar(6) collate utf8mb4_bin default null,
c8 varchar(1) collate utf8mb4_bin not null,
c9 varchar(20) collate utf8mb4_bin not null,
c10 varchar(100) collate utf8mb4_bin not null,
c11 varchar(200) collate utf8mb4_bin not null,
c12 varchar(60) collate utf8mb4_bin not null,
c13 varchar(80) collate utf8mb4_bin not null,
c14 varchar(20) collate utf8mb4_bin not null,
c15 varchar(50) collate utf8mb4_bin not null,
c16 varchar(90) collate utf8mb4_bin not null,
c17 varchar(40) collate utf8mb4_bin not null,
c18 varchar(30) collate utf8mb4_bin not null,
c19 varchar(60) collate utf8mb4_bin not null,
c29 varchar(60) collate utf8mb4_bin not null,
primary key(`c1`)
)Engine=Innodb default charset=utf8mb4 collate=utf8mb4_bin
partition by range columns(c1)
(
partition p20200101 values less than ('20200101'),
partition p20200102 values less than ('20200102')
);

create table t4(
c1 varchar(100) collate utf8mb4_bin not null ,
c2 varchar(10) collate utf8mb4_bin not null ,
c3 varchar(48) collate utf8mb4_bin not null ,
c4 varchar(2) collate utf8mb4_bin not null ,
c5 varchar(8) collate utf8mb4_bin not null,
c6 varchar(6) collate utf8mb4_bin not null,
c7 varchar(6) collate utf8mb4_bin default null,
c8 varchar(1) collate utf8mb4_bin not null,
c9 varchar(20) collate utf8mb4_bin not null,
c10 varchar(100) collate utf8mb4_bin not null,
c11 varchar(200) collate utf8mb4_bin not null,
c12 varchar(60) collate utf8mb4_bin not null,
c13 varchar(80) collate utf8mb4_bin not null,
c14 varchar(20) collate utf8mb4_bin not null,
c15 varchar(50) collate utf8mb4_bin not null,
c16 varchar(90) collate utf8mb4_bin not null,
c17 varchar(40) collate utf8mb4_bin not null,
c18 varchar(30) collate utf8mb4_bin not null,
c19 varchar(60) collate utf8mb4_bin not null,
c29 varchar(60) collate utf8mb4_bin not null,
primary key(`c1`)
)Engine=Innodb default charset=utf8mb4 collate=utf8mb4_bin
partition by range columns(c1)
(
partition p20200101 values less than ('20200101'),
partition p20200102 values less than ('20200102')
);

create table t5(
c1 varchar(100) collate utf8mb4_bin not null ,
c2 varchar(10) collate utf8mb4_bin not null ,
c3 varchar(48) collate utf8mb4_bin not null ,
c4 varchar(2) collate utf8mb4_bin not null ,
c5 varchar(8) collate utf8mb4_bin not null,
c6 varchar(6) collate utf8mb4_bin not null,
c7 varchar(6) collate utf8mb4_bin default null,
c8 varchar(1) collate utf8mb4_bin not null,
c9 varchar(20) collate utf8mb4_bin not null,
c10 varchar(100) collate utf8mb4_bin not null,
c11 varchar(200) collate utf8mb4_bin not null,
c12 varchar(60) collate utf8mb4_bin not null,
c13 varchar(80) collate utf8mb4_bin not null,
c14 varchar(20) collate utf8mb4_bin not null,
c15 varchar(50) collate utf8mb4_bin not null,
c16 varchar(90) collate utf8mb4_bin not null,
c17 varchar(40) collate utf8mb4_bin not null,
c18 varchar(30) collate utf8mb4_bin not null,
c19 varchar(60) collate utf8mb4_bin not null,
c29 varchar(60) collate utf8mb4_bin not null,
primary key(`c1`)
)Engine=Innodb default charset=utf8mb4 collate=utf8mb4_bin
partition by range columns(c1)
(
partition p20200101 values less than ('20200101'),
partition p20200102 values less than ('20200102')
);

create table t6(
c1 varchar(100) collate utf8mb4_bin not null ,
c2 varchar(10) collate utf8mb4_bin not null ,
c3 varchar(48) collate utf8mb4_bin not null ,
c4 varchar(2) collate utf8mb4_bin not null ,
c5 varchar(8) collate utf8mb4_bin not null,
c6 varchar(6) collate utf8mb4_bin not null,
c7 varchar(6) collate utf8mb4_bin default null,
c8 varchar(1) collate utf8mb4_bin not null,
c9 varchar(20) collate utf8mb4_bin not null,
c10 varchar(100) collate utf8mb4_bin not null,
c11 varchar(200) collate utf8mb4_bin not null,
c12 varchar(60) collate utf8mb4_bin not null,
c13 varchar(80) collate utf8mb4_bin not null,
c14 varchar(20) collate utf8mb4_bin not null,
c15 varchar(50) collate utf8mb4_bin not null,
c16 varchar(90) collate utf8mb4_bin not null,
c17 varchar(40) collate utf8mb4_bin not null,
c18 varchar(30) collate utf8mb4_bin not null,
c19 varchar(60) collate utf8mb4_bin not null,
c29 varchar(60) collate utf8mb4_bin not null,
primary key(`c1`)
)Engine=Innodb default charset=utf8mb4 collate=utf8mb4_bin
partition by range columns(c1)
(
partition p20200101 values less than ('20200101'),
partition p20200102 values less than ('20200102')
);

create table t7(
c1 varchar(100) collate utf8mb4_bin not null ,
c2 varchar(10) collate utf8mb4_bin not null ,
c3 varchar(48) collate utf8mb4_bin not null ,
c4 varchar(2) collate utf8mb4_bin not null ,
c5 varchar(8) collate utf8mb4_bin not null,
c6 varchar(6) collate utf8mb4_bin not null,
c7 varchar(6) collate utf8mb4_bin default null,
c8 varchar(1) collate utf8mb4_bin not null,
c9 varchar(20) collate utf8mb4_bin not null,
c10 varchar(100) collate utf8mb4_bin not null,
c11 varchar(200) collate utf8mb4_bin not null,
c12 varchar(60) collate utf8mb4_bin not null,
c13 varchar(80) collate utf8mb4_bin not null,
c14 varchar(20) collate utf8mb4_bin not null,
c15 varchar(50) collate utf8mb4_bin not null,
c16 varchar(90) collate utf8mb4_bin not null,
c17 varchar(40) collate utf8mb4_bin not null,
c18 varchar(30) collate utf8mb4_bin not null,
c19 varchar(60) collate utf8mb4_bin not null,
c29 varchar(60) collate utf8mb4_bin not null,
primary key(`c1`)
)Engine=Innodb default charset=utf8mb4 collate=utf8mb4_bin
partition by range columns(c1)
(
partition p20200101 values less than ('20200101'),
partition p20200102 values less than ('20200102')
);

create table t8(
c1 varchar(100) collate utf8mb4_bin not null ,
c2 varchar(10) collate utf8mb4_bin not null ,
c3 varchar(48) collate utf8mb4_bin not null ,
c4 varchar(2) collate utf8mb4_bin not null ,
c5 varchar(8) collate utf8mb4_bin not null,
c6 varchar(6) collate utf8mb4_bin not null,
c7 varchar(6) collate utf8mb4_bin default null,
c8 varchar(1) collate utf8mb4_bin not null,
c9 varchar(20) collate utf8mb4_bin not null,
c10 varchar(100) collate utf8mb4_bin not null,
c11 varchar(200) collate utf8mb4_bin not null,
c12 varchar(60) collate utf8mb4_bin not null,
c13 varchar(80) collate utf8mb4_bin not null,
c14 varchar(20) collate utf8mb4_bin not null,
c15 varchar(50) collate utf8mb4_bin not null,
c16 varchar(90) collate utf8mb4_bin not null,
c17 varchar(40) collate utf8mb4_bin not null,
c18 varchar(30) collate utf8mb4_bin not null,
c19 varchar(60) collate utf8mb4_bin not null,
c29 varchar(60) collate utf8mb4_bin not null,
primary key(`c1`)
)Engine=Innodb default charset=utf8mb4 collate=utf8mb4_bin
partition by range columns(c1)
(
partition p20200101 values less than ('20200101'),
partition p20200102 values less than ('20200102')
);

create table t9(
c1 varchar(100) collate utf8mb4_bin not null ,
c2 varchar(10) collate utf8mb4_bin not null ,
c3 varchar(48) collate utf8mb4_bin not null ,
c4 varchar(2) collate utf8mb4_bin not null ,
c5 varchar(8) collate utf8mb4_bin not null,
c6 varchar(6) collate utf8mb4_bin not null,
c7 varchar(6) collate utf8mb4_bin default null,
c8 varchar(1) collate utf8mb4_bin not null,
c9 varchar(20) collate utf8mb4_bin not null,
c10 varchar(100) collate utf8mb4_bin not null,
c11 varchar(200) collate utf8mb4_bin not null,
c12 varchar(60) collate utf8mb4_bin not null,
c13 varchar(80) collate utf8mb4_bin not null,
c14 varchar(20) collate utf8mb4_bin not null,
c15 varchar(50) collate utf8mb4_bin not null,
c16 varchar(90) collate utf8mb4_bin not null,
c17 varchar(40) collate utf8mb4_bin not null,
c18 varchar(30) collate utf8mb4_bin not null,
c19 varchar(60) collate utf8mb4_bin not null,
c29 varchar(60) collate utf8mb4_bin not null,
primary key(`c1`)
)Engine=Innodb default charset=utf8mb4 collate=utf8mb4_bin
partition by range columns(c1)
(
partition p20200101 values less than ('20200101'),
partition p20200102 values less than ('20200102')
);

create table t10(
c1 varchar(100) collate utf8mb4_bin not null ,
c2 varchar(10) collate utf8mb4_bin not null ,
c3 varchar(48) collate utf8mb4_bin not null ,
c4 varchar(2) collate utf8mb4_bin not null ,
c5 varchar(8) collate utf8mb4_bin not null,
c6 varchar(6) collate utf8mb4_bin not null,
c7 varchar(6) collate utf8mb4_bin default null,
c8 varchar(1) collate utf8mb4_bin not null,
c9 varchar(20) collate utf8mb4_bin not null,
c10 varchar(100) collate utf8mb4_bin not null,
c11 varchar(200) collate utf8mb4_bin not null,
c12 varchar(60) collate utf8mb4_bin not null,
c13 varchar(80) collate utf8mb4_bin not null,
c14 varchar(20) collate utf8mb4_bin not null,
c15 varchar(50) collate utf8mb4_bin not null,
c16 varchar(90) collate utf8mb4_bin not null,
c17 varchar(40) collate utf8mb4_bin not null,
c18 varchar(30) collate utf8mb4_bin not null,
c19 varchar(60) collate utf8mb4_bin not null,
c29 varchar(60) collate utf8mb4_bin not null,
primary key(`c1`)
)Engine=Innodb default charset=utf8mb4 collate=utf8mb4_bin
partition by range columns(c1)
(
partition p20200101 values less than ('20200101'),
partition p20200102 values less than ('20200102')
);

2、please running the shell script i provided to add partition 
#!/bin/bash

datebeg='2020-01-03'
dateend='2022-01-06'
beg_s=`date -d "$datebeg" +%s`
end_s=`date -d "$dateend" +%s`

while [ "$beg_s" -le "$end_s" ];do
day=`date -d @$beg_s +"%Y%m%d"`;

beg_s=$((beg_s+86400));
partition_name="p$day"
echo $partition_name

for table_name in `seq 10`
do

table_name="test.t$table_name"
mysql -uroot -p123 -e "alter table $table_name add PARTITION  (PARTITION $partition_name  VALUES LESS THAN ('$day'));"

done

done

3、please running the shell script i provided to to monitor the memory information of mysqld process,and you will see the memory of mysqld process rises rapidly
#!/bin/bash
while true
do
sleep 2
mem_total=$(ps aux|grep -w  [m]ysqld|awk '{print $5 }')
mem_percentage=$(ps aux|grep -w  [m]ysqld|awk '{print $4 }')
time_point=$(date +%Y-%m-%d:%H:%M:%S)
echo "########################################################"
echo "the memory used by mysqld occpied is $mem_total at $time_point"
echo "the memory percentage by mysqld occpied is $mem_percentage at $time_point"

done

Suggested fix:
I have no ability to provide effective advice
[29 Nov 2021 13:18] MySQL Verification Team 
Hi Mr. Raymond,

Thank you for your bug report.

However, this is not a bug in MySQL, but the expected behaviour of the Linux GNU malloc() library.

First of all, you are using a very old release of our 8.0 version. Current release is 8.0.27 and interim there were a large number of memory bugs fixed interim.

Next, since every new partition is also a new tablespace and a new file, increased memory usage is expected. This is tunable, which is explained in our Reference Manual.

Next, it is a known feature of the GNU malloc library on Linux. When the memory is deallocated in the process that uses most of memory, malloc library keeps that memory attached to that process, in order to increase the speed of the next allocation.

Hence, this is not a bug.
[29 Nov 2021 13:41] Kane Raymond 
Hello,MySQL Verification Team
thanks for your reply on time,as you mentioned above
Next, since every new partition is also a new tablespace and a new file, increased memory usage is expected. This is tunable, which is explained in our Reference Manual.

Could you provide a reference manual link about it?i could not find it.thank you very much.
[29 Nov 2021 13:44] MySQL Verification Team 
Hi Mr. Raymond,

We do recommend to every user of MySQL to read entire Reference Manual, at least the parts that you use.

Hence, we are grateful for every report that is filed, but we suppose that reporter has a full knowledge of MySQL server, which presumes reading the entire Manual.

You should at least read the chapter on the InnoDB storage engine and you will find all configuration settings there .........
[20 Jan 14:51] Przemyslaw Malkowski 
Hi,
To add more context to the problem, here is what an example memory overhead of the table cache looks like when I create 300 tables, each having ~700 partitions, and fill the table cache (default 4k) entirely. Tested with 8.0.40, BP=1G.

mysql> select count(*) from information_schema.partitions;
+----------+
| count(*) |
+----------+
|   227893 |
+----------+
1 row in set (0.90 sec)

mysql> show global status like 'Open_table%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| Open_table_definitions | 343   |
| Open_tables            | 4000  |
+------------------------+-------+
2 rows in set (0.00 sec)

mysql> select * from sys.memory_global_total;
+-----------------+
| total_allocated |
+-----------------+
| 10.29 GiB       |
+-----------------+
1 row in set (0.00 sec)

mysql> SELECT EVENT_NAME,format_bytes(CURRENT_NUMBER_OF_BYTES_USED) Mem_used FROM performance_schema.memory_summary_global_by_event_name order by CURRENT_NUMBER_OF_BYTES_USED desc limit 10;
+------------------------------------------+------------+
| EVENT_NAME                               | Mem_used   |
+------------------------------------------+------------+
| memory/sql/TABLE                         | 6.70 GiB   |
| memory/innodb/memory                     | 1.13 GiB   |
| memory/innodb/buf_buf_pool               | 1.02 GiB   |
| memory/innodb/os0event                   | 224.97 MiB |
| memory/performance_schema/file_instances | 154.00 MiB |
| memory/innodb/partitioning               | 122.61 MiB |
| memory/sql/TABLE_SHARE::mem_root         | 117.22 MiB |
| memory/sql/dd::objects                   | 98.49 MiB  |
| memory/innodb/dict0dict                  | 77.50 MiB  |
| memory/innodb/std                        | 61.74 MiB  |
+------------------------------------------+------------+
10 rows in set (0.01 sec)