6
InnoDB vs MyISAM performance in Zen Cart. Which is better?
1 Comment | Posted by admin in Performance
Zen 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).
1 Comment for InnoDB vs MyISAM performance in Zen Cart. Which is better?
Paul | January 17, 2010 at 23:41
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.