Re: Questions regarding handling of Postgres' stats data in case of tmpfs

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(dot)Schoening(at)AM-SoFT(dot)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
>
>
>