MySQL Bugs: #105735: Partitioned tables will increase memory usage

Bug #105735	Partitioned tables will increase memory usage
Submitted:	29 Nov 2021 11:23	Modified:	29 Nov 2021 13:20
Reporter:	Kane Raymond	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	8.0.19	OS:	Red Hat (7.5)
Assigned to:		CPU Architecture:	x86

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 mysql's 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

Hi,

Please, do not file one report several times.

This report is a total duplicate of the bug #105726.

Duplicate.