db-profiler.md

layout	group	subgroup	title	menu_title	menu_order	menu_node	version	contributor_name	contributor_link	github_link
default	config-guide	13_DBProfiler	Configure the database profiler	Configure the database profiler	1	parent	2.0	Atish Goswami	http://atishgoswami.com	config-guide/db-profiler/db-profiler.md

About the database profiler

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

Step 1: Modify the deployment configuration

Modify <your Magento install dir>/app/etc/env.php to add the following reference to the [database profiler class]({{ site.mage2000url }}lib/internal/Magento/Framework/DB/Profiler.php){:target="_blank"}:

{% highlight php startinline=true %} 'profiler' => [ 'class' => '\Magento\Framework\DB\Profiler', 'enabled' => true, ], {% endhighlight %}

An example follows:

{% highlight php startinline=true %} 'db' => array ( 'table_prefix' => '', 'connection' => array ( 'default' => array ( 'host' => 'localhost', 'dbname' => 'magento', 'username' => 'magento', 'password' => 'magento', 'model' => 'mysql4', 'engine' => 'innodb', 'initStatements' => 'SET NAMES utf8;', 'active' => '1', 'profiler' => [ 'class' => '\Magento\Framework\DB\Profiler', 'enabled' => true, ], ), ), ), {% endhighlight %}

Step 2: Configure the output

Configure the output in your Magento application boostrap file; this might be <your Magento install dir>/index.php or it could be located in a web server virtual host configuration.

The following example displays results in a three-column table:

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

To configure the output, add the following after the $bootstrap->run($app); line in your bootstrap file:

{% highlight php startinline=true %} /** @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 "

"; echo ""; echo ""; echo ""; echo ""; echo ""; foreach ($profiler->getQueryProfiles() as $query) { /* @var Zend_Db_Profiler_Query $query*/ echo ''; echo ''; echo ''; echo ''; echo ''; } echo "

Time [Total Time: ".$profiler->getTotalElapsedSecs()." secs]	SQL [Total: ".$profiler->getTotalNumQueries()." queries]	Query Params
', number_format(1000 * $query->getElapsedSecs(), 2), 'ms', '	', $query->getQuery(), '	', json_encode($query->getQueryParams()), '

"; {% endhighlight %}

Step 3: View the results

Go to any page in your {% glossarytooltip 1a70d3ac-6bd9-475a-8937-5f80ca785c14 %}storefront{% endglossarytooltip %} or {% glossarytooltip 18b930cf-09cc-47c9-a5e5-905f86c43f81 %}Magento Admin{% endglossarytooltip %} to view the results. A sample follows:

{:width="800px"}