Magento slow or crashes MySQL under high load: possible reason

Sometimes your Magento 1 website starts slow down or even crashing for no reason if load becomes higher than average.

Once we had exactly this situation with our Magento installation. When load became higher, website started to slow down periodically, or even crash. MySQL was crashing. We also had MySQL deadlocks as a bonus.

We started to debug it. First, we had a script running by cron. Crash usually happened after this script starts. Script was using MySQL and was making select queries to many Magento tables.

At first we thought that this script was responsible for crashes. We even optimized its queries and made it run about ten times faster. But all in vain, MySQL was still crashing time to time.

We started to check all queries that were running near crash time. And then we saw strange queries:

ALTER TABLE `cataloginventory_stock_status` DISABLE KEYS;

and

ALTER TABLE `cataloginventory_stock_status` ENABLE KEYS;

So we started checking where it came from. And then things became more clear.

In Magento, product stock status is updated every every time someone makes an order. And this causing reindex of a stock statuses. And reindex in turn causes disabling indexes on tables to make index process faster. It happens in abstract indexer parent class Mage_Index_Model_Indexer_Abstract::disableKeys and Mage_Index_Model_Indexer_Abstract::enableKeys.

    /**
     * Disable resource table keys
     *
     * @return Mage_Index_Model_Indexer_Abstract
     */
    public function disableKeys()
    {
        if (empty($this->_resourceName)) {
            return $this;
        }

        $resourceModel = $this->getResource();
        if ($resourceModel instanceof Mage_Index_Model_Resource_Abstract) {
            $resourceModel->useDisableKeys(true);
            $resourceModel->disableTableKeys();
        }

        return $this;
    }

    /**
     * Enable resource table keys
     *
     * @return Mage_Index_Model_Indexer_Abstract
     */
    public function enableKeys()
    {
        if (empty($this->_resourceName)) {
            return $this;
        }

        $resourceModel = $this->getResource();
        if ($resourceModel instanceof Mage_Index_Model_Resource_Abstract) {
            $resourceModel->useDisableKeys(true);
            $resourceModel->enableTableKeys();
        }

        return $this;
    }

But! MySQL’s DISABLE KEYS and ENABLE KEYS works only for MyISAM tables. cataloginventory_stock_status table is not one of them, it has “InnoDB” engine, like most tables in Magento. So those queries has no positive effect. Maybe they are just oblivious since time when tables were or were planned to be MyISAM.

We checked that those queries were reason of crashing by running them when our heavy cron script just started:

ALTER TABLE `cataloginventory_stock_status` DISABLE KEYS;ALTER TABLE `cataloginventory_stock_status` ENABLE KEYS;

After running it MySQL crashed instantly.

So we commented out those queries and after that MySQL stopped crashing.

Since I am not an expert in MySQL internals, I still do not know what exactly caused this MySQL behavior. And why it happened only when load was above our usual load. Any explanations appreciated.

Magento version we were using was 1.9.1.0, but latest 1.9 version has the same code at the moment of writing.

How to find node name of a class for config.xml in Magento (easy way)

Let’s assume we want to rewrite some core model (or resource model, or block, or helper). In order to do that we need to make changes in config.xml file of our module. In our case this is Mage_Catalog_Model_Resource_Product_Flat_Indexer:

<models>
    <catalog_resource>
        <rewrite>
            <product_flat_indexer>YourCompany_ExtensionName_Model_Resource_Product_Flat_Indexer</product_flat_indexer>
        </rewrite>
    </catalog_resource>
</models>

But it could be frustrating to figure out product_flat_indexer node name.

So we’ll use PHPStorm and Magicento plugin for it (plugin is paid, but it worth it to install if you work with Magento). With both of that installed we’ll just type Mage::getResourceModel(''); and use autocomplete feature:

Autocomplete

As you can see, the part after catalog/ is what we are looking for.

Overriding base magento layout with local.xml file

Sometimes you need to redefine Magento’s base layout entries. But since you should never edit Magento core files, there must be other way to do this.

Solution is simple: you need to create local.xml file in your theme’s layout folder (e.g. app/design/frontend/yourpackage/yourtheme/layout/local.xml) and override required entries there.

Real example: we want to override template that is defined in base layout in app/design/frontend/base/default/layout/persistent.xml:

<?xml version="1.0"?>
<layout version="0.1.0">

