Questions regarding handling of Postgres' stats data in case of tmpfs - Mailing list pgsql-admin

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