Re: Megabytes of stats saved after every connection

On Thu, 2005-07-28 at 13:40, Jan Wieck wrote:
> On 7/28/2005 2:28 PM, Tom Lane wrote:
>
> > Jan Wieck <JanWieck(at)Yahoo(dot)com> writes:
> >> On 7/28/2005 2:03 PM, Tom Lane wrote:
> >>> Well, there's the problem --- the stats subsystem is designed in a way
> >>> that makes it rewrite its entire stats collection on every update.
> >>> That's clearly not going to scale well to a large number of tables.
> >>> Offhand I don't see an easy solution ... Jan, any ideas?
> >
> >> PostgreSQL itself doesn't work too well with tens of thousands of
> >> tables.
> >
> > Really? AFAIK it should be pretty OK, assuming you are on a filesystem
> > that doesn't choke with tens of thousands of entries in a directory.
> > I think we should put down a TODO item to see if we can improve the
> > stats subsystem's performance in such cases.
>
> Okay, I should be more specific. The problem with tens of thousands of
> tables does not exist just because of them being there. It will emerge
> if all those tables are actually used because it will mean that you'd
> need all the pg_class and pg_attribute rows cached and also your vfd
> cache will constantly rotate.
>
> Then again, the stats file is only written. There is nothing that
> actually forces the blocks out. On a busy system, one individual stats
> file will be created, written to, renamed, live for 500ms and be thrown
> away by the next stat files rename operation. I would assume that with a
> decent filesystem and appropriate OS buffers, none of the data blocks of
> most stat files even hit the disk. I must be missing something.

Yeah, I found these three facets of the OP's system a bit disconcerting:

QUOTE ---
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.
ENDQUOTE ---

Any two of those choices could cause some issues, but all three together
are pretty much a death knell for performance, whether or not the
global/pgstat file is being written or not.

Just an observation.