TAG | Performance
There’s suboptimal query in admin/orders.php page which retrieves information about last orders. Unfortunately query is constructed in such way that MySQL scans whole orders, orders_products and orders_total table. As always it’s not a problem until orders table gets big ( for example 50 000 entries ). If You happen to have at least 10 000 orders ( I wish You that 
), You probably get impatient each time You request admin/orders.php page.
Good news is that there is quick fix. Just add index on (orders_id, class) on orders_total table to speed up the query. Here’s MySQL statement which does that for You:
The query is still suboptimal, but at least its quite fast until orders table gets really big (500 000 orders? It’ll depend on Your server).
23
Performance issues with Store Credit Module in admin area
0 Comments | Posted by admin in Performance
Store Credit is great module, unfortunately it’s preety inefecient in some places. For example, on every page request of admin/store_credit.php whole customers table is read, and for each customer additional SELECT and UPDATE query is executed to calculate and update pending points, even if the customer does not have any pending points!
It’s not a big deal when You have up to 1000 customers, but when there are more then 100 000 customers page loads in 30 seconds (store_credit.php executes over 2 x customer count, or in this example over 200 000 queries on each request).
To address that, change following code in admin/store_credit.php:
global $db;
$customers = $db->Execute("SELECT customers_id FROM " . TABLE_CUSTOMERS . " ORDER BY customers_id ASC");
while (!$customers->EOF) {
$customers_id = $customers->fields[‘customers_id’];
$pending_rewards = $this->get_pending_rewards($customers_id);
$db->Execute("UPDATE " . TABLE_STORE_CREDIT . "
SET pending = " . $pending_rewards . "
WHERE customers_id = " . $customers_id . "
LIMIT 1");
$customers->MoveNext();
}
}
to:
global $db;
$customers = $db->Execute("SELECT DISTINCT customers_id FROM " . TABLE_SC_REWARD_POINT_LOGS . " ORDER BY customers_id ASC");
while (!$customers->EOF) {
$customers_id = $customers->fields[‘customers_id’];
$pending_rewards = $this->get_pending_rewards($customers_id);
if($pending_rewards == 0.0) { $customers->MoveNext(); continue; }
$db->Execute("UPDATE " . TABLE_STORE_CREDIT . "
SET pending = " . $pending_rewards . "
WHERE customers_id = " . $customers_id . "
LIMIT 1");
$customers->MoveNext();
}
}
Thanks to that, only customers who actually have some pending points will be processed.
Version v1.5.1 of Query Log is available. You can download it from: www.data-diggers.com/contribs/query-log/downloads/querylog-current.zip .
What’s new in this version:
- You can now close ‘calculator’ layer (it’s the tool that highlights queries that match given regular expression)
- You can now disable query logging completely. In previous versions Query Log kept all queries in memory, no matter if You wanted to log them (display them) or not.
I noticed that following query takes long time to execute:
FROM orders o, orders_products op, products p
WHERE o.customers_id = ‘2345′
AND o.orders_id = op.orders_id
AND op.products_id = p.products_id
AND p.products_status = ‘1′
GROUP BY products_id
ORDER BY o.date_purchased DESC
LIMIT 6
I noticed that there’s no index on orders table on customers_id field. Without it MySQL has to scan whole table to find orders by customers_id (in this case ‘2345′). If orders table is small there’s no problem, but when it has 10 000 or more entries it can noticably slow down Zen Cart. So, let’s add index on that table
I included date_purchased in index, because some pages in admin area will use it for searching all orders of given customer.
In my case it decreased query time from 0.3 sec to 0.08 sec. It’s still too much, but it’s better then nothing.
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.
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).
Query Log for Zen Cart is open source, simple tool for monitoring performance of SQL queries. Depending on configuration it will display or save all executed queries sent to database together with information how much time each query took and which page and session executed it. With Query Log and some SQL knowledge You can improve performance of Your Zen Cart store.
At this moment there are two components:
- Basic Logger – it’ll display to trusted users all logged queries in Zen Cart shop footer.
- Database Logger – it’ll save all logged queries to database.
Demo Stores
Because Database Logger does not display anything to users no demo store is available for it.
Here’s demo store for Basic Logger: Basic Logger Demo Store
Basic Logger
Basic Logger for each page request logs all queries sent to database and displays them to trusted users in page footer together with time each query took. It will mark with red color queries that take much more time then average. See screencast to see it in action.
Note: Basic Logger is based on contribution made by Chemo for osCommerce, We just added few features to it.
Database Logger
Database Logger does everything that Basic Logger do. In addition it also saves all queries from all page requests of all visitors in database table. You can then execute various queries to check which queries should be optimized, which pages generates most queries, what is average time spent in database etc.
Database Logger is as fast as possible, it uses MyISAM tables without primary key and indexes to store queries (this means that MySQL server does not spend time updating lookup tables). Also, if MySQL ver. 5.0 or later is available You can use Archive storage engine to compress logs (installation script provides two versions of sql patches, one for MySQL ver. 5.0 or later, and one for earlier versions).
Warning: before turning on Database Logger make sure that You know what You’re doing. Storing all queries in database can take huge amounts of disk space.
You can do various things with query_log table, below are few simple use cases.
To list slowest queries execute:
FROM query_log
ORDER BY time DESC
LIMIT 100
You can check what is average time spent in database for each type of page (product_info, index, advanced_search_results etc.):
FROM query_log
GROUP BY page
ORDER BY average_time DESC
Listing queries executed on ‘product_info’ page that takes more then 0.1 sec to complete:
FROM query_log
WHERE page = ‘product_info’
AND time >= 0.1
Screencast
Here’s screencast showing how to install and use Query Log:
Download
Here You can download Query Log compatible with all 1.3.x Zen Cart versions.
Installation
Requirements:
- You can use Query Log with any version of Zen Cart 1.3.x.
- PHP >= 4.x is required.
- Query Cache is required. If You don’t want to use Query Cache You have to change includes/classes/db/mysql/query_factory.php to use Query Log
Query Log is quite easy to install. First You need to download it. After that extract it with Winzip or something similar and follow instructions below:
- BACKUP BACKUP BACKUP!
- Upload ‘includes’ directory (from directory corresponding to Your Zen Cart version) on Your server (via ftp, sftp or any other protocol).
- If You use MySQL >=5.0 copy and paste contents of sql_patch_for_mysql_5.x.sql into Store Admin Panel -> Tools -> Install SQL Patches. Execute the script.
- If You use MySQL <5.0 copy and paste contents of sql_patch.sql into Store Admin Panel -> Tools -> Install SQL Patches. Execute the script.
- Done.
If You use custom template add following line to proper tpl_main_page.php somewhere near end of the file:
After installation You’ll need to configure Query Log.
Configuration
Query Log adds three options to Admin Panel -> Configuration -> Logging:
- Enable Database Query Log
- Display queries to trusted users
- Display queries to users with following email addresses (list of trusted users
Enable Database Query Log
When enabled all logged queries will be saved in database in query_log table.
Display queries to trusted users
Enables Basic Logger. It will display queries only to logged in users with email addresses listed in Display queries to users with following email addresses
Display queries to users with following email addresses
Comma separated list of emails.
Stay in touch
If You wish to be notified about new versions of Query Log, use cases, new screen casts or anything else related to Query Log type Your email address below hit ‘Subscribe’. You will only receive news regarding this contribution.
Update: Query Cache V1.6 has been released – read here
Query Cache is free, in memory cache designed to work with Zen Cart. It can reduce number of queries sent to database by over 80% (see charts below). Thus, Query Cache might greatly reduce query execution time and response time of most Zen Cart stores.
You might want to jump quickly to see Demo Stores. Compare number of queries on each store.
Performance Analysis
We’ve ran some tests on demo installment of Zen Cart 1.3.8a which contains just a few products, few categories, one customer and no orders. Still, Zen Cart generated over nine hundred queries (>900!) just to load first page of the demo store (You can check how many queries generates Your store by turning on ‘Display Page Parse Time’, read more). After that, Query Cache has been installed and it managed to reduce number of queries by half (from 900 to 199 queries approx.).
Other pages where also checked, including: category view page, search results page and ’static’ (more or less) shipping information page. Results are presented on figures below:
 |
 |
 |
 |
Advantages of Query Cache
- reduces number of queries sent to database by 80%
- it’s very easy to install (jump to Installation instructions)
- reduces page generation time, reduces load on database server.
- works with all 1.3.x versions of Zen Cart
Disadvantages of Query Cache
As far as We can tell there is only one disadvantage: at this moment Query Cache can’t detect if database has been changed while php script is running. This means that if, for example, information about product is pulled from database, then product is updated, and retrieved again from database (all happening in one request) updated information might not be retrieved (in next request information will be updated).
Download
Here is package including Query Cache for ALL versions of Zen Cart 1.3.x:
Download Query Cache – latest version
Installation Instructions
First, download contribution here. Extract it with WinZip or something similar. Go to extracted directory. There are few directories named like ‘Zen-Cart 1.3.x’ where ‘x’ is minor version number of Zen Cart. To install this module:
- BACKUP BACKUP BACKUP!
- Upload ‘includes’ directory (from directory corresponding to Your Zen Cart version) on Your server (via ftp, sftp or any other protocol).
- Done.
Note: Query Cache v1.5 has feature that might slow down Your store if Your web server is very slow. In such case You can try to set QC_USE_PRODUCT_QUERY_DETECTION in includes/extra_configures/query_cache.php to FALSE.
This contribution is preconfigured to use Query Log _IF_ it’s available. If Query Log is not installed, Query Cache will still work without any problems (or at least it should;)
How to verify that Query Cache Works
Before installing Query Cache check how many queries Your store executes:
- Enable ‘Display The Page Parse Time’ option in Store Admin in Configuration -> Logging.
- Go to Your store, scroll down and see how many queries where executed.
Now, install Query Cache and visit the same page. Check query count, it should be much lower.
Screencast
Here is screencast showing how to install this contribution.
Demo Stores
Here are two Demo Stores:
Demo Store with Query Cache installed
Demo Store without Query Cache installed
Take a look at footer of each store. As You can see Query Cache greatly reduced number of executed queries.
Is it free? (Yes)
Yes, it is. You can use it on unlimited number of sites without any costs. You can redistribute it, change the source code (but leave us as original authors) etc. You can’t sell it or make any profit of it.
Stay in touch
We plan to release new version of Query Cache soon, with some new features that will further reduce number of executed queries and improve performance of Your store. 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.