<!-- ... skip ... -->

    <checkout_onepage_index>
        
        <!-- ... skip ... -->
        
        <reference name="checkout.onepage.billing">
            <action method="setTemplate"><template>persistent/checkout/onepage/billing.phtml</template></action>
            <block type="persistent/form_remember" name="persistent.remember.me" template="persistent/remember_me.phtml" />
            <block type="core/template" name="persistent.remember.me.tooltip" template="persistent/remember_me_tooltip.phtml" />
        </reference>
    </checkout_onepage_index>

<!-- ... skip ... -->    
    
</layout>

So we create local.xml file in our theme’s layout folder if it is not already there and put our override in it:

<?xml version="1.0"?>
<layout version="0.1.0">

    <checkout_onepage_index>
        <reference name="checkout.onepage.billing">
            <action method="setTemplate"><template>checkout/onepage/billing.phtml</template></action>
        </reference>
    </checkout_onepage_index>

</layout>

And voila: we have just replaced persistent/checkout/onepage/billing.phtml with our checkout/onepage/billing.phtml

Subscribing on events in Magento custom models

To subscribe on events only for your custom models in Magento you need to redefine $_eventPrefix property of your model like this:

<?php

class Vendor_Module_Model_Something extends Mage_Core_Model_Abstract
{
    protected $_eventPrefix = 'custom_event_prefix';
    
    ...
}

Then you can subscribe on your events in etc/config.xml file of your module as usual:

<?xml version="1.0"?>
<config>
    
    ...

    <global>

    ...

        <events>
            <custom_event_prefix_save_after>
                <observers>
                    <your_identifier>
                        <class>Vendor_Module_Model_Observer</class>
                        <method>yourMethodName</method>
                    </your_identifier>
                </observers>
            </custom_event_prefix_save_after>
        </events>
        
    ...

    </global>

...

</config>

What model events are available?

Here is a list of available events for a model in Magento 1.9 (assuming PREFIX will be replaced by your prefix):

PREFIX_load_before
PREFIX_load_after
PREFIX_save_commit_after
PREFIX_save_before
PREFIX_save_after
PREFIX_delete_before
PREFIX_delete_after
PREFIX_delete_commit_after
PREFIX_clear

Explanation of some:

  • PREFIX_save_commit_after this event happens after save transaction commit in resource model
  • PREFIX_delete_commit_after happens after delete transaction commit in resource model
  • PREFIX_clear happens before clearing data in model’s clearInstance() method. Rarely used

Other events are pretty obvious.

What about standard models?

Magento standard models already have their own event prefixes. If you need them, just do a search for $_eventPrefix = in app/code/core/Mage folder.

Where is bronto reminder auto login feature located

There is an service called Bronto, a marketing platform for e-commerce websites. It has it’s own extension for Magento. This extension has an “auto login” or “silent login” feature: when user clicks on link in abandoned cart email, he gets redirected to his cart and logs in automatically.

You may wonder, where is exactly “auto login” happens. For Magento 1.9 with Bronto 2.4 it is located in Bronto_Reminder_LoadController::indexAction. Actual url where Bronto redirects looks like this: http://site.com/reminder/load/index/message_id/0baa06eb00000000000000000000001b5fa3/id/eDRedi5YZGVbUEk9/?{some_more_params}

It wasn’t obvious for me at first so I decided to share this knowledge here. Hope it helps.

More on reminder emails in bronto documentation

A new WTFPL license for blog

When I was starting a blog, I wanted to use some free license for it’s contents. That was hard to choose a proper licence, because their texts was so hard to understand (also English is not my native language). So I didn’t spend much time on it and choosed one with a boring long name “Creative Commons Attribution 4.0 International License” and forgot about it.

Today I was browsing Ansible Galaxy to fing a role that will install pip in Vagrant box and in one of these roles I saw a license that called “WTFPL”. I went to their site and immidiately felt that this licence is totally fit my needs. It is simple and allowing to do everything with content. “WTFPL” stands for “Do What The Fuck You Want To Public License”. I even decided to switch blog to it.

Worth checking: http://www.wtfpl.net/about/

Django migration error: No installed app with label 'myapp'

How to get rid of this error

This usually happens when you use models from one app in another, as ForeignKey or ManyToMany etc. Django cannot add dependencies in migrations to other apps automatically, so you should manually adjust your migration file and add a dependency to other app’s migration, as described in Django docs:

