Bug #90209 Performance regression with > 15K tables in MySQL 8.0 (with general tablespaces)
Submitted: 24 Mar 21:19 Modified: 10 Jul 13:03
Reporter: Alexander Rubin Email Updates:
Status: Open Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:8.0 OS:Any
Assigned to: Umesh Shastry CPU Architecture:Any

[24 Mar 21:19] Alexander Rubin
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
[9 Apr 9:59] Umesh Shastry
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
[9 Apr 14:09] Alexander Rubin
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.
[10 Apr 5:39] Umesh Shastry
Thank you Alexander, for the details and clarification. I'll give it a try and get back to you on this.

Regards,
Umesh
[28 Jun 13:45] Alexander Rubin
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
[10 Jul 13:03] Alexander Rubin
Umesh, do you need anything else to verify? Thank you!