On Thu, 2005-07-28 at 13:40, Jan Wieck wrote: > On 7/28/2005 2:28 PM, Tom Lane wrote: > > > Jan Wieck <JanWieck@xxxxxxxxx> 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. ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
