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@xxxxxxxxxx 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 -- Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
