Three Symfony2 database optimisation tips to implement today

Hi! Today I will give you 3 simple pieces of advice for database optimization in Symfony2.

Tip 1.

To reduce the number of requests to the database, you can send a read request on read replica.
We need to create one or more database replications. In the AWS- service RDS it’s very easy to do. In the context menu choose Create Read Replica:blogsymfonycachess

A replication will be created after a few minutes, and from then on we can use it.
Now we need to add connection parameters to the replication’s Symfony2 project configuration (in the file app/config/config.yml)

doctrine:
    dbal:
       driver: "%database_driver%"
       host: "%database_host%"
       port: "%database_port%"
       dbname: "%database_name%"
       user: "%database_user%"
       password: "%database_password%"
       slaves:
           slave1:
               host: %database_slave1_host%
               port: %database_slave1_port%
               dbname: %database_slave1_name%
               user: %database_slave1_user%
               password: %database_slave1_password%
           slave2:
               host: %database_slave2_host%
               port: %database_slave2_port%
               dbname: %database_slave2_name%
               user: %database_slave2_user%
               password: %database_slave2_password%
           slave3:
               host: %database_slave3_host%
               port: %database_slave3_port%
               dbname: %database_slave3_name%
               user: %database_slave3_user%
               password: %database_slave3_password%

It’s important to understand when the system selects master, and when it selects a slave connection.
Master is chosen when the following commands are executed:’exec’, ‘executeUpdate’, ‘insert’, ‘delete’, ‘update’, ‘createSavepoint’, ‘releaseSavepoint’, ‘beginTransaction’, ‘rollback’, ‘commit’, ‘query’ or ‘prepare’. When connected to master, the system won’t connect to slave. If the are multiple slave connections they will be selected randomly.
In general we need to minimize the amount of requests that post to the base, to as few as possible.

Tip 2.

For caching the metadata database structure and the generated requests and results of these requests we can use an additional cache driver. In our example we used SncRedisBundle.
We have used this bundle before to save sessions in Redis. Now we will use it for saving and for database caching.
Caching metadata is important because every time a first request to a database is made it will build a virtual database structure for Doctrine. This can be a very expensive operation, especially if the database structure is large.
Also in this cache will be stored queries to the database which generates in Doctrine. This is also a very expensive operation. Lastly query results will also be cached, hopefully that’s clear (be careful and cautious with these results).
To implement as described we need to perform a few simple steps.
First of all – configure SncRedis. Maybe you will have something like this:

snc_redis:
     clients:
         sncredis:
              type: predis
              alias: sncredis
              dsn:
                    - "redis://%redis_host%?alias=master"
                    - "redis://%redis_host_repl1%"
                    - "redis://%redis_host_repl2%"
              options:
                    replication: true
      session:
          client: sncredis
          prefix: "ek:ses:blog"
          ttl: 31536000
      doctrine:
          metadata_cache:
               client: sncredis
               entity_manager: [default, cached]
          result_cache:
               client: sncredis
               entity_manager: [default, cached]
          query_cache:
               client: sncredis
               entity_manager: [default, cached]

Secondly we need to make some changes to the repositories. If you need to cache requests, before creating QueryBuilder you need to call the method: useQueryCache(true).
If you need to cache the result of a request you need to call the method: useResultCache(true, 600, $cacheId). The first parameter must be true. The second parameter is the time of cached results in seconds. The last parameter is an ID request. This ID must be unique for each request. In my case I generate this ID based on the parameters. Here is an example of part of a request:

$cacheId = 'Activity_';
/** @var Parameter $parameter */
foreach ($parameters as $parameter) {
 $cacheId .= serialize($parameter);
}
$cacheId = md5($cacheId);
$query = $this->createQueryBuilder('a')
 ->where($whr)
 ->setParameters(new ArrayCollection($parameters))
 ->orderBy('a.createdAt', 'DESC')
 ->setMaxResults($count)
 ->getQuery()
 ->useQueryCache(true)
 ->useResultCache(true, Util::CACHE_ACTIVITY_FEED, $cacheId)
 ->getResult();

Tip 3.

In Doctrine >=2.5 we now have a second level cache. The function is experimental but works fine. It’s used for caching the results of a simple request and then the system uses this cache whenever possible using lazy loading.
We need to specify caching regions. In entities we need to add the annotation @ORMCache. For example @ORMCache(usage=”NONSTRICT_READ_WRITE”, region=”region_video”). The usage parameter indicates cached mode. It’s at this stage that we need to find a compromise between caching performance and relevance of data. There are 3 modes and each of them has it’s own advantages and disadvantages, so choose carefully!
In this case we need to describe all entities which we want to cache. After creating regions we need to update Doctrine to version >=2.5.
Then we have to switch on the second level cache and describe all regions, clarifying cache lifetime. For this we add a few options in Doctrine configuration. Here’s part of my configuration, for better understanding:

doctrine:
     dbal:
          driver: "%database_driver%"
          host: "%database_host%"
          port: "%database_port%"
          dbname: "%database_name%"
          user: "%database_user%"
          password: "%database_password%"
          slaves:
               slave1:
                    host: %database_slave1_host%
                    port: %database_slave1_port%
                    dbname: %database_slave1_name%
                    user: %database_slave1_user%
                    password: %database_slave1_password%
               slave2:
                    host: %database_slave2_host%
                    port: %database_slave2_port%
                    dbname: %database_slave2_name%
                    user: %database_slave2_user%
                    password: %database_slave2_password%
         orm:
             entity_managers:
                default:
                    auto_mapping: true
                    metadata_cache_driver:
                        type: service
                        id: snc_redis.sncredis
                    query_cache_driver:
                        type: service
                        id: snc_redis.sncredis
                    result_cache_driver:
                        type: service
                        id: snc_redis.sncredis
                cached:
                   mappings:
                        CachedBackendBundle:
                             type: annotation
                             dir: %kernel.root_dir%/../src/Ekreative/BackendBundle/Entity
                             prefix: EkreativeBackendBundleEntity
                             alias: CachedBackendBundle
                 metadata_cache_driver:
                        type: service
                        id: snc_redis.sncredis
                 query_cache_driver:
                        type: service
                        id: snc_redis.sncredis
                 result_cache_driver:
                        type: service
                        id: snc_redis.sncredis

                 second_level_cache:
                        region_cache_driver:
                             type: service
                             id: snc_redis_cache_driver
                        region_lock_lifetime: 60
                        log_enabled: true
                        region_lifetime: 300
                        enabled: true
                        regions:
                            region_video:
                                lifetime: 300
                                cache_driver:
                                    type: service
                                    id: snc_redis_cache_driver
                            region_comment:
                                lifetime: 60
                                cache_driver:
                                    type: service
                                    id: snc_redis_cache_driver
                            region_user:
                                lifetime: 10
                                cache_driver:
                                    type: service
                                    id: snc_redis_cache_driver
                            region_category:
                                lifetime: 900
                                cache_driver:
                                    type: service
                                    id: snc_redis_cache_driver

Hopefully I’ve saved a little of your time and few nerve cells too 🙂