MySQL – Managing Multiple Data Files Rollover Size

MySQLpercona

we want to setup the mysql ibdata files in such a way that after every 100M , new data is added to another file specified in innodb_data_file_path

i.e. innodb_data_file_path=ibdata1:100M;ibdata2:100M:ibdata3:100M etc

and mysql uses it first uses ibdata1, when its filled uses ibdata2 etc.

Is it possible? if so how to add configuration?

EDIT:
We have a huge database of many gigs and we ran out of space recently. We could not upgrade the disk, so we are thinking of having the new data automatically go to new files and have flexibility of moving those files to newly added physical disks.

we do not have innodb_file_per_table ON, but we have many dynamically created tables per second. We think that all that diskIO and open files might create some corruption.

Best Answer

((100G is not the same as 100M. typo was fixed))

The line you gave is what you do -- in my.cnf. (Double check the syntax.)

That configuration was very important 15 years ago -- when some operating systems could not create a file bigger than 2GB (or 4GB). Now is is just an artifact. Most people simply have autoextend on the last entry and forget about it.

The config says, as you say, "fill up one before moving on to the next". The number (eg, "100M") is the pre-allocated size.

Why do you want this config? I am curious as to whether you have a use case for current machines.

It is usually better to have innodb_file_per_table=ON. Each table that you subsequently CREATE or ALTER is put into it's own "tablespace", which is identified by a file: <tablename>.ibd. At that point, you don't need a huge, growing, set of ibdata* files. (Leave autoextend on the last one, just in case.)

Limitations... There is no way to have that config automatically add another ibdata* file name, only autoextend the last one. Once created, you can't get rid of any of the files without dumping and reloading.

EDIT. "but we have many dynamically created tables per second" -- Ouch! How many tables do you have? How tiny are they? In general, thousands of tables is bad. Might it make sense to combine tables in some way? Would you care to elaborate on the design that 'needs' lots of tables?

Lots of tiny tables are better off in ibdata* (file_per_table = OFF). The OS overhead for more files is non-zero. (For big tables, the maintenance benefit of .ibd wins.) Regardless of the setting, there will be a .frm file per table.

Yes, lots of ibdata* files would let you migrate some to new drives. Use "symbolic links" (if unix) or the equivalent if Windows (Junctions?).

Related Solutions

Mysql – Too much IO to MySQL InnoDB log files

Based on MySQL Documentation and other options

[mysqld]
innodb_log_file_size    = 1G
innodb_log_buffer_size  = 256M
innodb_read_io_threads  = 16
innodb_write_io_threads = 16
innodb_purge_threads    = 1

Make your log files 1G: Small log files cause many unnecessary disk writes. The documentation says make the log file the same size as the buffer pool.
Make the log buffer 256M: The larger the log buffer, the less frequently flush logs are. You have it at 8M, which is the default.
Increase read and write I/O threads
make the purge thread independent of the master thread.

MySQL – Safely Delete Old ibdata File

Great question, and a very common issue.

When you changed the server configuration, to innodb_file_per_table = 1, the tables still lived on the common tablespace. You still had to recreate those using ALTER TABLE ENGINE=InnoDB (or any other operation that forced a tablespace recreation).

Once you have done that, why does MySQL still have a huge ib_data1? The truth is that InnoDB cannot really shrink (defragment) its tablespace files. That is not a huge issue in most cases, because the free space inside every file can be used by other data, index, etc. pages, and once you start using innodb_file_per_table you can always recreate each individual tablespace individually in order to free filesystem space.

However, the common tablespace is special. Even if it didn't contain any table or index information, it is always compulsory as it stores common data like the data dictionary, change buffer, etc. So if you delete it, you would end up with a fully unusable mysql installation. Answering your questions: 1) Yes it is being still used and 2) you won't be able to restart mysql again if you delete it. But, obviously, most of the ibdata1 file will be empty, it is just that it is very difficult to move page addresses around.

Here is a Pictorial Representation of InnoDB Architeture so you can see the contents of ibdata1

InnoDB Architecture

So, do you have to live with the 10GB file forever? Well, it is certainly a possibility, but there are ways to get rid of it, depending on the server version and your need for availability:

One approach, if availability is not a problem, and the database is small, would be to dump the whole database into a .sql or .txt with tools like mysqldump or mydumper,stop the server, delete all the files (or at least, all the innodb files), start mysql again and reload the data making sure that innodb_file_per_table is on. Very slow, but very easy.
If you do not want to suffer such a long process, there are ways to do it fully online, using a slave- you just set it up right from the beginning, and then switch nodes. If you do not have a second box, you can setup a temporary "slave" on the same machine (with the problem of extra resources needed)
If you use MySQL >= 5.6, you can move tablespaces around with IMPORT TABLESPACE. That would be a way to perform a quick backup, stop and wipe, and restore in binary format (way faster than with .sqls). Some tools like Percona XtraBackup may be useful for that.

This is a very common operation I've been asked about and performed a MySQL DBA. Hope this helps.

Best Answer

Related Solutions

Mysql – Too much IO to MySQL InnoDB log files

MySQL – Safely Delete Old ibdata File

Related Question