MySQL InnoDB, ibdata1 and innodb_file_per_table

C

ccto

Verified User
Joined
Feb 24, 2005
Messages
290
Location
Hong Kong
Hello Everyone,

If you are running with MySQL with InnoDB (MySQL 5.5 requires InnoDB on too),
then you shall find the /var/lib/mysql/ibdata1 always growing in size.

May I know any one using the parameter - innodb_file_per_table - to split the InnoDB data file into individual table?
Any impact?

ref.:
http://forums.cpanel.net/f43/innodb_file_per_table-converting-per-table-data-innodb-167942.html

How about existing running MySQL database?

Thank you very much for your sharing.
Regards
George
 
Directadmin itself does not use any databases. If you have custom software that does then it is up to you to try that or not.
 
@ccto, thank you for posting this. This was new to me. I read the thread at cPanel, and I have the same question as you, and as user "jols" has in post number 10: http://forums.cpanel.net/f43/innodb...-per-table-data-innodb-167942.html#post773842

I am now considering to add innodb_file_per_table=1 to my.cnf but not converting existing data already in /var/lib/mysql/ibdata1 , but only have this for new databases created. I am also wondering if that would be safe? And also wondering if that would limit future growth of ibdata1?
 
Exactly, it is, in certain extend, difficult to maintain InnoDB (especially backup/restore, and single tablespace file)
It seems the best/official way to backup is mysqldump (but not good for large db, and always be afraid of non-file system level restore)

I have some ibdata1 db size is around 2GB.
(I guess it is small compared some other hosting server),
but the size will only grow up, never shrink in future (even you delete that user database).
 
It's to scary for me to dump, delete and import all databases.

So I was hoping I could add innodb_file_per_table=1 to my.cnf and keep the old /var/lib/mysql/ibdata1 so that only new databases would have its own ibdata file. Running a hybrid, sort of. Would that be safe to do? Would that be a improvement on shared hosting? Also, would it be needed to add other rules to my.cnf, or could I just use the defaults and only add the new line innodb_file_per_table=1 ?

I have been reading on this topic for the last few days, but I do not feel sure about these things.
 
Well, that's the way it works. If you don't feel comfy with backing up and restoring your databases, you might be heading to bigger problems in the future ;)

It is required because an optimize on an default innodb table will allways result in a larger file, due to the structure of the file.

If you don't really need innodb, you might be better of with myisam.
 
Stupid.

I am backing up all the databases every night to a external server.

However, I am running a shared hosting server, and would like to avoid to delete all users databases and import them again.

Of course I would like to continue to offer innodb on MySQL 5.5.x

Nobody seems to be able to answer if I can add the mentioned line to my.cnf and keep it the old way for existing databases, and only have sepearate ibdata for new databases, or if that is not a good idea?
 
LOL :)

Anyway, I didn't try it myself but you should be able to test it on a second server. Is you add that line and restart mysqld, it might be effective for newly created tables only, but that doesn't solve the problem with your current tables. But 'if' that works, you could write a simple shellscript to dump one table, delete it and recreate it when importing. You shouldn't drop the database in that case or you might mess up user access rights. But as always... test first. Even if I would tell you it would give any problems, you still should test it first ;)
 
I did that on one of my server a while ago without any problem. However, when I did that, I add 2 lines below : (More info at http://dev.mysql.com/doc/innodb/1.1/en/innodb-other-changes-innodb_file_per_table.html )

innodb_file_format=Barracuda
innodb_file_per_table=1

Then, the new table created will have separate file per table (.ibd) while the old one still use the same old ibdata file. But do you realize that even it does create it's own file per table but ibdata is still required? You can't just copy that file as a backup. I read it from somewhere but can't remember where it is.

However, I still use MyISAM as a default engine for now as a shared hosting and use InnoDB only for some selective sites. This is due to innodb_buffer_pool_size option that I used to read that it has to be larger than real data using InnoDB or the performance may worse than MyISAM. Never really try that though.


ditto said:
It's to scary for me to dump, delete and import all databases.

So I was hoping I could add innodb_file_per_table=1 to my.cnf and keep the old /var/lib/mysql/ibdata1 so that only new databases would have its own ibdata file. Running a hybrid, sort of. Would that be safe to do? Would that be a improvement on shared hosting? Also, would it be needed to add other rules to my.cnf, or could I just use the defaults and only add the new line innodb_file_per_table=1 ?

I have been reading on this topic for the last few days, but I do not feel sure about these things.
Click to expand...
 
@nmb, thanks for sharing! So when running a shared hosting server using Innodb as default engine with innodb_file_per_table=1, would you still recommend that I set innodb_file_format=Barracuda in my.cnf?
 
Ok, I give up. This Innodb mess is just over-complex, and I will not continue to take any risk using it as default eninge. So now I have set this in etc/my.cnf making MyISAM the default eninge for new databases created:

Code: 
[mysqld]
innodb 
local-infile=0
default-storage-engine=MyISAM
innodb_file_format=Barracuda
innodb_file_per_table=1
 
ditto said:
@nmb, thanks for sharing! So when running a shared hosting server using Innodb as default engine with innodb_file_per_table=1, would you still recommend that I set innodb_file_format=Barracuda in my.cnf?
Click to expand...

Yes, I recommend to use Barracuda format to gain all benefits from lastest MySQL file format. I also prefer the newest one if possible :)

Also, in InnoDB, you may need to adjust below variables to match your need :
- innodb_buffer_pool_size (Performance impact is huge if set it too low and default is 128MB in MySQL 5.5)
- innodb_log_file_size
- innodb_log_buffer_size
 
Sorry for reviving the old thread.

If your ibdata1 file is not huge yet and you don't like to delete all tables then reimport, you can enable innodb_file_per_table in my.cnf, then do alter table to storage=innodb on each innodb table in the system. That way you will create separate ibd file for the table.

Here is what you can do:

Code: 
select concat('alter table ',TABLE_SCHEMA ,'.',table_name,' ENGINE=InnoDB;') FROM INFORMATION_SCHEMA.tables where table_type='BASE TABLE' and engine = 'InnoDB';

* taken from the cpanel forums

This will list all commands that you must do. They are something like:

Code: 
alter table <db>.<table> ENGINE=InnoDB;

When you do it, the existing table will move away from the system table space in it's own data file. Better

Drawback - this will NOT shrink the system data file. So if it is huge already... sorry, you will have to reimport all old databases :)
 
Mysql 5.5 to barracuda

So you say if i switch to barracuda with 200 databases already in innodb new users will use barracuda with no problem?

No known problems on switch?

Thanks
 
No known problems to me.

The only drawback is that ibdata1 will still use it's previous space.
 
Pbgs said:
So you say if i switch to barracuda with 200 databases already in innodb new users will use barracuda with no problem?

No known problems on switch?

Thanks
Click to expand...

Please note that when you switch it to barracuda, it doesn't mean it will start to use barracuda format immediately. You have to change Row format to make use of it.
 
You must log in or register to reply here.
Back
Top