Thread: Speed-up shared buffers prewarming
Hi hackers,
It is well known fact that queries using sequential scan can not be used to prewarm cache, because them are using ring buffer
even if shared buffers are almost empty.
I have searched hackers archive but failed to find any discussion about it.
What are the drawbacks of using free buffers even with BAM_BULKREAD strategy?
I mean the following trivial patch:
diff --git a/src/backend/storage/buffer/freelist.c b/src/backend/storage/buffer/freelist.c
index 6be80476db..243335d0e4 100644
--- a/src/backend/storage/buffer/freelist.c
+++ b/src/backend/storage/buffer/freelist.c
@@ -208,8 +208,15 @@ StrategyGetBuffer(BufferAccessStrategy strategy, uint32 *buf_state)
/*
* If given a strategy object, see whether it can select a buffer. We
* assume strategy objects don't need buffer_strategy_lock.
*/
- if (strategy != NULL)
+ if (strategy != NULL && StrategyControl->firstFreeBuffer < 0)
{
buf = GetBufferFromRing(strategy, buf_state);
if (buf != NULL)
So if there are free buffers, then use normal buffer allocation instead of GetBufferFromRing.
Right now it is necessary to use pg_prewarm extension in order to prewarm buffers.
But it is not convenient (you need to manually locate and prewarm all indexes and TOAST relation) and not always possible
(client may just not notice that server is restarted).
One potential problem which I can imagine is sync scan: when several seqscans of the same table are using the same pages from ring buffer.
But synchronization of concurrent sync scans is naturally achieved: backed which is moving first is moving slowly than catching up backends
which do not need to read something from the disk. It seems to me that if we allow to use all shared buffers instead of small ring buffer,
then concurrent seqscans will have more chances to reuse cached pages. I have performed multiple tests with spawning multiple parallel seqscans
after postgres restart and didn't observe any problems or degradation of performance comparing with master.
Also ring buffer is used not only for seqscan. There are several places in Postgres core and extension (for example pgvector) where BAM_BULKREAD strategy is used
also for index scan.
Certainly OS file cache should prevent redundant disk reads.
But it seems to be better in any case to use free memory inside Postgres process rather than rely on OS cache and perform syscalls to copy data from this cache.
Definitely it is possible that seqscan limited by ring buffer will be completed faster than seqscan filling all shared buffers especially if
size of shared buffers is large enough. OS will need some extra time to commit memory and may be swap out other regions to find enough physical
memory for shared buffers. But if data set fits in memory, then subsequent queries will be much faster. And it is quite common for modern servers
that size of shared buffers is comparable with database size.
I will be pleased you point me at some drawbacks of such approach.
Otherwise I can propose patch for commitfest.
It is well known fact that queries using sequential scan can not be used to prewarm cache, because them are using ring buffer
even if shared buffers are almost empty.
I have searched hackers archive but failed to find any discussion about it.
What are the drawbacks of using free buffers even with BAM_BULKREAD strategy?
I mean the following trivial patch:
diff --git a/src/backend/storage/buffer/freelist.c b/src/backend/storage/buffer/freelist.c
index 6be80476db..243335d0e4 100644
--- a/src/backend/storage/buffer/freelist.c
+++ b/src/backend/storage/buffer/freelist.c
@@ -208,8 +208,15 @@ StrategyGetBuffer(BufferAccessStrategy strategy, uint32 *buf_state)
/*
* If given a strategy object, see whether it can select a buffer. We
* assume strategy objects don't need buffer_strategy_lock.
*/
- if (strategy != NULL)
+ if (strategy != NULL && StrategyControl->firstFreeBuffer < 0)
{
buf = GetBufferFromRing(strategy, buf_state);
if (buf != NULL)
So if there are free buffers, then use normal buffer allocation instead of GetBufferFromRing.
Right now it is necessary to use pg_prewarm extension in order to prewarm buffers.
But it is not convenient (you need to manually locate and prewarm all indexes and TOAST relation) and not always possible
(client may just not notice that server is restarted).
One potential problem which I can imagine is sync scan: when several seqscans of the same table are using the same pages from ring buffer.
But synchronization of concurrent sync scans is naturally achieved: backed which is moving first is moving slowly than catching up backends
which do not need to read something from the disk. It seems to me that if we allow to use all shared buffers instead of small ring buffer,
then concurrent seqscans will have more chances to reuse cached pages. I have performed multiple tests with spawning multiple parallel seqscans
after postgres restart and didn't observe any problems or degradation of performance comparing with master.
Also ring buffer is used not only for seqscan. There are several places in Postgres core and extension (for example pgvector) where BAM_BULKREAD strategy is used
also for index scan.
Certainly OS file cache should prevent redundant disk reads.
But it seems to be better in any case to use free memory inside Postgres process rather than rely on OS cache and perform syscalls to copy data from this cache.
Definitely it is possible that seqscan limited by ring buffer will be completed faster than seqscan filling all shared buffers especially if
size of shared buffers is large enough. OS will need some extra time to commit memory and may be swap out other regions to find enough physical
memory for shared buffers. But if data set fits in memory, then subsequent queries will be much faster. And it is quite common for modern servers
that size of shared buffers is comparable with database size.
I will be pleased you point me at some drawbacks of such approach.
Otherwise I can propose patch for commitfest.
On Wed, 15 Mar 2023 at 21:38, Konstantin Knizhnik <knizhnik@garret.ru> wrote:
>
> Hi hackers,
>
> It is well known fact that queries using sequential scan can not be used to prewarm cache, because them are using
ringbuffer
> even if shared buffers are almost empty.
> I have searched hackers archive but failed to find any discussion about it.
> What are the drawbacks of using free buffers even with BAM_BULKREAD strategy?
> I mean the following trivial patch:
>
> diff --git a/src/backend/storage/buffer/freelist.c b/src/backend/storage/buffer/freelist.c
> index 6be80476db..243335d0e4 100644
> --- a/src/backend/storage/buffer/freelist.c
> +++ b/src/backend/storage/buffer/freelist.c
> @@ -208,8 +208,15 @@ StrategyGetBuffer(BufferAccessStrategy strategy, uint32 *buf_state)
> /*
> * If given a strategy object, see whether it can select a buffer. We
> * assume strategy objects don't need buffer_strategy_lock.
> */
> - if (strategy != NULL)
> + if (strategy != NULL && StrategyControl->firstFreeBuffer < 0)
> {
> buf = GetBufferFromRing(strategy, buf_state);
> if (buf != NULL)
>
> So if there are free buffers, then use normal buffer allocation instead of GetBufferFromRing.
Yes. As seen in [1], ring buffers aren't all that great in some cases,
and I think this is one. Buffer allocation should always make use of
the available resources, so that it doesn't take O(N/ring_size) scans
on a table to fill the buffers if that seqscan is the only workload of
the system.
> Definitely it is possible that seqscan limited by ring buffer will be completed faster than seqscan filling all
sharedbuffers especially if
> size of shared buffers is large enough. OS will need some extra time to commit memory and may be swap out other
regionsto find enough physical
> memory for shared buffers.
Not just that, but it is also possible that by ignoring the ring we're
going to hit pages that aren't yet in the CPU caches and we would thus
need to fetch the data from RAM (or from another NUMA node), which
could be more expensive than reading it from a local kernel's file
cache and writing it to the local cache lines.
Anyway, I'm all for this change - I don't think we need to be careful
about trashing other workload's buffers if the buffer is useless for
literally every workload.
Kind regards,
Matthias van de Meent
[1] https://www.postgresql.org/message-id/flat/20230111182720.ejifsclfwymw2reb%40awork3.anarazel.de
On Wed, Mar 15, 2023 at 4:38 PM Konstantin Knizhnik <knizhnik@garret.ru> wrote:
> It is well known fact that queries using sequential scan can not be used to prewarm cache, because them are using
ringbuffer
> even if shared buffers are almost empty.
> I have searched hackers archive but failed to find any discussion about it.
> What are the drawbacks of using free buffers even with BAM_BULKREAD strategy?
> I mean the following trivial patch:
It has been brought up at least in 2014 [1] and 2020 [2]
The part relevant to your patch is in the thread from 2020 here [3].
This quote in particular:
> a) Don't evict buffers when falling off the ringbuffer as long as
> there unused buffers on the freelist. Possibly just set their
> usagecount to zero as long that is the case.
> diff --git a/src/backend/storage/buffer/freelist.c b/src/backend/storage/buffer/freelist.c
> index 6be80476db..243335d0e4 100644
> --- a/src/backend/storage/buffer/freelist.c
> +++ b/src/backend/storage/buffer/freelist.c
> @@ -208,8 +208,15 @@ StrategyGetBuffer(BufferAccessStrategy strategy, uint32 *buf_state)
> /*
> * If given a strategy object, see whether it can select a buffer. We
> * assume strategy objects don't need buffer_strategy_lock.
> */
> - if (strategy != NULL)
> + if (strategy != NULL && StrategyControl->firstFreeBuffer < 0)
> {
> buf = GetBufferFromRing(strategy, buf_state);
> if (buf != NULL)
>
> So if there are free buffers, then use normal buffer allocation instead of GetBufferFromRing.
Similar to what you did.
- Melanie
[1] https://www.postgresql.org/message-id/flat/CAJRYxuL98fE_QN7McnCM5HUo8p9ceNJw%3D20GoN5NVdZdueJFqg%40mail.gmail.com
[2] https://www.postgresql.org/message-id/flat/20200206040026.trjzsmdsbl4gu2b6%40alap3.anarazel.de
[5] https://www.postgresql.org/message-id/20200206040026.trjzsmdsbl4gu2b6%40alap3.anarazel.de
Hi,
On 3/15/23 10:40 PM, Matthias van de Meent wrote:
> On Wed, 15 Mar 2023 at 21:38, Konstantin Knizhnik <knizhnik@garret.ru> wrote:
>>
>> Hi hackers,
>>
>> It is well known fact that queries using sequential scan can not be used to prewarm cache, because them are using
ringbuffer
>> even if shared buffers are almost empty.
>> I have searched hackers archive but failed to find any discussion about it.
>> What are the drawbacks of using free buffers even with BAM_BULKREAD strategy?
>> I mean the following trivial patch:
>>
>> diff --git a/src/backend/storage/buffer/freelist.c b/src/backend/storage/buffer/freelist.c
>> index 6be80476db..243335d0e4 100644
>> --- a/src/backend/storage/buffer/freelist.c
>> +++ b/src/backend/storage/buffer/freelist.c
>> @@ -208,8 +208,15 @@ StrategyGetBuffer(BufferAccessStrategy strategy, uint32 *buf_state)
>> /*
>> * If given a strategy object, see whether it can select a buffer. We
>> * assume strategy objects don't need buffer_strategy_lock.
>> */
>> - if (strategy != NULL)
>> + if (strategy != NULL && StrategyControl->firstFreeBuffer < 0)
>> {
>> buf = GetBufferFromRing(strategy, buf_state);
>> if (buf != NULL)
>>
>> So if there are free buffers, then use normal buffer allocation instead of GetBufferFromRing.
>
> Yes. As seen in [1], ring buffers aren't all that great in some cases,
> and I think this is one. Buffer allocation should always make use of
> the available resources, so that it doesn't take O(N/ring_size) scans
> on a table to fill the buffers if that seqscan is the only workload of
> the system.
Agree but then what do you think about also paying special attention to those buffers when eviction needs to be done?
Those buffers are "usually" needed briefly, so something like being able to distinguish them and be more aggressive
regardingtheir eviction.
Regards,
--
Bertrand Drouvot
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com