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.
Continue Reading
