6

I am getting slow page loads (upwards of 7 - 10 seconds) when a visitor adds a product to their cart. The rest of my site loads quickly having already implemented: APC, MEMCACHED and just recently VARNISH with the TURPENTINE module.

The magento 1.8.0 installation is running on a dedicated server with dual Xeon processors, 12GB RAM, SAS RAID 5 configuration.

CentOS 6.5 64bit (all applicable updates) PHP: 5.3.3 MySQL: 5.1.71

I've run the MySQL Tuner script (https://github.com/major/MySQLTuner-perl) + MySQL Tuning Primer Script (https://launchpad.net/mysql-tuning-primer/+download) and implemented all the recommendations.

Troubleshooting:

Enabled the Profiler in the Admin panel and have discovered a few bottle necks that seem to be slowing down the add to cart:

CODE PROFILER   TIME    CNT   EMALLOC   REALMEM
mage             7.9598   1      0         0 
.
.
mage::dispatch::controller::action::checkout_cart_index 7.9071 1 0 0     
.
DISPATCH EVENT:sales_quote_address_collect_totals_before   4.7420 2 2,418,224 3,145,728 
.
OBSERVER: auto_add_on_coupon_insertion 4.7411 2 2,388,872 3,145,728 

Slow Query Log:

# Query_time: 4.962430  Lock_time: 0.000184 Rows_sent: 1  Rows_examined: 52391
SET timestamp=1390674352;
SELECT COUNT(DISTINCT e.entity_id) FROM `catalog_product_flat_2` AS `e`
INNER JOIN `catalog_category_product_index` AS `cat_index` ON      cat_index.product_id=e.entity_id AND cat_index.store_id='2' AND cat_index.visibility IN(2, 4) AND cat_index.category_id = '2'
 INNER JOIN `catalog_product_index_price` AS `price_index` ON price_index.entity_id = e.entity_id AND price_index.website_id = '1' AND price_index.customer_group_id = 0 WHERE (((((e.news_from_date <= '2014-01-25 23:59:59') OR (e.news_from_date IS null))))) AND (((((e.news_to_date >= '2014-01-25 00:00:00') OR (e.news_to_date IS null))))) AND ((`e`.`news_from_date` IS not null) OR (`e`.`news_to_date` IS not null));

Given the above results of the Profiler and Slow Query Log, what would be the next recommended course of action? How can I get the add to cart load times down? I'm sure a lot of people can benefit from this.

Note: This is with zero load, only myself performing actions in the front end.

UPDATE (AM)

After more tweaking of the my.cnf the site was still sluggish on Add to Cart. This morning when trying to login to the admin panel, it was taking over 5 minutes to login at which point I restarted mysqld. Still the same. Add to Cart was the same as well (5 minutes +).

I cleared out the

/var/cache/ 
/var/locks/ 
/var/session/

Now the site is lightening fast here are the new profiler results:

mage 0.7792 1 0 0 

UPDATE 2 (PM)

Site has slowed back down over the course of the day:

mage 8.4715 1 0 0 

Clearing the /var/cache returned it back to .7 seconds on add to cart..

Why would this make such a huge difference in performance? Is it recommended to periodically clear out these directories and restart the services?

Cron is running regularly. Thanks!

  • Caching won't help you in this case. It's probably an issue with MySQL. Did you check your slow query log? Turn it on to see what's happing: dev.mysql.com/doc/refman/5.5/en/slow-query-log.html – Sander Mangel Jan 25 '14 at 19:05
  • @SanderMangel Yes it is enabled, I will edit my post to include the last query that coincides with the profiler. Thanks SR – SR_Magento Jan 25 '14 at 19:10
  • How many products/SKU's do you have in that installation? – Sander Mangel Jan 25 '14 at 19:14
  • That might be the problem. I'm not saying Magento CE can't handle a large amount of SKUs but it does slow it down considerably. You could test that by setting up a test environment and only adding 5k SKUs for example. – Sander Mangel Jan 25 '14 at 19:20
  • Hypothetical: If the server runs faster with 5k SKU's, then what is the solution for tuning the 50k SKU environment? More processing power? More RAM? Is there a way to by pass the query of all products to simply add 1 item to the cart? All usage data when monitoring the server shows little impact under add to cart condition. – SR_Magento Jan 26 '14 at 0:34
2

OBSERVER: auto_add_on_coupon_insertion 4.7411 2 2,388,872 3,145,728

seems to be a big contributor. Default Magento 1.8 does not have such an event observer. As a first step I would suggest going through any installed custom extension to see if disabling it solves the slowness.

  • This is a good point, however this morning I have updated the scenario as after clearing out the /var/cache/, /var/session/ and /var/locks/ the site is now loading bellow 1 second on add to cart. Just the same I have contacted the developer. Thank you SR. – SR_Magento Jan 27 '14 at 13:23
2

After much troubleshooting, the problem appears to be the inefficiencies of the Magento caching system.

The implementation of TURPENTINE (to complement VARNISH) ended up accelerating and making the problem more pronounced as the Site Crawler was Enabled to warm the cache.

Came across this article describing the issue I was having and provided a solution. Implementing REDIS has cured the issue and has improved performance on "Add to cart" as well as load times on cached pages.

0

Maybe you should try to truncate your quote tables...

SET FOREIGN_KEY_CHECKS = 0;
TRUNCATE TABLE sales_flat_quote;
TRUNCATE TABLE sales_flat_quote_address;
TRUNCATE TABLE sales_flat_quote_address_item;
TRUNCATE TABLE sales_flat_quote_item;
TRUNCATE TABLE sales_flat_quote_item_option;
TRUNCATE TABLE sales_flat_quote_payment;
TRUNCATE TABLE sales_flat_quote_shipping_rate;
SET FOREIGN_KEY_CHECKS = 1;
  • having a lot of legacy data in the quote tables might cause the issue but as OP posted the issue seems to be a query of 4 seconds on the catalog tables – Sander Mangel Jan 26 '14 at 10:23

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.