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:
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_driverHopefully I’ve saved a little of your time and few nerve cells too 🙂