In Magento 2.x as well as Magento 1.x there is an option to enable database logging. It allows to profile all queries in the system and perform optimization if necessary. In this article, I will show you how to enable database logging for MySQL adapter in Magento 2.

In my previous article, I wrote about separate database connections which can be configured for single Magento 2 instance. It might be helpful to check what is going on with database queries.

From another point, if it happens a Magento 2 website or some of the customizations have slow or unnecessary database connections. It might be worth checking it before, after and during a lifecycle of a website.

Overview

The Magento\Framework\DB\Adapter\Pdo\Mysql class uses Magento\Framework\DB\LoggerInterface interface. The LoggerInterface is passed via Mysql::__construct() method.

[php]
class Mysql extends \Zend_Db_Adapter_Pdo_Mysql implements AdapterInterface
{
public function __construct(
StringUtils $string,
DateTime $dateTime,
LoggerInterface $logger,
SelectFactory $selectFactory,
array $config = []
) {
//…
}
}
[/php]

Later $logger instance is used inside every Mysql class method and triggered before and after an operation, as shown below:

[php]
$this->logger->startTimer();
parent::_connect();
$this->logger->logStats(LoggerInterface::TYPE_CONNECT, ”);
[/php]

Database Log Classes

Magento Framework provides 2 implementations of the Magento\Framework\DB\LoggerInterface interface. The Magento\Framework\DB\Logger\Quiet has empty methods inside. It means that any time instance of the Quiet class is used nothing will happen.

It is a default preference for the LoggerInterface interface declared in the magento/app/etc/di.xml configuration file.

[xml]
<preference for="Magento\Framework\DB\LoggerInterface" type="Magento\Framework\DB\Logger\Quiet"/>
[/xml]

The Magento\Framework\DB\Logger\File class actually do the work. It creates a log file and dumps database queries into it. We, as developers, can specify the following information while working with File class:

• Name of a log file where all queries and its timings will be stored
• Flag to log all database queries performed
• Minimum query time for it to be logged. It is useful when “Log all queries” is not set and the log file will have only slow operations
• Flag to log call stack. It is useful for identifying the place in a code triggers query which might be improved.

[php]
class File extends LoggerAbstract
{
public function __construct(
Filesystem $filesystem,
$debugFile = ‘debug/db.log’,
$logAllQueries = false,
$logQueryTime = 0.05,
$logCallStack = false
) {
//…
}
}
[/php]

Enabling File Logger

In order to enable logging into file the magento/app/etc/di.xml configuration file should be updated. The new preference should be added for the Magento\Framework\DB\LoggerInterface interface.

[xml]
<preference for="Magento\Framework\DB\LoggerInterface" type="Magento\Framework\DB\Logger\File"/>
[/xml]

In addition to this, you may configure the File class with arguments.

[xml]
<type name="Magento\Framework\DB\Logger\File">
<arguments>
<argument name="debugFile" xsi:type="string">debug/my-custom.log</argument>
<argument name="logAllQueries" xsi:type="boolean">false</argument>
<argument name="logQueryTime" xsi:type="string">0.1</argument>
<argument name="logCallStack" xsi:type="boolean">true</argument>
</arguments>
</type>
[/xml]

With the configuration specified we have the following:

• Log file name is set to my-custom.log
• Log query time is set to 0.1 seconds
• Call stack is logged together with a query

With the Magento\Framework\DB\LoggerInterface interface you may create new logger which stores everything into an alternative storage. Such storage might be a database or network file system.

Alternative?

In the previous section, I showed you a proper way of enabling database logging. I recommend you to enable logging via di.xmlconfiguration file only in 1 case: for a production environment. Be careful when enabling database logging as it takes additional resources to write into the file. In case a debug/db.log file becomes bigger time required to write further logs increase.

In my day-to-day work, I prefer to go directly into the Magento\Framework\DB\Logger\File file and change the __contruct() argument values directly. It speeds up development since you don’t need to clear cache every time you want to change Log Query Time or debug vs non-debug call stack.

Outcome

In this article, we learned how to enable logging for database queries in Magento 2. Also, we reviewed existing logger classes from Magento Framework. The Magento\Framework\DB\Logger\File class allows configuring different behavior via arguments specified in a di.xml configuration file.