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.