inanzzz | Using doctrine memcached caching with QueryBuilder in symfony

This tutorial is based on native doctrine/cache package. First of all, memcached must be enabled in php.ini file and OS itself. Example below will show us how to use memcached cache to increase application performance. For more information, you can read Doctrine Caching chapter, memcached cheat sheet and phpMemcachedAdmin browser GUI.

Facts

metadata_cache_driver: Doctrine metadata stores information about entity structure, relations, validation rules so on which may come from annotations, YAMLs or XML definitions. When activated, it would prevent parsing class metadata in each request. This would provide performance but at same time, if we change anything in class metadata definitions, our application would still use old version from the cache so in such case we should manually clear the cache!

query_cache_driver: Doctrine2 uses DQL to generate SQL queries. Whether we use plain SQL query strings in DQL or QueryBuilder in repositories, Doctrine generates SQL query for each request. Enabling this option won't put the DB query results in cache automatically so we have to manually tell it to do so in QueryBuilder. If we change our SQL query strings or QueryBuilder definitions, our application would still use old version from the cache so in such case we should manually clear the cache!

result_cache_driver: Enabling this option won't put the DB query results in cache automatically so we have to manually tell it to do so in QueryBuilder by setting its lifetime and ID. If we change data in DB our application would still use old version from the cache so in such case we should manually clear the cache!

Notes

You can use commands below in between tests to get fresh results from memcached.

# List running processes
top

# Kill given process
kill -9 process_id

# Start memcached deamon
memcached -d

# Connect to memcached
telnet 127.0.01 11211

Configurations

You can individually use entries below while testing or all in one go.

doctrine:
    orm:
        metadata_cache_driver:
            type: memcached
            host: 127.0.0.1
            port: 11211
            instance_class: Memcached
        query_cache_driver:
            type: memcached
            host: 127.0.0.1
            port: 11211
            instance_class: Memcached
        result_cache_driver:
            type: memcached
            host: 127.0.0.1
            port: 11211
            instance_class: Memcached

You can also use entity manager based configuration like below.

doctrine:
    ...

    orm:
        ...

        entity_managers:
            em_one:
                metadata_cache_driver:
                    type: memcached
                    host: 127.0.0.1
                    port: 11211
                    instance_class: Memcached
                query_cache_driver:
                    type: memcached
                    host: 127.0.0.1
                    port: 11211
                    instance_class: Memcached
                result_cache_driver:
                    type: memcached
                    host: 127.0.0.1
                    port: 11211
                    instance_class: Memcached
                ...
            em_two:
                metadata_cache_driver:
                    type: memcached
                    host: 127.0.0.1
                    port: 11211
                    instance_class: Memcached
                query_cache_driver:
                    type: memcached
                    host: 127.0.0.1
                    port: 11211
                    instance_class: Memcached
                result_cache_driver:
                    type: memcached
                    host: 127.0.0.1
                    port: 11211
                    instance_class: Memcached
                ...

If you define memcached as a service in app/config/services.yml file like we did in one of the other posts, you can use configs below instead. With this way, you can also use defined service in your application for personal caching purposes.

doctrine:
    orm:
        metadata_cache_driver:
            type: service
            id: football.doctrine.cache.memcached # Defined in app/config/services.yml
        query_cache_driver:
            type: service
            id: football.doctrine.cache.memcached # Defined in app/config/services.yml
        result_cache_driver:
            type: service
            id: football.doctrine.cache.memcached # Defined in app/config/services.yml

Base Query

public function findAll()
{
    $qb = $this->createQueryBuilder('l')
        ->select('l, t, p')
        ->innerJoin('l.team', 't')
        ->innerJoin('t.player', 'p')
        ->orderBy('l.name', 'ASC')
        ->addOrderBy('t.name', 'ASC')
        ->addOrderBy('p.name', 'ASC')
        ->getQuery();

    $qb = $qb->getResult();

    return $qb;
}

TESTING

You can use browser based application phpMemcachedAdmin to monitor caching activities.

No cache enabled

Current memcached info

Mac:football inanzzz$ telnet 127.0.0.1 11211
Trying 127.0.0.1...
Connected to localhost.
Escape character is '^]'.

# No memory items found
stats items
END

Benchmark

Millisecond MB
3487        40.5 (*)
665         28.8
669         28.8
679         28.8
668         28.8

metadata_cache_driver enabled

You don't have to enable anything manually for it in QueryBuilder.

New memcached info

Mac:football inanzzz$ telnet 127.0.0.1 11211
Trying 127.0.0.1...
Connected to localhost.
Escape character is '^]'.

