How we accelerated Drupal Commerce

Disclamer: if everything that is described below seems to you like “childish prattle” 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, why the download of one page of the catalog took us 5 minutes, and how we managed to overcome it, please welcome under cat.

')
If you have ever picked up a printer through Yandex Market, you must represent the number of fields for such products. We have 184 fields with characteristics for each product in our database, from print speed to 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, what you want ...
Another feature, specifically of our project, is that many fields are used in the filter, which makes it impossible to cache the entire page of the directory. Thus, when a page is displayed, each time a query is made to the database.

The first time we created views, which honestly brought out all the filter fields, we did not manage to wait for the main page to load. And this is despite the fact that the site works on a separate server with very good characteristics.

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

1. Connecting memcached module

On any “kick” in the direction of Drupal, it’s said that it’s slow to work, - its evangelists answer “Use cache”. Actually, this and took up.
Standard Drupal caching, as you know, stores the cache in the same database, which in our case was initially useless.
Therefore, it was decided to keep the cache in RAM - good, it was enough on our server. For this purpose, we used memcached on the server and memcache_storage in Drupal (thanks to Eugene Spleshka for the wonderful module ).

After transferring the data to the cache, everything began to move noticeably faster, but still not the way we wanted. Understand further ...

2. Moving the form cache to memcached

In one of the cloudy days, we noticed some inhuman size of the cache_form table - more than 7Gb! The table was cleared, but it began to grow again at lightning speed.

The reason turned out to be simple: each “Buy” button in our product catalog is a mini-form that Drupal considers necessary to put in the cache, simultaneously dragging in there “everything that comes handy”. 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 slave only Drupal developers, the cache_form table is not automatically cleared , as is the case with other cache tables.

To understand the problem means to solve it by 90% :)
cache_form was also rendered in memcached (contrary to the recommendations of storing it in the database).
For periodic cleaning of the table, the optimizedb module was used (now we put it on all sites with Drupal Commerce by default).
The problem with the AJAX buttons was solved by xandeadx , 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 home page of the site has become “fly”. However, the catalog is still in trouble - the page opens for 2-3 minutes. :(

3. Refusal of fields in views

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

No sooner said than done. Some 1-2 hours of work of the team for sawing through the views and turning pages, and - lo and behold - we managed to reduce the download of the catalog page to "some" 40-50 seconds. Users will wait, right? They have no hurry ...

4. Another optimization attempt - rejection of the standard paginator, connection of caching in the view, caching of entities (entity)

Then the programmers again took out the tambourine from the cabinet (fortunately, they did not have time to remove them far).

Smart people have read that the problem of “brakes” can be a standard pager (aka paginator, aka paging). Cured by installing the views_litepager module.

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

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



The most significant result was obtained by connecting the cache to the views, however, everything turned out not to be smooth here either. First, when caching the query, our product filter started to produce the same result, we 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 you could again go to drink coffee while waiting for the page to load.

Page execution time was 69728.43 ms
Hmmm ...

6. Almost win. Manual query optimization

At a certain point, we realized that it was time to act with harsh methods. Namely, to study in detail what views such are requesting in the database, which takes at least 30 seconds to form a 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 not work so long!
“Wait, why do we need a type check? Do we have all entities with product id to be 'commerce_product'? ”

Picking up an 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 JOIN (including the incomprehensible deleted, which, in our experience, is always zero).
It was possible to completely get rid of at least one JOIN'a, but it was already late at 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 our experience will be useful to someone, and many convenient and fast online stores on Drupal Commerce will be born :)