MySQL 5.7 – Generic TableSpaces v2.0 – The New Wave

The concept of tablespace is nothing new. It’s just not older than me. Several other RDBMS (database systems) have implemented it for some time. In MySQL, it was implemented in the early days of InnoDB.

In general terms, what is a tablespace?

There are some English words that don’t make any sense when translated, or that shouldn’t be translated. Do you agree with me? For me, tablespace is one of those words. Let’s go: “space of tables.” A tablespace is a physical and/or logical area where one or more tables are grouped together. It’s like a reserved area for one or a group of tables, whether for organization (tables with the same purpose, application, etc.), volume, performance, security, and so on.

MySQL and its epic journey with tablespaces

In the beginning, and until version 5.1, all tables with the InnoDB storage engine were compulsorily created inside a single tablespace, also called shared tablespace. Physically, it consisted of a file with “Buzz syndrome” (because it grew to infinity and beyond), named ibdata, which was located just below the root of the DATADIR (data directory, defined by the datadir variable, where the MySQL schema resides). This implementation cost me many hours of sleep, as it was a real trap. Being a single file containing multiple tables, it caused OS contentions and was difficult to maintain and support.

With the advent of version 5.1, MySQL desperately implemented the possibility for each table to physically have its own tablespace. For those lost on the topic, check the innodb_file_per_table variable. So, since each tablespace would store a single table, there was an exponential gain, such as: higher performance, improved shrink (recovering space from deleted rows), reduced OS contention, lower total data volume, among others. However, with many more physical files now, there was a greater need to carefully manage the OS configuration to avoid exceeding the maximum number of open files per process (file descriptors).

As I write this article, my dog Rodolpho is biting everything in sight (including me), while MySQL 5.7 subversion 7 (5.7.7) RC (release candidate) is about to come out and hit the streets. Now, much improved, tablespaces are more rational, have extended maintenance, and are more configurable. However, the standout feature is the ability to use, simultaneously: exclusive tablespaces for each table, and shared tablespaces. Furthermore, now we can actually group certain tables into a given tablespace, thus reducing the number of file descriptors consumed. Also, organize tables by type/amount of access, volume, application. We have been doing this in Oracle for some time, so despite the excitement, it’s not truly new, but it has been repeatedly requested by the developer team. Another cool thing is that now you can specify the physical location of each tablespace. That’s right — you could, for example, place a tablespace on a specific storage, further improving performance.

Let’s get to work and play with the new feature

I’ll use the famous world database, which can be downloaded by clicking here.

Creating a brand-new tablespace:

CREATE TABLESPACE tablespace_5_7 ADD DATAFILE ‘tablespace_5_7_dbf.ibd’;

To create it at another mount point:

CREATE TABLESPACE tablespace_5_7 ADD DATAFILE ‘/mnt/app01/tablespace_5_7_dbf.ibd’;

The commands above just create the tablespace tablespace_5_7 and the physical file that defines it: /mnt/app01/tablespace_5_7_dbf.ibd, see:

shell> ls -lh
total 185M
-rw-rw— 1 mysql mysql 56 May 11 15:18 auto.cnf
-rw-rw— 1 mysql mysql 76M May 31 06:56 ibdata1
-rw-rw— 1 mysql mysql 48M May 31 06:56 ib_logfile0
-rw-rw— 1 mysql mysql 48M May 11 15:17 ib_logfile1
-rw-r—- 1 mysql mysql 12M May 31 06:54 ibtmp1
drwx—– 2 mysql mysql 4.0K May 11 15:17 mysql
-rw-rw— 1 root root 5 May 31 06:54 mysqld_safe.pid
srwxrwxrwx 1 mysql mysql 0 May 31 06:54 mysql.sock
-rw—– 1 mysql mysql 5 May 31 06:54 mysql.sock.lock
drwx—– 2 mysql mysql 4.0K May 11 15:17 performance_schema
-rw-r—- 1 mysql mysql 64K May 31 06:56 tablespace_5_7_dbf.ibd
drwx—– 2 mysql mysql 4.0K May 11 15:17 test
drwx—– 2 mysql mysql 4.0K May 11 15:24 world

Now, let’s create a table with data and “throw it” into our tablespace:

mysql> CREATE TABLE test.City SELECT * FROM world.City;
Query OK, 4079 rows affected (3.51 sec)
Records: 4079 Duplicates: 0 Warnings: 0

shell> ls -lh
total 496K
-rw-r—- 1 mysql mysql 8.6K May 31 06:58 City.frm
-rw-r—- 1 mysql mysql 480K May 31 06:58 City.ibd

Note that City.frm (table definition file) and City.ibd (tablespace of the City table containing data and indexes) were created.

mysql> ALTER TABLE test.City TABLESPACE=tablespace_5_7;
Query OK, 0 rows affected (4.32 sec)
Records: 0 Duplicates: 0 Warnings: 0

shell> ls -lh
total 12K
-rw-r—- 1 mysql mysql 8.6K May 31 06:59 City.frm

Oops! The .ibd disappeared!!! Let’s find it:

shell> ls -lh
total 185M
-rw-rw— 1 mysql mysql 56 May 11 15:18 auto.cnf
-rw-rw— 1 mysql mysql 76M May 31 06:59 ibdata1
-rw-rw— 1 mysql mysql 48M May 31 06:59 ib_logfile0
-rw-rw— 1 mysql mysql 48M May 11 15:17 ib_logfile1
-rw-r—- 1 mysql mysql 12M May 31 06:54 ibtmp1
drwx—– 2 mysql mysql 4.0K May 11 15:17 mysql
-rw-rw— 1 root root 5 May 31 06:54 mysqld_safe.pid
srwxrwxrwx 1 mysql mysql 0 May 31 06:54 mysql.sock
-rw—– 1 mysql mysql 5 May 31 06:54 mysql.sock.lock
drwx—– 2 mysql mysql 4.0K May 11 15:17 performance_schema
-rw-r—- 1 mysql mysql 464K May 31 06:59 tablespace_5_7_dbf.ibd
drwx—– 2 mysql mysql 4.0K May 31 06:59 test
drwx—– 2 mysql mysql 4.0K May 11 15:24 world

Ta-da! Note that our tablespace tablespace_5_7_dbf.ibd grew because it now holds the City table data. Also notice that the .ibd file alone was 480K, the tablespace started at 64K, and above all, after moving the table into our tablespace, there was significant disk space optimization.

Conclusion

I really liked seeing an old request fulfilled. In preliminary tests with tables up to 25 million rows, and swelling the tablespace up to 100 gigabytes, stable behavior was observed; however, the large space optimization seen at the start doesn’t hold for large tables. Performance, with heavy access to the same table, favored the table with exclusive tablespace. There is still a lot to test, but it is undoubtedly a new feature that will enable the exploration of multiple storages for improved performance.

Schedule a meeting here