Magento Part 3 - MySQL Setup & Performance

<< Back to Magento Performance Tips for Scalability Homepage

MySQL Master/Slave

As mentioned in Part 1 - Infrastructure & Hosting post, you need to take advantage of the MySQL master/slave support in Magento.

Gone are the days of running a single MySQL server for websites. MySQL's Master/slave replication is a great way leverage the power of multiple MySQL servers with very little effort.

Once you have setup your RDS (AWS database instances) as detailed in Part 1 - Infrastructure & Hosting, make sure your local.xml config file looks similar to the below (replace with your database connection details).

<resources>
    <db>
        <table_prefix><![CDATA[]]></table_prefix>
    </db>
    <default_setup>
        <connection>
            <host><![CDATA[RDS_HOST_MASTER:3306]]></host>
            <username><![CDATA[USER]]></username>
            <password><![CDATA[PASSWORD]]></password>
            <dbname><![CDATA[DATABASE]]></dbname>
            <initStatements><![CDATA[SET NAMES utf8]]></initStatements>
            <model><![CDATA[mysql4]]></model>
            <type><![CDATA[pdo_mysql]]></type>
            <pdoType><![CDATA[]]></pdoType>
            <active>1</active>
        </connection>
    </default_setup>
    <default_read>
        <connection>
            <use/>
            <host><![CDATA[RDS_HOST_REPLICA:3306]]></host>
            <username><![CDATA[USER]]></username>
            <password><![CDATA[PASSWORD]]></password>
            <dbname><![CDATA[DATABASE]]></dbname>
            <type><![CDATA[pdo_mysql]]></type>
            <model><![CDATA[mysql4]]></model>
            <pdoType><![CDATA[]]></pdoType>
            <initStatements>SET NAMES utf8</initStatements>
            <active>1</active>
        </connection>
    </default_read>
</resources>

With this basic setup, Magento will push all 'READ' queries to your slave database, and all of the writes and critical reads to your master MySQL server.

Tune MySQL

If you have just released a Magento site and it's not performing don't loose hope, you most likely need to tune MySQL a little to perform better.

There is this great script you can run on your server which helps identify issues you may need to fix. You can find it here:
http://turnkeye.com/blog/magento-performance-optimize-mysql/

Here are some of the config changes 1 usually make to MySQL servers:

key_buffer                 = 16M
max_allowed_packet         = 16M
thread_stack               = 192K
thread_cache_size          = 8
max_connections            = 120
query_cache_limit          = 1M
query_cache_size           = 48M
table_open_cache           = 3000

Part 4 - Magento Application Tuning