In this short article we will show you how to debug your database queries by enabling database logging. Logging is somewhat underused feature and the official guide offers lots of great tips for how to utilize it.
Today we are specifically going to look into how the database logging can be enabled and how to read it.
Enable query logging
It is extremely simple to enable this just run the following 2 commands in command line:
After this is done you should be able to see the log file at var/debug/db.log
To understand how the command works we can take a look at its implementation. Simply searching for the command name we can find that this class is responsible \Magento\Developer\Console\Command\QueryLogEnableCommand
Possible command input options:
- include-all-queries - if set logs everything
- query-time-threshold - determines the query time threshold that are logged. This way you can limit the logging to only slow queries.
- include-call-stack - determines if stack trace should be included in the logs
The command itself only saves the environment configuration. The rest of the magic happens in Magento\Framework\DB\Logger\LoggerProxy. The configuration is passed in as an arguments for the class.
The proxy class is preference for the default LoggerInterface in the same di.xml file.
From this point on everything is built in the core classes. If the database logger interface is used anywhere then the logger will already output the stats for each query using the following abstract function \Magento\Framework\DB\Logger\LoggerAbstract::getStats
Output for the above will be found in var/debug/db.log. It will look similar to this:
## 2021-02-20 12:25:31 ## 8 ## QUERY SQL: SELECT `flag`.* FROM `flag` WHERE (`flag`.`flag_code`='config_hash') AFF: 1 TIME: 0.0005 TRACE: #1 Magento\Framework\DB\Logger\File[Magento\Framework\DB\Logger\LoggerAbstract]#000000005e3d95fd000000002089ef9c#->getStats('query', 'SELECT `flag`.* ...', array(), &Magento\Framework\DB\Statement\Pdo\Mysql#000000005e3d96cb000000002089ef9c#) called at [vendor/magento/framework/DB/Logger/File.php:67] #2 Magento\Framework\DB\Logger\File#000000005e3d95fd000000002089ef9c#->logStats('query', 'SELECT `flag`.* ...', array(), &Magento\Framework\DB\Statement\Pdo\Mysql#000000005e3d96cb000000002089ef9c#) called at [vendor/magento/framework/DB/Logger/LoggerProxy.php:152]
Disable query logging
After you are done debugging make sure to disable the logs. Otherwise you might end up with extremely large files and storage could fill up quickly. You can use this command to disable the database query logging:
That is it for this article. Hopefully this helps when debugging performance problems for your store or perhaps when you need to find specific queries that may be causing any issues.