Magento 2 How To Log All Database Queries
We are involved with a number of Magento 2 websites and this means we are frequently looking to resolve errors and improve performance. An important part of resolving errors with Magento 2 is analysing what exactly is going on between the application and the database. To do that quickly and simply we often use the following method to log all database ‘traffic’ to a file so we can debug uncaught issues, or issues which are caught but only show an Asymmetric transaction rollback
in the exception.
To enable this useful logging functionality we just need to override Magento 2’s default database logger class (which is silent) with one which logs to a file. We do this in app/etc/di.xml
by replacing this preference:
<preference for="Magento\Framework\DB\LoggerInterface" type="Magento\Framework\DB\Logger\Quiet"/>
With this:
<preference for="Magento\Framework\DB\LoggerInterface" type="Magento\Framework\DB\Logger\File"/> <type name="Magento\Framework\DB\Logger\File"> <arguments> <argument name="logAllQueries" xsi:type="boolean">true</argument> </arguments> </type>
As you can see we’re changing the class used to implement the LoggerInterface
to a built-in one which logs to a file instead. As we do this we are also setting an argument for the Magento\Framework\DB\Logger\File
class of logAllQueries
to make sure it is set up correctly for our needs.
The default location of the log file is var/debug/db.log
so consult that file to see what queries are being run and when, for example:
## 2017-11-04 11:20:50 ## 24671 ## QUERY SQL: SELECT `core_config_data`.`value` FROM `core_config_data` WHERE (path = 'web/unsecure/base_url' and scope_id = 0) LIMIT 1 AFF: 1 TIME: 0.0001 ## 2017-11-04 11:20:50 ## 24671 ## QUERY SQL: SELECT `core_config_data`.`value` FROM `core_config_data` WHERE (path = 'web/secure/base_url' and scope_id = 0) LIMIT 1 AFF: 1 TIME: 0.0002
Obviously this will generate a huge amount of data very quickly, as every page load in Magento 2 executes hundreds of queries, so remember to disable this once you’ve finished using the output. Left running the log file would quickly reach gigabytes and bring your server to it’s knees!
Armed with knowledge of the queries being run behind your site you can now troubleshoot problems more easily and make performance tweaks to any of your custom modules.