Posted by admin on January 30, 2009 at 23:38
New version of Query Cache is available. If You have lots of categories in Your store, this release is a must. Download new version here. Version 1.6 adds following features:
- Option to prefetch products_to_categories table for faster execution of zen_get_categories_products_list() function. You can turn ON/OFF this functionality by changing QC_ZEN_GET_CATEGORIES_PRODUCTS_LIST_PREFETCH switch in includes/extra_configures/query_cache.php. If Your store executes thousands of queries it’s pretty good chance that this option will solve it.
- Query Cache can now work in Admin area too. Just enable QC_ENABLE_IN_ADMIN in admin/includes//extra_configures/query_cache.php.
By default new switches are set to false. If You have many (at least few hundreds) products linked to many categories prefetching products_to_categories table will probably improve performance. On one store this switch managed to reduce query count from 15 000 queries ( yes, 15 thousands queries!) to about 100 queries (99,3% less queries!).
If You’ll find any bugs, please report back.
Continue Reading
Posted by admin on January 12, 2009 at 15:24
Version 1.5 of Query Cache for Zen Cart has been released. New version reduces query count by 80% (previous version reduced query count by ‘only’ 50%). Here’s list of changes:
- 970 queries down to 198 queries ( v1.0 executed about 450 queries )
- some performance improvements to code
- includes/functions/functions_categories.php has been rewritten to use cache and prefetch data. It reduces query count by about 100 queries (it depends on number of categories in Your store)
- basic queries for product from products table (for example “select products_name, manufacturers_id from products where products_id = ’7′“) can now be rewritten to “select * from products where products_id = ’7′“. It saves about 100 queries on default Zen Cart demo store.
Download Query Cache v1.5. See updated blog entry on Query Cache for updated charts, demo stores and screencast.
Stay in touch
Just type Your address here to be notified of new versions of Query Cache (You’ll receive only updates on Query Cache). Quick info: We hate spam, Your email will not be given to anyone.
Continue Reading
Posted by admin on January 6, 2009 at 14:36
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
SELECT SQL_NO_CACHE count(*) AS total
FROM products p, products_to_categories p2c
WHERE p.products_id = p2c.products_id
AND p.products_status = ’1′
AND p2c.categories_id = ’22′
SELECT SQL_NO_CACHE products_type
FROM products
WHERE products_id = ’12′
SELECT SQL_NO_CACHE DISTINCT p.products_id, p.products_image, pd.products_name, p.master_categories_id
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:
UPDATE products
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:
SELECT products_type
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:
ALTER TABLE example ENGINE = InnoDB
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).
Continue Reading
