pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedMake recently inserted/updated records available in the buffer/cache
7+ messages / 4 participants
[nested] [flat]
* Make recently inserted/updated records available in the buffer/cache
@ 2019-12-02 17:03 Sachin Divekar <ssd532@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Sachin Divekar @ 2019-12-02 17:03 UTC (permalink / raw)
To: pgsql-performance
Hi,
I am looking for tuning my PG setup such that recently inserted or updated
record will be available in the buffer/cache (I believe they are same in
this context). Does PostgreSQL do it by default? If yes, just increasing
buffer size sufficient? What will be its effect on LRU performance -- I
guess there won't be any adverse effect?
My use case is that I am going to use it as a queue and performance will be
dependent upon whether the recently updated record is available in the
cache.
Thank you.
regards
Sachin
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Make recently inserted/updated records available in the buffer/cache
@ 2019-12-02 17:13 Hüseyin Demir <demirhuseyinn.94@gmail.com>
parent: Sachin Divekar <ssd532@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Hüseyin Demir @ 2019-12-02 17:13 UTC (permalink / raw)
To: Sachin Divekar <ssd532@gmail.com>; +Cc: pgsql-performance
Hi,
PostgreSQL decides which pages should be evicted from memory and written to
disk with the help of LRU algorithm. Thus, it depends on your query work
load. In OLTP systems, the algorithm is beneficial to business
requirements(almost :) )
It's hard to figure out that a configuration change will affect the
performance in a good way. Maybe, you can use PostgreSQL warmup features in
order to make sure the data pages that you need will be available in cache.
Because the results of LRU algorithm can vary depending on your business
and system workload.
Best Regards.
Sachin Divekar <ssd532@gmail.com>, 2 Ara 2019 Pzt, 20:03 tarihinde şunu
yazdı:
> Hi,
>
> I am looking for tuning my PG setup such that recently inserted or updated
> record will be available in the buffer/cache (I believe they are same in
> this context). Does PostgreSQL do it by default? If yes, just increasing
> buffer size sufficient? What will be its effect on LRU performance -- I
> guess there won't be any adverse effect?
>
> My use case is that I am going to use it as a queue and performance will
> be dependent upon whether the recently updated record is available in the
> cache.
>
> Thank you.
>
> regards
> Sachin
>
--
*Hüseyin DEMİR*
IT SOLUTION ARCHITECT
0534-614-72-06
demirhuseyinn.94@gmail.com
selfarrival.blogspot.com.tr
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Make recently inserted/updated records available in the buffer/cache
@ 2019-12-02 17:17 MichaelDBA <MichaelDBA@sqlexec.com>
parent: Hüseyin Demir <demirhuseyinn.94@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: MichaelDBA @ 2019-12-02 17:17 UTC (permalink / raw)
To: Hüseyin Demir <demirhuseyinn.94@gmail.com>; +Cc: Sachin Divekar <ssd532@gmail.com>; pgsql-performance
All updated/dirty records go through PG internal memory buffer,
shared_buffers. Make sure that is configured optimally. Use
pg_buffercache extension to set it correctly.
Regards,
Michael Vitale
Hüseyin Demir wrote on 12/2/2019 12:13 PM:
> I guess there won't be any adverse effect
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Make recently inserted/updated records available in the buffer/cache
@ 2019-12-03 17:29 Michael Lewis <mlewis@entrata.com>
parent: MichaelDBA <MichaelDBA@sqlexec.com>
0 siblings, 1 reply; 7+ messages in thread
From: Michael Lewis @ 2019-12-03 17:29 UTC (permalink / raw)
To: MichaelDBA <MichaelDBA@sqlexec.com>; +Cc: Hüseyin Demir <demirhuseyinn.94@gmail.com>; Sachin Divekar <ssd532@gmail.com>; pgsql-performance
"I am going to use it as a queue"
You may want to look at lowering fillfactor if this queue is going to have
frequent updates, and also make autovacuum/analyze much more aggressive
assuming many updates and deletes.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Make recently inserted/updated records available in the buffer/cache
@ 2019-12-03 17:32 MichaelDBA <MichaelDBA@sqlexec.com>
parent: Michael Lewis <mlewis@entrata.com>
0 siblings, 1 reply; 7+ messages in thread
From: MichaelDBA @ 2019-12-03 17:32 UTC (permalink / raw)
To: Michael Lewis <mlewis@entrata.com>; +Cc: Hüseyin Demir <demirhuseyinn.94@gmail.com>; Sachin Divekar <ssd532@gmail.com>; pgsql-performance
Yep, I concur completely! For tables treated like queues you gotta do
this stuff or deal with bloat and fragmented indexes.
Michael Lewis wrote on 12/3/2019 12:29 PM:
> "I am going to use it as a queue"
>
> You may want to look at lowering fillfactor if this queue is going to
> have frequent updates, and also make autovacuum/analyze much more
> aggressive assuming many updates and deletes.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Make recently inserted/updated records available in the buffer/cache
@ 2019-12-03 18:45 Sachin Divekar <ssd532@gmail.com>
parent: MichaelDBA <MichaelDBA@sqlexec.com>
0 siblings, 1 reply; 7+ messages in thread
From: Sachin Divekar @ 2019-12-03 18:45 UTC (permalink / raw)
To: MichaelDBA <MichaelDBA@sqlexec.com>; +Cc: Michael Lewis <mlewis@entrata.com>; Hüseyin Demir <demirhuseyinn.94@gmail.com>; pgsql-performance
Thank you, Michaels.
.
I didn't know about fillfactor and table bloat. Did some reading on those
topics. We will definitely need to tweak these settings.
I am also going to use SKIP LOCKED to _select for update_. Any suggestions
on tuning parameters for SKIP LOCKED?
Thanks
On Tue, Dec 3, 2019 at 11:02 PM MichaelDBA <MichaelDBA@sqlexec.com> wrote:
> Yep, I concur completely! For tables treated like queues you gotta do
> this stuff or deal with bloat and fragmented indexes.
>
> Michael Lewis wrote on 12/3/2019 12:29 PM:
> > "I am going to use it as a queue"
> >
> > You may want to look at lowering fillfactor if this queue is going to
> > have frequent updates, and also make autovacuum/analyze much more
> > aggressive assuming many updates and deletes.
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Make recently inserted/updated records available in the buffer/cache
@ 2019-12-03 18:58 Michael Lewis <mlewis@entrata.com>
parent: Sachin Divekar <ssd532@gmail.com>
0 siblings, 0 replies; 7+ messages in thread
From: Michael Lewis @ 2019-12-03 18:58 UTC (permalink / raw)
To: Sachin Divekar <ssd532@gmail.com>; +Cc: MichaelDBA <MichaelDBA@sqlexec.com>; Hüseyin Demir <demirhuseyinn.94@gmail.com>; pgsql-performance
On Tue, Dec 3, 2019 at 11:46 AM Sachin Divekar <ssd532@gmail.com> wrote:
> I am also going to use SKIP LOCKED to _select for update_. Any suggestions
> on tuning parameters for SKIP LOCKED?
>
I am not aware of any. Either you use it because it fits your need, or not.
Note- please don't top-post (reply and include all the previous
conversation below) on the Postgres mailing lists. Quote only the part(s)
you are responding to and reply there.
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2019-12-03 18:58 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-12-02 17:03 Make recently inserted/updated records available in the buffer/cache Sachin Divekar <ssd532@gmail.com>
2019-12-02 17:13 ` Hüseyin Demir <demirhuseyinn.94@gmail.com>
2019-12-02 17:17 ` MichaelDBA <MichaelDBA@sqlexec.com>
2019-12-03 17:29 ` Michael Lewis <mlewis@entrata.com>
2019-12-03 17:32 ` MichaelDBA <MichaelDBA@sqlexec.com>
2019-12-03 18:45 ` Sachin Divekar <ssd532@gmail.com>
2019-12-03 18:58 ` Michael Lewis <mlewis@entrata.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox