40 million tables in MySQL 8.0 with ZFS

40 million tables in MySQL 8In 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:

  1. Too many files. For each table MySQL creates an FRM file. With MySQL 8.0, this is not the case for InnoDB tables (new data dictionary): it does not create FRM files, only creates IBD file.
  2. Too much storage overhead. Just to create 40 million tables we will need to have ~4 – 5 Tb of space. The ZFS filesystem can help here a lot, through compression – see below.
  3. MySQL does not work well with so many tables. We have observed a lot of overhead (MySQL needs to open/close table definition files) and contention (table definitions needs to be stored in memory to avoid performance penalty, which introduce mutex contention)


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:

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):

40 million tables in MySQL 8

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:

Sysbench shell script:

Sysbench lua script:

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:

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:

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:

And the results with 0.01 (1%) are the following:


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:


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”.

Share this post

Comments (9)

  • Alvaro Hernandez

    It’s great that you are able to achieve 40M tables in MySQL!

    Mind you that 5 years ago I created 1B first, and later 2B tables in PostgreSQL: https://www.pgcon.org/2013/schedule/events/595.en.html

    September 3, 2018 at 7:15 pm
    • Alexander Rubin

      Hi Alvaro,

      Challenge accepted :). I will attempt to create 1B tables in MySQL and share the results in a blog post.


      September 11, 2018 at 7:48 am
  • Nick Vyzas

    Is this FreeBSD, Solaris or ZFS on Linux?

    September 7, 2018 at 5:07 pm
  • Eric

    Nice post Alexander – We currently average around 15K databases per server (2.4MM tables per server) and earlier this year moved away from INNODB_FILE_PER_TABLE and created a General Tablespace per database (MySQL 5.7). Reducing the number of INNODB_OPEN_FILES helped us significantly as well…especially the MySQL startup times.

    September 10, 2018 at 10:57 am
  • Laurent

    Why using ZFS gzip compression in place of lz4, which is ways faster and still provide compression levels close to gzip ?

    September 28, 2018 at 8:25 am

Comments are closed.

Use Percona's Technical Forum to ask any follow-up questions on this blog topic.