2091129 - Bugzilla::DB pings MySQL before every query (about 200 pings per show_bug.cgi request)

Status: RESOLVED FIXED

(bugzilla.mozilla.org :: General, enhancement)

Description

[David Lawrence [:dkl]]

Each query goes through one of the methods that Bugzilla::DB passes on to DBI (prepare, do, selectrow_array, selectall_arrayref, etc.). Each of these calls DBIx::Connector->dbh to get a database handle, and DBIx::Connector->dbh pings the server on every call unless a transaction is open. The Bugzilla::DB object is kept for the whole life of the process (state $main_dbh in connect_main), so this happens on every query of every request.

The result is a ping before nearly every query. That's an extra round trip to the database server each time, even though the connection was already checked a moment earlier.

Measured (Devel::NYTProf, local dev stack under hypnotoad, warm requests, anonymous user):

┌──────────────────────────────┬──────────────────┬──────────────────────┐
│ Page │ Queries executed │ DB pings per request │
├──────────────────────────────┼──────────────────┼──────────────────────┤
│ /show_bug.cgi?id=1 │ 102 │ 197 │
├──────────────────────────────┼──────────────────┼──────────────────────┤
│ /buglist.cgi?product=Firefox │ 31 │ 64 │
├──────────────────────────────┼──────────────────┼──────────────────────┤
│ /rest/bug/1 │ 28 │ 50 │
└──────────────────────────────┴──────────────────┴──────────────────────┘

Locally a ping costs about 0.01 ms because the database runs on the same host. In production, where the database is on another host, each ping is a full network round trip. With a round trip of about 0.5 ms, that would be roughly 100 ms per show_bug.cgi request spent on pings alone. This is an estimate and hasn't been measured in production. Pages with more queries (larger bugs, logged-in users, bigger buglists) do proportionally more pings.

Steps to reproduce

1. Start the dev stack with USE_NYTPROF=1 and HTTP_BACKEND=hypnotoad.
2. Request /show_bug.cgi?id=1 a few times.
3. Look at the call count for DBI::db::ping in the latest profile. It is about twice the number of DBI::st::execute calls.

Expected

One connection check per request is enough to catch connections that dropped while the worker was idle. Later queries in the same request can reuse the handle.

Proposed fix

Override Bugzilla::DB::dbh so it reuses the handle DBIx::Connector last returned, stored in Bugzilla->request_cache. It asks DBIx::Connector again, which is what sends the ping, only when:

• a new request starts (before_dispatch clears the request cache),
• the process has forked (pid change),
• the cached handle is no longer Active, or
• the last check is more than 5 seconds old, for long-running processes such as the job queue, which don't clear the request cache between queries.

With this change, each of the pages above makes 1 ping per request, and the BMO integration suite (t/bmo/.t, extensions//t/bmo/*.t) passes. A new unit test covers each re-check condition and reconnecting after a disconnect.

Trade-off

If the connection drops in the middle of a request, the next query in that request fails instead of reconnecting silently. That is the same failure you get today when the connection drops between a ping and the query that follows it.

Comment 1

[BMO Github Automation]

Authored by https://github.com/dklawren
https://github.com/mozilla/bmo/commit/6273f0ae4d44e868226c5ea4ed842ae361166517
[master] Bug 2091129 - Bugzilla::DB pings MySQL before every query (about 200 pings per show_bug.cgi request)

(via GitHub webhook, authenticated as shared-secret)