Обсуждение: Questions regarding handling of Postgres' stats data in case of tmpfs

Hi all,

we are currently investigating I/O performance of one of our Ubuntu
VMs and it showed that most of the produced I/O is produced by our
used Postgres 9.1 stats collector process. I've already read some
interesting things about that topic[1][2] and putting the directory
for the stats file into a tmpfs sounds very well to me. But the
following sentence in [1] shows some lack of understanding on my side
which I would like to ask about:

> After restart, the PostgreSQL will copy the files to the new
> location (and back when it's stopped).

So, as the name "stats_temp_directory" already suggests, the file seems
to be a temporary one, but I wasn't aware of that it is persisted on
server shutdown somewhere else and copied on startup only. The problem
I have now is that currently the file is always persistent in the temp
dir, because regarding [1] the file is written atomically, which makes
sense of course. So in case of unclean shutdown and such the last
successful write would always be available in theory.

If I use tmpfs that may change... It might not change if only the
Postgres process is killed, because in theory that wouldn't influence
tmpfs and the last successful write of the file would still be
available. It definitely changes if something happens to the VM
itself, which we already had in the past because of e.g. problems with
some iSCSI device on which the VM is hosted.

So, where is the data for the stats persisted on shutdown? Or is it
really only available during the runtime of the process? I didn't had
that feeling while reading the documentation and blog post.

If the Postgres process is e.g. killed and restarted, being unable to
persist the stats from the temp dir elsewhere, would it recognize the
more current last successful write in the temp dir and use the stats
from there during the first new startup after unclean shutdown? Or is
such temp data always discarded?

If we host the stats file on a tmpfs, is there any way we could
interfere in a way that Postgres persists the temp file "once a while"
to some available persistent store? Besides a clean restart of course. ;-)
The goal would be to not loose stats for a week of successful running
just because some day the VM breaks for any reason. Because Postgres
wouldn't be aware of the use of tmpfs, I guess it wouldn't do anything
on it's own to prevent such a situation.

Would I need to use some stacked/overlay/whatever file system, binding
tmpfs over the persistent "stats_temp_directory" and sync manually
using e.g. cron "once a while"? The only thing of overlay filesystems
I know so far is that those are primary used with Live-CDs and
therefore the lower filesystem is read only, while I would need two rw
ones, where the upper one writes through to the lower one on demand...

[1] already mentioned optimizations to the handling of the stats file
in Postgres 9.3, but I would be very grateful if you could answer my
questions anyway, because I simply don't know when we are able to
upgrade and would like to understand handling of the stats file
better.

Thanks a lot!

P.S.: I posted my questions on Serverfault as well[3], because of [2],
but didn't get any attention yet, so am trying it here as well.

[1]: http://blog.pgaddict.com/posts/the-two-kinds-of-stats-in-postgresql
[2]: http://serverfault.com/a/524038/333397
[3]: http://serverfault.com/questions/751009/persist-statistics-data-from-postgres-stats-temp-directory-on-demand

Mit freundlichen Grüßen,

Thorsten Schöning

--
Thorsten Schöning       E-Mail: Thorsten.Schoening@AM-SoFT.de
AM-SoFT IT-Systeme      http://www.AM-SoFT.de/

Telefon...........05151-  9468- 55
Fax...............05151-  9468- 88
Mobil..............0178-8 9468- 04

AM-SoFT GmbH IT-Systeme, Brandenburger Str. 7c, 31789 Hameln
AG Hannover HRB 207 694 - Geschäftsführer: Andreas Muchow

