Adding master-slave MySQL database replication to Sugar with ease, thanks to mysqlnd_ms

bsoremsugar —  September 21, 2012 — 7 Comments

After you’ve done all the optimizations with a Sugar instance that lives on one server, the next logical move is to split it to multiple servers, one for the database and another for the web server. This enables you to tweak the configuration for both servers independently, as database servers require different tuning settings for disk access and such that web services do not benefit from.

Once you are past that, you’ll often notice that accessing your database can often be blocked by heavy running SQL queries, most often INSERT and UPDATE statements that require lots of index changes, or even SELECT statements which can bit a bit heavy-weight with the InnoDB storage engine. This is where master-slave replication comes in handy, where all write database queries are directed to the master database, and the slave is used for read operations. This enables you to configure the database performance setting even further, as the master will be a more strict transactional database, while the slave more used for pure data retrieval. This also helps balance your database load as for most apps they will be read intensive, often to the order of 2-1 or 3-1 reads to writes.

While setup on the database side is rather trivial, in the past you needed to specifically tweak your app to do master/slave replication to manually manage the database connections. We did this in Sugar by specifically enabling a read slave for Reporting in the Enterprise and Ultimate editions of the product. But now, it’s much easier thanks to the mysqlnd_ms extension, which can be used with PHP 5.3 or later. With this addon, you can define the master and slave databases in a config file at the extension level, and then each query passed thru the extension will be parsed to see if it is a read or write query and be directed accordingly.

Check out the documentation for mysqlnd_ms here, and let us know what you think about this extension.

7 responses to Adding master-slave MySQL database replication to Sugar with ease, thanks to mysqlnd_ms

  1. 

    Nice post. Are you suggesting Master/Slave both be separate servers?

  2. 

    Hi John, Have you seen any issues where code assumes Database has been written to master but not replicated to slave in time for the next read. E.G: if you set a bean value, and then read it immediately afterwards – reading from slave the record many not exist?

  3. 

    Thanks for the suggestion about using mysqlnd_ms with SugarCRM, but I wanted to make sure readers noted that conventional MySQL master/slave replication (the “trivial” kind) still has to perform all write queries (via replaying the replication log) on each slave, so your slaves are not “used for pure data retrieval”, and can also be blocked by writes. In this scenario, having slaves is only useful for distributing read load (huge JOINs and such), so it’s important to note that you need at least 2 databases being used for reads to see any advantage.

    • 

      Great point! However, the nice part is you can tune the slaves to be better at data retrieval versus the transactional tuning of the master, so theres some advantages you can achieve with the help of a good DB admin.

  4. 

    Hi John.. does it work with Sugar 7.1.0?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s