Best Storage Engine for MYSQL / Site Speed Improvements

[celiac101]

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?

 

[24x7server]

Hi,

I've read that innodb performs faster with MariaDB than MyISAM...is this true?

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

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?

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

Does anyone have additional suggestions to speed up this large, busy php/mysql site on this old server?

php-fpm on all sites may load up things, try switching limited sites on it and measure the performance thereafter..

 

[cPanelLauren]

Hello @celiac101

I've read that innodb performs faster with MariaDB than MyISAM...is this true?

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

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?

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

Does anyone have additional suggestions to speed up this large, busy php/mysql site on this old server?

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,

 

[celiac101]

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

 

[cPanelLauren]

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.

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

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:

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,

 

[celiac101]

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.

 

[cPanelLauren]

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,

 

[celiac101]

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?