# 3 memory items found
stats items
STAT items:13:number 1
STAT items:13:age 30
STAT items:13:evicted 0
STAT items:13:evicted_nonzero 0
STAT items:13:evicted_time 0
STAT items:13:outofmemory 0
STAT items:13:tailrepairs 0
STAT items:13:reclaimed 0
STAT items:13:expired_unfetched 0
STAT items:13:evicted_unfetched 0
STAT items:13:crawler_reclaimed 0
STAT items:13:crawler_items_checked 0
STAT items:13:lrutail_reflocked 0
STAT items:14:number 1
STAT items:14:age 30
STAT items:14:evicted 0
STAT items:14:evicted_nonzero 0
STAT items:14:evicted_time 0
STAT items:14:outofmemory 0
STAT items:14:tailrepairs 0
STAT items:14:reclaimed 0
STAT items:14:expired_unfetched 0
STAT items:14:evicted_unfetched 0
STAT items:14:crawler_reclaimed 0
STAT items:14:crawler_items_checked 0
STAT items:14:lrutail_reflocked 0
STAT items:15:number 1
STAT items:15:age 30
STAT items:15:evicted 0
STAT items:15:evicted_nonzero 0
STAT items:15:evicted_time 0
STAT items:15:outofmemory 0
STAT items:15:tailrepairs 0
STAT items:15:reclaimed 0
STAT items:15:expired_unfetched 0
STAT items:15:evicted_unfetched 0
STAT items:15:crawler_reclaimed 0
STAT items:15:crawler_items_checked 0
STAT items:15:lrutail_reflocked 0
END

# Team entity metadata
stats cachedump 13 100
ITEM sf2orm_default_f2fbb5259b59469e8f50fbd0bfaef6747e168f1e5a445e5cb5812f652c835bf9[Application\BackendBundle\Entity\Team$CLASSMETADATA][1] [1036 b; 1450466352 s]
END

# League entity metadata
stats cachedump 14 100
ITEM sf2orm_default_f2fbb5259b59469e8f50fbd0bfaef6747e168f1e5a445e5cb5812f652c835bf9[Application\BackendBundle\Entity\League$CLASSMETADATA][1] [1650 b; 1450466352 s]
END

# Player entity metadata
stats cachedump 15 100
ITEM sf2orm_default_f2fbb5259b59469e8f50fbd0bfaef6747e168f1e5a445e5cb5812f652c835bf9[Application\BackendBundle\Entity\Player$CLASSMETADATA][1] [1898 b; 1450466352 s]
END

Benchmark

Millisecond MB
3019        40.2 (*)
700         28.5
690         28.5
676         28.5
666         28.5

query_cache_driver enabled

You must add line below to the query to enable it manually.

...
->getQuery()
->useQueryCache(true);

New memcached info

Mac:football inanzzz$ telnet 127.0.0.1 11211
Trying 127.0.0.1...
Connected to localhost.
Escape character is '^]'.

# 1 memory items found
stats items
STAT items:13:number 1
STAT items:13:age 19
STAT items:13:evicted 0
STAT items:13:evicted_nonzero 0
STAT items:13:evicted_time 0
STAT items:13:outofmemory 0
STAT items:13:tailrepairs 0
STAT items:13:reclaimed 0
STAT items:13:expired_unfetched 0
STAT items:13:evicted_unfetched 0
STAT items:13:crawler_reclaimed 0
STAT items:13:crawler_items_checked 0
STAT items:13:lrutail_reflocked 0
END

# Query cache related item
stats cachedump 13 100
ITEM sf2orm_default_f2fbb5259b59469e8f50fbd0bfaef6747e168f1e5a445e5cb5812f652c835bf9[07aa386f36aa0c503519f5baf6076bda][1] [1014 b; 1450467303 s]
END

Benchmark

Millisecond MB
2206        40.2 (*)
687         26.5
654         26.5
674         26.5
657         26.5

result_cache_driver enabled

You must add line below to the query to enable it manually.

...
->getQuery()
->useResultCache(true, 60, 'my_unique_cache_id');

New memcached info

Mac:football inanzzz$ telnet 127.0.0.1 11211
Trying 127.0.0.1...
Connected to localhost.
Escape character is '^]'.

# 1 memory items found
stats items 
STAT items:29:number 1
STAT items:29:age 23
STAT items:29:evicted 0
STAT items:29:evicted_nonzero 0
STAT items:29:evicted_time 0
STAT items:29:outofmemory 0
STAT items:29:tailrepairs 0
STAT items:29:reclaimed 0
STAT items:29:expired_unfetched 0
STAT items:29:evicted_unfetched 0
STAT items:29:crawler_reclaimed 0
STAT items:29:crawler_items_checked 0
STAT items:29:lrutail_reflocked 0
END

