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

XF 1.2 UGH! Converted Forum - Now I find out about INNODB

Discussion in 'Installation, Upgrade, and Import Support' started by ProCom, Nov 8, 2013.

  1. ProCom

    ProCom Active Member

    Well, isn't this just peachy!

    I spend months evaluating xF, testing my conversion, and today I finally pull the trigger on my biggest site conversion. I'm 6 hours into the conversion (to give you an idea it took 2 hours to import posts / topics) and then while my search cache rebuild is underway I decide to browse the forum here.

    Well, I stumble on this MySQL: MyISAM or INNODB thread and check my tables...

    EVERY SINGLE ONE OF THEM IS MyISAM!!!


    So, what do I do? I mean, my forum seems to be running fine, but it also hasn't been hammered by a bunch of concurrent users yet.

    The last time I tried to convert a platform from MyISAM to INNODB (another project different platform) it went terribly terribly wrong and I had to do a full server restore and roll back to MyISAM. The thought of going through that again is terrifying at best.

    So my questions for the masters:
    1. Why the heck on a brand new install weren't the correct table structures setup?
    2. Do I "have to" convert the tables to INNODB?
    3. What happens if I don't convert to INNODB?
    I vaguely remember that some of the problems I ran into before was related to the fact that I'm still on a 32 bit CentOS and innodb didn't play well with my settings, memory, or whatever :(

    Some of my tables aren't super huge, but also not tiny:
    upload_2013-11-7_23-17-46.png

    I'm kinda freaking out about this, so thanks in advance for your help and suggestions!
     
  2. MattW

    MattW Well-Known Member

    To answer 1, I suspect the default engine set in your MySQL install is MyISAM, which is why on a fresh install, the tables are MyISAM.

    You can also change the tables to InnoDB with phpmyadmin, you just have a to do a table at a time. I've changed a phpbb3 forum from MyISAM to InnoDB, and back again with no ill effects.
     
    Chris D likes this.
  3. Chris D

    Chris D XenForo Developer Staff Member

    It's worth noting that if you do decide to convert the tables there's a couple which are deliberately MyIsam I think.
     
    MattW likes this.
  4. ProCom

    ProCom Active Member

    Thanks guys!

    Yup, I've seen the masters, like Brogan post about the tables that should remain myISAM, but I'm just freaking out about the process of converting them and if it will make things better.

    As it is right now, my loads on my relatively beefy VPS are going up and there are barely any peeps on my forum. I'm guessing it might have to do with the table types, but at this point that's totally just a guess.
     
  5. MattW

    MattW Well-Known Member

    What do you have in your my.cnf file? A few people report that load increases after conversions when MySQL isn't tuned for the new XF setup
     
    Chris D likes this.
  6. ProCom

    ProCom Active Member

    Here's my.cnf:

    So, it looks like innodb is turned off? This is starting to get over my payscale / skill level for sure.
     
  7. MattW

    MattW Well-Known Member

    Yes, InnoDB is disabled, so as it stands, you'd not be able to convert the tables.

    You need to remove the skip-InnoDB value from the config file, and remove the hash before the other InnoDB settings, as they are currently commented out.

    Then restart MySQL.

    You will then be able to use InnoDB.
     
    SneakyDave, ProCom and Chris D like this.
  8. MattW

    MattW Well-Known Member

    Also, if you want any help doing the above, drop me a PC, and I'll be happy to help out.
     
    TheBigK, Chris D and ProCom like this.
  9. CyclingTribe

    CyclingTribe Well-Known Member

    Firstly, relax - it's something that can be sorted out with a little time and patience.

    Secondly I would expect the server load to be a little higher at the start because visitors are downloading everything afresh (caching should help to lower the load as more people login and things move forward), usergroup promotions are checked at first login (and lots of people will rush in at the start), people will "play" with the new software (and you'll be doing lots of admin stuff as things crop up), and you're unlikely to be optimised for XF on your server at the start - which, if you ask here, will be something you can get help with once you're settled in. (y)

    Cheers,
    Shaun :D
     
    Last edited: Nov 8, 2013
    gldtn, ProCom and MattW like this.
  10. CyclingTribe

    CyclingTribe Well-Known Member

    Oh, and good luck. (y)
     
    ProCom likes this.
  11. ProCom

    ProCom Active Member

    Thanks for all the help Matt!

    Hopefully this discussion will help some other poor saps er... newbie users like me that got into the same bind.

    Is there any way to quantify how much better INNODB is for xF than myisam? I mean, is it like I put diesel gasoline in my unleaded car and now it's all gummed up, or are we talking the difference between 87 and 88 octane gas (i.e., very slight difference)?

    Also, are the sizes of those tables such that I would expect to run into problems converting them to Innodb in phpmyadmin?
     
  12. MattW

    MattW Well-Known Member

    No, should be fine converting those to InnoDB via phpmyadmin once you have enabled it in my.cnf

    Probably best to check the max execution time allowed for PHP in your PHP.ini file to make sure the script can run for as long as needed.
     
    ProCom likes this.
  13. ProCom

    ProCom Active Member

    Thanks Shaun! <breathing heavily into paper lunch bag>
     
    CyclingTribe and MattW like this.
  14. ProCom

    ProCom Active Member

    My friend that runs a bunch of stuff on xF just shot over the following... how's this look?

    It made me wonder if all the tables from various addons should all also be innodb too? I'm assuming they should be.
     
    MattW likes this.
  15. MattW

    MattW Well-Known Member

    Looks good!

    I'd dump a copy of the DB before running it just incase.

    All the add ons I've got installed have InnoDB tables as they pick up the default table type when they are installed.
     
  16. HWS

    HWS Well-Known Member

    Before you convert to InnoDB it is worth to get familiar with the very different administration tasks of that table engine. If you are used to MyISAM you have to change all infrastructure. InnoDB tables cannot be handled as easily as MyISAM tables.

    Maybe you have to change your backup solution also.

    And last but not least check if there is a reason why InnoDB is disabled at your machine.
     
  17. ProCom

    ProCom Active Member

    Ugh, I have MORE complexity coming my way?

    Also, I finally got my backup processes / scripts all configured and now I may have to change those too?!?! <sad face>

    I believe InnoDB was disabled since we just never used it on our VPS and never expected we would.

    On a side note, I had a bit of a freak-out! I removed the skip-innodb line and removed the commented out "#" and restarted mysql and got this nasty error:

    root@server [/var/lib/mysql]# /etc/init.d/mysql restart
    ERROR! MySQL manager or server PID file could not be found!
    Starting MySQL...... ERROR! Manager of pid-file quit without updating file.

    ... and then all my sites were dead. :eek:

    After some testing I discovered it was one of the following (which I recommented and restarted):
    #innodb_data_home_dir = /var/lib/mysql
    #innodb_data_file_path = ibdata1:250M:autoextend
    #innodb_autoextend_increment = 50M
    #innodb_log_group_home_dir = /var/lib/mysql
    #innodb_log_arch_dir = /var/lib/mysql

    So, one of those was the thing that gave me that error and didn't let me restart mysql
     
  18. MattW

    MattW Well-Known Member

    You should have an error log in your /var/lib/mysql folder that should give an indication why it's not starting. Looking at those, I suspect it could be either or both of these

    #innodb_data_file_path = ibdata1:250M:autoextend
    #innodb_autoextend_increment = 50M
     
  19. HWS

    HWS Well-Known Member

    You disabled very important lines in your config.

    I highly recommend that you get familiar with InnoDB first (with reading the MySQL documentation) and stay with MyISAM until you are comfortable with it.
     
  20. ProCom

    ProCom Active Member

    Trial and error... it was this one:
    #innodb_log_arch_dir = /var/lib/mysql

    How much do I have to worry about fine tuning all these settings before converting over to innodB? I'm on VPS, 32 bit CentOS version 5.10 with 4 GB RAM.
     

Share This Page