MySQL database query logging can be enabled/disabled from command line. This will help in debugging database queries.
Enable database query logging
The following CLI command enabled database/mysql query log:
bin/magento dev:query-log:enable
Flush cache
bin/magento cache:flush
After that, browse your Magento site.
The database queries logs are saved in the file var/debug/db.log.
Note:
The database query log file size should be checked. Thedb.logfile can take a lot of space as the file size goes on increasing based on the visit and activity on the Magento site.
By default, all the database queries are logged in the db.log file.
It will also include the call stack for the database query.
Here’s a sample from var/debug/db.log:
## 2022-10-26 16:15:37
## 56786 ## CONNECT
TIME: 0.0134
TRACE: #1 Magento\Framework\DB\Logger\File[Magento\Framework\DB\Logger\LoggerAbstract]#000000002566b81400000000201f09a9#->getStats('connect', '', array(), NULL) called at [vendor/magento/framework/DB/Logger/File.php:67]
#2 Magento\Framework\DB\Logger\File#000000002566b81400000000201f09a9#->logStats('connect', '', array(), NULL) called at [vendor/magento/framework/DB/Logger/LoggerProxy.php:152]
#3 Magento\Framework\DB\Logger\LoggerProxy#000000002566bad400000000201f09a9#->logStats('connect', '') called at [vendor/magento/framework/DB/Adapter/Pdo/Mysql.php:425]
#4 Magento\Framework\DB\Adapter\Pdo\Mysql\Interceptor[Magento\Framework\DB\Adapter\Pdo\Mysql]#000000002566baef00000000201f09a9#->_connect() called at [vendor/magento/zendframework1/library/Zend/Db/Adapter/Abstract.php:460]
#5 Magento\Framework\DB\Adapter\Pdo\Mysql\Interceptor[Zend_Db_Adapter_Abstract]#000000002566baef00000000201f09a9#->query('SELECT `store_we...', array()) called at [vendor/magento/zendframework1/library/Zend/Db/Adapter/Pdo/Abstract.php:238]
#6 Magento\Framework\DB\Adapter\Pdo\Mysql\Interceptor[Zend_Db_Adapter_Pdo_Abstract]#000000002566baef00000000201f09a9#->query('SELECT `store_we...', array()) called at [vendor/magento/framework/DB/Adapter/Pdo/Mysql.php:560]
#7 Magento\Framework\DB\Adapter\Pdo\Mysql\Interceptor[Magento\Framework\DB\Adapter\Pdo\Mysql]#000000002566baef00000000201f09a9#->_query('SELECT `store_we...', array()) called at [vendor/magento/framework/DB/Adapter/Pdo/Mysql.php:627]
#8 Magento\Framework\DB\Adapter\Pdo\Mysql\Interceptor[Magento\Framework\DB\Adapter\Pdo\Mysql]#000000002566baef00000000201f09a9#->query(&Magento\Framework\DB\Select#000000002566b82400000000201f09a9#, array()) called at [generated/code/Magento/Framework/DB/Adapter/Pdo/Mysql/Interceptor.php:95]
#9 Magento\Framework\DB\Adapter\Pdo\Mysql\Interceptor#000000002566baef00000000201f09a9#->query(&Magento\Framework\DB\Select#000000002566b82400000000201f09a9#, array()) called at [vendor/magento/zendframework1/library/Zend/Db/Adapter/Abstract.php:737]
#10 Magento\Framework\DB\Adapter\Pdo\Mysql\Interceptor[Zend_Db_Adapter_Abstract]#000000002566baef00000000201f09a9#->fetchAll(&Magento\Framework\DB\Select#000000002566b82400000000201f09a9#, array(), 2) called at [generated/code/Magento/Framework/DB/Adapter/Pdo/Mysql/Interceptor.php:1085]
#11 Magento\Framework\DB\Adapter\Pdo\Mysql\Interceptor#000000002566baef00000000201f09a9#->fetchAll(&Magento\Framework\DB\Select#000000002566b82400000000201f09a9#) called at [vendor/magento/module-store/App/Config/Source/RuntimeConfigSource.php:101]
#12 Magento\Store\App\Config\Source\RuntimeConfigSource#000000002566bb1600000000201f09a9#->getEntities('store_website', 'code') called at [vendor/magento/module-store/App/Config/Source/RuntimeConfigSource.php:61]
#13 Magento\Store\App\Config\Source\RuntimeConfigSource#000000002566bb1600000000201f09a9#->get('') called at [vendor/magento/framework/App/Config/ConfigSourceAggregated.php:42]
#14 Magento\Framework\App\Config\ConfigSourceAggregated#000000002566bb1c00000000201f09a9#->get('') called at [generated/code/Magento/Framework/App/Config/ConfigSourceAggregated/Proxy.php:95]
#15 Magento\Framework\App\Config\ConfigSourceAggregated\Proxy#000000002566bd0c00000000201f09a9#->get() called at [vendor/magento/module-store/App/Config/Type/Scopes.php:63]
...
...
Exclude Stack Trace in Database Log
This will exclude the stack trace information and will only include the SQL queries.
bin/magento dev:query-log:enable --include-call-stack=false
Sample output in var/debug/db.log
## 2022-10-26 16:28:47^M
## 2648 ## QUERY
SQL: SELECT `main_table`.* FROM `core_config_data` AS `main_table`
AFF: 39
TIME: 0.0077
## 2022-10-26 16:28:48^M
## 2648 ## QUERY
SQL: SELECT `store_website`.* FROM `store_website`
AFF: 2
TIME: 0.0009
## 2022-10-26 16:28:48^M
## 2648 ## QUERY
SQL: SELECT `store_group`.* FROM `store_group`
AFF: 2
TIME: 0.0018
## 2022-10-26 16:28:48^M
## 2648 ## QUERY
SQL: SELECT `store`.* FROM `store`
AFF: 2
TIME: 0.0017
Only Log Slow MySQL Queries
This will log database queries that are slow or take more than a particular time duration (in seconds).
For example, if we want to log database queries that take more than 1.000 seconds then we can set it as follows:
bin/magento dev:query-log:enable --include-all-queries=false --query-time-threshold=1.000
Enable Database Log Manual
bin/magento dev:query-log:enable --help
Description:
Enable DB query logging
Usage:
dev:query-log:enable [options]
Options:
--include-all-queries[=INCLUDE-ALL-QUERIES] Log all queries. [true|false] [default: "true"]
--query-time-threshold[=QUERY-TIME-THRESHOLD] Query time thresholds. [default: "0.001"]
--include-call-stack[=INCLUDE-CALL-STACK] Include call stack. [true|false] [default: "true"]
-h, --help Display this help message
-q, --quiet Do not output any message
-V, --version Display this application version
--ansi Force ANSI output
--no-ansi Disable ANSI output
-n, --no-interaction Do not ask any interactive question
-v|vv|vvv, --verbose Increase the verbosity of messages: 1 for normal output, 2 for more verbose output and 3 for debug
Disable database query logging
The following CLI command disabled database/mysql query log:
bin/magento dev:query-log:disable
Flush cache
bin/magento cache:flush
Disable Database Log Manual
bin/magento dev:query-log:disable --help
Description:
Disable DB query logging
Usage:
dev:query-log:disable
Options:
-h, --help Display this help message
-q, --quiet Do not output any message
-V, --version Display this application version
--ansi Force ANSI output
--no-ansi Disable ANSI output
-n, --no-interaction Do not ask any interactive question
-v|vv|vvv, --verbose Increase the verbosity of messages: 1 for normal output, 2 for more verbose output and 3 for debug
Hope this helps. Thanks.