Dear Postgresql experts,
For some time I had been trying to work out why every connection to my
database resulted in several megabytes of data being written to the
disk, however trivial the query. I think I've found the culprit:
global/pgstat.stat. This is with 7.4.7.
This is for a web application which uses a new connection for each CGI
request. The server doesn't have a particularly high disk bandwidth and
this mysterious activity had been the bottleneck for some time. The
system is a little unusual as one of the databases has tens of thousands
of tables (though I saw these writes whichever database I connected to).
Looking at the output of vmstat I could see about 2.7Mbytes being
written up to about 5 seconds after the query was processed. I was
scratching my head about this for a long time, but today I noticed that
this size was just a little larger than my global/pgstat.stat file. So
I turned off stat_start_collector and stats_row_level and the writes
vanished. Turing them back on, the pgstats.stats file is much smaller
(10k) and the writes are invisible against the background noise.
So can I expect this file to grow again? I think I need the stats,
though I'm not entirely sure about that.
Was the entire file re-written, even when the only query I've run is
"select 1"? Is this necessary?
Any comments or suggestions gratefully received.
--Phil.