MySQL Bugs: #90209: Performance regression with > 15K tables in MySQL 8.0 (with general tablespaces)

Bug #90209	Performance regression with > 15K tables in MySQL 8.0 (with general tablespaces)
Submitted:	24 Mar 2018 21:19	Modified:	27 Aug 2019 8:12
Reporter:	Alexander Rubin	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S5 (Performance)
Version:	8.0, 8.0.17	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
When comparing performance between MySQL 8.0 and MySQL 5.7 with many tables, I can see significant performance regression when using MySQL 8.0 general tablespaces.
I'm using sysbench to do a test and both readonly and read-write test shows performance regression:

mysql 8.0.4: transactions:                        146693 (1831.34 per sec.)
mysql 5.7: transactions:                          1716584 (21456.24 per sec.)

Regression only shows up when we increase the number of tables / schemas beyond 5K databases (=15K tables), we are using 3 tables in each database:

1500 databases (x3 tables)

8:   3879485 (48489.42 per sec.)
5.7: 3482584 (43530.00 per sec.)

3,000 databases:
  8: 3327284 (41589.30 per sec.)
5.7: 3343915 (41795.68 per sec.)

5,000 databases:
    8:1718052 (21473.69 per sec.) 
5.7: 2597348 (32464.42 per sec.)

How to repeat:
1. Create 10K or more databases. In MySQL 8 use general tablespaces and create 1 tablespace per database, assign tables to the tablespace. Each database can have many tables but we will only use 3 of them

Create db sample script:

#!/bin/bash

function do_db {
    mysql -vvv -e "create database $db";
    mysql $db < schema.sql
}

c=0
for m in {1..20000} 
do
    for i in {1..50} 
    do    
        let c=$c+1
        echo $c    
        db="customer_$c"
        do_db &
    done
    wait
done

2. Run sysbench script:

sysbench.sh:

conn=" --db-driver=mysql --mysql-host=127.0.0.1 --mysql-db=test --mysql-user=root --mysql-password= "
sysbench $conn --report-interval=1 --num-threads=16 --max-requests=0 --max-time=300 --test=select_custom.lua run | tee -a sysbench_1.txt

select_custom.lua:
pathtest = string.match(test, "(.*/)") or ""

function event(thread_id)
   local dbid = sb_rand(1, 1000)
   local id = sb_rand(1, 155)
   
   rs = db_query("select * from customer_" .. dbid .. ".tbl where id = " .. id)
end

Suggested fix:
Fix the regression

Hello Alexander,

Thank you for the report and feedback.
Could you please provide "schema.sql" which is referred in the test case? Also, I see your create db sample script will create 1M db's, do we need 1M or just 20K enough? Do we need general tablespaces just for 8.0 or 5.7 also? Please let me know.

Thanks,
Umesh

Thank you Umesh,

20K schemas is enough.
This test is comparing general tablespaces in 8.0 to non-general tablespaces in 5.7. (The reason for that is in 8.0 there are no frm files)
Schemas can be anything, I've used drupal schema before but can be 150 sbtest schema. I can share those if needed.

Thank you Alexander, for the details and clarification. I'll give it a try and get back to you on this.

Regards,
Umesh

More mutex contention recorded when doing inserts across many random tables:

----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 6394947
--Thread 139661471680256 has waited at ha_innodb.cc line 5807 for 0.00 seconds the semaphore:
Mutex at 0x7f06adf117b8, Mutex DICT_SYS created dict0dict.cc:1020, lock var 1

--Thread 139666569660160 has waited at fil0fil.cc line 5548 for 0.00 seconds the semaphore:
Mutex at 0x7f06ac2dacc8, Mutex FIL_SHARD created fil0fil.cc:1712, lock var 1

OS WAIT ARRAY INFO: signal count 5938114
RW-shared spins 6409719, rounds 7668237, OS waits 1237435
RW-excl spins 10378730, rounds 142025636, OS waits 2727649
RW-sx spins 1468548, rounds 12568969, OS waits 155302
Spin rounds per wait: 1.20 RW-shared, 13.68 RW-excl, 8.56 RW-sx

----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 6424133
--Thread 139666569660160 has waited at ha_innodb.cc line 6073 for 0.00 seconds the semaphore:
Mutex at 0x7f06adf117b8, Mutex DICT_SYS created dict0dict.cc:1020, lock var 1

--Thread 139661603579648 has waited at dict0dict.cc line 5343 for 0.00 seconds the semaphore:
Mutex at 0x7f06aefcd1a8, Mutex DICT_PERSIST_DIRTY_TABLES created dict0dict.cc:5122, lock var 1

