MySQL Performance: MyISAM vs InnoDB
A major factor in database performance is the storage engine used by the database, and more specifically, its tables. Different storage engines provide better performance in one situation over another. For general use, there are two contenders to be considered. These are MyISAM, which is the default MySQL storage engine, or InnoDB, which is an alternative engine built-in to MySQL intended for high-performance databases. Before we can understand the difference between the two storage engines, we need to understand the term “locking.”
To protect the integrity of the data stored within databases, MySQL employs locking. Locking, simply put, means protecting data from being accessed. When a lock is applied, the data cannot be modified except by the query that initiated the lock. Locking is a necessary component to ensure the accuracy of the stored information. Each storage engine has a different method of locking used. Depending on your data and query practices, one engine can outperform another. In this series, we will look at the two most common types of locking employed by our two storage engines.
Table locking: The technique of locking an entire table when one or more cells within the table need to be updated or deleted. Table locking is the default method employed by the default storage engine, MyISAM.
|Example: MyISAM Table Locking||Column A||Column B||Column C|
|Query 1 UPDATE||Row 1||Writing||data||data|
|Query 2 SELECT (Wait)||Row 2||data||data||data|
|Query 3 UPDATE (Wait)||Row 3||data||data||data|
|Query 4 SELECT (Wait)||Row 4||data||data||data|
|Query 5 SELECT (Wait)||Row 5||data||data||data|
|The example illustrates how a single write operation locks the entire table causing other queries to wait for the UPDATE query finish.|
Row-level locking: The act of locking an effective range of rows in a table while one or more cells within the range are modified or deleted. Row-level locking is the method used by the InnoDB storage engine and is intended for high-performance databases.
|Example: InnoDB Row-Level Locking||Column A||Column A||Column A|
|Query 1 UPDATE||Row 1||Writing||data||data|
|Query 2 SELECT||Row 2||Reading||data||data|
|Query 3 UPDATE||Row 3||data||Writing||data|
|Query 4 SELECT||Row 4||Reading||Reading||Reading|
|Query 5 SELECT||Row 5||Reading||data||Reading|
|The example shows how using row-level locking allows for multiple queries to run on individual rows by locking only the rows being updated instead of the entire table.|
By comparing the two storage engines, we get to the crux of the argument between using InnoDB over MyISAM. An application or website that has a frequently used table works exceptionally well using the InnoDB storage engine by resolving table-locking bottlenecks. However, the question of using one over the other is a subjective as neither of them is perfect in all situations. There are strengths and limitations to both storage engines. Intimate knowledge of the database structure and query practices is critical for selecting the best storage engine for your tables.
MyISAM will out-perform InnoDB on large tables that require vastly more read activity versus write activity. MyISAM’s readabilities outshine InnoDB because locking the entire table is quicker than figuring out which rows are locked in the table. The more information in the table, the more time it takes InnoDB to figure out which ones are not accessible. If your application relies on huge tables that do not change data frequently, then MyISAM will out-perform InnoDB. Conversely, InnoDB outperforms MyISAM when data within the table changes frequently. Table changes write data more than reading data per second. In these situations, InnoDB can keep up with large amounts of requests easier than locking the entire table for each one.
Should I use InnoDB with WordPress, Magento or Joomla Sites?
The short answer here is yes, in most cases. We have encountered several table-locking bottlenecks when clients are using some of the standard web applications of today. Most users of popular third-party applications like WordPress, Magento, and Joomla have limited knowledge of the underlying database components or code involved to make an informed decision on storage engines. Most table-locking bottlenecks from these content management systems (CMS) are generally resolved by changing all the tables for the site over to InnoDB instead of the default MyISAM. If you are hosting many of these types of CMS on your server, it would be beneficial to change the default storage engine in MySQL to use InnoDB for all new tables so that any new table installations start off with InnoDB.
Past Experience and additional thoughts
Over the past 15 years, I’ve overseen some fairly significant web applications, powered by WordPress, which pushed the envelope in terms of what the CMS can do.
One instance saw us taking 5,000+ XML (NewsML + other types) files pushed to us from financial PR companies. Big ones. Every day. We spun this digital straw into WordPress posts, 24 hours a day, like if Rumplestiltskin had been a web developer. These posts were pushed into Google Finance, Yahoo Finance, and etc, and therefore we had major numbers of database writes and major numbers of database reads to contend with.
We’ve also built automatically loading e-commerce websites, running WordPress + WooCommerce, driven by daily data dumps in CSV and in XML formats, with product numbers into the xxx,000 range, when attributes and product variations are taken into account – which they surely must be!
So, in the course of our peregrinations around the big WordPress world, we’ve learned a thing or two along the way. Some of the more advanced clustering, load balancing and generally cloudy lessons, we’ll save for our internal power nerd sessions and conferences, but there are lessons which those running and looking to tune up more standard WordPress installations can learn.
And this is how we feed what we know back into the Open Source community and help those at the other end of the learning curve sink their teeth into becoming WP tuners!
Today, I’m going to describe how to take a janky old WordPress website, and wring some performance out of its MySQL database, by changing the storage engine from MyISAM to InnoDB.
This is not going to be a discussion over which is better. I’m just going to cut through all that and say InnoDB delivers significant performance gains at scale. Usually. And end that.
How to Convert WP MySQL Tables Storage Engine from MyISAM to InnoDB
Note, for the purposes of this tutorial, and often in the wild, we’re going table by table, in order to make sure our operation completes successfully. There is a scripty way to do this, but that’s not a great way to learn, and, on massive tables (which is when this becomes important) its nice to do it one by one, otherwise, you have to build error logging and other gubbins in there.
Back up before.
Back up before.
Remember to back up your tables and database as often as is practicable, including and especially before doing something like this, which might corrupt your database, borking everything in a table, and generally breaking stuff.
Step 1 – Find out what version of MySQL you have
This is important. If you are running MySQL version 5.5 or greater, then you won’t have to worry at all about step two, which is concerned with Full Text Indexes.
Before MySQL 5.5, InnoDB did not support full text indexes, so any you have on your tables won’t work if you convert them. This doesn’t mean don’t convert, but it does mean, first drop those full text indexes.
Luckily, we’re here to help you find those full text indexes!
Step 2 – Find & Drop Full Text Indexes
Go into your MySQL, or run the following in PHPMyAdmin, for each table which uses MyISAM – I’m using wp_posts as an example:
SHOW INDEX FROM wp_posts;
This will show you the indexes. As long as none of them are in full text, you are good to convert this table.
If there is a full text index, then drop it by issuing the following command:
DROP INDEX index_name ON wp_posts
Insert the name of your full text index, and drop it. Then you will be good to convert this table.
Step 3 – Convert the Table’s Storage Engine
ALTER TABLE wp_posts ENGINE=InnoDB;
This will then complete, and you’ll have successfully completed the alteration for this table. Now, to do the other old tables too!
Rinse & Repeat
Repeat this as often as you find MyISAM tables. I tend to start with the biggest ones, and work down, but that’s not really necessary. Its just that often bigger tables are more of a treat to fix when something goes wrong, so I take them first.
Once you’ve got them all, you will be able to tune your My.cnf file and get this setup for InnoDB tables, in order to get things blazing.
Having tables in both InnoDB and MyISAM, which often happens with WP sites which have been around a while, means you have to support both in your my.cnf files.
In my opinion, it is optimal, and scales better, to have all your tables in one storage engine, and for that storage engine to be InnoDB. Then get as much power to your database server(s) and ensure this is configured correctly for your queries.
Additional, if you feel brave enough then you can follow the below to upgrade all your databases :
Setting the Default Storage Engine
Set your default storage engine to InnoDB by adding default_storage_engine=InnoDB to the [mysqld] section of the system config file located at: /etc/my.cnf . Restarting the MySQL service is necessary for the server to detect changes to the file.
~ $ cat /etc/my.cnf [mysqld] log-error=/var/lib/mysql/mysql.err innodb_file_per_table=1 default-storage-engine=innodb innodb_buffer_pool_size=128M
Unfortunately, MySQL does not inherently have an option to convert tables, leaving each table to be changed individually. We have put together an easy to follow maintenance plan for this process. The script, which you can run on the necessary server via shell access (SSH) will convert all tables between storage engines.
Step 1: Prep
Plan to start at a time of day where downtime would have minimal consequences. This process itself does not require any downtime, however, downtime may be necessary to recover from unforeseen circumstances.
Step 2: Backup All Databases To A File
The command below creates a single file backup of all databases named all-databases-backup.sqld and can be deleted once the conversion has succeeded and there are no apparent problems.
mysqldump --all-databases > all-databases-backup.sql
Step 3: Record Existing Table Engines To A File
Run the following script to record the existing table engines to a file named table-engine-backup.sql. You can then “import” or “run” this file later to convert back to their original engines if necessary.
mysql -Bse 'SELECT CONCAT("ALTER TABLE ",table_schema,".",table_name," ENGINE=",Engine,";") FROM information_schema.tables WHERE table_schema NOT IN("mysql","information_schema","performance_schema");' | tee table-engine-backup.sql
If you need to revert the table engines back for any reason, run:
mysql < table-engine-backup.sql
Step 4a: Convert MyISAM Tables To InnoDB
The below command will proceed even if a table fails and lets you know which tables failed to convert. The output is saved to the file named convert-to-innodb.log for later review.
mysql -Bse 'SELECT CONCAT("ALTER TABLE ",table_schema,".",table_name," ENGINE=InnoDB;") FROM information_schema.tables WHERE table_schema NOT IN ("mysql","information_schema","performance_schema") AND Engine = "MyISAM";' | while read -r i; do echo $i; mysql -e "$i"; done | tee convert-to-innodb.log
Step 4b: Convert All InnoDB Tables To MyISAM
This command will proceed even if a table fails and lets you know which tables failed to convert. The output is also saved to the file named convert-to-myisam.log for later review.
mysql -Bse 'SELECT CONCAT("ALTER TABLE ",table_schema,".",table_name," ENGINE=MyISAM;") FROM information_schema.tables WHERE table_schema NOT IN ("mysql","information_schema","performance_schema") AND Engine = "InnoDB";' | while read -r i; do echo $i; mysql -e "$i"; done | tee convert-to-myisam.log
The following commands illustrate how converting a single table is accomplished.
Backup A Single Table To A File
mysqldump database_name table_name > backup-table_name.sql
Convert A Single Table To InnoDB
mysql -Bse ‘ALTER TABLE database_name.table_name ENGINE=InnoDB;’
Convert A Single Table To MyISAM:
mysql -Bse ‘ALTER TABLE database_name.table_name ENGINE=MyISAM;’
That’s about it.
Please be aware that following those guidelines will be your responsibility and we are not liable for any issues resulting.