Please whitelist cPanel in your adblocker so that you’re able to see our version release promotions, thanks!

The Community Forums

Interact with an entire community of cPanel & WHM users!

Best Storage Engine for MYSQL / Site Speed Improvements

Discussion in 'Workarounds and Optimization' started by celiac101, Apr 25, 2018.

  1. celiac101

    celiac101 Well-Known Member

    Joined:
    Dec 19, 2012
    Messages:
    77
    Likes Received:
    1
    Trophy Points:
    8
    cPanel Access Level:
    Website Owner
    My server, hardware-wise, is rather old...~5 years, (it was a mid-level server at the time). It has ~130G of memory, and has the configuration below.
    • Intel(R) Xeon(R) CPU E5-2620 0 @ 2.00GHz
    • PHP Version 7.0.29
    • MySQL Version 5.5.5-10.1.32-MariaDB
    • default-storage-engine=MyISAM
    • Apache handler: PHP-FPM
    • Server version: Apache/2.4.33 (cPanel)
    I'm trying to speed things up on a large forum/article/blog site (~3 Gig database) with ~1M posts and ~1M unique users per month, which runs on this server.

    I saw measurable speed improvements in site speed tests after making the following changes, in this order, over the last few months:
    • Upgraded PHP from 5.6.x to 7.0.x;
    • Upgraded MySQL Version from 5.6 to 5.5.5-10.1.32-MariaDB;
    • Turned on PHP-FPM for all accounts;
    • Changed my Apache MPM from MPM Prefork to MPM Event;
    I have a few questions:
    1. I've read that innodb performs faster with MariaDB than MyISAM...is this true?
    2. If this is true, is switching from one to the the other as simple as changing default-storage-engine=MyISAM to default-storage-engine=innodb and restarting mysql?
    3. Does anyone have additional suggestions to speed up this large, busy php/mysql site on this old server?
     
  2. 24x7server

    24x7server Well-Known Member

    Joined:
    Apr 17, 2013
    Messages:
    1,888
    Likes Received:
    90
    Trophy Points:
    78
    Location:
    India
    cPanel Access Level:
    Root Administrator
    Hi,

    Yes, that is true and one reason if the row level locking..

    You may have to do this manually. Refer to the below link:
    MySQL :: MySQL 8.0 Reference Manual :: 15.8.1.4 Converting Tables from MyISAM to InnoDB

    php-fpm on all sites may load up things, try switching limited sites on it and measure the performance thereafter..
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  3. cPanelLauren

    cPanelLauren Forums Analyst II
    Staff Member

    Joined:
    Nov 14, 2017
    Messages:
    3,517
    Likes Received:
    251
    Trophy Points:
    193
    Location:
    Houston
    cPanel Access Level:
    DataCenter Provider
    Hello @celiac101

    This is true in most cases as InnoDB utilizes row level locking where MyISAM requires full table level locking. mariadb goes over the features of them both in their documentation Choosing the Right Storage Engine

    Not quite you have to change the storage engine in the table itself. Since you're using MariaDB they have documentation on how to convert from MyISAM to InnoDB Converting Tables from MyISAM to InnoDB

    You've already done the server specific items, which is awesome. You might want to look at a CDN like CloudFlare which could help performance with loading some resources, utilize caching (if you're not already), there are tools like Pingdom Tools which will break down for you what's taking the longest to load and why and offer some useful suggestions to increase the speed. You might also want to check out litespeed LiteSpeed Enterprise Web Server - LiteSpeed Technologies

    Thank you,
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  4. celiac101

    celiac101 Well-Known Member

    Joined:
    Dec 19, 2012
    Messages:
    77
    Likes Received:
    1
    Trophy Points:
    8
    cPanel Access Level:
    Website Owner
    Also, what about nginx? I do serve a fair amount of images on my site, and have heard that this will speed things up considerably. I've also heard negative things about it. I've been waiting to see it added as an option in Easyapache 4, and it has been planned for years now, but so far nothing.

    Ok, it looks like I'll next switch to innodb. I know this may sound silly, but the reason I'm still on MyISAM is because I know how to fix and repair tables when things go wrong...I mean I have a cheat sheet that's saved me several times over the years. I will assume that I can find similar commands to repair an index for a corrupted table, for example:
    • /etc/rc.d/init.d/mysqld stop
    • myisamchk --silent --force *.MYI
    • myisamchk -r --force --safe-recover /var/lib/mysql/my_table_name/*.MYI
    • myisamchk --recover /home/virtual/site4/fst/var/lib/mysql/my_table_name/*.MYI
     
  5. cPanelLauren

    cPanelLauren Forums Analyst II
    Staff Member

    Joined:
    Nov 14, 2017
    Messages:
    3,517
    Likes Received:
    251
    Trophy Points:
    193
    Location:
    Houston
    cPanel Access Level:
    DataCenter Provider
    I know we're moving toward offering nginx but I can't give you a date when that will occur. Nginx is an option especially for caching but as of right now it's not supported which is why I suggested Litespeed as a potentially viable option


    Repairing issues with InnoDB tables is a bit different than it is with MyISAM in that respect. MariaDB gives some great instruction on this:

    mysqlcheck
    REPAIR TABLE

    We do have a tutorial about repairing InnoDB corruption which might be a good idea to read before making any decisions:

    InnoDB Corruption Repair Guide

    Thank you,
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  6. celiac101

    celiac101 Well-Known Member

    Joined:
    Dec 19, 2012
    Messages:
    77
    Likes Received:
    1
    Trophy Points:
    8
    cPanel Access Level:
    Website Owner
    Ok, so the InnoDB repair guide is rather nightmarish...is it safe to say that one way that MyISAM beats InnoDb, hands down, is the simplicity of repairing a table that gets corrupted?

    Although I've done a ton of work to speed things up already, I will take another look at whether or not I should do the MyISAM to InnoDB transition. I wish I had a better idea of just exactly how much speed improvement it would offer.
     
  7. cPanelLauren

    cPanelLauren Forums Analyst II
    Staff Member

    Joined:
    Nov 14, 2017
    Messages:
    3,517
    Likes Received:
    251
    Trophy Points:
    193
    Location:
    Houston
    cPanel Access Level:
    DataCenter Provider
    Hi @celiac101

    As far as the ease of repair in those instances, you could say that but I will point out that MyISAM offers no data integrity in the event of a hardware failure or crash data could become corrupt, InnoDB uses a transactional log and records data to a tablespace file (ibdata1) InnoDB in most cases would be able to autorecover based on the data in the logs.

    There's a great resource on this subject at MySQL's forums here MySQL :: MySQL: InnoDB -vs- MyISAM

    Thank you,
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  8. celiac101

    celiac101 Well-Known Member

    Joined:
    Dec 19, 2012
    Messages:
    77
    Likes Received:
    1
    Trophy Points:
    8
    cPanel Access Level:
    Website Owner
    I forgot to mention that I am also running Memcached. How does this compare to Litespeed? Can Litespeed and Memcached be run together? If not, would it make sense for me to switch?
     
Loading...

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice