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

converting 8GIB database to InnoDB

Discussion in 'Installation, Upgrade, and Import Support' started by xmlxp, Feb 7, 2012.

  1. xmlxp

    xmlxp Active Member

    when I imported my vBulletin database to xenforo the InnoDB was disabled in my server and i didnt know i should check if it is disabled or enabled because it is not mentioned in xenforo manual so all my database tables are MyISAM, how I wish that xenforo-requirements-test or xenforo install script includes InnoDB enabled check :)

    1. I want to know if converting for my case to InnoDB is a must OR highly recomended???

    if it is a must then this mean i dont have time and i have to convert right now or the database will be corrupted because xenforo is programmed for InnoDB .

    if it is highly recomended then it mean xenforo can work with MyISAM with no serious issues and no fear of database corruption and i have plenty of time to convert to InnoDB any time i want

    I really want the answer to be highly recomended so I can take all my time to think and plan to make a very carefull checklist for the conversion from MyISAM to InooDB

    2. I have 2 tables that really concerns me because of thier size is very large which might lead to conversion failuer :

    xf_post ( 3,726,212 rows and 2.1 GIB size )
    xf_search_index ( 3,833,158 rows and 4.9 GIB size )
    Edit: xf_search_index should be MyISAM so it wont need to be conveted

    The total dabase size is 8 GIB and 20 millions rows

    Do you think phpMyAdmin can handle the conversion? what other things i should consider?

    Thanks in advance for any reply
  2. DBA

    DBA Well-Known Member

    <slightly off topic>

    I'd recommend implementing the XF Enhanced Search as it'll allow you to drop the xf_search_index table, reducing your database size by over half.

  3. xmlxp

    xmlxp Active Member

    Thank you DBA

    XF Enhanced Search will be in my list now and my concerns now are lowered by 50% :)

    waiting kindly to hear from xenforo experts the comments on the rest of my questions
  4. Brogan

    Brogan XenForo Moderator Staff Member

  5. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

    Kier recommends making the switch:



    I have never tried changing the storage engine on such a large database. I prefer to run large queries using the mysql prompt in the shell. That way the connection won't possibly timeout before the query returns. But even if you use phpmyadmin and the connection times out, the query should still finish you just won't be notified.

    Make a backup first.
  6. xmlxp

    xmlxp Active Member

    Hello Borgan and Jake

    from what I read, I understand if I choose to keep my xenforo board database on MyISAM I ONLY just have to live with table locking as it was for me the past 10 years with my vBulletin board 2.x and 3.x? well, my vBulletin database been all those years on MyISAM and table locking did not cause me any real trouble

    actually I am fairly satisfied currently with the performance and the speed of my Xenforo boared on MyISAM, and from what I read from google search, InnoDB is a very resource intensive and certinly the board will be slower in case i converted to InnoDB unless I moved to a high performance server with at least 16 rams of memory according to my current database size.

    Any comments from mysql experts will be very apreciated
  7. Brogan

    Brogan XenForo Moderator Staff Member

    I'm fairly sure Jake, Kier and Mike are more than qualified to comment.
  8. Slavik

    Slavik XenForo Moderator Staff Member

    Few things.

    Remember that over HALF of your database is the search tables. This should ideally be dropped in favor of Elasticsearch. Those search tables however are not run on InnoDB, they are left as MyISAM anyway.

    InnoDB provides numerous benefits over MyISAM, so you should make the switch. Just remember, not every table in XenForo uses InnoDB, off the top of my head you need to leave the import log, the search tables, the session tables as MyISAM.
  9. Rudy

    Rudy Well-Known Member

    In my case it depended more on traffic, not the number of database rows. Several years ago, MyISAM was the cause of our forum grinding to a halt: simultaneous postings were locking up vB's post table for several minutes at a time. It did take awhile to convert to InnoDB back then, but it immediately cleared up the issue.

    We now routinely have 800+ members online on our vB 3.7 forum during peak hours and most days, never have a problem. Back then, I'd say we had 450-500 members online during peak hours, and the forum was all but inaccessible before I switched away from MyISAM.

    To be honest, I even had the table locking issue on a very low volume phpBB2 forum. I converted a handful of tables to InnoDB and the occasional (but annoying) delays were removed permanently.

    In general, tables that are primarily read-only (IOW, updated only a couple of times per day) would probably live nicely on MyISAM. But if the table is in constant use, being written to, InnoDB will prevent problems with locking.
  10. xmlxp

    xmlxp Active Member

    Hello Rudy

    my board have around 250 - 300 online members with around 1000 new post every day and i did not notice niether the board members any slow in the performance or speed of the board. the only locking happen is just when i do backups. i can confirm from what i saw on my board that xenforo can work efficiently on MyISAM if the server and mysql are well optimised.

    I have no idea how well my board will perform on InnoDB But from what i read in google searches InnoDB is a lot more resource intensive and slower than MyISAM and its for comercial websites more than personal websites.

Share This Page