Wednesday, February 6, 2013

Using master and slave databases with Symfony 2 + Doctrine

One of our customers works from different locations in the Netherlands, but with their backoffice on a server in south-east Germany and an extra penalty caused by the crappy internet connection, this can sometimes be very slow.
So the first thing I thought of to solve this is to run a mirror of their backoffice on location. Setting up the MySQL replication[1] and automatic syncing of files[2] was easy. But then my code had to be adjusted to work with a Master server (the server in Germany) and a Slave server (within the local network).
For those of you unfamiliar with the concept of Master and Slave database servers: The idea is is to have multiple database servers, one Master server (who 'owns' all the data) and one, or multiple, Slave servers, who receive a copy of all the changes from the Master server. The Master server needs to be accessible to all the Slave servers (i.e. via the internet), but the Slave servers can be servers within a local network.
When I started my search on the internet for information on how to achieve this, I found many complicated solutions, including solutions that told you to change vendor code, to write custom connection classes etc. But I figured there had to be an easier way and (thankfully) I was right.
Using master and slave servers is actually pretty easy and only required a small addition to my config.yml:

# Doctrine Configuration
doctrine:
    dbal:
        driver:   %database_driver%
        host:     %database_host%
        port:     %database_port%
        dbname:   %database_name%
        user:     %database_user%
        password: %database_password%
        #Start of new configuration
        slaves:
            slave1:
                host:     %database_host_slave1%
                port:     %database_port_slave1%
                dbname:   %database_name_slave1%
                user:     %database_user_slave1%
                password: %database_password_slave1%
        #End of new configuration

    orm:
        auto_generate_proxy_classes: %kernel.debug%
        auto_mapping: true

I also updated my parameters.yml to keep all the connection variables in one place:
parameters:
    database_driver:   pdo_mysql
    database_host:     192.168.1.12
    database_port:     ~
    database_name:     db_prod
    database_user:     someuser
    database_password: somepasswd
    
    #New parameters
    database_host_slave1:     127.0.0.1
    database_port_slave1:     ~
    database_name_slave1:     db_dev
    database_user_slave1:     root
    database_password_slave1: ~

Note: Whenever you use slaves, the DoctrineExtension.php loader will automatically change the wrapperClass to ..., unless you specify your own wrapperClass (use wrapper_class as a key in your config)

Links:

[1] HowtoForge - How To Set Up MySQL Database Replication With SSL Encryption On CentOS 5.4
http://www.howtoforge.com/how-to-set-up-mysql-database-replication-with-ssl-encryption-on-centos-5.4
[2] HowtoForge - Mirror Your Web Site With rsync On Fedora 10
http://www.howtoforge.com/mirror-your-web-site-with-rsync-on-fedora-10

4 comments:

  1. I was recently testing this setup and noticed it's not perfect. Slave is only used with executeQuery(). As soon as your code calls something else, it switches to master. Unfortunately this includes prepared statements which makes the setup unusable in most scenarios.

    Check the MasterSlaveConnection source code: https://github.com/doctrine/dbal/blob/master/lib/Doctrine/DBAL/Connections/MasterSlaveConnection.php

    ReplyDelete
  2. Hey Kuba,

    the way around that is to use the keep_slave option.

    To improve performance I also added the following code to the MasterSlaveConnection class

    public function executeQuery($query, array $params = array(), $types = array(), \Doctrine\DBAL\Cache\QueryCacheProfile $qcp = null)
    {
    $this->connect('slave');
    return parent::executeQuery($query,$params,$types,$qcp);
    }

    public function getWrappedConnection()
    {
    $this->connect('slave');
    return parent::getWrappedConnection();
    }


    This makes sure the slave connection is used for ALL read queries!

    ReplyDelete
  3. Guys, as you can see on Doctrine doc, executeQuery creates prepared statements so I think there isn't any problem:

    http://docs.doctrine-project.org/projects/doctrine-dbal/en/latest/reference/data-retrieval-and-manipulation.html#using-prepared-statements

    executeQuery($sql, $params, $types) - Create a prepared statement for the passed SQL query, bind the given params with their binding types and execute the query. This method returns the executed prepared statement for iteration and is useful for SELECT statements.

    AlexAgile

    ReplyDelete