| Lists: | pgsql-hackers |
|---|
| From: | Cedric Villemain <Cedric(dot)Villemain+pgsql(at)abcSQL(dot)com> |
|---|---|
| To: | PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org> |
| Subject: | doing also VM cache snapshot and restore with pg_prewarm, having more information of the VM inside PostgreSQL |
| Date: | 2024-01-03 23:57:33 |
| Message-ID: | [email protected] |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Lists: | pgsql-hackers |
Hi,
for 15 years pgfincore has been sitting quietly and being used in large
setups to help in HA and resources management.
It can perfectly stay as is, to be honest I was expecting to one day
include a windows support and propose that to PostgreSQL, it appears
getting support on linux and BSD is more than enough today.
So I wonder if there are interest for having virtual memory snapshot and
restore operations with, for example, pg_prewarm/autowarm ?
Some usecases covered: snapshot/restore cache around cronjobs, around
dumps, switchover, failover, on stop/start of postgres (think kernel
upgrade with a cold restart), ...
pgfincore also provides some nice information with mincore (on FreeBSD
mincore is more interesting) or cachestat, again it can remain as an out
of tree extension but I will be happy to add to commitfest if there are
interest from the community.
An example of cachestat output:
postgres=# select *from vm_relation_cachestat('foo',range:=1024*32);
block_start | block_count | nr_pages | nr_cache | nr_dirty |
nr_writeback | nr_evicted | nr_recently_evicted
-------------+-------------+----------+----------+----------+--------------+------------+---------------------
0 | 32768 | 65536 | 62294 | 0 |
0 | 3242 | 3242
32768 | 32768 | 65536 | 39279 | 0 |
0 | 26257 | 26257
65536 | 32768 | 65536 | 22516 | 0 |
0 | 43020 | 43020
98304 | 32768 | 65536 | 24944 | 0 |
0 | 40592 | 40592
131072 | 1672 | 3344 | 487 | 0 |
0 | 2857 | 2857
Comments?
---
Cédric Villemain +33 (0)6 20 30 22 52
https://Data-Bene.io
PostgreSQL Expertise, Support, Training, R&D
| From: | Jim Nasby <jim(dot)nasby(at)gmail(dot)com> |
|---|---|
| To: | Cedric Villemain <Cedric(dot)Villemain+pgsql(at)abcSQL(dot)com>, PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org> |
| Subject: | Re: doing also VM cache snapshot and restore with pg_prewarm, having more information of the VM inside PostgreSQL |
| Date: | 2024-01-04 22:41:55 |
| Message-ID: | [email protected] |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Lists: | pgsql-hackers |
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
</head>
<body>
<div class="moz-cite-prefix">On 1/3/24 5:57 PM, Cedric Villemain
wrote:<br>
</div>
<blockquote type="cite"
cite="mid:aa94afb9-0bfe-4421-b8b4-8a6e67ce55b1(at)abcSQL(dot)com">
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
<p>for 15 years pgfincore has been sitting quietly and being used
in large setups to help in HA and resources management.<br>
It can perfectly stay as is, to be honest I was expecting to one
day include a windows support and propose that to PostgreSQL, it
appears getting support on linux and BSD is more than enough
today.</p>
<p>So I wonder if there are interest for having virtual memory
snapshot and restore operations with, for example,
pg_prewarm/autowarm ?<br>
</p>
</blockquote>
<p>IMHO, to improve the user experience here we'd need something
that combined the abilities of all these extensions into a
cohesive interface that allowed users to simply say "please get
this data into cache". Simply moving pgfincore into core Postgres
wouldn't satisfy that need.</p>
<p>So I think the real question is whether the community feels
spport for better cache (buffercache + filesystem) management is a
worthwhile feature to add to Postgres.</p>
<p>Micromanaging cache contents for periodic jobs seems almost like
a mis-feature. While it's a useful tool to have in the toolbox,
it's also a non-trivial layer of complexity. IMHO not something
we'd want to add. Though, there might be smaller items that would
make creating tools to do that easier, such as some ability to see
what blocks a backend is accessing (perhaps via a hook).</p>
<p>On the surface, improving RTO via cache warming sounds
interesting ... but I'm not sure how useful it would be in
reality. Users that care about RTO would almost always have some
form of hot-standby, and generally those will already have a lot
of data in cache. While they won't have read-only data in cache, I
have to wonder if the answer to that problem is allowing writers
to tell a replica what blocks are being read, so the replica can
keep them in cache. Also, most (all?) operations that require a
restart could be handled via a failover, so I'm not sure how much
cache management moves the needle there.<br>
</p>
<blockquote type="cite"
cite="mid:aa94afb9-0bfe-4421-b8b4-8a6e67ce55b1(at)abcSQL(dot)com">
<p> </p>
<p>Some usecases covered: snapshot/restore cache around cronjobs,
around dumps, switchover, failover, on stop/start of postgres
(think kernel upgrade with a cold restart), ...<br>
<br>
</p>
<p>pgfincore also provides some nice information with mincore (on
FreeBSD mincore is more interesting) or cachestat, again it can
remain as an out of tree extension but I will be happy to add to
commitfest if there are interest from the community.<br>
An example of cachestat output:</p>
<p><span style="font-family:monospace"><span
style="color:#000000;background-color:#ffffff;">postgres=#
select *from vm_relation_cachestat('foo',range:=1024*32); </span><br>
block_start | block_count | nr_pages | nr_cache | nr_dirty |
nr_writeback | nr_evicted | nr_recently_evicted <br>
-------------+-------------+----------+----------+----------+--------------+------------+---------------------
<br>
0 | 32768 | 65536 | 62294 | 0 |
0 | 3242 | 3242 <br>
32768 | 32768 | 65536 | 39279 | 0 |
0 | 26257 | 26257 <br>
65536 | 32768 | 65536 | 22516 | 0 |
0 | 43020 | 43020 <br>
98304 | 32768 | 65536 | 24944 | 0 |
0 | 40592 | 40592 <br>
131072 | 1672 | 3344 | 487 | 0 |
0 | 2857 | 2857<br>
<br>
</span></p>
<p><span style="font-family:monospace"><br>
</span></p>
<p><span style="font-family:monospace">Comments?<br>
</span></p>
<pre class="moz-signature" cols="72">---
Cédric Villemain +33 (0)6 20 30 22 52
<a class="moz-txt-link-freetext" href="https://Data-Bene.io"
moz-do-not-send="true">https://Data-Bene.io</a>
PostgreSQL Expertise, Support, Training, R&D</pre>
</blockquote>
<p><br>
</p>
<pre class="moz-signature" cols="72">--
Jim Nasby, Data Architect, Austin TX</pre>
</body>
</html>
| Attachment | Content-Type | Size |
|---|---|---|
| unknown_filename | text/html | 5.1 KB |
| From: | Cédric Villemain <cedric(dot)villemain+pgsql(at)abcsql(dot)com> |
|---|---|
| To: | Jim Nasby <jim(dot)nasby(at)gmail(dot)com>, PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org> |
| Subject: | Re: doing also VM cache snapshot and restore with pg_prewarm, having more information of the VM inside PostgreSQL |
| Date: | 2024-01-05 09:32:53 |
| Message-ID: | [email protected] |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Lists: | pgsql-hackers |
Le 04/01/2024 à 23:41, Jim Nasby a écrit :
> On 1/3/24 5:57 PM, Cedric Villemain wrote:
>>
>> for 15 years pgfincore has been sitting quietly and being used in
>> large setups to help in HA and resources management.
>> It can perfectly stay as is, to be honest I was expecting to one day
>> include a windows support and propose that to PostgreSQL, it appears
>> getting support on linux and BSD is more than enough today.
>>
>> So I wonder if there are interest for having virtual memory snapshot
>> and restore operations with, for example, pg_prewarm/autowarm ?
>>
> IMHO, to improve the user experience here we'd need something that
> combined the abilities of all these extensions into a cohesive interface
> that allowed users to simply say "please get this data into cache".
> Simply moving pgfincore into core Postgres wouldn't satisfy that need.
This is exactly why I proposed those additions to pg_prewarm and autowarm.
> So I think the real question is whether the community feels spport for
> better cache (buffercache + filesystem) management is a worthwhile
> feature to add to Postgres.
Agreed, to add in an extension more probably and only the "filesystem"
part as the buffercache is done already.
> Micromanaging cache contents for periodic jobs seems almost like a
> mis-feature. While it's a useful tool to have in the toolbox, it's also
> a non-trivial layer of complexity. IMHO not something we'd want to add.
> Though, there might be smaller items that would make creating tools to
> do that easier, such as some ability to see what blocks a backend is
> accessing (perhaps via a hook).
From my point of view it's not so complex, but this is subjective and I
won't argue in this area.
I confirm that having someway to get feedback on current position/block
activity (and distance/context: heap scan, index build, analyze, ...)
would be super useful to allow external management. Maybe the "progress"
facilities can be used for that. Maybe worth looking at that for another
proposal than the current one.
To be clear I am not proposing that PostgreSQL handles those tasks
transparently or itself, but offering options to the users via
extensions like we do with pg_prewarm and pg_buffercache.
It's just the same for virtual memory/filesystem.
---
Cédric Villemain +33 (0)6 20 30 22 52
https://Data-Bene.io
PostgreSQL Expertise, Support, Training, R&D