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.

Running integration tests in PHPStorm with PHPUnit and Docker

What are we trying to make?

Make PHPStorm run PHPUnit integration tests that use MySQL database using Docker.

If you only need to run unit tests with PHPUnit, without using database or Redis or something else, you can try this: http://obrown.io/2015/12/23/phpunit-docker-phpstorm.html

How we could get this done?

By using Docker Compose to tie up containers.

The main idea is to set up Docker Compose test environment, create a shell script that runs PHP in this environment, and use that shell script as PHP interpreter in PHPStorm.

Step 1:

You will need an image with at least PHPUnit installed. You can use existing image which can be found on Docker Hub or create your own. Example Dockerfile on Github: https://github.com/binary-data/phpunit-dockerfile. Image that I use for examples is located on Docker Hub: https://hub.docker.com/r/binarydata/phpunit/

Since building Docker images is not a topic for this article, more information on this can be found in Docker documentation

Step 2:

You will need to create a docker-compose.yml file with configuration describing your test environment, like this:

version: '2'
services:
  phpunit:
    image: binarydata/phpunit # our PHPUnit image, created or found on Docker Hub
    volumes:
      - /home/user/work/project/src/:/home/user/work/project/src/ # folder where project resides. Should have same path
      - /tmp/:/tmp/ # linking /tmp/ folder because PHPStorm stores its stuff there
    depends_on:
      - db
    links:
      - db
  db:
    image: mariadb # image with MySQL database, I use mariadb here
    environment:
      MYSQL_ROOT_PASSWORD: 123456
      MYSQL_DATABASE: integration_tests

You can add as many services as you need in your test environment, like Redis or Memcached. More information on Docker Compose you can find in its documentation

Step 3:

Create a shell script, say docker.sh, next to docker-compose.yml that runs PHP in your environment:

#!/usr/bin/env bash
cd `dirname $0` && docker-compose run --rm phpunit php "$@"

cd dirname $0 part changes working directory to where docker-compose.yml is located, so docker-compose run could see it. Then it runs php inside phpunit container, with all linked containers started by Docker Compose.

Make script executable:

chmod +x /path/to/docker.sh

Step 4:

Set up PHP interpreter in PHPStorm. Go to “Settings -> Languages & Frameworks -> PHP”:

Add interpreter

Add “Other Local” interpreter and provide a path to previously created docker.sh:

Set interpreter

You should see that PHPStorm detected PHP version. You can ignore warning about php.ini file.

Then select newly created interpreter as project interpreter.

Step 5:

Create configuration for your tests. Go to “Run -> Edit configurations” and add PHPUnit configuration:

Create configuration

I have added path to PHPunit to interpreter include path there, so PHPStorm could see it:

-d include_path=./:/usr/local/lib/php:/root/.composer/vendor/phpunit

and set a working directory, same as in docker-compose.yml.

Now you should be able to run tests.

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