• This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn more.

Why InnoDB is not really a good idea when you do something stupid...

MGSteve

Well-known member
#1
... like accidentally deleting an InnoDB database.

With myISAM - fine, just restore the files from the backup. With innoDB - sure, you could restore the InnoDB files, but you'd loose any changes in any other innoDB files made since the backup was done.

I guess the only alternative is to do a dump of the InnoDB data before each backup, but that's hardly ideal.

Unless you turn on the option that creates an InnoDB file per table, but that's a bit like overkill. What would be better is an option to create an InnoDB file per database.

grrr. Fortunately nothing was deleted other than an empty XF install, but that's beside the point.


/rant over.
 

Jason

Well-known member
#4
You really should do regular dumps as well, anyway.

It's easier for a binary file to be corrupted without you ever noticing, until that point comes where you try and use it. At least dumped tables are stored in plain text, making it easier to spot any data corruption. Also, due to the fact they're stored in plain text, there is less chance of any serious data corruption compared, to say, binary files.
 

MGSteve

Well-known member
#5
When you say overkill with innoDB files per table what kind of overkill we are talking about? Can you please explain? Thanks
Overkill in so much that if you have loads of InnoDB databases with lots of tables, they all go in one folder and it'll make a helluva mess of the data folder!

erm, that said, I am assuming that's where they go, if its per table in the database specific folders, that's fine!

You really should do regular dumps as well, anyway.

It's easier for a binary file to be corrupted without you ever noticing, until that point comes where you try and use it. At least dumped tables are stored in plain text, making it easier to spot any data corruption. Also, due to the fact they're stored in plain text, there is less chance of any serious data corruption compared, to say, binary files.
I just wish there was a way to do full & incremental dumps in mySQL though, otherwise in our case, we'd be dumping out 3-4GB of InnoDB tables every night just so they get backed up
 

Deebs

Well-known member
#6
Innodb table per file basically performs the same way as MyISAM, you get a directory named after the database, then all tables appear as individual files within the directory. Same as MyISAM. Benefits of innodb table per file include supporting different file format types, compression of tables, easier to reclaim space when optimizing tables (the system table space cannot reclaim space so if all databases are in there and you delete them and want the space back, tough).
 
#7
Innodb table per file basically performs the same way as MyISAM, you get a directory named after the database, then all tables appear as individual files within the directory. Same as MyISAM. Benefits of innodb table per file include supporting different file format types, compression of tables, easier to reclaim space when optimizing tables (the system table space cannot reclaim space so if all databases are in there and you delete them and want the space back, tough).
Does that causes more I/O usage? Sorry, I am just trying to figure good InnoDB settings for my new forums. Which is the best option? InnoDB files per table or without it?
 

Deebs

Well-known member
#8
It allows you better control over I/O as you can then move stuff around more easily. Personally I always use InnoDB files per table.
 

MGSteve

Well-known member
#9
Does that causes more I/O usage? Sorry, I am just trying to figure good InnoDB settings for my new forums. Which is the best option? InnoDB files per table or without it?
No more than using myISAM instead, but you get all the benefits of InnoDB goodness.

It allows you better control over I/O as you can then move stuff around more easily. Personally I always use InnoDB files per table.
And helps when restoring after stupid mistakes - hopefully.
In any case, one of my pet hates with InnoDB is the fact the space isn't freed up when you delete a database, using the files option fixes this.

Thanks for your feedback, I will have to implement this on my server :)
 

CyclingTribe

Well-known member
#10
It only applies to tables created after the setting is applied; it doesn't shrink your existing ibdata1 file (some instructions on how to possibly do that here, but I'd make sure you have a backup before trying it!).

Actually it's got me thinking - I've got a 5GB ibdata1 file and I know that probably over half of that is empty space (from my previous software and demo/test installs etc.) so I might have a go at trying to shrink it a little with the forced re-application of the engine type.
 
#11
Are this good settings for innoDB? Where do u add option for innoDB files per table?

Code:
innodb_buffer_pool_size = 128M
innodb_log_file_size = 32M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
innodb_thread_concurrency = 16
innodb_additional_mem_pool_size = 8M
 
A

account8226

Guest
#12
Does anyone knows why my resource manager tables are stored with InnoDB, contrary to my XenForo's tables that are stored with MyISAM.

Is that a choice from Kier and Mike to use InnoDB ? Or just my server that did not import correctly the RM's tables.
 
A

account8226

Guest
#14
A

account8226

Guest
#17
Don't listen to @Brogan. He just makes stuff up for fun.

(That was a joke, and yes, he's sure - and correct).
You probably have MyISAM set as the default engine (or skip InnoDB in your my.cnf).
It's probably because I imported from vB to xF like 1 year ago.

What do you suggest to me ? Should I convert the MyISAM xf's tables to InnoDB ?
I'm afraid to corrupt my database by doing this.

  • And MyISAM is taking AGES to backup. It's really really much longer :(
What should I do ?
 

Brogan

XenForo moderator
Staff member
#18
The tables have been set to the various engines for a reason.

You don't have to use the recommended settings but you may encounter problems.
 
A

account8226

Guest
#19
The tables have been set to the various engines for a reason.

You don't have to use the recommended settings but you may encounter problems.
Do you think it will be ok by running an alter table query onto theses tables (without corrupting everyting) from MyISAM to InnoDB ?