Magento Community – Add to Cart / Checkout Performance Issues

ceMySQL

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!

Best Answer

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.

Define performance

Do you mean the page load time for a single user, or the overall capacity/total concurrency? The two are very distinctly different - and not strictly related. It is entirely possible to have a fast store with limited capacity; or a slow store with lots of capacity.

So when addressing either type of performance:

Single user perceived page load time
Total capacity/concurrency

You have to tackle each independently with their own solutions - especially since each have their own bottlenecks.

Lets make the assumption you are with a competent host that has already configured the other aspects of your server optimally for your store.

Single user perceived page load time

Is MySQL the bottleneck
No. Not directly. Its all about latency, in the majority of cases when testing page load time - only the caches will be hit. So the key here is to minimise latency.

Tune MySQL cache sizes appropriately (there is no right answer, we tune settings entirely differently, monthly, per store)
Reduce network latency. For 64 byte frames; 51.2µs for 10Mbps, 5.12µs for 100Mbps and 4.096µs for 1Gbps. This gives a improvement of 20% just by transitioning from 100Mbps to 1Gbps. ^s1
Increase network capacity. You would be surprised at the many megabytes per second being exchanged between a Web and DB server, usually in excess of 10MB/s - so a minimum of 100Mb/s is required ^s1. Or, just move the DB server locally.
Using SOLR. External engines are sometimes better suited, SOLR certainly is faster for LARGER catalogues (and I'd stress, larger catalogues). Even un-tuned SOLR will produce layered navigation and search results faster than MySQL can.

But these changes will have such a fractional impact on page load time - where the bottleneck is really elsewhere.

Tune the application. Magento has some fairly big bugs with the way it builds collections and caches them; we've come across a number of big core code issues that can cripple performance. In a few cases, simply removing the product count display on the layered navigation results shaved 2 seconds of loading a big collection.
Review MySQL slow logs. Check slow queries and add indexes as necessary. The difference between running a complex query with multiple joins with and without appropriate indexes can be tens of seconds.

The application is the bottleneck. Not the software. So merely improving core-code or making your template less heavy will have a far more dramatic effect on performance than ANY MySQL configuration change.

What wouldn't we bother with

Changing the storage engine. MariaDB and Percona share the same InnoDB engine - Percona XtraDB. They can be treated as one and the same. In terms of single query execution time - performance will exactly mirror a vanilla MySQL build. This comes into play under load/concurrency.
Running a MySQL slave. This won't improve performance unless the slave is located physically closer (from a network perspective), or that the slave has better hardware than the master. This comes into play under load/concurrency.
Running an external DB server. This is by far the worst advice we see repeatedly handed out by many hosts/agencies. Until you have hit a ceiling on hardware/resources or you've got multiple web servers (read: high-availability), MySQL on the local machine for a Magento store is A Good Idea. It cuts out all the network overhead and latency. Even a 100Gb/s network (yes, one hundred gigabits per second) will not compare with a local unix socket for raw volume, throughput and latency.

^s1 For separate database servers only. Doesn't apply to local DB servers.

Total capacity/concurrency

Is MySQL the bottleneck
Maybe. But only once you've nailed your PHP performance and capacity to the point where MySQL is slowing things down. If you've got Varnish and FPC properly configured (don't get us started on how many failed attempts we've seen with either) - then MySQL does become a bottleneck.

So in addition to the above modifications.

Change MySQL engine. XtraDB can excel under load and does show genuine benefits over a stock MySQL distribution.
Stay up to date with MySQL. 5.5 performs better than 5.0 under load.
Change PHP MySQL driver. PHP 5.3 and newer has a native MySQL driver, but in some circumstances, we've found PHP 5.2 with the separate driver to outperform MySQLND for Magento. Test it for yourself
Change search engine. Moving the search out to SOLR/Sphinx (or even some 3rd party external services) can really alleviate the burden of non-transactional load (ie. people not placing orders)
Change layered navigation engine. Again, SOLR is a brilliant engine for layered navigation and due to its non-locking nature is far faster than MySQL.
Add a MySQL slave. This does help browsing (non-transactional) load, but won't help you process more orders per hour - as it is reliant on the Master to process and replicate this data

What wouldn't we bother with

Master/Master. Due to the pretty high tipping point of hardware saturation of a Master/Slave set up (in excess of 1000 orders per hour) - we've never found it a requirement to use Master/Master in production. We have performed extensive testing, but never found it to be advantageous from a performance perspective and with the inherent risks and problems of Master/Master, it simply isn't worth it.

Read vs Write Scalability

The last paragraph really leads on to a key area of read and write scalability. Read scaling can be performed fairly infinitely without too much complication with the addition of more and more slaves.

Given Magento's ratio of Reads to Writes is about 0.1% - considering writes shouldn't be much of a concern. That's why I've not bothered mentioning MySQL Cluster and its clever features like auto-sharding (splitting tables off to separate machines).

Hardware, hardware, hardware

Hardware is easily the quickest answer when it comes to improvement, so I've deliberately not mentioned it above in both scenarios.

But all the software changes in the world aren't going to make any difference if your underlying hardware is insufficient. That could mean...

Low-quality switches with limited buffers
Overly saturated network links
Geographically distant servers
Poor network QoS/CoS
Limited total amount of RAM
Low memory bandwidth RAM
Low IOPs HDD subsystem
Software RAID controller
Low clock speed CPU
Low bandwidth chipset
Hardware virtualisation (almost all types apart from Kernel Level Virtualisation)

Nowadays, there's a really high ceiling on how high you can actually scale on hardware. Lets ignore the myth of infinite scaling "in the cloud" as cloud hardware tends to be fairly mediocre. For example Amazon's flagship models only being 12 Cores @ 3.3GHz. But outside of this, there are some very powerful servers available - our top server has 160 cores and 2TB (yes, Terabytes) of RAM. We've not seen anyone exceed the capabilities of that just yet.

So you've got a massive scope for vertical scaling, before you even need to consider horizontal scaling.

The ever moving target

Its worth mentioning that in the pursuit of performance, the bottleneck will always keep moving.

For a stock Magento store.

Turn the caches on. PHP is the bottleneck
Add a backend cache. PHP is the bottleneck
Add a application-level full page cache. PHP is the bottleneck
Add a server-level front-end cache (eg. Varnish). MySQL is the bottleneck
Add an alternative search/layered navigation engine (eg. SOLR/Sphinx). PHP is the bottleneck
Add more application servers. MySQL is the bottleneck
Add a MySQL slave. Front-end cache is the bottleneck
Add more front-end cache servers. PHP is the bottleneck
Add more application servers. SOLR/Sphinx is the bottleneck

Etcetera.

It pretty much becomes a case of rinse-wash repeat. But what is clear to understand is that MySQL certainly isn't the first port of call for optimisation - and really only comes into play when MySQL is consuming more CPU proportionally to PHP - and this ONLY ever happens when both FPC and Varnish are in use and the server(s) are purely processing orders and nothing much else (because everything else is in caches).

Don't make changes blindly

Simply adding a MySQL slave because you read us say above that it will help, can cost you performance and reliability on a huge level. A congested network, low spec slave server or even improper settings can cause replication problems that can render your store slower than it was to begin with - or cause synchronisation issues between the Master and Slave.

To put things into perspective - some real world examples.

Example 1 - 300 orders per hour

We've used the following hardware to serve 300 orders per hour; and only at that tipping point - we then felt the need to add a dedicated MySQL server and a local MySQL slave.

1 Server
CPU: 2x Intel E5-4620
RAM: 64GB HDD: 4x 80k IOP/s SSDs
RAID: Hardware RAID 10
Magento Version: Magento EE
OS: MageStack

During the entire time, load averages remained under 3.00.

Example 2 - 180 orders per hour

Just two days ago, a new client of ours easily soaked up a big traffic spike. Processing 180 orders per hour with a single-server and Magento Community Edition.

1 Server
CPU: 2x Intel E5-4620
RAM: 64GB HDD: 4x 80k IOP/s SSDs
RAID: Hardware RAID 10
Magento Version: Magento CE
OS: MageStack
Website: Wellgosh.com

During the entire time, load averages remained under 6.00. The load was higher in this scenario and that was down to a couple of factors.

No store-side tuning was performed like in Example 1
The lack of an application-level FPC

And given the recency of this, we've still got the detailed statistics to give some feedback by means of graphs. These tell an excellent story of how load is distributed amongst the key components of a separated Magento architecture (load balancer, web server, db server etc. - using MageStack).

So from front to back ... the date you want to look at is at 12:00am on 22nd February.

Firewall Packets

Varnish Traffic

Nginx Traffic

MySQL Load
MySQL Threads MySQL Bytes MySQL Queries

CPU Load

And most importantly, distribution of load

This image really tells it all. And it is that MySQL is certainly not a burden - not yet at least. So our advice, focus your performance concerns elsewhere, unless you are processing more than a few thousand orders per hour.

Load Distribution

And in summary

Making performance changes isn't "one size fits all". It is a case of analysing your current bottlenecks and making subtle changes/adjustments to suit your store and infrastructure.

But performance aside, there are other benefits to using Percona

We do use Percona XtraDB, almost exclusively. We run custom-compiled builds of MySQL that we developed specifically for Magento and had consulted Percona during the process. But it wasn't just performance that influenced this choice.

The Percona Toolkit
- pt-query-digest
- xtrabackup
- etc.
Percona release frequency
Percona consultative support
Warm cache restarts with InnoDB pool preservation

And much more. So using it over MySQL had other advantages than just performance. In fact - MySQL is and has always been the least of our concerns in the pursuit of performance and stability.

Attributions: wellgosh.com, sonassi.com, iebmedia.com.

Check for Customer Email Address Exists in Checkout – Magento

Looking at Using Javascript to validate response in the text box pointed me in the right direction.

I still have to clean up the code and integrate it correctly in firecheckout, but the following code basically does exactly what I want:

/** 
 * Check if customer email address already exists 
 */ 

public function checkEmailexistsAction()
{       

    if ($this->_expireAjax()) {
        echo 'Ajax Expired'; 
        return;
    }
    if (!$this->getRequest()->isPost()) {
        echo '!isPost'; 
        return;
    }

    $websiteId = Mage::app()->getWebsite()->getId();
    $data = $this->getRequest()->getPost('billing', array());
    $email = $data['email'];

    $customer = Mage::getModel('customer/customer');

    if ($websiteId) {
        $customer->setWebsiteId($websiteId);
        }

    $customer->loadByEmail($email);

    if ($customer->getId()) {
        // This is the part of the code that runs when a customer exists ... 
        echo 'Yup - this one exists!, email address = ' . $email . ' and ID = ' . $customer->getId(); 
        }
    else {
        // This is the part of the code that runs when a customer does not yet exist ...
        echo 'Nope.. This one is not there...'; 
    }
}

Posting 'billing[email]=someemailaddress@somehost.org' to /firecheckout/index/checkEmailexists now reliably returns a yes or no.