How We Accelerated Drupal Commerce

Disclamer: if everything that is written below seems to you like “baby talk” and very obvious things, we will be happy to work with you :)

Background: about a year ago, our small but proud web studio received an order to develop an online store printer38.ru. And since we specialize in CMS Drupal, we decided to use Drupal Commerce as an online store module.

Those who are interested in why it took us 5 minutes to load one page of the catalog, and how we managed to overcome it, welcome under cat.


If you ever picked up a printer through Yandex Market, you should represent the number of fields for similar products. We have 184 fields with characteristics for every product in the database - from print speed to the availability of battery life.



Here I must say about one feature of CMS Drupal - for each field a separate table is created in the database. Fee for versatility, whatever you want ...
Another feature, specifically of our project, is that many fields are used in the filter, which is why it is not possible to cache the entire catalog page. Thus, each time a page is displayed, a request to the database occurs.

The first time we created views, which honestly displayed all the filter fields, we were not able to wait for the main page to load. And this despite the fact that the site runs on a separate server with very good characteristics.

The classic “dancing with a tambourine” began - MySQL optimization, query caching, hard debugging and profiling :)
I will try in this post to restore the sequence of optimization actions, as a result of which we were able to achieve an acceptable speed of loading the site pages.

1. Connecting the memcached module

For any “kick” in the direction of Drupal - they say it works slowly - its evangelists respond “Use cache”. Actually, this was done.
Drupal standard caching, as you know, saves the cache in the same database, which in our case was initially useless.
Therefore, it was decided to keep the cache in RAM - fortunately, it was enough on our server. For this purpose, they used memcached on the server and memcache_storage in Drupal (thanks to Eugene Spleshka for the wonderful module ).

After transferring data to the cache, everything began to move noticeably faster, but still not the way I wanted to. We understand further ...

2. Removing the form cache in memcached

One cloudy day we noticed some inhuman size of the cache_form table - more than 7Gb! The table was cleaned, however, it again began to grow at a lightning pace.

The reason was simple: each Buy button in our product catalog is a mini-form that Drupal considers necessary to cache, while simultaneously dragging “whatever comes to hand” there. And when using AJAX buttons (as in our case), the cache starts to grow an order of magnitude faster . In addition to this, for some Drupal-led developers only, the cache_form table is not automatically cleared , as is the case with other cache tables.

To understand the problem means to solve it 90% :)
cache_form was also rendered in memcached (contrary to recommendations to store it in the database).
For periodic table cleaning we used the optimizedb module (now we put it by default on all sites with Drupal Commerce). Xandeadx solved the
problem with the AJAX buttons , but its solution appeared only a couple of months after the actions I described, so at that time we could not use it in our project. ;) The main page of the site began to “fly”. However, the catalog is still in trouble - the page opens for 2-3 minutes. :(

3. Failure from fields in views

Friends, as our most experienced developer said, “We all know that Drupal caches nodes. So why don’t we use it, and every time we make views pull all the data from the database? ”

No sooner said than done. Some 1-2 hours of the team working on sawing up views and page turning, and, lo and behold, they managed to reduce the loading of the catalog page to “some” 40-50 seconds. Users will wait, right? They have nowhere to hurry ...

4. Another optimization attempt - abandoning the standard paginator, connecting caching in the "view", caching entities

Then the programmers again took the tambourine from the cabinet (fortunately, they didn’t manage to remove it far).

Do smart people read that the problem of "loose" can be a standard pager (aka paginator aka paging). Cured by installing the module views_litepager .

At the same time, we installed the commerce_entitycache module , which should cache the entity (s) of the product object.

However, all these "dances" gave only a small increase in speed.



The most significant result was the connection of the cache for views, however, not everything went smoothly here either. Firstly, when caching a request, our product filter began to produce the same result, I had to disable it. And secondly, acceleration was observed only when loading a “clean” page, when no filter was selected. It was worth choosing at least one checkbox, and again you could go drink coffee while waiting for the page to load.

Page execution time was 69728.43 ms Hmm
...

6. Almost a victory. Manual query request optimization

At some point, we realized that it was time to act with harsh methods. Namely, to study in detail what exactly such views asks in the database, that it takes at least 30 seconds to generate the result.

And we saw something like this:

...
INNER JOIN
   {commerce_product} commerce_product_field_data_field_product_reference 
      ON field_data_field_product_reference.field_product_reference_product_id = commerce_product_field_data_field_product_reference.product_id  
INNER JOIN
   {field_data_commerce_price} commerce_product_field_data_field_product_reference__field_data_commerce_price 
      ON commerce_product_field_data_field_product_reference.product_id = commerce_product_field_data_field_product_reference__field_data_commerce_price.entity_id 
      AND (
         commerce_product_field_data_field_product_reference__field_data_commerce_price.entity_type = 'commerce_product' 
         AND commerce_product_field_data_field_product_reference__field_data_commerce_price.deleted = '0'
      )  
INNER JOIN
   {field_data_field_printer_a4_speed_2} commerce_product_field_data_field_product_reference__field_data_field_printer_a4_speed_2 
      ON commerce_product_field_data_field_product_reference.product_id = commerce_product_field_data_field_product_reference__field_data_field_printer_a4_speed_2.entity_id 
      AND (
         commerce_product_field_data_field_product_reference__field_data_field_printer_a4_speed_2.entity_type = 'commerce_product' 
         AND commerce_product_field_data_field_product_reference__field_data_field_printer_a4_speed_2.deleted = '0'
      ) 
...

and so - for each field involved in filtering.
Yes, there are many JOINs. But they can’t work out SO long!
“Wait, why do we need a type check? But all entities with product id must be 'commerce_product'? ”

Having picked up the IDE, we write a small hook in our module:

/**
 * Implementation of hook_views_query_alter
 * @param type $view
 * @param type $query 
 */
function mymodule_views_query_alter(&$view, &$query) {
    if ($view->name == 'catalog_v_2') { 
        foreach ($query->table_queue as $key=>$item) {
               $query->table_queue[$key]['join']->extra=array();
        }                                                
    } 
}

That is, we simply “throw out” all additional conditions from the JOIN (including the incomprehensible deleted, which, in our experience, is always zero).
It was possible to completely get rid of at least one JOIN, but it was already late night, and everyone wanted to sleep :)

Compare:

Before: 34665.211 ms
After: 0.13 ms

Yes, “there is no limit to perfection”, so we continue optimization experiments. We hope that our experience will be useful to someone, and many convenient and fast online stores on Drupal Commerce will be born :)