Increased performance for high-capacity stores: how we achieved it
CS-Cart version 2.0.7 (released just recently) includes significant performance optimization which was done specifically for highly-loaded and large (more than 100,000 items) online stores. We have decided to provide some general insight into the approach we’ve used. Ilya Shalnev, Chief Architect of CS-Cart, shares the results of this work:
* * *
The testing has taken place on Core 2 Duo e5400 server with 4Gb RAM and 250Gb SATA HDD working under CentOS 5.3.
Our method was quite simple: as SQL queries make the most significant impact on server-side performance, we have performed the debug and picked all MySQL queries taking more than 0.01 second on our server.
The debug was done on a test store with 100,000 products stored in 500 categories (with a maximum of 4-level depth).
Other important aspects of product specifications include:
- Number of secondary categories: 1 – 30%, 2 – 20%, 3 – 10%;
- Product options used: color – 40%, size – 20%, color and size – 30%;
- Product descriptions: “description_one” – 50%, “description_two” – 10%, “description_description” – 10%;
- Product quantity discounts used: for 2 products – 10%, for 3 – 20%, for 5 – 40%;
- Tags used: “test” – 60%, “sample” – 70%;
- Thumbnail image used: 100%;
- Number of additional images: 1 – 10%, 2 – 10%;
- “Featured Products” blocks: 7 products – 50%, 5 products – 50%;
- Product features used: Manufacturer – 30%, Format – 10%;
Next, we’ve performed the optimization of these queries by changing the database structure and using some other methods.
The table below outlines the end results for MySQL queries and PHP code execution for various page types of a test store with 100 000 products:
| Page | Execution time before optimization, sec: | Execution time after optimization, sec: |
|---|---|---|
| Home page | 0,09 | 0,09 |
| Category page (2 000 products) | 5,68 | 0,37 |
| Advanced search page (clear, all products found) | 52,77 | 18,38 |
| Advanced search page (by description, 1 299 products found) | 301,18 | 10,04 |
| Advanced search page (price range from 2 000 to 7 000, 40 389 products found) | 22,89 | 8,76 |
| Advanced search page (price range from 2 000 to 5 000, 200 products found) | 0,57 | 0,27 |
| Advanced search page (no products found) | 267,78 | 1,46 |
* * *
As you can see, in most cases we have managed to make most resource-consuming queries execute about ten or more times faster than before! So, it is safe to assume that CS-Cart has become much faster on high loads, allowing your online stores to keep running smoothly even with a large product base and/or at the peak of popularity.
Another feature we have introduced is caching for queries which take more than 3 seconds to complete. Such queries are not executed for the second time and their results are seamlessly taken from cache. In case the content of corresponding tables changes, the cache is cleared in order to be filled with the new data.
Still, we are sure that many of you would be interested to know what else can be done to achieve even higher page loading speed, so stay tuned to receive further information on optimizing CS-Cart performance.