class Migration(migrations.Migration):

    dependencies = [
        ('app1', '0001_initial'),
        # added dependency to enable using models from app2 in move_m1
        ('app2', '0004_foobar'),
    ]

    operations = [
        migrations.RunPython(move_m1),
    ]

uWSGI configuration file must have supported extension

I faced a problem when setting up uWSGI server. I wanted to run it in Emperor mode. I repeated steps from official docs, but vassal processes wasn’t able to start. It seemed that uWSGI Emperor didn’t see vassal config files.

Spend a whole bunch of hours, digging into that problem. Then I realized my vassal config haven’t had any extension. I added .ini extension to config file and it worked. After restarting main uWSGI proccess, vassal proccess started immidiately.

Although ini files is default choice, uWSGI also supports json, xml and yaml formats.

The point of this story is pretty simple: RTFM.

How to connect PyCharm to postgresql using Vagrant

If you want to connect PyCharm to postgresql data source, it is pretty easy to do.

Prerequisites:

  • PyCharm (I think this will also work in other JetBrains products)
  • Vagrant box with Postgresql inside it. I assume Vagrant is already configured with PyCharm
  • Postgresql jdbc driver installed

Step 1

Click on a green plus button in a “Database” editor tab. Then choose “PostgreSQL” and enter your settings. If you didn’t change PostgreSQL settings, defaults should be ok.

PostgreSQL connection settings

Don’t forget to enter your database name, username and password. Host should be localhost or 127.0.0.1, because we are going to connect through SSH tunnel.

Step 2

Go to “SSH/SSL” tab and check “Use SSH tunnel” checkbox. Then set proxy host to 127.0.0.1, port to 2222, proxy user to vagrant and password to vagrant.

PostgreSQL SSH settings

I tried connecting with Vagrant private key (which is usually located in user home folder under /home/user/vagrant.d/insecure_private_key), but that didn’t work: I only saw “Auth fail” message

Step 3

Now you are ready to press “Test connection” button on a “Database” tab. You should see a message telling that connection is successfull:

PostgreSQL SSH success

Generating requests with Apache JMeter

The problem

UPD 31 July 2015: For Django and python I now prefer to use requests library and Django management commands. With management commands it is easier to collect data required for generating requests. But I will leave the post here because it can be helpful for someone.

I develop a web application. One of it’s modules collects data from user request (user agent, device etc.) and shows visit statistics. While developing statistic module I needed to generate sample request data.

My requirements was:

  • ability to generate requests (of course)
  • ability to use external data sources (links from my application)
  • easy to run on my local machine
  • easy to configure

TL;DR: I ended up with Apache JMeter. Although purpose of this tool is load testing, it helped me reach my goal quickly.

The solution

Start with downloading and running JMeter.

First of all, we need to set up users. Add a Thread Group from “Threads (Users)” section:

Thread Group

Thread group options depends on how many requests you want to generate. Then add HTTP Request from “Samplers”:

Thread Group

There we are setting up our webservers host (localhost in my case) and path. See a ${link} parameter in Path? I will explain it below.

Then we add a HTTP Header Manager from “Config Element”:

Thread Group

Here you can add necessary headers. There is another variable ${userAgent}.

Now we need to populate our variables. JMeter has a tool for that called CSV Data Set Config. It takes data from CSV file.

Thread Group

Here we define a filename, variable name that we used before and csv options, such as encoding and delimiter.

Generate CSV file with my links was pretty easy with simple Django management command (because I use Django)

I also needed to use different browser User Agents, so I downloaded CSV list of them here and created another CSV Data Set Config:

Thread Group

Now our variables in HTTP Request and HTTP Header Manager will be populated with values from CSV file. I also set first row in CSV files to match variable names, but don’t know if this even needed.

That’s all! Now we can run our test case.

But one more thing: if you want to track requests you need to add listener from “Listener” group (I picked up “View Results in Table”):

Thread Group

Alternatives

JMeter is not the only one. I tried several things:

Tsung

This one have a simple XML config, which I could populate with external data. But Tsung requires running it on a remote host, and this adds a lot of pain. But maybe I just didn’t go deeper through docs.

Gatling.io

Gatling has a nice documentation, but it’s config is written in it’s own DSL. Remember, one of my requirements was “easy to configure”, and I didn’t want to spend my day learning yet another language (too lazy, eh). Also, quickstart section of docs told me that I need to manually collect an initial test case. It is a great feature for testing sites, but not for my goal.

ab (Apache Bench)

Didn’t find out how to configure it :(

Thanks for reading! Hope it helps.