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.
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.
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!