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.