How to optimise database architecture for high volume sites?

Question

Question is less about specific Mysql configuration items, but more about handling multiple databases, splitting read and writes to multiple database servers, master+master? Master+Multiple Slaves?

What have people had the best experience with, and are there any examples on how to achieve this?

Answer 1

We've got a fairly vast experience of MySQL clusters - and Percona have worked with us on a number of occasions when pushing the boundaries of complex configurations.

Can Magento natively handle read-only slaves

Magento is natively capable of splitting off reads/writes to different database servers (with the exception of a few broken releases, eg. EE 1.11) - allowing you to offset select load to an additional (or more) server(s); and forwarding all the update/write queries to a single master.

When should I do it

This is a more appropriate question. With dedicated Magento operating systems like MageStack - it is becoming more common for in-built server side advanced caching techniques to be available and easily used (such as Varnish front end caching and Redis back end caching).

Historically, Magento has never been bound by MySQL - but rather PHP. But as Varnish and Full Page Caching (FPC) are used more frequently, the burden of repeated tasks (category/product loads, frequent searches) is suddenly absorbed and PHP becomes less of a burden. In fact, it only really comes into play to generate the content initially, or complete non-cachable scenarios (add to cart, order completion etc.); for the purpose of explanation we're deliberately ignoring administrative load.

We have always stood by the fact that MySQL isn't an areas of concern for most retailers, as seen both here and here. But if your in the region of processing hundreds of orders per hour, not single or double digits - it will soon become an areas for optimisation.

Ultimately for smaller stores (<25k daily unique visitors)

Your efforts would be far better focused on simply finding an appropriate host who can suggest the right hardware to be on from the offset and that has configured the machine in the most optimal fashion for your store. Don't waste your time pursuing Master/Slave or Master/Master configurations - which will yield no performance benefit and will ultimately require continual attention and advanced MySQL knowledge.

Ultimately hardware sizing and selection will have a bigger part to play than MySQL optimisation.

But for larger stores

As your store starts to grow, converting or transactional load becomes more of a burden with the repeated task of completing complex inserts and updates. The addition of each new order will trigger the decrement of catalogue stock, callbacks from payment gateways and updates from EPOS/ERP systems. Combine this with the associated cache purge of the respective products/categories and you'll soon see MySQL load disproportionately increase.

Multi-master is never a solution we recommend or consider as a viable option, but Master/Slave can yield benefits (we stress, on Enterprise-size stores) by offsetting read load to secondary/tertiary nodes.

But I still want to do it

First configure your slaves. We're big advocates of the Percona utilities and MySQL branches - they have an ideal tool for taking hot backups of your existing DB - innobackupex. There is a good write up here.

On the master

Replace $TIMESTAMP or tab complete.

mysql
> GRANT REPLICATION SLAVE ON *.*  TO 'repl'@'$slaveip' IDENTIFIED BY '$slavepass';
> quit;
innobackupex --user=username --password=password /path/to/backupdir
innobackupex --user=username --password=password /
       --apply-log /path/to/backupdir/$TIMESTAMP/

rsync -avprP -e ssh /path/to/backupdir/$TIMESTAMP TheSlave:/path/to/mysql/
scp /etc/mysql/my.cnf TheSlave:/etc/mysql/my.cnf

On the slave

/etc/init.d/mysql stop
mv /path/to/mysql/datadir /path/to/mysql/datadir_bak
mv /path/to/mysql/$TIMESTAMP /path/to/mysql/datadir
chown -R mysql:mysql /path/to/mysql/datadir
sed -i 's#server-id=1#server-id=2#g' /etc/mysql/my.cnf
/etc/init.d/mysql start
cat /var/lib/mysql/xtrabackup_binlog_info
> TheMaster-bin.000001     481

mysql
> CHANGE MASTER TO MASTER_HOST='$masterip', MASTER_USER='repl', MASTER_PASSWORD='$slavepass', MASTER_LOG_FILE='TheMaster-bin.000001', MASTER_LOG_POS=481;
> START SLAVE;

Then once your slave is operational, in practice, it only takes a few additional lines of code to achieve.

In ./app/etc/local.xml

<default_read>
  <connection>
    <use/>
    <host><![CDATA[host]]></host>
    <username><![CDATA[username]]></username>
    <password><![CDATA[password]]></password>
    <dbname><![CDATA[dbname]]></dbname>
    <type>pdo_mysql</type>
    <model>mysql4</model>
    <initStatements>SET NAMES utf8</initStatements>
    <active>1</active>
  </connection>
</default_read>

Sources

Answer 2

In general Magento is CPU bound, not database bound, and most of the CPU activity can be cached which is why you'll find so many tutorials on varnish / nginx setups. You can also move your admin to a seperate webnode as detailed here.

For general robustness, the absolute best bang for buck will be a managed MySQL service.

I only have experience with Amazon RDS, but they automate failover, backups, upgrades, scaling up/down, as well as read replica creation. So you can have a high availability master node that has automatic failover -- Amazon uses a customized binary log replication in order to keep the slave in sync, failover takes less than 2 minutes usually, and then you can create as many read replicas as you need to scale out for your reporting/integration needs.

I looked into splitting reads/writes which is very doable with Magento's architecture, but the database isn't a bottleneck in my use case. I highly recommend utilizing profiling like xhprof / xhgui rather than guessing at what needs to be optimized. The first rule of profiling is to measure.

Answer 3

I haven't had any production experience with this, but after some digging I have found this article. In this article someone explains how to setup master-slave replication for Magento, so it might be useful to you.

Most important bit:

/app/etc/local.xml

<default_setup>
    <connection>
        <host><![CDATA[Master-host]]></host>
        <username><![CDATA[user]]></username>
        <password><![CDATA[pass]]></password>
        <dbname><![CDATA[magentodb]]></dbname>
        <active>1</active>
    </connection>
</default_setup>
<default_read>
    <connection>
        <use/>
        <host><![CDATA[Slave-host]]></host>
        <username><![CDATA[user]]></username>
        <password><![CDATA[pass]]></password>
        <dbname><![CDATA[magento]]></dbname>
        <type>pdo_mysql</type>
        <model>mysql4</model>
        <initStatements>SET NAMES utf8</initStatements>
        <active>1</active>
    </connection>
</default_read> 

Configuration for master MySQL server (/etc/mysql/my.cnf) add below content in the file:

[mysqld]
server-id       = 1
log_bin         = /var/log/mysql/mysql-bin.log
expire_logs_days    = 10
max_binlog_size     = 100M
binlog_do_db        = magento_demo
binlog_ignore_db    = mysql 

Configuration for slave MySQL server (/etc/mysql/my.cnf) add below content in the file:

[mysqld]
server-id=2
log-bin=mysql-bin
master-host=192.168.1.2
master-user=username
master-password=111111
master-port=3306
replicate-do-db=magento_demo
replicate-ignore-db=mysql
master-connect-retry=60 

Restart both MySQL servers afterwards

Answer 4

One idea is that you can split your catalog reads to slave server(s) using dns round-robin.

Thus setup normal master -> slave(s) replication in MySQL.

Then in your Magento setup you can configure your catalog to do reads from your round-robin configured dns host. Writes will stay to your master database.

You can do this in app/etc/local.xml

<catalog_read_setup>
   <connection>
      <host><![CDATA[round.robbin.dns.host]]></host>
      <username><![CDATA[USERNAME]]></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>
</catalog_read_setup>
<catalog_read>
   <connection>
     <use>catalog_read_setup</use>
   </connection>
 </catalog_read>

You can redirect any core (and 3rd party) modules to use a different MySQL instance in the same manner.