Stop postgres writing to pgstat.tmp :: how disable / to verify that postgres statistics are correctly disabled

From: Ceriel Jacobs <cerieljacobs(at)gmail(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Subject: Stop postgres writing to pgstat.tmp :: how disable / to verify that postgres statistics are correctly disabled
Date: 2009-02-12 22:10:03
Message-ID: D4A27036-87C0-466C-B157-9E2F0042F61B@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

In the search to speed up the slow generate_tiles.py script (~ 37
tiles per minute) that connects with a postgresql 8.3.5 database on
Mac OS X 10.5.6 installed via macports, I found out with 'iofileb.d' &
'iofile.d' that much disk i/o was used by writing to /global/pgstat.tmp.

Now I disabled statistics (I think) by changing /opt/local/var/db/
postgresql83/defaultdb/postgresql.conf while the postgres daemon was
being stopped:
#track_activities = on
track_activities = off
#track_counts = on
track_counts = off
#update_process_title = on
update_process_title = off

After (re)starting postgres daemon, there is a major speed improvement
(> 575 tiles / minute), but still some statistics writing is being done:
$ sudo iofileb.d
Password:
Tracing... Hit Ctrl-C to end.
^C
PID CMD KB FILE
100 perl 4 ??/webmin/miniserv.pid #webmin
1030 Python 4 ??/33481/21874.png
1030 Python 4 ??/33481/21876.png
1030 Python 4 ??/33481/21878.png
1030 Python 4 ??/33481/21879.png
1030 Python 4 ??/33481/21880.png
1030 Python 4 ??/33481/21881.png
1030 Python 4 ??/33481/21882.png
1030 Python 4 ??/33481/21883.png
1030 Python 4 ??/33481/21884.png
1030 Python 4 ??/33481/21885.png
1030 Python 4 ??/33481/21886.png
1030 Python 4 ??/33481/21887.png
1030 Python 4 ??/33481/21888.png
1030 Python 4 ??/33481/21889.png
1030 Python 4 ??/33481/21890.png
1030 Python 4 ??/33481/21891.png
1030 Python 4 ??/33481/21892.png
1030 Python 4 ??/33481/21893.png
1030 Python 4 ??/33481/21894.png
1030 Python 4 ??/33481/21895.png
1030 Python 4 ??/33481/21896.png
1030 Python 4 ??/33481/21897.png
1030 Python 4 ??/33481/21898.png
1030 Python 4 ??/33481/21899.png
1030 Python 4 ??/33481/21900.png
1030 Python 4 ??/33481/21901.png
1030 Python 4 ??/33481/21902.png
1030 Python 8 ??/33481/21875.png
1030 Python 8 ??/33481/21877.png
1025 postgres 168 ??/global/pgstat.tmp

= 124KB for images versus 168KB for postgres in this (short) period of
time.

Questions:
1. How can I verify that I edited the correct postgresql.conf file,
in other words how can I verify which postgresql.conf file is used by
MacPorts daemon?

I would say by checking the process tree, and check which data
directory is specified:
103 postgres /opt/local/lib/postgresql83/bin/postgres -D /opt/
local/var/db/postgresql83/defaultdb
105 postgres postgres: writer process
106 postgres postgres: wal writer process
107 postgres postgres: autovacuum launcher process
108 postgres postgres: stats collector process
259 postgres postgres: postgres gis [local] idle

2. Is it correct to think that when disabling statistics in
postgresql.conf, no pgstat.tmp should be written to, or are more
actions required?

3. Or should I restart the server machine before these 'disabling
statistics' changes become effective in postgres server?

~Ceriel

Browse pgsql-admin by date

  From Date Subject
Next Message Tom Lane 2009-02-12 22:13:03 Re: [ADMIN] database corruption help
Previous Message John Lister 2009-02-12 21:45:35 Re: database corruption help