18

I'm running several Magento CE shops and speed them up with caching, however the cart and checkout still remain slow. Does anyone have experience or tips in speeding up these pages?

Perhaps through optimizing the database?

Some queries were performed when saving an order from checkout do show up in the slow query log on the server and the database does seem to be the bottleneck.

  • what does slow mean? 1s? 5s? ... also provide some details about the size of the shops (number of simple products, number of configurable, size of sales_flat_quote* tables and so on). – FlorinelChis Jan 22 '13 at 21:55
  • Slow is between 5 and 10 seconds depending on how busy the shop is. There are 50.000 simple products, no other types. The sales_flat_quote id have to look up tomorrow (no acces at the moment) – Sander Mangel Jan 22 '13 at 22:07
  • 1
    how many shopping cart price rules you have? they slow down the cart. Also we hope to address this issue in the Service Pack release, expected Q1/Q2 '13 – Piotr Kaminski Jan 22 '13 at 22:09
  • @macki no price rules at the moment, thanks for the mention tho. thats good to know – Sander Mangel Jan 22 '13 at 22:11
27
  1. From personal experience, disable the Mage_Rss module which forces a 'cache clean' 4 times in checkout process -- very expensive if you are using filesystem cache, probably still expensive if you are using database or memcached.

  2. CE Only Disable Mage_Downloadable for similar reasons as long as you aren't using Downloadable product, this will speed up checkout and cart actions when you have multiple items in the cart because there are observers on things like checkout_type_onepage_save_order_after which multiply response time by the # of items in cart.

  3. Wire up xhprof / xhgui and do some profiling.

  • Thanks XHProf and XHGui sounds like a nice weekend project to dive into – Sander Mangel Jan 22 '13 at 22:02
  • 1
    You should really try to desactivate mage_rss or commenting its observers. speeds up order save immediately – STANISLAS PIERRE-ALEXANDRE Jan 23 '13 at 7:01
  • 1
    I'd like to hear firsthand feedback if the Mage_Rss disable trick works for the OP – philwinkle Feb 8 '13 at 17:36
  • 2
    I would like to point out that EE requires Mage_Downloadable as a dependency: Module "Enterprise_PricePermissions" requires module "Mage_Downloadable – philwinkle Feb 10 '13 at 19:50
  • 1
    I never reported back and for that I'm sorry. Disabling Mage_Rss did speed it up considerably, disabling Mage_Downloadable didn't result in any notable performance improvements but I have to say I didn't do a 'proper' benchmark, just several runs in my browser. – Sander Mangel Aug 27 '13 at 18:08
4
  • Set your indexes to manual.
  • Disable cache tag storage

Both these changes will have a MASSIVE impact on performance as it prevented Magento flushing out caches and re-indexing every time an order goes through.

It comes at a cost though, that content may be stale as a result - stock levels etc.

  • thanks, the faulty stock levels are an issue on most shops tho. But ill keep it in mind for shops without stock management! – Sander Mangel Feb 22 '13 at 7:06
2

If you want to solve it the experimental way, there is an extension from the first magento hackathon in munich, german:

https://github.com/magento-hackathon/MongoDB-OrderTransactions

They queue the orders into a mongo db, the idea was, if the mysql-server is free of load to write them back. But I don't know how far this project is ready. Afaik works all the writing, but not the back writing.

  • Thanks, I was actually looking at noSQL solutions and this might be a nice starting point! – Sander Mangel Jan 24 '13 at 8:58
2

I don't know your Magento CE Version you are struggling with. But I had serious performance Issues with my CE 1.6.
Reason: Wrong and missing indexes. They where fixed in CE 1.6.2
You might check if it helps you.
I reduced the checkout time for 38 lines with 73 items total from 123 sec to 4 sec !!!!

Here it comes:

/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;

/* Foreign Keys must be dropped in the target to ensure that requires changes can be done*/

ALTER TABLE `core_url_rewrite` 
DROP FOREIGN KEY `FK_CORE_URL_REWRITE_CTGR_ID_CAT_CTGR_ENTT_ENTT_ID`  , 
DROP FOREIGN KEY `FK_CORE_URL_REWRITE_STORE_ID_CORE_STORE_STORE_ID`  ;


/* Alter table in target */
ALTER TABLE `catalog_category_entity_varchar` 
DROP KEY `MAGMI_CCEV_OPTIMIZATION_IDX` ;


/* Alter table in target */
ALTER TABLE `catalog_product_bundle_stock_index` 
DROP KEY `PRIMARY`, ADD PRIMARY KEY(`entity_id`,`website_id`,`stock_id`,`option_id`) ;


