Bug #90209 Performance regression with > 15K tables in MySQL 8.0 (with general tablespaces)
Submitted: 24 Mar 21:19 Modified: 9 Apr 14:09
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