InnoDB vs MyISAM performance in Zen Cart. Which is better?
7Zen Cart uses MyISAM tables to store data, but MySQL offers other storage engines too. Is MyISAM the best choice? Is it the fastest one? These questions will be answered.. right now: No, it isn’t (at least not always). Read below to find out more.
Performance test
Note: In test We used MySQL 5.0.41. Results can vary depending on MySQL version You use.
Let’s find out which of the two is faster. To do this We’ll need to test their performance. We created Java application which executes typical SELECT queries against Zen Cart demo store database and measures query performance. Here are some of those queries:
SELECT queries
FROM products p, products_to_categories p2c
WHERE p.products_id = p2c.products_id
AND p.products_status = ’1′
AND p2c.categories_id = ’22′
FROM products
WHERE products_id = ’12′
FROM (products p
LEFT JOIN featured f ON p.products_id = f.products_id
LEFT JOIN products_description pd ON p.products_id = pd.products_id )
WHERE p.products_id = f.products_id
AND p.products_id = pd.products_id
AND p.products_status = 1 AND f.STATUS = 1
AND pd.language_id = ’1′
Note that We added SQL_NO_CACHE to prevent MySQL from caching query results.
Now, it’s important to simulate many customers wandering around Zen Cart store. Therefore We used 10 threads to send queries to database.
InnoDB managed to perform 1186 queries per second on average, where MyISAM executed only 958 queries per second on average. InnoDB was faster by almost 25%!
Locking strategy
Very important feature (from performance perspective) is that InnoDB uses per row locking, where MyISAM uses table locking. What does it mean? We’ll clarify it with example.
Let’s assume that We’re executing following update against products table:
SET products_ordered = products_ordered + 1
WHERE products_id = 12
and other thread (other http request from other customer) is executing at the same time following query:
FROM products
WHERE products_id = ’17′
Because MyISAM uses table locking when updates are made to tables, it will prevent execution of other SELECT queries until update statement finishes, even if those queries don’t look at updated rows.
InnoDB on the other hand will lock only row with products_id field set to 17, allowing concurrent execution of SELECT queries (unless those queries are asking for data from updated row).
It does not give much performance boost when Your store has low traffic, but when it’ll get more popular (hopefully
) and You’ll have ten thousands visits per day it’ll make a difference ( the more traffic You’ll get the bigger the difference should be).
How to convert MyISAM table to InnoDB table
To convert any table example to InnoDB execute following SQL code:
Unfortunately Zen Cart database contains about 80 tables, so changing each of them by hand would be inconvenient. Also, one may want to change back to MyISAM for some reason, and it would have to repeat whole process again. To address that problem We created simple Zen Cart contribution that automates conversion from MyISAM to InnoDB. You can download it here:
Download Change Storage Engine
Installation is very easy, just copy unzipped folder to Your store directory. This contribution does not overwrite any Zen Cart files, so You don’t have to worry about that. To change all tables in Your database from MyISAM/InnoDB to InnoDB/MyISAM go to Tools->Change Storage Engine and click ‘Change’. That’s it!
Summary
InnoDB is faster then MyISAM engine, it also scales better. But before You change all Your tables to InnoDB make sure to (as always) make backup of Your database. Also, after changing tables to InnoDB check if Your store runs faster as MySQL performance differs from version to version.
Updates
Version 1.1 of Change Storage Engine is available (use link above, it always points to newest version). It’ll omit MyISAM tables with FULLTEXT indexes (InnoDB does not support FULLTEXT indexes).
I’m glad someone has taken the time to appreciate InnoDB over MyISAM. Coming from an Oracle DB background I find it appalling that a commercial system like Zen Cart would use a non-relational DB engine by default and have the application do the relationship management – that’s what a DB is for! The Zen Cart team need to learn proper database design.
“InnoDB is faster then MyISAM engine, it also scales better.”
If this was true, why would MyISAM be the default storage engine? I suggest you look a little deeper into them so you know the differences and know the proper uses for them.
The most important aspect of innoDB wasn’t even mentioned in this article.
I assume You have some knowledge about DB, MySQL and query optimization. Therefore You also know that everything depends on particular use case, type of executed queries, context etc. Note that I explicitly titled the article “InnoDB vs MyISAM performance in Zen Cart. Which is better?”. I didn’t state that it was completely serious test on InnoDB vs MyISAM in general. In Zen Cart however my tests show that InnoDB outperforms MyISAM.
As for most important aspect of InnoDB – You probably have in mind foreign keys. I didn’t mention them here because Zen Cart does not support them. I would say that for Zen Cart community one of biggest advantage of InnoDB is auto repair feature. Average Zen Cart user knows little about SQL and does not know how to repair crashed table.
There’s existing patchwork that provides referential integrity on top of InnoDB for Zen Cart. See this thread:
http://www.zen-cart.com/forum/showthread.php?t=116479
@mark: actually, myisam is not the default engine for mysql 5.5 anymore. InnoDB is. MySQL reports that InnoDB permormance was improved up to 3.5 times for linux and up to 15 times for windows.
Even in the old days, InnoDB was a rival of myisam. Today, it’s more secure, faster and advanced engine.
Um, your update basically nullified your advice. Why not just admit this is a bad idea? The truth of the matter is different storage engines have different features.
Your test is only looking at a join, which maybe true that it’s faster in certain instances, but you’re leaving out some very important trade-offs.
By converting tables to innodb, you may want to inform store owners that they’ll lose the ability for full text searches, which is critical for the advanced search later on.
And just to clarify, the most important aspect of the innodb storage engine is for transaction support.
Also, I cringe that you would recommend people run an ALTER TABLE command on production database like that. You realize how long that may take depending on the size of the database and the tables they may have in there?
Store owners, I would definitely think twice before you do this.
Sorry for late reply.
I don’t see how my update nullifies my advice. Can You elaborate on trade-offs? What exactly average zen cart store owner has to keep in mind when moving from MyISAM to InnoDB?
Sure that InnoDB tables do not support full text search, but Zen Cart does not use it anyway. Also keep in mind that the script ignores any tables with existing full text index.
And yes, changing storage engine may take considerable amount of time but only in case of very large tables. Any store with as large database most probably already uses InnoDB or is maintained by someone with mysql experience (who knows what to expect when changing storage engine).
I also don’t know where You have read that I recommend doing this on production server? And You state that changing storage engine to InnoDB is a bad idea, but You do not write WHY it is a bad idea.
“Store owners, I would definitely think twice before you do this.”
I agree with that. Actually, You (store owners) should think twice before making ANY changes to Your store. And You always should do backups etc.