/* Alter table in target */
ALTER TABLE `catalog_product_entity_media_gallery` 
DROP KEY `MAGMI_CPEM_OPTIMIZATION_IDX` ;


/* Alter table in target */
ALTER TABLE `core_url_rewrite` 
CHANGE `id_path` `id_path` varchar(255)  COLLATE utf8_general_ci NULL COMMENT 'Id Path' after `store_id` , 
CHANGE `request_path` `request_path` varchar(255)  COLLATE utf8_general_ci NULL COMMENT 'Request Path' after `id_path` , 
CHANGE `target_path` `target_path` varchar(255)  COLLATE utf8_general_ci NULL COMMENT 'Target Path' after `request_path` , 
CHANGE `is_system` `is_system` smallint(5) unsigned   NULL DEFAULT 1 COMMENT 'Defines is Rewrite System' after `target_path` , 
CHANGE `options` `options` varchar(255)  COLLATE utf8_general_ci NULL COMMENT 'Options' after `is_system` , 
CHANGE `description` `description` varchar(255)  COLLATE utf8_general_ci NULL COMMENT 'Deascription' after `options` , 
CHANGE `category_id` `category_id` int(10) unsigned   NULL COMMENT 'Category Id' after `description` , 
CHANGE `product_id` `product_id` int(10) unsigned   NULL COMMENT 'Product Id' after `category_id` , 
ADD KEY `FK_CORE_URL_REWRITE_PRODUCT_ID_CATALOG_CATEGORY_ENTITY_ENTITY_ID`(`product_id`) , 
DROP KEY `FK_CORE_URL_REWRITE_PRODUCT_ID_CATALOG_PRODUCT_ENTITY_ENTITY_ID` , 
ADD CONSTRAINT `FK_CORE_URL_REWRITE_PRODUCT_ID_CATALOG_CATEGORY_ENTITY_ENTITY_ID` 
FOREIGN KEY (`product_id`) REFERENCES `catalog_product_entity` (`entity_id`) ON DELETE CASCADE ON UPDATE CASCADE , 
DROP FOREIGN KEY `FK_CORE_URL_REWRITE_PRODUCT_ID_CATALOG_PRODUCT_ENTITY_ENTITY_ID`  ;


/* Alter table in target */
ALTER TABLE `eav_attribute` 
DROP KEY `MAGMI_EA_CODE_OPTIMIZATION_IDX` ;


/* Alter table in target */
ALTER TABLE `eav_attribute_option_value` 
DROP KEY `MAGMI_EAOV_OPTIMIZATION_IDX` ;


/* The foreign keys that were dropped are now re-created*/

ALTER TABLE `core_url_rewrite` 
ADD CONSTRAINT `FK_CORE_URL_REWRITE_CTGR_ID_CAT_CTGR_ENTT_ENTT_ID` 
FOREIGN KEY (`category_id`) REFERENCES `catalog_category_entity` (`entity_id`) ON DELETE CASCADE ON UPDATE CASCADE , 
ADD CONSTRAINT `FK_CORE_URL_REWRITE_STORE_ID_CORE_STORE_STORE_ID` 
FOREIGN KEY (`store_id`) REFERENCES `core_store` (`store_id`) ON DELETE CASCADE ON UPDATE CASCADE ;

/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
1

The best way to speed up the big database operations is to have your database on its own server that is optimized for database use. There isn't much that can be improved in the checkout area code-wise (though certain types of products, like Configurable, can really bog down the quoting process) since very little can be safely cached.

  • Thanks, was afraid for that. It's already a dedicated installation with seperate DB server. Hopefully they'll optimise the checkout in Mage 2 :) – Sander Mangel Jan 22 '13 at 21:54
  • 1
    2.0 Includes a lot of rewrites so far, so we can hope. Honestly, the product models themselves are a lot of what makes checkout slow - it has to iterate over the type instances for each product while creating the quote/converting to order, and that can be an expensive process. – Andrew Quackenbos Jan 22 '13 at 21:56
1

Maybe look at splitting the reads and writes out in your DB. You'll need near immediate replication set up though which is what's always worried me with doing it although others may have more info on how best to configure it.

  • Hm not sure if that's a reliable solution to be honest, sounds like a pretty big change of incomplete data. The idea is really nice tho! – Sander Mangel Jan 24 '13 at 8:55
  • I agree to a certain extent and admit I've never actually done it myself although I do see various people writing blog posts etc suggesting they have seen a marked improvement in performance. If I can find some benchmarks etc I'll post them up. – Richard Cleverley Jan 24 '13 at 8:58
  • Thanks Richard, ill have a talk with my hosting company as well about this. They have a lot more experience with this kind of stuff I think. If I have one more information i'll post it – Sander Mangel Jan 24 '13 at 9:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.