--Thread 139666700797696 has waited at ha_innodb.cc line 6073 for 0.00 seconds the semaphore:
Mutex at 0x7f06adf117b8, Mutex DICT_SYS created dict0dict.cc:1020, lock var 1

--Thread 139661471971072 has waited at ha_innodb.cc line 6073 for 0.00 seconds the semaphore:
Mutex at 0x7f06adf117b8, Mutex DICT_SYS created dict0dict.cc:1020, lock var 1

--Thread 139661604161280 has waited at dict0dict.cc line 5299 for 0.00 seconds the semaphore:
Mutex at 0x7f06aefcd1a8, Mutex DICT_PERSIST_DIRTY_TABLES created dict0dict.cc:5122, lock var 1

--Thread 139666568206080 has waited at dict0dict.cc line 5343 for 0.00 seconds the semaphore:
Mutex at 0x7f06aefcd1a8, Mutex DICT_PERSIST_DIRTY_TABLES created dict0dict.cc:5122, lock var 1

--Thread 139661468899072 has waited at dict0dd.cc line 3487 for 0.00 seconds the semaphore:
Mutex at 0x7f06adf117b8, Mutex DICT_SYS created dict0dict.cc:1020, lock var 1

--Thread 139661602998016 has waited at dict0dict.cc line 5343 for 0.00 seconds the semaphore:
Mutex at 0x7f06aefcd1a8, Mutex DICT_PERSIST_DIRTY_TABLES created dict0dict.cc:5122, lock var 1

--Thread 139661605033728 has waited at ha_innodb.cc line 6073 for 0.00 seconds the semaphore:
Mutex at 0x7f06adf117b8, Mutex DICT_SYS created dict0dict.cc:1020, lock var 1

--Thread 139661602125568 has waited at ha_innodb.cc line 6073 for 0.00 seconds the semaphore:
Mutex at 0x7f06adf117b8, Mutex DICT_SYS created dict0dict.cc:1020, lock var 1

--Thread 139666569369344 has waited at ha_innodb.cc line 6073 for 0.00 seconds the semaphore:
Mutex at 0x7f06adf117b8, Mutex DICT_SYS created dict0dict.cc:1020, lock var 1

--Thread 139666570417920 has waited at ha_innodb.cc line 6073 for 0.00 seconds the semaphore:
Mutex at 0x7f06adf117b8, Mutex DICT_SYS created dict0dict.cc:1020, lock var 1

--Thread 139666565588736 has waited at ha_innodb.cc line 6073 for 0.00 seconds the semaphore:
Mutex at 0x7f06adf117b8, Mutex DICT_SYS created dict0dict.cc:1020, lock var 1

--Thread 139661599799040 has waited at ha_innodb.cc line 6073 for 0.00 seconds the semaphore:
Mutex at 0x7f06adf117b8, Mutex DICT_SYS created dict0dict.cc:1020, lock var 1

--Thread 139661601543936 has waited at ha_innodb.cc line 6073 for 0.00 seconds the semaphore:
Mutex at 0x7f06adf117b8, Mutex DICT_SYS created dict0dict.cc:1020, lock var 1

--Thread 139661471680256 has waited at dict0dict.cc line 5343 for 0.00 seconds the semaphore:
Mutex at 0x7f06aefcd1a8, Mutex DICT_PERSIST_DIRTY_TABLES created dict0dict.cc:5122, lock var 1

OS WAIT ARRAY INFO: signal count 5965623
RW-shared spins 6452582, rounds 7713082, OS waits 1239362
RW-excl spins 10393412, rounds 142083486, OS waits 2728127
RW-sx spins 1468548, rounds 12568969, OS waits 155302
Spin rounds per wait: 1.20 RW-shared, 13.67 RW-excl, 8.56 RW-sx

Umesh, do you need anything else to verify? Thank you!

Please accept my sincere apologies, Alexander. Somehow I lost track of this bug completely. Could you please confirm if this is the test case which confirms the issue? Also, I assume that for both i.e 5.7/8.0 MySQL server are running with default settings.

- 8.0.x (with General Tablespace)

