Finding slow queries #

This page is for database administrators who look for the SQL that makes an Exponential page slow. It answers: which switches show the statements of a request, how to limit them to the slow ones, how to count statements per request over time, what Oracle adds, and how this fits with your engine's own tools.

Contents #

1. First: is it the database?
2. Statements in the debug output
3. EXPLAIN on MySQL and MariaDB
4. Unbalanced transactions
5. The SQL profiler
6. Oracle's slow query log
7. The engine's own tools
8. Checklist

First: is it the database? #

Most requests are answered by caches before they reach the database (Performance and scaling). Measure first:

php bin/php/console exp:benchmark --help

exp:benchmark measures page timings over HTTP, compares servers and runs kernel probes, among them the database round trip; --save and --baseline catch a slowdown between two runs (Benchmark).

Statements in the debug output #

Turn the debug output on for your own address only, never for everyone:

# settings/override/site.ini.append.php
[DebugSettings]
DebugOutput=enabled
DebugByIP=enabled
DebugIPList[]=203.0.113.10

[DatabaseSettings]
SQLOutput=enabled
# only statements slower than this many milliseconds (0: all)
SlowQueriesOutput=50

Clear the INI cache (php bin/php/ezcache.php --clear-tag=ini --allow-root-user), restart Velocity if it serves the site, and load the page: the debug output lists the statements with their times, on every engine (12.4 Debug output). Switch it off again when you are done; the output shows paths, queries and settings.

EXPLAIN on MySQL and MariaDB #

[DatabaseSettings]
SQLOutput=enabled
QueryAnalysisOutput=enabled

adds MySQL's EXPLAIN of each statement to the debug output. It works on MySQL and MariaDB only. On other engines copy a statement from the debug output and run EXPLAIN (PostgreSQL, SQLite: EXPLAIN QUERY PLAN) or EXPLAIN PLAN FOR (Oracle) yourself.

Unbalanced transactions #

[DatabaseSettings] DebugTransactions=enabled records a stack trace for every begin and commit, to find a transaction that was opened and never closed. Use it only while you investigate.

The SQL profiler #

eZDBInterface::profileSQL() writes one line per request to var/tmp/sql_profile.log: statements, SELECTs, distinct SELECTs, exact repeats, time in the database and in the repeats. It is off unless var/tmp/sql_profile.on exists; the switch in Setup > System information (box Database queries, policy setup/managecache) creates and removes that file. In 6.0.15 only the MySQL driver (eZMySQLiDB) calls it, so it counts statements on MySQL and MariaDB.

It counts the statements that reached the database: with the query cache on, a cached answer is not in it, which makes it the tool to see what the cache leaves (The SQL query cache). Many exact repeats on one page point at code that runs the same query in a loop.

Oracle's slow query log #

The ezoracle extension writes slow statements to var/log/oracle-slow.log when ezoracle.ini [LogSettings] SlowQueryThreshold is set; MaskLiterals masks string literals so the log holds no content. [TraceSettings] tags each session in V$SESSION with siteaccess, module and view, so AWR and ASH reports show which page ran a statement (Oracle: persistent connections, DRCP and tuning). ext:ezoracle:report --top-sql lists the heaviest statements from V$SQL.

The engine's own tools #

The slow query log of MySQL and MariaDB, pg_stat_statements and log_min_duration_statement on PostgreSQL, the profiler of MongoDB and Oracle's AWR are features of the servers, not of Exponential. They see every statement of every process (web, Velocity, cron) without touching the site's settings, and are the right tools for production. Use the debug output to connect a statement to a page.

Checklist #

• To do: Measured with exp:benchmark before and after a change.
• To do: Debug output limited to your address and switched off afterwards.
• To do: The engine's own slow query log enabled with a sensible threshold.
• To do: Repeated statements reported to whoever maintains the extension or template.

Related pages #

• Performance and scaling
• Benchmarking a hosted site
• Slow site runbook
• Free documentation: this page is free to copy, translate and improve under the GNU FDL 1.3.