A lot of the times when we see a performance related issue on a website it’s related to the database not being optimized.
MyISAM is often a primary culprit and is an older, less efficient, and less reliable database storage engine than the more modern InnoDB.
Part of the reason for this is that MyISAM will lock whole database tables, which can have a logjam effect on database queries, where as InnoDB only has row-level locking which allows queries to process much faster. Converting can result in huge improvements in response time and reduced server load.
By default, vCanopy uses InnoDB, but older websites sometimes use MyISAM, and sometimes websites will have a mix of both.
Below will walk you through how to convert MyISAM to InnoDB. Be sure to take a backup of your database before making the following changes.
Converting via phpMyAdmin
If your website has a fairly small database or only a few tables that need converting, phpMyAdmin makes this quick and easy.
STEP 1. OPEN UP PHPMYADMIN
In your account, open up phpMyAdmin by clicking the database icon next to your website.
STEP 2. CLICK THROUGH TO YOUR DATABASE AND CHECK FOR MYISAM TABLES
The website in this example has a mix of both MyISAM and InnoDB as you can see below:
STEP 3. CONVERT YOUR TABLES INTO INNODB
Click on the table you wish to alter and then click through to the operations tab at the top.
You’ll see the following layout, and under storage engine you can select “InnoDB” from the dropdown and click “Go”.
You’ll see a success message as shown below, and you can repeat the same process for your remaining tables. Click your database name in left hand column to return to your table overview.
Converting via WP-CLI
If your website has a lot of MyISAM tables then it’s far more efficient to use WP CLI to convert them over into InnoDB.
STEP 1. SSH INTO YOUR SERVER
Please see the following articles to get started:
Generate your SSH Key:
Generate SSH Key on Windows with Putty
Generate SSH Key on Windows with Windows Subsystem for Linux
Generate SSH Key on Windows with Windows CMD/PowerShell
Add your SSH Key to vCanopy:
Add/Remove an SSH Key to/from an Active vCanopy Server
Connect to your server:
Connect to a vCanopy server by SSH as Root user.
STEP 2. CHECK FOR MYISAM TABLES
First navigate to your websites htdocs directory with the following command (switching out “site.url” for your domain name):
cd /var/www/site.url/htdocs
Next, run the following command to run the search:
wp db query "SHOW TABLE STATUS WHERE Engine = 'MyISAM'" --allow-root
In my case, we can see I have quite a few tables that need converting:
STEP 3. CONVERT MYISAM TO INNODB
Be sure that you’ve backed up your database before proceeding! You can create a backup with this command:
gp wp site.url db export /var/www/site.url/htdocs/name_of_backup.sql --all-tablespaces --add-drop-table
The following command will convert all MyISAM to InnoDB:
wp db query "$(wp db query "SELECT CONCAT('ALTER TABLE ',table_name,' engine=InnoDB;') FROM information_schema.tables WHERE engine = 'MyISAM' AND table_schema = '$(wp db size --format=csv --allow-root | awk -F, '{print $1}' | tail -1)'" --allow-root --silent --skip-column-names)" --allow-rootBig thank you to Cloud Keeper for the above code.
You’re now all set!