Sep 18, 2016

Database Query Logging in Magento 2

Database Query Logging in Magento 2
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 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 customizations have slow or unnecessary database connections. It might be worth checking it before, after and during 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.

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

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

$this->logger->startTimer();
parent::_connect();
$this->logger->logStats(LoggerInterface::TYPE_CONNECT, '');

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.

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

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 log file will have only slow operations
  • Flag to log call stack. It is useful for identifying place in a code triggers query which might be improved.
class File extends LoggerAbstract
{
    public function __construct(
        Filesystem $filesystem,
        $debugFile = 'debug/db.log',
        $logAllQueries = false,
        $logQueryTime = 0.05,
        $logCallStack = false
    ) {
    //...
    }
}

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.

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

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

<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>

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 prevoius section I showed you a proper way of enabling database logging. I recommend you to enable logging via di.xml configuration file only in 1 case: for production environment. Be careful when enabling database logging as it takes additional resources to write into 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 to configure different behavior via arguments specified in a di.xml configuration file.

magento 2 db logger

Share Article

Related Articles

Comments

Next Article Previous Article

Join Devletter Community

Subscribe to FREE Magento 2 tips and tutorials email I send every week.

I will send you my 3 best tutorials with interesting content immediately.

LinkedIn Twitter Facebook