1. 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...

Discussion in 'Off Topic' started by MGSteve, May 31, 2012.

  1. MGSteve

    MGSteve Well-Known Member

    ... 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.
  2. Deebs

    Deebs Well-Known Member

    Personally I would recommend that everyone uses innodb file per table regardless.
    CyclingTribe likes this.
  3. hellreturn

    hellreturn Active Member

    When you say overkill with innoDB files per table what kind of overkill we are talking about? Can you please explain? Thanks
  4. Jason

    Jason Well-Known Member

    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.
  5. MGSteve

    MGSteve Well-Known Member

    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!

    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
  6. Deebs

    Deebs Well-Known Member

    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. hellreturn

    hellreturn Active Member

    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?
  8. Deebs

    Deebs Well-Known Member

    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.
  9. MGSteve

    MGSteve Well-Known Member

    No more than using myISAM instead, but you get all the benefits of InnoDB goodness.

    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 :)
  10. CyclingTribe

    CyclingTribe Well-Known Member

    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. hellreturn

    hellreturn Active Member

    Are this good settings for innoDB? Where do u add option for innoDB files per table?

    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
  12. account8226

    account8226 Guest

    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.
  13. Brogan

    Brogan XenForo Moderator Staff Member

  14. account8226

    account8226 Guest

    Are you sure about this :confused: ?
    That's really strange, all my XF tables are MyISAM (and it's a lot faster to backup as en example).

    Should I change to InnoDB ? I think I am using MyISAM for more than 1 year, and I never had any problems with more than 1 millions posts.
  15. Chris D

    Chris D XenForo Developer Staff Member

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

    (That was a joke, and yes, he's sure - and correct).
  16. CyclingTribe

    CyclingTribe Well-Known Member

    You probably have MyISAM set as the default engine (or skip InnoDB in your my.cnf).
  17. account8226

    account8226 Guest

    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 ?
  18. Brogan

    Brogan XenForo Moderator Staff Member

    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.
  19. account8226

    account8226 Guest

    Do you think it will be ok by running an alter table query onto theses tables (without corrupting everyting) from MyISAM to InnoDB ?
  20. Brogan

    Brogan XenForo Moderator Staff Member

    Several people have done it without issue.

    Take a backup first.

Share This Page