#/bin/bash
function do_db {
        db_exist=$(mysql -A -s -Nbe "select 1 from information_schema.schemata where schema_name = '$db'")
        if [ "$db_exist" == "1" ]; then echo "Already exists $db"; return 0; fi; 
        mysql -vvv -e "create database $db";
        mysql -vvv $db -e "CREATE TABLESPACE $db ADD DATAFILE '$db.ibd' engine=InnoDB;"
        for i in {1..15}
        do
                table="CREATE TABLE sbtest$i ( id int(10) unsigned NOT NULL AUTO_INCREMENT, k int(10) unsigned NOT NULL DEFAULT '0', c varchar(120) NOT NULL DEFAULT '', pad varchar(60) NOT NULL DEFAULT '', PRIMARY KEY (id), KEY k_1 (k) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 tablespace $db;"
                mysql $db -e "$table"
        done
}
c=0
for m in {1..20000}
do
        for i in {1..50}
        do
                let c=$c+1
                echo $c
                db="customer_$c"
                do_db &
        done
        wait
done

- 5.7.x

#/bin/bash
function do_db {
        db_exist=$(mysql -A -s -Nbe "select 1 from information_schema.schemata where schema_name = '$db'")
        if [ "$db_exist" == "1" ]; then echo "Already exists $db"; return 0; fi; 
        mysql -vvv -e "create database $db";
        for i in {1..15}
        do
                table="CREATE TABLE tbl$i ( id int(10) unsigned NOT NULL AUTO_INCREMENT, k int(10) unsigned NOT NULL DEFAULT '0', c varchar(120) NOT NULL DEFAULT '', pad varchar(60) NOT NULL DEFAULT '', PRIMARY KEY (id), KEY k_1 (k) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;"
                mysql $db -e "$table"
        done
}
c=0
for m in {1..20000}
do
        for i in {1..50}
        do
                let c=$c+1
                echo $c
                db="customer_$c"
                do_db &
        done
        wait
done

- sysbench.sh
conn=" --db-driver=mysql --mysql-host=127.0.0.1 --mysql-db=test --mysql-user=root --mysql-password= "
sysbench $conn --report-interval=1 --num-threads=16 --max-requests=0 --max-time=300 --test=select_custom.lua run | tee -a sysbench_1.txt

- select_custom.lua:
pathtest = string.match(test, "(.*/)") or ""

function event(thread_id)
   local dbid = sb_rand(1, 1000)
   local id = sb_rand(1, 155)
   
   rs = db_query("select * from customer_" .. dbid .. ".tbl where id = " .. id)
end

regards,
Umesh

Yes, this is a correct test case. Thank you!

Thank you for confirming the test case.

regards,
Umesh

Not sure if this is related or not, but I have encountered a similar performance regression but this time for reads. Concurrent full table scans (ex. concurrent count) lead to a severe cripple in read performance, to a point where increasing the concurrency above 4 threads, leads to worse performance than in single threaded scenarios. Additionally running concurrent mysqldump instances to dump concurrently a set of 50000 tables from one database appears to be crippled to single thread performance. In MySQL 5.6.28 it scales to the point where the RAID array is overloaded. 

