Advice on Innodb datafile sizes

Hello.

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
vetrixicodeAsked:
Who is Participating?
 
Marcus BointonConnect With a Mentor Commented:
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. http://dev.mysql.com/doc/refman/5.0/en/innodb-configuration.html

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.
0
 
Hardware4200Commented:
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
0
 
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.
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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
0
 
Marcus BointonCommented:
You'll gain far more performance with partitioning (especially in MySQL 5.1) than you will by trying to optimise file sizes... http://dev.mysql.com/doc/refman/5.1/en/partitioning.html

Alternative solutions exist without having to upgrade MySQL: http://sequoia.continuent.org/

If you've got budget for consultancy on this kind of stuff, these guys are very good: http://www.mysqlperformanceblog.com/mysql-consulting/
0
 
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?

Tks
0
All Courses

From novice to tech pro — start learning today.