Which queries are slowing down a Drupal route, and how you isolate them in production

Which query is slowing down a Drupal page, and how do you find it?

You find it only if something wrote the page name next to the query while the request was running. Afterwards it cannot be reconstructed: the database keeps the slow queries, but to it they are statements that arrived over a connection, not pages of a site.

Native Observability is a Drupal module that records from the inside what happens during every request. Its Database Observer submodule does one thing: when a query goes over a duration threshold, it stores one row holding two pieces of information side by side, the query and the page that ran it.

This page covers three things: what you find inside that row, how far you can trust what you read, and where to start. How much the module slows the site down is a different question, and the measured answer is in the article about its cost. The same problem for outgoing HTTP calls to external services is covered in the article on the external service that slows down a route.

Why the tools you already have do not answer this

They answer nearby questions, and none of them is this one.

The MySQL slow query log knows queries and ignores pages. That is not a configuration limit, it is its definition: the manual describes it as the list of SQL statements that take longer than a given duration. Statements, not pages. The fields it writes are the database's own, that is duration, rows read and rows sent, and none of them says where the request came from: the database receives commands over a connection and does not know a site sits above it. You end up with the guilty query and go looking for it by hand inside the code.

Development tools look at the page you have open right now, not at yesterday's slow one that you cannot reproduce.

Two points worth straightening, because they circulate upside down. First: Devel states that it is safe on a production site, in those words, on its project page, as long as the development information permission goes only to developers. So the boundary is not development against production: it is between the request you are making and the history of everybody else's requests. Second: XHProf goes into a level of detail you will not find here, because it reconstructs the call tree, while Native Observability times services and stops there.

DB Performance comes closest, and the difference is sharp: it groups slow queries by shape and suggests indexes, but the word route and the word URL never appear on its project page. None of these tools writes down which page the query came from.

What you see, once the link is there

One row for every slow query, and nothing for the others.

In a measurement environment running Drupal 11, with the module's default settings, a page built to be slow answers in about 0.6 seconds. This is what stays in the table:

route_name        testing_native_observability.probe_slow_db
duration_ms       603.968
query_text        SELECT SLEEP(:s)
query_hash        0f907bc4...

Four pieces of information, and all of them earn their place:

• the route is the name Drupal gives a page from the code's point of view. It says which part of the site produced the query, even when the address changes on every visit;
• the duration is in milliseconds, so about 0.6 seconds;
• the query text sits next to the class and the method that ran it;
• the fingerprint is a code computed on the SQL text with the values stripped out. It lets you find the same query on other pages and on other days, even when the values change.

A second heavy query ran during the same request, and it does not appear. It took less than the default threshold of one hundred milliseconds, so it was not stored. The threshold behaves like a gate, not like a recorder.

The same rows are readable on screen at /admin/reports/native-observability/database-observer, with the access native observability database observer permission.

How far you can trust that table

It depends on two settings, and the second one deserves attention.

The first is the threshold, one hundred milliseconds by default: it decides how long a query has to take before it reaches the table. Lowering it does not bring more truth, it brings more rows, and below a few milliseconds you are recording the site working normally.

The second is the maximum number of rows per request, twenty by default. If one page produces more than twenty slow queries, the rest are dropped, and for now no screen tells you so. Twenty rows in the table are indistinguishable from twenty slow queries that really happened.

There is a way to notice, though, because the module also stores how many slow queries it saw, before dropping any. This query lines up the requests where it happened, comparing the stored rows (stored_rows) with the ones seen (slow_queries_seen):

SELECT request_id, route_name, COUNT(*) AS stored_rows,
       MAX(JSON_EXTRACT(payload, '$.request_summary.slow_query_count')) AS slow_queries_seen
FROM native_observability_database_query
GROUP BY request_id, route_name
HAVING slow_queries_seen > stored_rows;

In the measurement environment the cut is visible with the naked eye: a page running twenty five slow queries leaves exactly twenty rows in the table, and the five extra ones exist nowhere.

One last thing, so you do not go looking elsewhere: the Database Observer writes nothing to Drupal's log. What it saw lives in its own table and its own report, and nowhere else.

If another system is the one calling the slow page

The link survives through two HTTP headers. The caller puts its own identifier in X-Native-Observability-Parent-Request-Id, Drupal answers with X-Native-Observability-Request-Id, and the module keeps the tie between the two requests. From there you reach the slow queries of that page.

One warning, so nobody is disappointed: towards a domain other than yours, the identifier has to be set by the caller. The module adds it by itself only to calls that stay on the same site.

Where to start

Four steps, and after the first one the table starts filling up.

1. Install the submodule: drush en native_observability_database_observer -y. It pulls in the main module.
2. Give the access native observability database observer permission to whoever reads the report, and administer native observability database observer to whoever changes its settings.
3. Leave the threshold at one hundred milliseconds for a few days, then look at the report. If the table stays empty, the pages you are watching have no slow queries: that is a result, not a failure.
4. Before drawing conclusions, run the check query above. If it returns rows, you are reading only part of what happened.

What this demonstration does not prove

The slowness in the measurement environment is fake, and it is fair to know that.

• The delay is injected by a test page. The demonstration shows the mechanism, not that the module can diagnose real slowness on its own, which usually comes from a missing index or from a query repeated inside a loop.
• To show rows being dropped, the threshold was lowered on purpose. The case where real slowness produces more than twenty slow queries in a single request was not reproduced.
• No concurrency and no fix measured again: one request at a time, on a page that is slow by construction. The absolute timings are not to be read as a performance measurement.

Sources and references

All sources were consulted on 27 September 2026.

1. The Slow Query Log, MySQL 8.4 Reference Manual (opens in a new tab). Definition of the slow log and the fields it writes. Read from the rendered DOM: the site answers 403 to curl.
2. Devel, project page (opens in a new tab). The sentence about production safety and the permission to grant. Read from the rendered DOM.
3. WebProfiler, project page (opens in a new tab). The toolbar at the bottom of every page. Read from the rendered DOM.
4. XHProf, project page (opens in a new tab). Hierarchical profiler, breakdown by callers and callees. Read from the rendered DOM.
5. DB Performance, project page (opens in a new tab). Grouping by query shape and index suggestions, release 1.0.0-alpha2. Read from the rendered DOM.
6. Native Observability, project page (opens in a new tab). Requirements and submodules.
7. Module source, 2.0.x branch (opens in a new tab). The classes named on this page open from there: DatabaseQueryObserverSubscriber for the slow query count taken before the per request limit, DatabaseObserverSettingsForm for the constraints on the two settings, TraceSubscriber for the two HTTP headers.
8. The numbers on this page come from a Drupal 11 measurement environment I built myself, not from a public site: commands and results are reproduced above in full, and the conditions they hold under are in the section "What this demonstration does not prove". It is not a source you can open, it is a measurement you can repeat.