Advice on Innodb datafile sizes


Id like some advice regarding MySQL 5 innodb datafile sizes.

I have a DB containing 25 Gb of data (data + indexes), about 60 million rows.

What is best, to have one single datafile, or several? If several is best, whats the best datafile size? (I guess Ill have 100 Gb of data soon).

Can I configure MySQL to create an additional datafile automatically when needed? For example, I would like to have 4 Gb datafiles, and when the last datafile gets full, MySQL creates another 4 Gb datafile automatically. Is that possible?

What is the advantages and disadvantages of having one or several datafiles? Performance, backups?

Im moving this database to a CentOS 5.1 64bit system, 8 Gb RAM, 1 Tb HD.

Thank you
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

I have always found it better split the files just in case you need to move files around to other spindles.  I create 4G files .

Splitting the files helps when you want to move things around from a disk management perspective.  backups I normally use mysqldump for backups
Marcus BointonCommented:
One problem with InnoDB files is that they only ever get bigger. If you set innodb_file_per_table in your my.cnf you can get finer control of your data split, plus you recover space after deletes.

I use mysqldump for backups too - the advent of --single-transaction makes it easy to take consistent backups without having to shut down.
vetrixicodeAuthor Commented:
Tks for the comments guys.

My main concern is performance and stability.
How large can I let a datafile grow? Is it safe to let it grow really big? It will be a Linux system.
Using innodb_file_per_table seems a good ideia. Is there any disadvantage to that?
Is there a way to configure MySQL to automatically create a new datafile when a certain size is reached?

Thank you
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Marcus BointonCommented:
You'll gain far more performance with partitioning (especially in MySQL 5.1) than you will by trying to optimise file sizes...

Alternative solutions exist without having to upgrade MySQL:

If you've got budget for consultancy on this kind of stuff, these guys are very good:
vetrixicodeAuthor Commented:
Hi Squinky.

Using partitioning is a good ideia, but the questions remain...
Even with partitioning, how large can I let a datafile grow? Is it safe to let it grow really big? Is there any disadvantage to using innodb_file_per_table? Is there a way to configure MySQL to automatically create a new datafile when a certain size is reached?

Marcus BointonCommented:
I couldn't tell you for sure, but the docs say that the max tablespace is 64Tb. I think if you had that much data you might have bigger concerns!

You can create multiple InnoDB tablespace files of whatever size you like, but only the last one defined can be auto extending.

If you set innodb_data_home_dir to an empty string, you can put each innodb data file in a separate absolute path, which makes it easy to spread across spindles, as Hardware4200 said. How much this will gain I don't know - if all your DB activity is within the most recent data file, then all your activity would be on one spindle anyway. I suspect that splitting by table would be better from that point of view simply because you're likely to have more of them, so random accesses will spread the load further. Again, partitioning would probably give much bigger returns anyway.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.