Doctrine 2 Master-slave database using Symfony 2

In some of our projects we use Percona Server instead of MySQL database, with splited Write / read nodes. Doctrine has an excelent built-in solution to handle such situation wich is supported by Symfony 2

MasterSlaveConnection Class

This class enable us to define master node, and one or more slaves nodes. By default SELECT queries are made using one of slaves, and other operations (INSERT, UPDATE, DELETE) made via master.

But the class has few limitations, which we should remember, espacially second point:

1. Master picked when ‘exec’, ‘executeUpdate’, ‘insert’, ‘delete’, ‘update’, ‘createSavepoint’, ‘releaseSavepoint’, ‘beginTransaction’, ‘rollback’, ‘commit’, ‘query’ or ‘prepare’ is called.
2. If master was picked once during the lifetime of the connection it will always get picked afterwards.

More about the class in Doctrine documentation

According to these we were a little bit worried that to much queries will go to master. But in our tests with quite complex application, we have about 99,8% queries go to slaves nodes, which is great result for us.

Configure Doctrine MasterSlaveConnection in Symfony 2

We must configure using slaves in doctrine section in our Symfony 2 config.yml

Only one connection

If we have only one connection in our doctrine config (default)

doctrine:
    dbal:
        driver:   %database_driver%
        host:     %database_host%
        port:     %database_port%
        dbname:   %database_name%
        user:     %database_user%
        password: %database_password%
        charset:  UTF8
        
        slaves:
            slave1:
                dbname:   %database_slave1_name%
                host:     %database_slave1_host%
                port:     %database_slave1_port%
                user:     %database_slave1_user%
                password: %database_slave1_password%
            slave2:
                dbname:   %database_slave2_name%
                host:     %database_slave2_host%
                port:     %database_slave2_port%
                user:     %database_slave2_user%
                password: %database_slave2_password%

Using few connections

If we have more the one connection in our doctrine config, using connections section.

doctrine:
    dbal:
      default_connection:  default      
      connections:
         default:
            driver:   "%database_driver%"
            host:     "%database_host%"
            port:     "%database_port%"
            dbname:   "%database_name%"
            user:     "%database_user%"
            password: "%database_password%"
            charset:  UTF8
            slaves:
                slave1:
                    dbname:               "%database_name%"
                    host:                 "%database_host%"
                    port:                 "%database_slave1_port%"
                    user:                 "%database_user%"
                    password:             "%database_password%"
                    charset:              UTF8
                slave2:
                    dbname:               "%database_name%"
                    host:                 "%database_host%"
                    port:                 "%database_slave2_port%"
                    user:                 "%database_user%"
                    password:             "%database_password%"
                    charset:              UTF8    

We use HAProxy for defining nodes, so the nodes differs in our configuration only by port.

Alternative master-slave solutions

Override doctrine classes

There is an easy way to override Doctrine Doctrine_Query and Doctrine_Record classes to implement own logic, how doctrine should choose connections.
http://doctrine.readthedocs.org/en/latest/en/cookbook/master-and-slave-connections.html

External application – MySQL Proxy

We could use external application, which will work as proxy server. Our app will be connect to proxy, and the proxy will balance to master or slaves server.
For MySQL/Percona there is Mysql Proxy https://dev.mysql.com/downloads/mysql-proxy/ currently still in alpha.