Speeding Up ZenCart with MySQL Memory Tables

Published October 28th, 2008

Problem Statement:

ZenCart is a plug-and-play e-commerce solutions for companies toying with e-commerce concepts. But when your e-commerce site is driving 70% of your revenue and you have a large product catalog of tens of thousands of products, you need to “tune” Zen Cart to scale. It has many performance issues:  poor PHP code,  extremely slow MySQL queries, etc.  We have dealt with both of these issues over the years and have a great deal of experience in tuning Zen Cart.

When you have a large product catalog, 15K or more products and thousands of categories, Zen Cart can be very slow. Our problem: how to speed up Zen Cart catalog display so that site feels more responsive under heavy load conditions. The site in question has over 15K products, hundreds of categories and a typical heavy load condition is over 100K+ consumer sessions

Here we will provide a simple MySQL based solution that will speed up Zen Cart significantly and it only requires two-lines of PHP code change and as usual a bit of MySQL hacking.

The Solution: Speeding Up Catalog Display

After investigating Zen Cart MySQL query using nothing more than a simple SHOW FULL PROCESSLIST command for a short while, we realized that Zen Cart makes heavy use of its zen_products and zen_categories tables.

Since these tables are only changed when a product or a category is changed by the administrator, it made sense to make these frequently used tables faster as follows:

1. Create a duplicate table called zen_products_mem from zen_products using engine type HEAP
2. Create a duplicate table called zen_categories_mem from zen_categories using engine type HEAP
3. Load products from zen_products to zen_products_ram using REPLACE INTO zen_products_ram SELECT * FROM zen_products
4. Ditto for zen_categories_ram
5. Edit the DOC_ROOT/includes/database_tables.php to point TABLE_PRODUCTS constant to zen_products_ram and TABLE_CATEGORIES to zen_categories_ram

Congrats! you are now using memory tables to serve product and category data in Zen Cart. This should significantly speed up your page load time.

Making the Solution Sticky

If this solution works for you and you want to make this permanent, you should read our article: Automatically Loading Memory Tables on MySQL startup or reboot.

About Our Platform (System Requirements)

The above solution was applied on the following platform:

• Linux Operating System - CentOS 4.5
• Apache Server - 2.4.x
• MySQL Server - 5.x
• PHP - 4.4.x

Although we will not provide the exact make or model of our hardware/network equipment, you can get a good idea of our platform from the following sanitized description of our hosting platform:

• Hardware load balanced network with direct path response
• Web network with n Web nodes - each node with 8GB RAM and dual core CPUs
• Centralized MySQL server with over 32GB of physical RAM
• True 100Mbps burstable tier-1 data center
• Complete remote management of each piece of hardware from our head quarters

Category: Performance | Tags: