How to monitor and to log slow queries in Symfony + Doctrine

Question

I am working on a Symfony 3.4 based project using Doctrine 2.6.

Is it somehow possible to automatically monitor all Doctrine queries and log those with an execution time above some threshold?

I have used blackfire.io and other tools on my dev version to find and fix potential performance issues. However even the best testing is always something different than running the same code on real data in production.

Thus I would like to know which queries take the most time to execute when running in production.

Answer 1

You can log all Doctrine queries with the doctrine.dbal.logging config option (see https://symfony.com/doc/current/reference/configuration/doctrine.html).

But that won't give you the time spent per query. To do that, you have to create your own logger similar to the one used by Symfony for the debug toolbar (see https://github.com/symfony/symfony/blob/master/src/Symfony/Bridge/Doctrine/Logger/DbalLogger.php).

Another possible solution is to use the tools given by your database engine to log slow queries automatically in a dedicated file so you can analyze them later.