How Doctrine2 ORM killed the benefit of my cache to relieve my database
I am working on a web application, which like many, will look for almost invariable data in the database. In my case the list of countries. It can be said that this list is quite stable. So it becomes less interesting to retrieve the data in MariaDB, but for the consistency of the other data stored in the database I don't want to replace the “country” table in the database with something else.
So I decided to cache the entire “country” table in Redis. I need information from the “country” table on all my pages or even several times per page, so the gain should be interesting.
To limit the impact in my already existing code, I implement NormalizableInterface and DenormalizableInterface in the Country entity and that's the entire Doctrine entity in JSON that I store in Redis. I am changing my method allowing me to find a country to use Redis with a fallback to MariaDB if my data has expired. In any case I return the properly hydrated Pays entity.
After a few tests, I can see that Redis is being used, great, I'm pushing my code to prod. I am glued to my monitoring tools, curious to see the gain and now, it's a bit of a cold shower, my gain is very far from what I had estimated.
After analyzing the logs, I realize that I still have a lot of SQL queries to the “country” table in addition to hits on Redis.
The analysis shows me 2 things:
- All the Redis hits that I make and that I only use for display in the browser, I save 100% of SQL queries. The entity doesn't come from Doctrine but I don't use it with Doctrine either, so no problem.
- All the Redis hits I do to re-use Doctrine after, I save 0% of the SQL queries. Systematically, I have an SQL query that will fetch the data in the database.
My application does much more persistence than display (sort of back office), I finally plummeted performance because for almost every SQL query, I added a Redis query and a deserialization. I went on a speleology internship in the Doctrine2 code, especially the EntityManager and the UnitOfWork, to understand what's going on. To take into account my entity from Redis and avoid an exception from Doctrine telling me that the Country entity is unknown, I use the merge () function to add my entity to the EntityManager.
$post = new Post(); $post->setCountry($country); ... $em->merge($country); $em->persist($post); $em->flush($post);
My problem is in the operation of this merge() method. The merge() method of the EntityManager is almost a proxy to the merge() method of the UnitOfWork. By reading the code we quickly see that Doctrine is trying to replace our entity $country with its previously detached version. Except that coming from Redis, this entity never existed in the UnitOfWork. The latter therefore does a find() to get an attachable entity. I didn't investigate further to find out why he couldn't settle for what I gave him.
All this being a bit annoying, I tried another approach using a reference to the entity. Which gives me the code:
$countryRef = $em->getReference('MC\AppBundle\Entity\Pays', $country->getId()); $post = new Post(); $post->setCountry($countryRef); ... $em->persist($post); $em->flush($post);
Unfortunately at the time of calling persist(), Doctrine does a find() again.
So I find myself stuck, with my entity that comes from Redis and the ORM of Doctrine that systematically wants to fetch the entity itself via the database. I haven't investigated using the cache in Doctrine to replace my Redis cache because I want to share my Redis cache with other applications that don't use Doctrine.
The only solution I found is to not use the ORM for persistence and manage everything by hand via Doctrine dbal, no more EntityManager nor UnitOfWork and I can finally appreciate some perf gains on my application.