Mysql Multiple data files rollover size

Question

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.

Answer 1

((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?).