MySQL – InnoDB Files Created Even When innodb_file_per_table is OFF

innodbMySQLmysql-5.6mysql-5.7mysqldump

We have installed mysql 5.7 in ubuntu 16.04 and we got big surprise because databases are being stored individually in each folder even parameter innodb_file_per_table is 0.

We decided to change innodb_file_per_table to 0 because there are over 6000 databases with 40 tables in each of them. We have to migrate to this solution actually because current solution with innodb_file_per_table=ON is causing too many performance issues giving "too many open files" at error log frequently.

In this current server We are not able to do a simple backup (dump) because mysql-server-5.6 is crashing in –all-databases parameter.

We think if we configure a new mysql-server-5.7 with innodb_file_per_table=OFF we will get better performance..

Any advice/help will be welcome.

Best Answer

Not matter what you configure innodb_file_per_table to be (0 or 1), the following still occurs

MySQL creates a database folder
MySQL creates every table's .frm in a database folder

EXAMPLE

Your database directory (datadir) is /var/lib/mysql

You run the following commands

CREATE DATABASE IF NOT EXISTS mydb;
USE mydb
CREATE TABLE tb1 (a INT NOT NULL) ENGINE=InnoDB;
CREATE TABLE tb2 LIKE tb1;

When innodb_file_per_table is 1

You get /var/lib/mysql/mydb
You get /var/lib/mysql/mydb/tb1.frm
You get /var/lib/mysql/mydb/tb1.ibd
You get /var/lib/mysql/mydb/tb2.frm
You get /var/lib/mysql/mydb/tb2.ibd

When innodb_file_per_table is 0

You get /var/lib/mysql/mydb
You get /var/lib/mysql/mydb/tb1.frm
You get /var/lib/mysql/mydb/tb2.frm

The major difference is the fact that data and index blocks for newly created tables end up inside the system tablespace file known as ibdata1 when it is 0.

Just as a pictorial reference, please note Percona's view of InnoDB

YOUR QUESTION

Changing innodb_file_per_table to 0 does not make currently created tables just migrate into ibdata1. You have to manually move those tables into ibdata1.

This will not alleviate the too many open files issues. Why ?

Regardless of storage engine, mysqld always checks for the existence of tables by first checking for the tables .frm file. Then, the mysqld process will reference the table's intergrity based on the storage engine.

In the case of InnoDB, the data dictionary of the InnoDB table is then checked.

Two file handles are opened

One file handle for the .frm
One file handle for the .ibd when accessing the table

If you move the table and index data from the .ibd into the ibdata1 file, you get this

One file handle for the .frm
One file handle for the ibdata1 when accessing the table

All the file handles will pile up inside ibdata1 instead of separate files.

STILL WANT TO MOVE TABLE INTO `ibdata1` ?

While I highly discourage this, it is possible

In the example I gave you, in order to move a table into ibdata1 after setting innodb_file_per_table to 0, just run this

USE mydb
ALTER TABLE tb1 ENGINE=InnoDB;
ALTER TABLE tb2 ENGINE=InnoDB;

This recreates the table, and migrates the data and indexes into ibdata1. If you are willing to do this 240,000 times (6000 X 40), be my guest.

WHAT CAN YOU DO FROM HERE ?

You could setup MySQL Replication and perform backups on a Slave

You do the following

STOP SLAVE;
Perform mysqldump on the Slave without --single-transaction
START SLAVE;

STOP SLAVE;
SET GLOBAL innodb_fast_shutdown = 0;
Shutdown mysqld
Make a physical copy of the datadir
StartUp mysqld
START SLAVE;

GIVE IT A TRY !!!

STEP #1

Set innodb_file_per_table to 0 in /etc/my.cnf

[mysqld]
innodb_file_per_table = 0

STEP #2

service mysql restart

STEP #3

Convert every InnoDB table to InnoDB again

echo "SET SQL_LOG_BIN = 0;" > /root/ConvertInnoDBToInnoDB.sql
MYSQL_CONN="-u... -p..."
SQL="SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=InnoDB;')"
SQL="${SQL} FROM information_schema.tables WHERE engine='InnoDB'"
mysql ${MYSQL_CONN} -AN -e"${SQL}" >> /root/ConvertInnoDBToInnoDB.sql
less /root/ConvertInnoDBToInnoDB.sql

Once you view he script and are satisfied, login to mysql and run this

mysql> source /root/ConvertInnoDBToInnoDB.sql

EPILOGUE

All .ibd files will vanish and all InnoDB tables and indexes will exist inside ibdata1

Give it a Try !!!

