public inbox for [email protected]
help / color / mirror / Atom feedFrom: Sami Imseih <[email protected]>
To: Ilia Evdokimov <[email protected]>
Cc: Alexander Korotkov <[email protected]>
Cc: Michael Paquier <[email protected]>
Cc: Greg Sabino Mullane <[email protected]>
Cc: Andrey Borodin <[email protected]>
Cc: Alena Rybakina <[email protected]>
Cc: pgsql-hackers <[email protected]>
Subject: Re: Sample rate added to pg_stat_statements
Date: Wed, 19 Feb 2025 19:04:41 -0600
Message-ID: <CAA5RZ0vOjQDC=OvUHuKMPYc8UT_peKt_yWpVaEqiP-JG2yqB9w@mail.gmail.com> (raw)
In-Reply-To: <CAA5RZ0trJoVqGmELHOc=G2SxHQHKdTb1aAvzf3vHdfVP8UH+Xg@mail.gmail.com>
References: <[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
<CAA5RZ0s+khQ_u84qS_cAHzxVQQHK_UmWXxRBWTTex7zW1250tQ@mail.gmail.com>
<[email protected]>
<CAA5RZ0vxn_UiUkUK5SdngObLZzw40RhafB7SHydxzBw2_xjjiA@mail.gmail.com>
<[email protected]>
<[email protected]>
<[email protected]>
<CAA5RZ0tJjrX6YiQVDZCOA_BDMuhO-po_T8a3PmjxEEH_iL-qMg@mail.gmail.com>
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
<CAA5RZ0vz+Abyck0dn0fHJ5nTfJU7wdLRxfUj481Ga-BGNY0Txg@mail.gmail.com>
<[email protected]>
<CAA5RZ0trJoVqGmELHOc=G2SxHQHKdTb1aAvzf3vHdfVP8UH+Xg@mail.gmail.com>
> But instead of blindly reducing the frequency via PRNG, we can take a more thoughtful approach with threshold by execute time:
> Find the most frequent query by column 'calls' in pg_stat_statements;
> In this query look at info about execution time: min_exec_time, max_exec_time, etc;
> Gradually increase the threshold from min_exec_time to max_exec_time, limiting the tracking of this query.
> Monitor performance: once the bottleneck is resolved, stop at the current threshold value.
This approach allows us to:
> Eliminate the spin-lock bottleneck;
> Preserve data about slow queries, which may be critical for performance analysis;
> Reduce the load on the most frequent queries causing contention, instead of uniformly reducing the frequency for all queries.
In my opinion, sample rate is a better fit for pg_stat_statements,
since the queries that
you care about the most are usually the most frequently executed. Sampling them
will still provide enough good data without the risk of not capturing
statistics about
them at all.
Longer running queries will also likely be the least frequent, so they
are already not likely
contributing to the spinlock contention. Also, the least frequent
queries will likely be aged
out faster, so pg_stat_statements was never really a good candidate to
track those anyways;
slow query logging with log_min_duration_statement is a better way to
ensure you capture
the data.
Maybe others may have a different opinion?
--
Sami
view thread (58+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: Sample rate added to pg_stat_statements
In-Reply-To: <CAA5RZ0vOjQDC=OvUHuKMPYc8UT_peKt_yWpVaEqiP-JG2yqB9w@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox