Message-ID: <5be4578b-cfdb-4129-b1d0-58d4d5e0bd84@tantorlabs.com>
Date: Thu, 9 Jan 2025 17:08:58 +0300
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Sample rate added to pg_stat_statements
To: Sami Imseih <samimseih@gmail.com>
Cc: "Andrey M. Borodin" <x4mmm@yandex-team.ru>,
 Alexander Korotkov <aekorotkov@gmail.com>,
 Michael Paquier <michael@paquier.xyz>,
 Greg Sabino Mullane <htamfids@gmail.com>,
 pgsql-hackers <pgsql-hackers@postgresql.org>
References: <fe99e0ca-e564-480e-b865-5f0cee30bc60@tantorlabs.com>
 <6707FCA9-FD1A-4609-A1A6-142456C14E0C@yandex-team.ru>
 <CAKAnmmLD8C-Y6CVF74Ccup8h9YpzBYzSvWWRG_Qf8+5YEMh4-g@mail.gmail.com>
 <Zz0MFPq1s4WFxxpL@paquier.xyz>
 <CAPpHfdsTKAQqC3A48-MGQhrhfEamXZPb64w=utk7thQcOMNr7Q@mail.gmail.com>
 <320a31e6-4c91-438b-ab17-8a1d72384727@tantorlabs.com>
 <f202ed0f-28c3-45aa-884c-db0a78322342@tantorlabs.com>
 <fb3df2e0-2cd4-49ea-bfda-148bc43d613d@tantorlabs.com>
 <52e5e3aa-066a-4daf-8d65-77c34a2f0d98@tantorlabs.com>
 <6482A576-4465-4E46-A42C-F1767E8DDE30@yandex-team.ru>
 <CAA5RZ0s=JH4owVr=Tfr4DYpFnBrYJjKoq2d7NqdkBQKY5whiRQ@mail.gmail.com>
 <20994d10-ab44-4c6b-91bc-c4675cd2e58e@tantorlabs.com>
 <9D8A5BC8-A3A5-4DA9-9382-F30D343247D1@yandex-team.ru>
 <CAA5RZ0sBsajHdE_HPoJ-C5rp8QmfArd6a6yJo6bDMVpyW25E+Q@mail.gmail.com>
 <6cf6270b-e8fa-4d99-b74a-17e781cdf309@tantorlabs.com>
 <9af7cb4e-d191-421f-85a2-22d8c406b47a@tantorlabs.com>
 <CAA5RZ0v+o0cmebUALqxf5ocamgztiGcJqb14HZMFTDWHh855fg@mail.gmail.com>
Content-Language: en-US
From: Ilia Evdokimov <ilya.evdokimov@tantorlabs.com>
In-Reply-To: <CAA5RZ0v+o0cmebUALqxf5ocamgztiGcJqb14HZMFTDWHh855fg@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/5be4578b-cfdb-4129-b1d0-58d4d5e0bd84%40tantorlabs.com>
Precedence: bulk


On 09.01.2025 05:29, Sami Imseih wrote:
>> Unfortunately, these changes do not achieve the intended sampling goal.
>> I looked into this more deeply: while the sampled-out queries do not
>> appear in pg_stat_statements, an entry is still allocated in the hash
>> table after normalization, which, in my view, should not happen when
>> sampling is in effect. Therefore, patch v9 is unlikely to meet our needs.
> pg_stat_statements creates entries as "sticky" initially to give them
> more time to stay in the hash before the first execution completes.
> It's not perfect, but it works for the majority of cases. So, what you
> are observing is how pg_stat_statements currently works.
>
> If an entry is popular enough, we will need it anyways ( even
> with the proposed sampling ). An entry that's not popular will
> eventually be aged out.
>
>   From my understanding, what the proposed sampling will do is
> to reduce the overhead  of incrementing counters of popular entries,
> because of the spinlock to update the counters. This is particularly
> the case with high concurrency on large machines ( high cpu count ),
> and especially when there is a small set of popular entries.
> IMO, This patch should also have a benchmark that proves
> that a user can benefit with sampling in those types of
> workloads.


Ah, so patch version 9 might be the best fit to achieve this. I’ll need 
to benchmark it on a large, high-concurrency machine then.

--
Best regards,
Ilia Evdokimov,
Tantor Labs LLC.