This post is a follow-up to Process to Optimize Magento under AWS, to explain more details of enhancing the diamond search in JewelsBoutique.com, via summary table.
Background
As an online shop to offer Internet shoppers distinctive diamonds, JewelsBoutique.com usually keeps over 40,000 diamonds in our Magento catalog all under the same category, consequently the diamond search page used to load extremely slow – often taking more than 15 seconds after making one simple filter adjustment. What’s more, every time when Magento was refreshing index for the layered filter at back-end, the page was nearly unresponsive, this period usually lasted over 3 hours a day. Since JewelsBoutique.com wants to bring a good experience for Internet shoppers, such bad performance was not acceptable.
Finding solution
We knew the bottleneck was at database side because of the large product set. At the beginning, we tried to use regular way to enhance the database performance: starting from moving to a stronger database server, then monitor database status, adjust server parameters, and adjust table index, make partial cache, but that didn’t help too much. After looking into Magento schema, we understood that with its EAV pattern, it’s impossible to make significant improvement to speed up the query behind diamond search, and because diamond has more than 10 attributes needed to be indexed for layered filter, so Magento’s flat category/catalog couldn’t help either because that doesn’t solve the re-indexing problem for these attributes. After more research of Magento code/schema and analyzing the diamond processing flow, we came up with the idea to create one ‘diamond summary table’ (similar to Magento’s flat catalog table but much more lightweight) for diamond search.
Steps
1. Create the summary table schema. The table only contains the needed diamond attributes which we need for diamond search and listing. The sample SQL to create this table is like below:
|
CREATE TABLE `summary_diamond` ( `id` int(10) unsigned NOT NULL default ’0′, `diamond_shape_id` int(11) NOT NULL default ’0′, — more attributes come here ) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
You can see the table structure is very simple and flat, it should perform very quickly for regular query on 40,000 records.
2. Update the summary table. Everyday after diamond importing task is completed, the following code truncates the summary table, then extracts all diamonds from Magento’s EAV tables and insert into it:
|
$write->query(‘TRUNCATE TABLE `summary_diamond`’); $sql = <<<END INSERT INTO `summary_diamond` SELECT `e`.entity_id AS `id`, `_table_diamond_shape`.`value` AS `diamond_shape_id`, …… FROM `catalog_product_entity` AS `e` INNER JOIN `catalog_product_entity_int` AS `_table_diamond_shape` ON (_table_diamond_shape.entity_id = e.entity_id) AND (_table_diamond_shape.attribute_id=’494′) AND (_table_diamond_shape.store_id=0) ……. END; $write->query($sql); |
Now you may use the summary table as a persistent-view for all diamonds, that’s it!
3. Turn off the index/layered-search flag on all diamond attributes(we don’t need Magento for this any longer), and change diamond search page to use this new table. After applying this approach, the page speed issue is solved completely (now diamond search usually takes less than 2 seconds to load the page), and normally the table rebuilding only took <15 sec.
One step further
After applying the three steps above, the diamond search worked very well, so we started marketing the site and drawing more traffic. Soon a problem was found: when the backend code was updating summary table, if there were many visits using diamond search, there would be a lot of concurrent read to diamond summary table, which caused the table lock and page weren’t responsive, the contention could cause the table update to last over 30 minutes, during this period diamond search couldn’t be used at all. So we adjusted the table update flow, to use a temp table to rebuild the summary table “offline”, after it’s done, simply drop the existing table and rename temp table to be formal one, this only took 1-2 sec to finish. The code is like below:
|
$write->query(‘CREATE TABLE IF NOT EXISTS `temp_summary_diamond` LIKE `summary_diamond`’); $sql = <<<END INSERT INTO `temp_summary_diamond` SELECT `e`.entity_id AS `id`, `_table_diamond_shape`.`value` AS `diamond_shape_id`, …… FROM `catalog_product_entity` AS `e` INNER JOIN `catalog_product_entity_int` AS `_table_diamond_shape` ON (_table_diamond_shape.entity_id = e.entity_id) AND (_table_diamond_shape.attribute_id=’494′) AND (_table_diamond_shape.store_id=0) ……. END; $write->query($sql); $write->query(‘DROP TABLE `summary_diamond`’); $write->query(‘ALTER TABLE `temp_summary_diamond` RENAME TO `summary_diamond`’); |
Now during importing diamonds at back-end, the diamond search works quickly without any impact.
Final wrap up
With all these changes in place, the diamond search started to perform stably. After some team brainstorming, we decided to show a ‘more diamond is coming…’ tip on the top-left corner during the backend processing period, to let our users know what’s happening behind the scenes and to expect more/new diamonds in search results very soon.
Tags: diamond, performance
[...] Magento performance optimization: build our own “summary table” for diamond search | Jew… [...]