MySQL 8.0 General Tablespaces: File per Database (and no FRM files)

MySQL 8.0 General TablespacesIn this blog post, we’ll look at MySQL 8.0 general tablespaces.


MySQL 8.0 (the DMR version is available now) has two great features (among others):

  1. The new data dictionary completely removed *.frm files, which is great
  2. The ability to create a tablespace and assign a group of tables to it (originally introduced in 5.7).

With those two options, we can use MySQL for creating multi-tenant environments with a “schema per customer” approach.

Schema per Customer with MySQL 8.0

Using schema per customer with older MySQL versions presents issues  … namely the number of files. (I’ve described schema per customer approach in MySQL in an older blog post.) Let’s say you are hosting a Drupal-based site for your customers, and you create a new database (AKA “schema”) per each customer. You do not want to create one schema for all because each customer wants to extend Drupal and use plugins that will create their own unique set of tables. With tablespace per table and an FRM file, 10K customers will end up with:

  • 65 tables per schema,
  • Two files per table, and
  • 10K schemas

. . . or a grand total of 1.3 million files!

With MySQL 8.0, we can create a tablespace file per each schema and place those tablespace files in a specific set of directories. For example, if we have demo, test and production accounts, we can create a set of directories (outside of the MySQL datadir) and place tablespaces inside them. With no FRM files, we will only have 10 thousands of files, evenly split across multiple locations.


Now let’s look at the directory:

The downside of this approach is that the “create tables” command should have the tablespace name in it. I’ve created a sample “deploy” script to create a new schema for a customer:

Size and Timing

In the next post, I plan to benchmark the performance of millions of tables with MySQL 8.0 and tablespace file per database. Here, I’ve compared the create table performance between MySQL 5.7 (with FRM and file per table), MySQL 8.0 with a file per table (FRMs are gone) and MySQL 8.0 with a file per database. Time to create 1000 databases for Drupal (no data), 65 tables in each database:

  • MySQL 5.7, file per table: 3m21.819s
  • MySQL 8.0,  file per table: 2m54.358s
  • MySQL 8.0,  file per database: 1m55.133s

What about the size on disk? It did not change much. Actually, the size on disk for the blank tables (no data) is more in MySQL 8.0:

  • 8.0: 10M (10485760 bytes) per 65 blank tables (Drupal)
  • 5.7: 9.2M (9280821 bytes) per 65 blank tables, including FRM files

With 10K schemas, it is 100G just to store tablespaces (schema overhead). At the same time, it is not 100% overhead: InnoDB creates 112K+ the tablespace file right away (it depends upon the table structure). When the data is loaded it will use this reserved space.


New Data Dictionary

MySQL 8.0 uses a new transactional data dictionary.

MySQL Server 8.0 now incorporates a global data dictionary containing information about database objects in transactional tables. In previous MySQL releases, dictionary data was stored in metadata files and nontransactional system tables.

That also means that all old metadata files are gone: no .frm, .par, .trn, .trg files. In addition tables inside a MySQL database are not using MyISAM tables anymore. The new installation has no single MyISAM table, although the MyISAM engine is supported:


FRM free installation looks great (performance testing of it is my next step). I would also love to see some additional features in MySQL 8.0:

  • Easier tablespace level manipulations, i.e. “optimize tablespace” to re-claim space in the general tablespace file; add “if exists / if not exists” to create/drop tablespace
  • Much smaller “reserved” space: if the data dictionary is stored elsewhere, we can create a one-page file (16K) + table structure.

Please note: MySQL 8.0 is not production ready and only available for preview.

Learn more about Percona Server for MySQL

Share this post

Comments (9)

  • mark

    so optimize tablespace is just your suggestion, it is not a existing feature

    October 4, 2016 at 11:39 pm
    • Alexander Rubin

      Correct, that is my feature request

      October 5, 2016 at 7:57 pm
      • Simon J Mudd

        What do you expect this to do?

        I’d expect it to re-organise the pages such to the beginning of the tablespace file so that free space at the end could be “truncated away”. It should of course do this in the background without blocking writes to the tables in the tablespace. Perhaps there should be 2 modes: an “I can lock you and optimise perfectly” mode and the normal “I’ll try and save you space but if you keep making changes it might not be as good as you want and may require you to run me several times to get a decent space saving”.

        If you made a public FR for this on please post it. If not please add one as I think the FR is quite valid.

        November 21, 2016 at 5:58 pm
  • lefred

    Thank you Alex for that nice blog post.

    Do you think that something like “USE TABLESPACE blabla” so all CREATE statements in the same session if you don’t define a TABLESPACE will use it ?

    For you drupal example, only one line (or two if you still need to create the tablespace) should then be necessary to be added in your import dump.


    October 5, 2016 at 5:52 pm
    • Alexander Rubin

      Thank you Fred!
      Yes, USE TABLESPACE or even USE dbname will be very helpful here I thinks.

      October 5, 2016 at 7:59 pm
  • Daniël van Eeden

    Something like CREATE SCHEMA foo_customer DEFAULT TABLESPACE foo_customer_data001 would be nice.
    The it doesn’t have to be in the CREATE TABLE statement, this would also help for software not updated to take advantage of this.

    October 7, 2016 at 3:11 am
  • Jörg Brühe

    Using a single tablespace per schema is no new idea – in February, I filed a request to support this via a config option.
    I called it “InnoDB-File-Per-Schema supported by a configuration option”, check it at
    And if you read that description to the end, you will also find the request for “optimize” on that storage unit …

    November 4, 2016 at 8:12 am
  • onebyzerosite

    Is percona server supports for innodb_file_per_schema as configuration like innodb_file_per_table?

    May 23, 2017 at 12:51 pm

Comments are closed.

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