Megabytes of stats saved after every connection

From: Phil Endecott <spam_from_postgresql_general(at)chezphil(dot)org>
To: pgsql-general(at)postgresql(dot)org
Subject: Megabytes of stats saved after every connection
Date: 2005-07-28 15:48:09
Message-ID: 42E8FE39.5040504@chezphil.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tony Caduto 2005-07-28 15:48:13 Raise Notice question
Previous Message Bricklen Anderson 2005-07-28 15:20:20 Re: problem inserting with sequence