In my previous blog post about millions of table in MySQL 8, I was able to create one million tables and test the performance of it. My next challenge is to create 40 million tables in MySQL 8 using shared tablespaces (one tablespace per schema). In this blog post I’m showing how to do it and what challenges we can expect.
Once again – why do we need so many tables in MySQL, what is the use case? The main reason is: customer isolation. With the new focus on security and privacy (take GDPR for example) it is much easier and more beneficial to create a separate schema (or “database” in MySQL terms) for each customer. That creates a new set of challenges that we will need to solve. Here is the summary:
When I approached the task of creating 40 million tables, my first challenge was disk space. Just to create them, I needed at least 5Tb of fast disk storage. The good news is: we have the ZFS filesystem which provides compression out of the box. With compression I was able to use just a 250G drive with ZFS – the compression ratio is > 10x:
|
1 |
# du -sh --apparent-size /var/lib/mysql-data<br>4.7T /var/lib/mysql-data<br># du -sh /var/lib/mysql-data<br>131G /var/lib/mysql-data<br> |
The second challenge is how to create those tables in a reasonable amount of time. I created a script to “provision” the databases (create all 40 millions tables). The good new is that the performance regression in “create table” speed and scalability bug was fixed so I was able to use this script to create 40 million tables using shared tablespaces (one tablespace per schema):
|
1 |
#/bin/bash<br>function do_db {<br> db_exist=$(mysql -A -s -Nbe "select 1 from information_schema.schemata where schema_name = '$db'")<br> if [ "$db_exist" == "1" ]; then echo "Already exists $db"; return 0; fi; <br> mysql -vvv -e "create database $db";<br> mysql -vvv $db -e "CREATE TABLESPACE $db ADD DATAFILE '$db.ibd' engine=InnoDB;"<br> for i in {1..100}<br> do<br> 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;"<br> mysql $db -e "$table"<br> done<br>}<br>c=0<br>for m in {1..4000000}<br>do<br> for i in {1..40}<br> do<br> let c=$c+1<br> echo $c<br> db="sbtest_$c"<br> do_db &<br> done<br> wait<br> #if [ $c > 4000000 ]; then exit; fi<br>done<br> |
Now it’s time for a real test. I’m using the latest MySQL 8 version (at the time of writing): 8.0.12. This implements the new data dictionary.
MySQL config file:
|
1 |
[mysqld]<br>datadir=/var/lib/mysql-data<br>socket=/var/lib/mysql-data/mysql.sock<br><br>datadir=/var/lib/mysql-data<br>log-error = /var/lib/mysql-log/error.log<br>server_id = 12345<br>log_bin = /var/lib/mysql-log/binlog<br>relay_log=/var/lib/mysql-log/relay-bin<br>skip-log-bin=1<br><br>innodb_log_group_home_dir = /var/lib/mysql-log<br>innodb_doublewrite = 0<br>innodb_flush_log_at_trx_commit=0<br>innodb_log_file_size=2G<br>innodb_buffer_pool_size=4G<br><br>tablespace_definition_cache = 524288<br>schema_definition_cache = 524288<br>table_definition_cache = 524288<br>table_open_cache=524288<br>open-files-limit=1000000<br> |
Sysbench shell script:
|
1 |
function run_sb() {<br>conn=" --db-driver=mysql --mysql-socket=/var/lib/mysql-data/mysql.sock --mysql-db=sbtest_1 --mysql-user=sbtest --mysql-password=abc "<br>sysbench $conn --oltp_db_count=$db_count --oltp_tables_count=$table_count --oltp-table-size=10000 --report-interval=1 --num-threads=$num_threads --max-requests=0 --max-time=$max_time ./select_custom.lua run | tee -a sysbench_2.txt<br><br>}<br><br>let db_count=400000<br>table_count=100<br>max_time=10000<br>num_threads=32<br>run_sb |
Sysbench lua script:
|
1 |
pathtest = "/usr/share/sysbench/tests/include/oltp_legacy/"<br><br>if pathtest then<br> dofile(pathtest .. "common.lua")<br>else<br> require("common")<br>end<br><br>function thread_init(thread_id)<br> set_vars()<br>end<br><br>function event()<br> local table_name<br> local i<br> local c_val<br> local k_val<br> local pad_val<br> oltp_db_count = tonumber(oltp_db_count) or 1<br> -- local oltp_db_count = 4<br> <br> table_name = "sbtest_" .. sb_rand(1, oltp_db_count)..".sbtest".. sb_rand(1, oltp_tables_count)<br><br> k_val = sb_rand(1, oltp_table_size)<br> c_val = sb_rand_str([[<br>###########-###########-###########-###########-###########-###########-###########-###########-###########-###########]])<br> pad_val = sb_rand_str([[<br>###########-###########-###########-###########-###########]])<br><br> rs = db_query("SELECT id FROM " .. table_name .." LIMIT 1")<br>end<br> |
Please note that the tables are empty – no data.
Now we can run the benchmark. Unfortunately, we have a serious mutex contention in the data dictionary. Here are the results:
|
1 |
[ 453s ] thds: 32 tps: 1203.96 qps: 1203.96 (r/w/o: 1203.96/0.00/0.00) lat (ms,95%): 41.10 err/s: 0.00 reconn/s: 0.00<br>[ 454s ] thds: 32 tps: 1202.32 qps: 1202.32 (r/w/o: 1202.32/0.00/0.00) lat (ms,95%): 42.61 err/s: 0.00 reconn/s: 0.00<br>[ 455s ] thds: 32 tps: 1196.74 qps: 1196.74 (r/w/o: 1196.74/0.00/0.00) lat (ms,95%): 41.10 err/s: 0.00 reconn/s: 0.00<br>[ 456s ] thds: 32 tps: 1197.18 qps: 1197.18 (r/w/o: 1197.18/0.00/0.00) lat (ms,95%): 41.10 err/s: 0.00 reconn/s: 0.00<br>[ 457s ] thds: 32 tps: 887.11 qps: 887.11 (r/w/o: 887.11/0.00/0.00) lat (ms,95%): 41.10 err/s: 0.00 reconn/s: 0.00<br>[ 458s ] thds: 32 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00<br>[ 459s ] thds: 32 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00<br>[ 460s ] thds: 32 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00<br>[ 461s ] thds: 32 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00<br>[ 462s ] thds: 32 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00<br>[ 463s ] thds: 32 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00<br>[ 464s ] thds: 32 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00<br>[ 465s ] thds: 32 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00<br>[ 466s ] thds: 32 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00<br>[ 467s ] thds: 32 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00<br>[ 468s ] thds: 32 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00<br>[ 469s ] thds: 32 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00<br>[ 470s ] thds: 32 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00<br>[ 471s ] thds: 32 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00<br>[ 472s ] thds: 32 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00<br>[ 473s ] thds: 32 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00<br>[ 474s ] thds: 32 tps: 403.96 qps: 403.96 (r/w/o: 403.96/0.00/0.00) lat (ms,95%): 16819.24 err/s: 0.00 reconn/s: 0.00<br>[ 475s ] thds: 32 tps: 1196.00 qps: 1196.00 (r/w/o: 1196.00/0.00/0.00) lat (ms,95%): 41.85 err/s: 0.00 reconn/s: 0.00<br>[ 476s ] thds: 32 tps: 1208.96 qps: 1208.96 (r/w/o: 1208.96/0.00/0.00) lat (ms,95%): 41.85 err/s: 0.00 reconn/s: 0.00<br>[ 477s ] thds: 32 tps: 1192.06 qps: 1192.06 (r/w/o: 1192.06/0.00/0.00) lat (ms,95%): 41.85 err/s: 0.00 reconn/s: 0.00<br>[ 478s ] thds: 32 tps: 1173.89 qps: 1173.89 (r/w/o: 1173.89/0.00/0.00) lat (ms,95%): 43.39 err/s: 0.00 reconn/s: 0.00 |
As we can see, for ~15 seconds no queries were processed: a complete MySQL stall. That situation – complete stall – happens constantly, every ~25-30 seconds.
Show engine innodb status query shows mutex contention:
|
1 |
SEMAPHORES<br>----------<br>OS WAIT ARRAY INFO: reservation count 498635<br>--Thread 140456572004096 has waited at que0que.cc line 1072 for 0.00 seconds the semaphore:<br>Mutex at 0x34a3fe0, Mutex PARSER created pars0pars.cc:98, lock var 1<br><br>--Thread 140451898689280 has waited at que0que.cc line 1072 for 0.00 seconds the semaphore:<br>Mutex at 0x34a3fe0, Mutex PARSER created pars0pars.cc:98, lock var 1<br><br>--Thread 140451896919808 has waited at que0que.cc line 1072 for 0.00 seconds the semaphore:<br>Mutex at 0x34a3fe0, Mutex PARSER created pars0pars.cc:98, lock var 1<br><br>--Thread 140456571119360 has waited at que0que.cc line 1072 for 0.00 seconds the semaphore:<br>Mutex at 0x34a3fe0, Mutex PARSER created pars0pars.cc:98, lock var 1<br><br>--Thread 140457044215552 has waited at que0que.cc line 1072 for 0.00 seconds the semaphore:<br>Mutex at 0x34a3fe0, Mutex PARSER created pars0pars.cc:98, lock var 1<br><br>--Thread 140456572299008 has waited at que0que.cc line 1072 for 0.00 seconds the semaphore:<br>Mutex at 0x34a3fe0, Mutex PARSER created pars0pars.cc:98, lock var 1<br><br>--Thread 140457043035904 has waited at que0que.cc line 1072 for 0.00 seconds the semaphore:<br>Mutex at 0x34a3fe0, Mutex PARSER created pars0pars.cc:98, lock var 1<br><br>--Thread 140456571709184 has waited at que0que.cc line 1072 for 0.00 seconds the semaphore:<br>Mutex at 0x34a3fe0, Mutex PARSER created pars0pars.cc:98, lock var 1<br><br>--Thread 140451897214720 has waited at que0que.cc line 1072 for 0.00 seconds the semaphore:<br>Mutex at 0x34a3fe0, Mutex PARSER created pars0pars.cc:98, lock var 1<br><br>--Thread 140451896624896 has waited at que0que.cc line 1072 for 0.00 seconds the semaphore:<br>Mutex at 0x34a3fe0, Mutex PARSER created pars0pars.cc:98, lock var 1<br><br>--Thread 140457042740992 has waited at que0que.cc line 1072 for 0.00 seconds the semaphore:<br>Mutex at 0x34a3fe0, Mutex PARSER created pars0pars.cc:98, lock var 1<br><br>--Thread 140451899279104 has waited at que0que.cc line 1072 for 0.00 seconds the semaphore:<br>Mutex at 0x34a3fe0, Mutex PARSER created pars0pars.cc:98, lock var 1<br><br>--Thread 140457042446080 has waited at que0que.cc line 1072 for 0.00 seconds the semaphore:<br>Mutex at 0x34a3fe0, Mutex PARSER created pars0pars.cc:98, lock var 1<br><br>OS WAIT ARRAY INFO: signal count 89024<br>RW-shared spins 11216, rounds 14847, OS waits 3641 |
I’ve filed a new MySQL bug: DICT_SYS mutex contention causes complete stall when running with 40 mill tables.
I’ve also tested with pareto distribution in sysbench, and even set the ratio to 0.05 (5%) and 0.01 (1%), and mutex contention is still an issue. I have used the following updated sysbench script:
|
1 |
function run_sb() {<br>conn=" --db-driver=mysql --mysql-socket=/var/lib/mysql-data/mysql.sock --mysql-db=sbtest_1 --mysql-user=sbtest --mysql-password=abc "<br>sysbench $conn --rand-type=$rand_type --rand-pareto-h=$pareto_h --oltp_db_count=$db_count --oltp_tables_count=$table_count --oltp-table-size=10000 --report-interval=1 --num-threads=$num_threads --max-requests=0 --max-time=$max_time $test_name run | tee -a sysbench_2.txt<br><br>}<br><br>let db_count=400000<br>table_count=100<br>max_time=10000<br>num_threads=32<br>rand_type="pareto"<br>pareto_h=0.01<br>test_name="./select_custom.lua"<br>echo "Now running $rand_type for $max_time seconds, test=$test_name"<br>run_sb |
And the results with 0.01 (1%) are the following:
|
1 |
[ 55s ] thds: 32 tps: 72465.29 qps: 72465.29 (r/w/o: 72465.29/0.00/0.00) lat (ms,95%): 0.53 err/s: 0.00 reconn/s: 0.00<br>[ 56s ] thds: 32 tps: 68641.04 qps: 68641.04 (r/w/o: 68641.04/0.00/0.00) lat (ms,95%): 0.61 err/s: 0.00 reconn/s: 0.00<br>[ 57s ] thds: 32 tps: 70479.82 qps: 70479.82 (r/w/o: 70479.82/0.00/0.00) lat (ms,95%): 0.57 err/s: 0.00 reconn/s: 0.00<br>[ 58s ] thds: 32 tps: 31395.55 qps: 31395.55 (r/w/o: 31395.55/0.00/0.00) lat (ms,95%): 0.49 err/s: 0.00 reconn/s: 0.00<br>[ 59s ] thds: 32 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00<br>[ 60s ] thds: 32 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00<br>[ 61s ] thds: 32 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00<br>[ 62s ] thds: 32 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00<br>[ 63s ] thds: 32 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00<br>[ 64s ] thds: 32 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00<br>[ 65s ] thds: 32 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00<br>[ 66s ] thds: 32 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00<br>[ 67s ] thds: 32 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00<br>[ 68s ] thds: 32 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00<br>[ 69s ] thds: 32 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00<br>[ 70s ] thds: 32 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00<br>[ 71s ] thds: 32 tps: 18879.04 qps: 18879.04 (r/w/o: 18879.04/0.00/0.00) lat (ms,95%): 0.75 err/s: 0.00 reconn/s: 0.00<br>[ 72s ] thds: 32 tps: 70924.82 qps: 70924.82 (r/w/o: 70924.82/0.00/0.00) lat (ms,95%): 0.48 err/s: 0.00 reconn/s: 0.00<br>[ 73s ] thds: 32 tps: 72395.57 qps: 72395.57 (r/w/o: 72395.57/0.00/0.00) lat (ms,95%): 0.47 err/s: 0.00 reconn/s: 0.00<br>[ 74s ] thds: 32 tps: 72483.22 qps: 72484.22 (r/w/o: 72484.22/0.00/0.00) lat (ms,95%): 0.58 err/s: 0.00 reconn/s: 0.00 |
The ZFS filesystem provides compression, which helps tremendously in this case. When MySQL creates an InnoDB table it will create a new blank .ibd file and pre-allocate some pages, which will be blank. I have configured ZFS compression and can see > 10x compression ratio:
|
1 |
# zfs get all | grep compressratio<br>mysqldata compressratio 12.47x -<br>mysqldata refcompressratio 1.00x -<br>mysqldata/mysql compressratio 12.47x -<br>mysqldata/mysql refcompressratio 1.00x -<br>mysqldata/mysql/data compressratio 12.51x -<br>mysqldata/mysql/data refcompressratio 12.54x -<br>mysqldata/mysql/log compressratio 2.79x -<br>mysqldata/mysql/log refcompressratio 4.57x - |
It is possible to create 40 million tables with MySQL 8.0 using shared tablespaces. ZFS provides an excellent compression ratio (with gzip) which can help by reducing the overhead of “schema per customer” architecture. Unfortunately, the new data dictionary in MySQL 8.0.12 suffers from the DICT_SYS mutex contention and causes constant “stalls”.
