1

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.

  • Check show global variables like 'innodb_file_per_table'; if you set it in my.cnf to see if the config is properly loaded. Check the import script if it is maybe enabling it dynamically. – jkavalik May 10 '16 at 19:29
  • Hi jkavalik, this is the result: mysql> show global variables like '%innodb_file_per_table%'; innodb_file_per_table | OFF – David Martinez May 10 '16 at 19:57
2

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

InnoDB Plumbing

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;

or

  • 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 !!!

  • Thx Rolando, very clear reply. But we have a big RAM issue after to connect it to Internet. Mysqld eats RAM and it has to restart every 20min. I dont like mysql 5.7 at all. We are downgrade it to 5.6. – David Martinez May 19 '16 at 12:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.