I'm using Percona flavor of MySQL, corresponding to 8.0.15, however, since the issue does not appear to affect their TokuDB engine, I strongly suspect it's a core InnoDB issue (also reported on Percona side at: https://jira.percona.com/browse/PS-5539 ) . It should be easily reproducible by running concurrently mysqldump and watching the scaling over a HDD RAID array.

The issue still exists and reproduciable with latest mysql version 8.0.17.

Working test case MySQL 5.7.27 vs MySQL 8.0.17

5000 databases (5 tables each) = 25000 tables

5.7.27: transactions: 6560976 (21869.23 per sec.)
8.0.17: transactions: 3012340 (10040.88 per sec.)

Testcase:

------------
mysql5.7.27:
------------

#/bin/bash
function do_db {
        db_exist=$(mysql --user=msandbox --password=msandbox --socket=/tmp/mysql_sandbox22202.sock -A -s -Nbe "select 1 from information_schema.schemata where schema_name = '$db'")
        if [ "$db_exist" == "1" ]; then echo "Already exists $db"; return 0; fi;
        mysql --user=msandbox --password=msandbox --socket=/tmp/mysql_sandbox22202.sock -vvv -e "create database $db";
        for i in {1..5}
        do
                table="CREATE TABLE tbl$i ( id int(10) unsigned NOT NULL AUTO_INCREMENT, k int(10) unsigned NOT NULL DEFAULT '0', c varchar(120) NOT NULL DEFAULT '', pad varchar(60) NOT NULL DEFAULT '', PRIMARY KEY (id), KEY k_1 (k) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;"
                mysql  --user=msandbox --password=msandbox --socket=/tmp/mysql_sandbox22202.sock  $db -e "$table"
        done
}
c=0
for m in {1..1000}
do
        for i in {1..5}
        do
                let c=$c+1
                echo $c
                db="customer_$c"
                do_db &
        done
        wait
done

conn=" --db-driver=mysql --mysql-host=127.0.0.1 --mysql-db=test --mysql-user=msandbox --mysql-password=msandbox --mysql-port=22202 "
sysbench $conn --report-interval=1 --num-threads=16 --max-requests=0 --max-time=300 --test=select_custom.lua run | tee -a sysbench_57.txt

SQL statistics:
    queries performed:
        read:                            6560976
        write:                           0
        other:                           0
        total:                           6560976
    transactions:                        6560976 (21869.23 per sec.)
    queries:                             6560976 (21869.23 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

---------------
8.0.17
-----------------
#/bin/bash
function do_db {
        db_exist=$(mysql -A -s -Nbe "select 1 from information_schema.schemata where schema_name = '$db'")
        if [ "$db_exist" == "1" ]; then echo "Already exists $db"; return 0; fi;
        mysql -vvv -e "create database $db";
        mysql -vvv $db -e "CREATE TABLESPACE $db ADD DATAFILE '$db.ibd' engine=InnoDB;"
        for i in {1..5}
        do
                table="CREATE TABLE tbl$i ( id int(10) unsigned NOT NULL AUTO_INCREMENT, k int(10) unsigned NOT NULL DEFAULT '0', c varchar(120) NOT NULL DEFAULT '', pad varchar(60) NOT NULL DEFAULT '', PRIMARY KEY (id), KEY k_1 (k) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 tablespace $db;"
                mysql --user=msandbox --password=msandbox --socket=/tmp/mysql_sandbox22425.sock $db -e "$table"
        done
}
c=0
for m in {1..1000}
do
        for i in {1..5}
        do
                let c=$c+1
                echo $c
                db="customer_$c"
                do_db &
        done
        wait
done

--------------
conn=" --db-driver=mysql --mysql-host=127.0.0.1 --mysql-db=test --mysql-user=msandbox --mysql-password=msandbox --mysql-port=22425 "
sysbench $conn --report-interval=1 --num-threads=16 --max-requests=0 --max-time=300 --test=select_custom.lua run | tee -a sysbench_80.txt

SQL statistics:
    queries performed:
        read:                            3012340
        write:                           0
        other:                           0
        total:                           3012340
    transactions:                        3012340 (10040.88 per sec.)
    queries:                             3012340 (10040.88 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

------------------------
select_custom.lua 
---------------------------
pathtest = string.match(test, "(.*/)") or ""

function event(thread_id)
   local dbid = sb_rand(1, 5000)
   local id = sb_rand(1, 5)

   rs = db_query("select * from customer_" .. dbid .. ".tbl" .. id .. "  where id = " .. id)
end

Thank you Alexander, Lalit.

regards,
Umesh

Test results - 8.0.17, 5.7.27

Attachment: 90209.results (application/octet-stream, text), 5.78 KiB.

Lalit, have you also tested with fixed number o databases but variable number of tables, with one table per file storage? Like 1K,5K,10K tables/database.

Sergiu,

I don't think the different number of table count in the different databases will change the result. But I tested with innodb_file_per_table one table per file and the result not showing much difference as compared to the test with general tablespaces.

--------
8.0.17 (with innodb_file_per_table one table per file)
--------
sysbench $conn --report-interval=1 --num-threads=16 --max-requests=0 --max-time=300 --test=select_custom.lua run | tee -a sysbench_80.txt

SQL statistics:
    queries performed:
        read:                            3385703
        write:                           0
        other:                           0
        total:                           3385703
    transactions:                        3385703 (11285.45 per sec.)
    queries:                             3385703 (11285.45 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

Lalit, may I ask you to do a retest but with following parameters:
table_open_cache = 200000
table_open_cache_instances = 2

For all my production setups (MySQL 5.6, 5.7 and 8), I had to increase the cache size and keep it at 1 or 2 instance otherwise the performance was severely degrading.

I am running into a similar issue.  In my tests, CREATE TABLE is 4 times slower in MySQL 8 vs 5.7.  Are there plans to improve this?

After switching from MariaDB 10.5 to MySQL 8.0.35, we also recognized that
our integration tests run much longer.

DROP and CREATE is slower by a factor of 4.

A truncate instead of drop create for the 150+ tables reduced the runtime by a factor of 2 - which I do not understand. I always thought that truncate is an alias for drop/create.

Maybe someone can explain this to me?