# Result cache related item
stats cachedump 29 100
ITEM sf2orm_default_f2fbb5259b59469e8f50fbd0bfaef6747e168f1e5a445e5cb5812f652c835bf9[my_unique_cache_id][1] [44017 b; 1450468525 s]
END

Benchmark

Millisecond MB
3291        41.0 (*)
646         29.0
647         29.0
681         29.0
656         29.0

All 3 enabled

New memcached info

Mac:football inanzzz$ telnet 127.0.0.1 11211
Trying 127.0.0.1...
Connected to localhost.
Escape character is '^]'.

# 4 memory items found
stats items
STAT items:13:number 2
STAT items:13:age 29
STAT items:13:evicted 0
STAT items:13:evicted_nonzero 0
STAT items:13:evicted_time 0
STAT items:13:outofmemory 0
STAT items:13:tailrepairs 0
STAT items:13:reclaimed 0
STAT items:13:expired_unfetched 0
STAT items:13:evicted_unfetched 0
STAT items:13:crawler_reclaimed 0
STAT items:13:crawler_items_checked 0
STAT items:13:lrutail_reflocked 0
STAT items:14:number 1
STAT items:14:age 29
STAT items:14:evicted 0
STAT items:14:evicted_nonzero 0
STAT items:14:evicted_time 0
STAT items:14:outofmemory 0
STAT items:14:tailrepairs 0
STAT items:14:reclaimed 0
STAT items:14:expired_unfetched 0
STAT items:14:evicted_unfetched 0
STAT items:14:crawler_reclaimed 0
STAT items:14:crawler_items_checked 0
STAT items:14:lrutail_reflocked 0
STAT items:15:number 1
STAT items:15:age 29
STAT items:15:evicted 0
STAT items:15:evicted_nonzero 0
STAT items:15:evicted_time 0
STAT items:15:outofmemory 0
STAT items:15:tailrepairs 0
STAT items:15:reclaimed 0
STAT items:15:expired_unfetched 0
STAT items:15:evicted_unfetched 0
STAT items:15:crawler_reclaimed 0
STAT items:15:crawler_items_checked 0
STAT items:15:lrutail_reflocked 0
STAT items:29:number 1
STAT items:29:age 28
STAT items:29:evicted 0
STAT items:29:evicted_nonzero 0
STAT items:29:evicted_time 0
STAT items:29:outofmemory 0
STAT items:29:tailrepairs 0
STAT items:29:reclaimed 0
STAT items:29:expired_unfetched 0
STAT items:29:evicted_unfetched 0
STAT items:29:crawler_reclaimed 0
STAT items:29:crawler_items_checked 0
STAT items:29:lrutail_reflocked 0
END

# Query cache related data and Team entity metadata under same item
stats cachedump 13 100 
ITEM sf2orm_default_f2fbb5259b59469e8f50fbd0bfaef6747e168f1e5a445e5cb5812f652c835bf9[07aa386f36aa0c503519f5baf6076bda][1] [1014 b; 1450468063 s]
ITEM sf2orm_default_f2fbb5259b59469e8f50fbd0bfaef6747e168f1e5a445e5cb5812f652c835bf9[Application\BackendBundle\Entity\Team$CLASSMETADATA][1] [1036 b; 1450468063 s]
END

# League entity metadata
stats cachedump 14 100
ITEM sf2orm_default_f2fbb5259b59469e8f50fbd0bfaef6747e168f1e5a445e5cb5812f652c835bf9[Application\BackendBundle\Entity\League$CLASSMETADATA][1] [1650 b; 1450468063 s]
END

# Player entity metadata
stats cachedump 15 100
ITEM sf2orm_default_f2fbb5259b59469e8f50fbd0bfaef6747e168f1e5a445e5cb5812f652c835bf9[Application\BackendBundle\Entity\Player$CLASSMETADATA][1] [1898 b; 1450468063 s]
END

# Result cache related item
stats cachedump 29 100
ITEM sf2orm_default_f2fbb5259b59469e8f50fbd0bfaef6747e168f1e5a445e5cb5812f652c835bf9[my_unique_cache_id][1] [44017 b; 1450468743 s]
END

Benchmark

Millisecond MB
2476        41.2 (*)
928         26.8
641         26.8
628         26.8
637         26.8
618         26.8
645         26.8

Using doctrine memcached caching with QueryBuilder in symfony

Facts

Notes

Configurations

Base Query

TESTING

No cache enabled

Current memcached info

Benchmark

metadata_cache_driver enabled

New memcached info

Benchmark

query_cache_driver enabled

New memcached info

Benchmark

result_cache_driver enabled

New memcached info

Benchmark

All 3 enabled

New memcached info

Benchmark

Memory usage