28

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?

18

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

| improve this answer | |
  • "Historically, Magento has never been bound by MySQL - but rather PHP." I'm not sure what Magento you speak of but EAV has always been a performance issue. :) – B00MER Feb 2 '13 at 22:22
  • 1
    Well, I'm referring to the 400+ Magento servers that we manage ... as a majority rule, there's a lot of other bottlenecks before MySQL is a consideration. In fact, a prime example is one of our customers during December. With 15k unique visitors per hour, processing 200 orders per hour on a single server set up (32 cores, 64GB RAM). For the typical reader of this question, they are extremely unlikely to be doing even this volume. So at the levels of traffic and transactions they'll be encountering, MySQL is not the bottleneck. – Ben Lessani - Sonassi Feb 2 '13 at 23:16
  • 1
    @Brandon. I just mean to add. I don't deny that tuning MySQL isn't a requirement - it obviously is. But configuring a Master/Master or Master/Slave setup to improve performance isn't required until you actually hit a certain tipping point - and that is pretty high. Also, its much more possible to cause a performance bottleneck or risk data integrity, attempting to do such a thing. – Ben Lessani - Sonassi Feb 2 '13 at 23:37
5

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.

| improve this answer | |
  • Please, we are not here to build a bookmark website where questions are answered with links. Include the essential parts of the answer here, and provide the link for reference. – j0k Jan 23 '13 at 14:14
  • @j0k The links are provided for reference and the answer stands on its own -- if you disagree, please be more specific. – Ralph Tice Jan 23 '13 at 14:15
  • Yes, at least, your answer is better than the other one. What I mean is, OP might need more technical stuff on what configure, why not an architecture schema, etc ... Even if you experience is great! – j0k Jan 23 '13 at 14:22
5

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

| improve this answer | |
  • 1
    Lone link is considered a poor answer since it is meaningless by itself and target resource is not guaranteed to be alive in the future. It would be preferable to include the essential parts of the answer here, and provide the link for reference. – j0k Jan 23 '13 at 14:13
  • @j0k, done as requested ;) – Kenny Jan 23 '13 at 16:04
3

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.

| improve this answer | |
  • 1
    DNS round robin is not a solution of any kind. MySQL proxy or HAProxy are far more sophisticated solutions to balancing MySQL read load. – Ben Lessani - Sonassi Jan 30 '13 at 13:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.