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

InnoDB or MyISAM - what is better for xenForo ???

Discussion in 'XenForo Questions and Support' started by shenmuee, Mar 12, 2011.

  1. shenmuee

    shenmuee Active Member

    i was wonder as a continuity to this post. if someone have some spare time, please share with me (and others) your feelings about using InnoDB and MyISAM storage engines in MySQL?

    Which one is faster or serves better for xenForo, in your opinion?

    My xenForo phpmyadmin issed;

  2. Brogan

    Brogan XenForo Moderator Staff Member

    Here are a few posts about them:

    And this post by Kier:
    shenmuee likes this.
  3. shenmuee

    shenmuee Active Member

    Thank you Brogan I'm going to read them all >.<
  4. Mike

    Mike XenForo Developer Staff Member

    I should note that the vast majority of the default tables should be InnoDB - we actually specifically create them as that. I can only guess your server settings changed after installation.
    shenmuee likes this.
  5. Brogan

    Brogan XenForo Moderator Staff Member

    Ah yes, good spot there Mike.
    I hadn't realised that the XenForo tables were incorrect.
  6. shenmuee

    shenmuee Active Member

    @Brogan! senin xenForo tablolarını görebilir miyim acaba? bir ekran görüntüsü ekleyebilir misin?
    benim tablolar yanlış sanırım =/
  7. Brogan

    Brogan XenForo Moderator Staff Member

    Evet, senin tabloların yanlış. Bu dogru.
    Yes, your tables are incorrect. They should be:



    The rest are InnoDB.
    shenmuee and Mike like this.
  8. shenmuee

    shenmuee Active Member

    teşekkürler Brogan. how do i fix my tables like yours?
  9. Brogan

    Brogan XenForo Moderator Staff Member

    You can change the type by selecting the table, clicking on the Operations tab, then choosing the Storage engine.

    You should investigate why they changed though as that may occur again in the future.
    shenmuee likes this.
  10. Jaxel

    Jaxel Well-Known Member

    Basically when it comes down to it... MyISAM is better for WRITING, InnoDB is better for READING.

    For tables that get written to often, such as session tables, it would be better for MyISAM. But tables are are just storages of information to be parsed later (like 99% of the tables out there), InnoDB is probably better.

    That being said, MyISAM also supports full-text searching, while InnoDB does not.
    shenmuee likes this.
  11. Kier

    Kier XenForo Developer Staff Member

    That's true, but misses some important points, the most important of which is support for table-level or row-level locking.

    While MyISAM is faster for writing, this comes at the expense of locking the entire table while write operations are happening. This is never a problem during development, when there are rarely going to be more than a couple of people concurrently connecting to the database, but in full-scale production when there are potentially thousands, it manifests itself as a serious lack of scalability.

    InnoDB, while slower, is hugely more scaleable and reliable because of its support for transactions (the ability to roll-back changes in a multi-step operation) and row-level locking, so that during write operations only the affected rows are locked rather than the entire table, which allows multiple operations to be executed simultaneously on the same table.

    On balance, InnoDB is a superior solution, and should be the default choice of table type unless there are specific reasons to require another type, such as wanting to use MySQL Full Text searching (requires MyISAM) or extremely fast non-permanent data throughput (could use a HEAP / MEMORY table).
    AndyB, shenmuee and Walter like this.
  12. shenmuee

    shenmuee Active Member

    Ayrıca bu sql komutu işe yaradı >.<

    alter table xf_admin engine=innodb;
  13. Brogan

    Brogan XenForo Moderator Staff Member

    Yes, that's another way of doing it :)

    Did you find out what caused them to change?
  14. a legacy reborn

    a legacy reborn Well-Known Member

    I have the same problem...fixing...

  15. jeffwidman

    jeffwidman Active Member

    I know this is an ancient thread, but wanted to add for anyone who stumbles across via Google.

    If you don't know what version of MySQL you're running, just ignore--this isn't for you.

    MySQL 5.6 and MariaDB 10 support fulltext indexes on InnoDB tables, so if you're running one of those, you can also convert xf_search_index to InnoDB. The one thing you'll need to change is in the Admin Control Panel > Performance, you'll need to uncheck use delayed insert. I know it's implied it's a good thing to check, but it's not supported on InnoDB tables. It's basically a hack to get MyISAM to do what InnoDB does natively, and if you try to leave it on for InnoDB tables, your users won't be able to post to the forum anymore (found out the hard way).

    I switched the search index table to InnoDB on a forum that has >2M posts, and was shocked by how much faster the search index rebuild went compared to when it was MyISAM. I did a little researching, and basically on large tables InnoDB has a few characteristics that make for much faster inserts. It's also probably affected by me having a small MyISAM cache but a large InnoDB buffer pool. I'm also caching all my sessions to Memcached, so I went ahead and just switched all the tables to InnoDB. That lets MySQL handle figuring out what should be cached without me having to pick and choose whether to push more RAM toward MyISAM or InnoDB.
    Last edited: Jun 12, 2015
    hellreturn and DRaver like this.

Share This Page