MySQL – Should innodb_file_per_table Be Enabled for Single Tenant SaaS?

Answering one by one:

Performance: yes, this is the main problem. Not only in resource for file descriptors, but also for the mysql open table cache and data dictionary, InnoDB checks on start and regenerating table statistics when opened. To be fair, these last ones (except InnoDB checks) will be found no matter the value of innodb_file_per_table, only because a huge number of tables.

The largest problem, however -in my opinion-, was that in the case of a DROP TABLE with innodb_file_per_table = 1 older versions of 5.5 required a full buffer pool lock, basically stalling the whole server. Some patches applied in the latest versions of MySQL 5.5 and 5.6, plus the buffer pool partitioning (available in 5.5 and enabled by default in 5.6) made that a minor issue.

On the bright side, you can in 5.6 set a manual path of every table, which may help in some cases, for example, to put the most problematic tables on a separate disk or an SSD, etc.
Stability: I wouldn't say that it is more stable, but it is true that if you have disk corruption and a single huge file, you can only pray. Having different files makes easier to "save" all but one or a few. Not convinced that this is a decisive point -InnodB is well regarded about its disk consistency (assuming there is no physical damage).
Concurrency: This is true, but only in specific cases. Ext filesystems had a limitation that if you were using O_DIRECT mode, you couldn't write in concurrency to the same file descriptor. That is completely true, but is a very specific case: you could use a different filesystem (XFS is very recommended when handling many files and does not have that problem), you can use the filesystem cache if you have enough ram and in some cases the high concurrency is not a problem if writes go to different files at the same time (thus the innodb_file_per_table=1 or several ibdata files).
I cannot see your link, I suppose it is this? Yes, I agree 100% that one thing that it is missing in InnoDB is more control about the tablespace management (funnily enough, it is done for the disk tables in the memory-only engine NDB). I have not seen such a thing implemented. You are supposed to do all optimizations at RAID/LVM/physical level.

I think that you miss one thing that is the number 1 reason for having innodb_file_per_table = 1 - the flexibility for backups, restores, management and moving tables around inidividually (something made even easier with the transportable tablespaces in MySQL 5.6). With it enabled, you will be able to recover disk space when tables are dropped and make defragmentation possible. When you have thousands of tables in a single file, physical backup and restore is impossible, and you have to use logical backups (mysqldump/LOAD DATA).

So what are the alternatives? I have found this problem before, and I have helped solving it on hosts with literally millions of tables in several ways:

If disk space is not a problem, you can have some tables in the common tablespace and some separately (innodb_file_per_table is a dynamic variable, although you need to rebuild the table for each one you want to move in or out). This solution is ok if your problem is having some tables that are very dynamic -being created and destroyed frequently- while other are static, but you may end up with a very large common tablespace. However, if using innodb_file_per_table = 0 works better with you, then end of discussion.
You can have several common tablespace files (ibdata1;ibdata2;ibdata3). This is ok if you want to separate them in several disks, or you do not want to deal with huge files, but it will not solve the "having many tables" problem.
Change your schema- For example, wordpress multisite installation puts many tables together, and if I remember correctly (I may be wrong), it only creates a new table per site, not a full database. If your tables are going to be very small, this is the way to go- modify the structure so that you have larger and less tables
Choose a different engine - Wordpress.com uses MyISAM at scale and they do not care about corruption because they have a good backup/recovery system. Maybe this is your case if most of your tables are read-only (or almost read-only) or you put a good caching system in-between.
What is the number 1 way to deal with this problem? What I have been doing on behalf of my clients most of the time is what Wordpress, Facebook and other do- sharding. Do not try to handle a million of users in a single powerful machine- use smaller ones and divide load (and databases and users) between several servers. You do not have to handle the sharding all by yourself, there are tools to help you do that, like the Upstream MySQL Fabric (for Java and Python) or the engine Spider.

Please note that if the bottleneck is not on CPU/disk/memory, you can even use virtual machines in order to consolidate services.

In general, innodb_file_per_table is something that you want ON, for the flexibility reasons that I told you before. If you cannot have it on (test it first, maybe you would be surprised! -avoid premature optimization), you can disable it for all or some of the tables (but the change will be more painful than anything else. But be prepared for the consequences. Try to avoid that with some of the recommendations that I told you at the end.

Update: 5.7 labs releases have announced that may finally become obsolete and we will finally have better tablespace management thanks to a CREATE TABLESPACE syntax (search for "InnoDB: General Tablespaces"). With this, you will be free to create your own "innodb_file_per_database" or any other schema you want.