Catalog Rules updates with Zend Queue

  • SumoMe

Queuing Catalog Rules Updates in Magento with Zend Queue

It’s not a secret that Magento Catalog and Sales rules have caused more than one problem here and there. The issue per se is not what Magento does with the rules but the series of events that get triggered when it does.

The core of the problem is very easy to address. However let’s understand why this becomes a nightmare when left unattended: Magento expects you as a Developer to do proper house cleaning and as a business administrator expects that you understand the consequences of your actions. It’s almost fair to say that this is not a bug or poor design, rather lack of proper testing!

When you save a product in Magento’s administration many events gets dispatched, one of them forces Magento to update some tables, more specifically these 2 tables:

  • catalogrule_product
  • catalogrule_product_price

These tables get larger over time as you add more customer groups, websites and products and how you configure them.

In a one to one relationship, one of the main culprits is the following snippet of code:

  /**
     * Apply all catalog price rules for specific product
     *
     * @param   Varien_Event_Observer $observer
     * @return  Mage_CatalogRule_Model_Observer
     */
    public function applyAllRulesOnProduct($observer) {
        $product = $observer->getEvent()->getProduct();
        if ($product->getIsMassupdate()) {
            return;
        }
        Mage::getModel('catalogrule/rule')->applyAllRulesToProduct($product);
        return $this;
    }

And I mentioned one to one relationship because, as you may have guessed things get worse when you are indexing…

Talk to me… what’s so bad about that line of code?

That line of code was solely responsible for adding 200-330 extra seconds when saving a simple product in the administration area and an extra 400-1000 seconds when saving bundle products. Yes! You read that right, saving a product was excruciatingly painful.

Just look at this New Relic snapshot, notice how this was affecting not just the backend but the frontend as well:

queue-bundle

Why is that line of code so bad? Because the after-mentioned tables get so much data that it will lock the operation until MySQL gets updated. Some of the conditions that will cause issues:

  • More than one website/store front view
  • More than one customer group
  • More than 300 different SKUS
  • One or more catalog rule
  • Catalog Rules matching all stores and all customer groups (there’s many business cases why this is needed)

How do we solve this? Tell me it’s easy.

Before we get to the solution (sorry! – I am a “talker”), I’d like for you to understand what a queue is and how it works.

In Computer Science, a queue is a computing list in which entries are deleted from one end and inserted at the other. Think of what happens when you go to Starbucks to get your coffee! They use the First In First Out approach with some exceptions, of course. FIFO then guarantees that operations happen in the order they were scheduled or expected to happen.

As to how to solve this problem: it’s very simple. What we have done is to add Zend Queue to the mix with the MySQL adapter, we went with this adapter for 2 reasons:

  • Don’t need add any extra component to the architecture
  • Our database server is configured properly (not to mention all of the resources it has) so adding a small database to it really doesn’t amount to much.

Here is what we did:

  • We overrode the CatalogRule Observer with one of our own, this is the most important part.
  • We created a Magento shell script to run our runCatalogRulesUpdate function
  • We added an extra database following the following naming convention: Magento’s database name with “_queue” appended at the end. this approach had 2 slight advantages:
    • We don’t need to hardcode the database name
    • The code is very portable that way

After we used this module, saving products in the administration area took no longer than 4s.

If you want to use this module, here what you need to do first:

  • Create a database, if you follow the module example, simply use your Magento current database name and append “_queue” to it.
  • Import this file into that database: lib/Zend/Queue/Adapter/Db/mysql.sql
  • Install the Catalog Rule Queue Module 
  • Set up a cron jon to run the shell/catalog_update.php file. It really doesn’t matter how often that cron job runs, because queue has an internal timeout variable.
  • Profit

You can checkout this module in bitbucket!

Let me know what you think in the comments and if this module can use some better coding Smile

Something worth mentioning is that Zend Queue provides the implementation for other Queue containers and migrating to one of those should be trivial.

VN:F [1.9.22_1171]
Rating: 0.0/10 (0 votes cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)

Author: Luis Tineo

Husband, Father, performance improvement junkie, biker and video gamer, Linux user and in my day job I'm a Software Engineer at BuyerQuest.

Share This Post On