MySQL Bugs: #110275: The number of tables increases, the table creation speed slows down.

Bug #110275	The number of tables increases, the table creation speed slows down.
Submitted:	6 Mar 2023 4:42	Modified:	7 Mar 2023 8:58
Reporter:	xinsong jia	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S5 (Performance)
Version:	8.0.25	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	Table creation performance

Description:
Create a sample table for "create ... like" & "insert ... select", then create tables concurrently(400). After one round is complete, proceed to the next round of creation, create tens of thousands of tables. The time taken to create 400 tables increases as the number of tables in the instance increases, including "create ... like", "create ... like & insert ... values", and "create ... like & insert ... select".Run the command "iostat" and detect that the disk I/O performance and write performance deteriorate.

How to repeat:
#!/bin/bash
PATH=/bin:/sbin:/usr/bin:/usr/sbin:/usr/local/bin:/usr/local/sbin:~/bin
export PATH

if [ ! $1 ]
then
    echo 'Format error.'
    echo "Correct Format: bash $0 dbname round_num table_num host password port"
    exit 1
fi

if [ ! $2 ]
then
    echo 'Format error.'
    echo "Correct Format: bash $0 dbname round_num table_num host password port"
    exit 1
fi

if [ ! $3 ]
then
    echo 'Format error.'
    echo "Correct Format: bash $0 dbname round_num table_num host password port"
    exit 1
fi

if [ ! $4 ]
then
    echo 'Format error.'
    echo "Correct Format: bash $0 dbname round_num table_num host password port"
    exit 1
fi

if [ ! $5 ]
then
    echo 'Format error.'
    echo "Correct Format: bash $0 dbname round_num table_num host password port"
    exit 1
fi

if [ ! $6 ]
then
    echo 'Format error.'
    echo "Correct Format: bash $0 dbname round_num table_num host password port"
    exit 1
fi

# your database name
dbname=$1
# round num
round_num=$2
# table num for concurrent creation every round, total_table_num = round_num * table_num
table_num=$3
# your host
host=$4
# your password
password=$5
# your port
port=$6

echo "drop database if exists ${dbname} ;create database ${dbname} ;use ${dbname} ;create table sample_table (id bigint primary key auto_increment,tinyint1 tinyint default null,smallint1 smallint default null,mediumint1 mediumint default null,ageint int default null,ageinterger1 integer default null,bigint1 bigint default null,float1 float default null,double1 double default null,decimal1 decimal default null,datel date default null,timel time default null,yearl year default null,datetimel datetime default null,timestampl timestamp on update current_timestamp,char1 char default null,varchar1 varchar(255) default null,tinytext1 tinytext default null,text1 text default null,longtext1 longtext default null,tinyblob1 tinyblob default null,blob1 blob default null,longblob1 longblob default null,mediumblob1 mediumblob default null,mediumtext1 mediumtext default null);insert into sample_table(tinyint1,smallint1,mediumint1,ageint,ageinterger1,bigint1,float1,double1,decimal1,datel,timel,yearl,datetimel,char1,varchar1,tinytext1,text1,longtext1,tinyblob1,blob1,longblob1,mediumblob1,mediumtext1)values ...;"  > table_create_$dbname.sql

mysql -h$host -uroot -p$password -P$port -A < table_create_$dbname.sql 

wait

for ((j=1;j<=$round_num;j++))
do
{
    for ((i=1;i<=$table_num;i++))
    do
    {
	mysql -h$host -uroot -p$password -P$port -A -D$dbname -e"create table test${j}_${i} like sample_table;insert into test${j}_${i} select * from sample_table;"  >> /dev/null 2>&1
    }&
    done
    wait
}
done

shell script

Attachment: Create_table.sh (text/x-sh), 59.38 KiB.

Hi Mr. jia,

Thank you for your bug report.

However, we do not understand what is the problem that you report.

If you increase the concurrency significantly, then the performance per thread will deteriorate, due to many semaphores, mutexes, MDL locks etc  ......

If you increase number of tables created, dramatically,  then yes, performance for each new DDL shall get slower and slower, since  the sizes of all the relevant caches are remaining the same.

This is basic knowledge and you can understand it if you read fully our Reference Manual.

Not a bug.

Hello,

Thank you very much for your reply.
As shown in the script, I create the same number of tables concurrently each time (e.g. 400 tables), and the next 400 tables can be created only after the last 400 tables are created, neither increased concurrency nor the number of tables created. As the total number of tables stored in the database instance increases, the time required to create 400 tables increases, is this normal and also because the sizes of all the relevant caches are remaining the same?

Best wishes & regards

Hi Mr. jia,

Yes, this is normal since the number of your tables has surpassed all created table caches. There are several table caches, about which you can read in the Reference Manual.

At certain point, any of those table caches gets filled up, you do not increase their sizes, so the creation speed slows down, naturally !!!

As explained in our Reference Manual.

Not a bug ......