El sáb, 23-01-2016 a las 16:47 +0100, Thorsten Schöning escribió:
> Hi all,
>
> we are currently investigating I/O performance of one of our Ubuntu
> VMs and it showed that most of the produced I/O is produced by our
> used Postgres 9.1 stats collector process. I've already read some
> interesting things about that topic[1][2] and putting the directory
> for the stats file into a tmpfs sounds very well to me. But the
> following sentence in [1] shows some lack of understanding on my side
> which I would like to ask about:
>
> > After restart, the PostgreSQL will copy the files to the new
> > location (and back when it's stopped).
>
> So, as the name "stats_temp_directory" already suggests, the file
> seems
> to be a temporary one, but I wasn't aware of that it is persisted on
> server shutdown somewhere else and copied on startup only. The
> problem
> I have now is that currently the file is always persistent in the
> temp
> dir, because regarding [1] the file is written atomically, which
> makes
> sense of course. So in case of unclean shutdown and such the last
> successful write would always be available in theory.
>
> If I use tmpfs that may change... It might not change if only the
> Postgres process is killed, because in theory that wouldn't influence
> tmpfs and the last successful write of the file would still be
> available. It definitely changes if something happens to the VM
> itself, which we already had in the past because of e.g. problems
> with
> some iSCSI device on which the VM is hosted.
>
> So, where is the data for the stats persisted on shutdown? Or is it
> really only available during the runtime of the process? I didn't had
> that feeling while reading the documentation and blog post.
>
> If the Postgres process is e.g. killed and restarted, being unable to
> persist the stats from the temp dir elsewhere, would it recognize the
> more current last successful write in the temp dir and use the stats
> from there during the first new startup after unclean shutdown? Or is
> such temp data always discarded?
>
> If we host the stats file on a tmpfs, is there any way we could
> interfere in a way that Postgres persists the temp file "once a
> while"
> to some available persistent store? Besides a clean restart of
> course. ;-)
> The goal would be to not loose stats for a week of successful running
> just because some day the VM breaks for any reason. Because Postgres
> wouldn't be aware of the use of tmpfs, I guess it wouldn't do
> anything
> on it's own to prevent such a situation.
>
> Would I need to use some stacked/overlay/whatever file system,
> binding
> tmpfs over the persistent "stats_temp_directory" and sync manually
> using e.g. cron "once a while"? The only thing of overlay filesystems
> I know so far is that those are primary used with Live-CDs and
> therefore the lower filesystem is read only, while I would need two
> rw
> ones, where the upper one writes through to the lower one on
> demand...
>
> [1] already mentioned optimizations to the handling of the stats file
> in Postgres 9.3, but I would be very grateful if you could answer my
> questions anyway, because I simply don't know when we are able to
> upgrade and would like to understand handling of the stats file
> better.

This article could help you:
http://thebuild.com/blog/2016/02/02/always-do-this-4-put-stats_temp_dir
ectory-on-a-memory-file-system/

>
> Thanks a lot!
>
> P.S.: I posted my questions on Serverfault as well[3], because of
> [2],
> but didn't get any attention yet, so am trying it here as well.
>
> [1]:
> http://blog.pgaddict.com/posts/the-two-kinds-of-stats-in-postgresql
> [2]: http://serverfault.com/a/524038/333397
> [3]: http://serverfault.com/questions/751009/persist-statistics-data-
> from-postgres-stats-temp-directory-on-demand
>
> Mit freundlichen Grüßen,
>
> Thorsten Schöning
>
> --
> Thorsten Schöning       E-Mail: Thorsten.Schoening@AM-SoFT.de
> AM-SoFT IT-Systeme      http://www.AM-SoFT.de/
>
> Telefon...........05151-  9468- 55
> Fax...............05151-  9468- 88
> Mobil..............0178-8 9468- 04
>
> AM-SoFT GmbH IT-Systeme, Brandenburger Str. 7c, 31789 Hameln
> AG Hannover HRB 207 694 - Geschäftsführer: Andreas Muchow
>
>
>

Guten Tag jaime soler,
am Mittwoch, 3. Februar 2016 um 10:19 schrieben Sie:

> This article could help you:
> http://thebuild.com/blog/2016/02/02/always-do-this-4-put-stats_temp_dir
> ectory-on-a-memory-file-system/

Thanks, that was what I hoped to find.

Mit freundlichen Grüßen,

Thorsten Schöning

--
Thorsten Schöning       E-Mail: Thorsten.Schoening@AM-SoFT.de
AM-SoFT IT-Systeme      http://www.AM-SoFT.de/

Telefon...........05151-  9468- 55
Fax...............05151-  9468- 88
Mobil..............0178-8 9468- 04

AM-SoFT GmbH IT-Systeme, Brandenburger Str. 7c, 31789 Hameln
AG Hannover HRB 207 694 - Geschäftsführer: Andreas Muchow