Benefits of using general tablespaces in Mysql

Written by

Mysql is one of the most popular open source relational database management systems out there and they have been adding new mature features to it's databases on a regular basis. Mysql earlier was known to be a fast database mostly suitable for web applications based on LAMP stack. But since large deployments of Mysql has been taking place in recent times and also in some large companies, it was inevitable that Mysql would need a better data file management than it's traditional system tablespace used for it's InnoDB engine. In this article we will discuss how general tablespace can benefit us and how we can add a general tablespace to a Mysql installation. For us to use general tablespaces in Mysql we need a version higher than 5.7 of Mysql. So lets begin.

One of the many problems which Mysql faces by use of system tablespace is, it does not support many features like large indexes and data compression which any advanced deployment of Mysql needs. Large indexes are needed when you are using ut8mb4 character encoding in Mysql to get true utf8 character set support. Data compression can help you reduce the size of Mysql tablespace data files considerably and also it can make IO faster since there is less data to read and write back to disk when compression is on.

Mysql tried to solve these problems by introducing per table tablespace called as file_per_table. This supports large indexes, better file format like Barracuda and data compression. But soon the limitation of this tablespace also started to surface which was due to use of one data file per table for the use of this tablespace type. So if you have like 1000s of tables then Mysql could run out of open file handles of the operating system since it needs to keep open all the data files associated with the tables at the same time which is same as number of tables across all databases. This also causes an issue with commits since Mysql has to issue a data flush to disk for each of these data files separately. So if your database has a large number of tables like more than 500 replacing system table space with general tablespace is a much better option than to go with file_per_table tablespace approach. Only scenario which I can think of in favor of using files_per_table tablespace is when you need to reclaim disk space after dropping tables. General tablespaces like system tablespace does not shrink the size of tablespace data file once tables are dropped. Whereas file_per_table tablespace can remove the data file completely once the table is dropped reclaiming all the space taken by data file of the dropped table.

So now let's see how we can add a general tablespace to a Mysql installation. You can simple add a general tablespace by using CREATE TABLESPACE sql statement in Mysql version 5.7 and higher. You may like to add innodb_page_size = 16K option in your Mysql .cnf file if you intend to use compression in the data file of a general tablespace.

Once you have created a system tablespace you can add new tables to this tablespace by using the following options in your CREATE TABLE sql statement. You can also move a table data to new tablespace by using the below option in your ALTER TABLE sql statement.

TABLESPACE=tablespace_name

If you want to use compression for your table data then add these additional options to your CREATE TABLE or ALTER TABLE sql statement.

KEY_BLOCK_SIZE=value ROW_FORMAT=COMPRESSED

In terms of data file management, I can suggest the following arrangement of tables to best utilize general tablespaces along with file_per_table tablespaces. You can keep your metadata tables (or tables with mostly static data) in file_per_table tablespace and move all your transactional tables to a general tablespace. This will help you exploit the benefits of both these tablespaces at the same time.

Below is a chart which shows how using general tablespace compression affects the data size stored on the disk vs increase in computing time when inserting 1 million records in the database. As you can see by playing with file block size values of the tablespace you can get a balance between data size on disk vs CPU usage.

So as you can see there is a clear trend of decreasing data size on disk when file block size of the tablespace is decreased. Also there is a clear trend of increasing CPU usage when file block size of the tablespace is decreased to achieve better compression. So a good tradeoff of obtaining a good enough compression ratio and relatively less CPU usage is to use 8k file block size for the general tablespace. This is specially true if you use SSD for storage. If you have spinning HDD then you can go for 4k file block size for the tablespace and a faster CPU since 4k/2k file block size will further reduce disk IO than 8k file block size. The below chart will help you configure your system to take advantage of Mysql general tablespace compression for InnoDB tables.

Recommendations for use with InnoDB page size of 16k