Reading list Switch to dark mode

    How to debug slow MYSQL queries in Magento 2

    Updated 20 November 2023

    This article talks about a couple of ways in which we can debug MYSQL queries(slow queries, waiting for a long process) for Magento 2 and find the adaptive solution.

    First, Magento by default provides us with its inbuilt mysql query logger functionality.

    To enable the same we can run the following command

    php bin/magento dev:query-log:enable

    This command will write database activity logs to the <magento_root>/var/debug/db.log file

    screenshot-nimbusweb.me-2023.05.23-15_10_55

    To disable the MySQL query logger use the following command

    Searching for an experienced
    Magento 2 Company ?
    Find out More
    php bin/magento dev:query-log:disable

    We can also log limited data, for that please check php bin/magento dev:query-log:enable –help command to know further options.

    screenshot-nimbusweb.me-2023.05.23-17_44_42

    Second, We can use the MYSQL Command Line tool to debug slow queries in order to do so please follow the below

    • Log into your MYSQL by running the following command
      mysql -u root -p
    • Select the database
      use magento_database;
    • Now, you need to enable the slow query log
      SET GLOBAL slow_query_log = 'ON';
    • You can also set query running time threshold in seconds
      SET GLOBAL long_query_time = X;
    • Now, you can check if the above settings are properly done or not, run the following command
      show variables like '%slow%’;
    screenshot-nimbusweb.me-2023.05.23-17_47_27

    Here you can also check your log file path

    Third, is Magento 2 DB Profiler

    The Magento 2 database profiler displays all queries implemented on a page, including the time for each query and what parameters were applied.

    We will be outputting the following options for our example here

    • Total time (displays the total amount of time to run all queries on the page)
    • SQL (displays all SQL queries; the row header displays the count of queries)
    • Query Params (displays the parameters for each SQL query)

    In order to enable this you need to modify /app/etc/env.php with the following code under db->connection->default.

    'profiler' => [
        'class' => '\Magento\Framework\DB\Profiler',
        'enabled' => true,
    ],

    final file content

    'db' => [
        'table_prefix' => '',
        'connection' => [
            'default' => [
                'host' => 'localhost',
                'dbname' => 'magento',
                'username' => 'root',
                'password' => 'password',
                'model' => 'mysql4',
                'engine' => 'innodb',
                'initStatements' => 'SET NAMES utf8;',
                'active' => '1',
                'driver_options' => [
                    1014 => false
                ],
                'profiler' => [
                    'class' => '\Magento\Framework\DB\Profiler',
                    'enabled' => true,
                ]
            ]
        ]
    ],

    Now, you need to configure where to see the output and for that follow below

    open /pub/index.php and add following code after $bootstrap->run($app); line

    /** @var \Magento\Framework\App\ResourceConnection $res */
    $res = \Magento\Framework\App\ObjectManager::getInstance()->get('Magento\Framework\App\ResourceConnection');
    /** @var Magento\Framework\DB\Profiler $profiler */
    $profiler = $res->getConnection('read')->getProfiler();
    echo "<table cellpadding='0' cellspacing='0' border='1'>";
    echo "<tr>";
    echo "<th>Time <br/>[Total Time: ".$profiler->getTotalElapsedSecs()." secs]</th>";
    echo "<th>SQL [Total: ".$profiler->getTotalNumQueries()." queries]</th>";
    echo "<th>Query Params</th>";
    echo "</tr>";
    foreach ($profiler->getQueryProfiles() as $query) {
        /** @var Zend_Db_Profiler_Query $query*/
        echo '<tr>';
        echo '<td>', number_format(1000 * $query->getElapsedSecs(), 2), 'ms', '</td>';
        echo '<td>', $query->getQuery(), '</td>';
        echo '<td>', json_encode($query->getQueryParams()), '</td>';
        echo '</tr>';
    }
    echo "</table>";

    Result will be in bottom of the page

    screenshot-192.168.15.154-2023.05.23-17_32_40

    Lastly, there is an amazing tool named Quick Development Toolbar (GitHub)

    This repository will add a floating toolbar on top of your Magento 2 pages.

    screenshot-192.168.15.154-2023.05.23-16_31_42

    When you go to the Queries tab you will see that all queries for that page are listed here with query time and other details

    screenshot-192.168.15.154-2023.05.23-17_09_53
    screenshot-192.168.15.154-2023.05.23-17_10_17

    Hope this helps you in debugging and writing better code.

    . . .

    Leave a Comment

    Your email address will not be published. Required fields are marked*


    Be the first to comment.

    Back to Top

    Message Sent!

    If you have more details or questions, you can reply to the received confirmation email.

    Back to Home