X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 8A27E9DD6B6 for ; Thu, 1 Dec 2005 17:16:01 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 88540-06 for ; Thu, 1 Dec 2005 17:15:57 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from koolancexeon.g2switchworks.com (mail.g2switchworks.com [63.87.162.25]) by postgresql.org (Postfix) with ESMTP id 6A7B29DD626 for ; Thu, 1 Dec 2005 17:15:57 -0400 (AST) Received: mail.g2switchworks.com 10.10.1.8 from 10.10.1.37 10.10.1.37 via HTTP with MS-WebStorage 6.5.6944 Received: from state.g2switchworks.com by mail.g2switchworks.com; 01 Dec 2005 15:15:59 -0600 Subject: Re: 15,000 tables From: Scott Marlowe To: Michael Riess Cc: pgsql-performance@postgresql.org In-Reply-To: References: <60psogzqui.fsf@dba2.int.libertyrms.com> Content-Type: text/plain Content-Transfer-Encoding: 7bit Message-Id: <1133471756.16010.63.camel@state.g2switchworks.com> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.6 (1.4.6-2) Date: Thu, 01 Dec 2005 15:15:59 -0600 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.001 required=5 tests=[UNPARSEABLE_RELAY=0.001] X-Spam-Score: 0.001 X-Spam-Level: X-Archive-Number: 200512/43 X-Sequence-Number: 15864 On Thu, 2005-12-01 at 13:34, Michael Riess wrote: > > Michael Riess writes: > >>> On 12/1/05, Michael Riess wrote: > >>>> we are currently running a postgres server (upgraded to 8.1) which > >>>> has one large database with approx. 15,000 tables. Unfortunately > >>>> performance suffers from that, because the internal tables > >>>> (especially that which holds the attribute info) get too large. > >>>> > >>>> (We NEED that many tables, please don't recommend to reduce them) > >>>> > >>> Have you ANALYZEd your database? VACUUMing? > >> Of course ... before 8.1 we routinely did a vacuum full analyze each > >> night. As of 8.1 we use autovacuum. > > > > VACUUM FULL was probably always overkill, unless "always" includes > > versions prior to 7.3... > > Well, we tried switching to daily VACUUM ANALYZE and weekly VACUUM FULL, > but the database got considerably slower near the end of the week. Generally, this means either your vacuums are too infrequent, or your fsm settings are too small. Note that vacuum and analyze aren't "married" any more, like in the old days. You can issue either separately, depending on your usage conditions. Note that with the newest versions of PostgreSQL you can change the settings for vacuum priority so that while it takes longer to vacuum, it doesn't stomp on the other processes toes so much anymore, so more frequent plain vacuums may be the answer.