Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We have an application where just one of the table will be growing into million of lines but the rest will just below a million. So what is the advice should we go with innodb_file_per_table or leave just as one .ibd? I read some articles say do not go with it as you need more disk access when there joins to be performed? We will have join between this table and others for reporting generation purposes.

share|improve this question
add comment

3 Answers

up vote 7 down vote accepted

You must go with innodb_file_per_table and you need to do some how cleaning with InnoDB's current infrastructure.

I have seen many DB hosting clients setup MySQL and leave InnoDB in its default state. This causes the system tablespace (better known as ibdata1) grow wildly.

Even if you switched to innodb_file_per_table, the .ibd file would have to be extracted from ibdata1 and ibdata will never shrink. For example, if you have a table called mydb.mytable that is inside ibdata1 taking up 2GB, to extract it you have to the following:

STEP 01) Add this to /etc/my.cnf

[mysqld]
innodb_file_per_table

STEP 02) service mysql restart

STEP 03) ALTER TABLE mydb.mytable ENGINE=InnoDB;

That will make the file /var/lib/mysql/mydb/mytable.ibd

Unfortunately, the 2GB of space that was occupied by the table before the change cannot be reclaimed. I wrote past posts about how and why to cleanup InnoDB's infrastructure:

Once you make this major change, don't forget to increase innodb_open_files (default 300). Otherwise, disk access is very limited.

With regard to joins, make sure you have proper indexes that support the join criteria.

UPDATE 2012-04-02 11:30 EDT

Using innodb_file_per_table in a fresh installattion cause ibdata1 to grow very slowly because all DDL is done external to ibdata. You can shrink any InnoDB table as I mentioned before like this:

ALTER TABLE mydb.mytable ENGINE=InnoDB;

UPDATE 2012-04-02 16:50 EDT

When it comes to backups, be extremely careful making copies of .ibd files. Why?

Inside every .ibd file is special value known as the tablespace_id. There is a list of tablespace_id values in ibdata1. If you ever perform table maintenance that requires dropping and recreating the table, the tablespace_id will become different. Making a copy of such a .ibd file can only be intergated back into the database for usage if you also make a copy of ibdata1. That endangers the tablespace_id of all other InnoDB tables. In light of this, it is preferable that you perform mysqldump backups because mysqldumps are logical copies of the data. In other words, the backup is independent of the point-in-time of the ibdata1 and you are free to reload without operability issues.

share|improve this answer
    
this is going to be a fresh server.so what should I do then? Why do you say is good to go with innodb_file_per_table ? Overtime a this table will be growing what should be on look out any kind of limit errors? –  newbie14 Apr 1 '12 at 5:10
add comment

I have an application where I have exactly this situation: some big tables and some smaller ones.

I decided to leave the small ones in the ibdata1 while putting the bigger ones in their own files.

I do so by having innodb_file_per_table switched on by default and only switch it off temporarily for moving a table to ibdata1 with ALTER TABLE.

share|improve this answer
    
@gigigi how to leave the small ones in the ibdata1 and the big one in their own files isnt it that if you set this innodb_file_per_table means all will be in their own files? –  newbie14 Apr 3 '12 at 18:25
    
Only after you ALTER TABLE them. So my strategy is to have innodb_file_per_table switched on regularly and casually switch it off in order to move a small table into ibdata1 and afterwards switch it on again. –  glglgl Apr 3 '12 at 21:42
    
is it fine to remain always as innodb_file_per_table not a problem here right? –  newbie14 Apr 4 '12 at 2:48
    
should be ok, IMHO... –  glglgl Apr 4 '12 at 6:43
add comment

Agree with @RolandoMySQLDBA, for something he doesn't mention: backups.

If you have your MySQL backup regime all worked out, AND you aren't including its .ibd file in your file system backup strategy, then this is not so important. But consider that adding ONE BYTES to any innodb table will cause an incremental backup of your .ibd table other wise, and you can see that you'll quickly be running out of backup storage.

share|improve this answer
    
dear all yes I plan to run backup too. if I need master to master backup what difference is it from just master to slave? Is it both will backup each other is it? On the settings what need to be changed? –  newbie14 Apr 3 '12 at 2:55
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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