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 538F59DCCC6 for ; Thu, 1 Dec 2005 15:41:02 -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 48296-06 for ; Thu, 1 Dec 2005 15:40:58 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from xproxy.gmail.com (xproxy.gmail.com [66.249.82.204]) by postgresql.org (Postfix) with ESMTP id 244019DCAD1 for ; Thu, 1 Dec 2005 15:40:59 -0400 (AST) Received: by xproxy.gmail.com with SMTP id s12so371236wxc for ; Thu, 01 Dec 2005 11:41:00 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=caMTdHzZuMZhDwU4qIPiYwRMiFMiY6PaC9L3cH9DYHb1JqZAt6HObxjOJb5lq3P4EY+A0gj4fuQA/kNYYRvbB/tv74eXOibq8V5cjM6YL/5KdsbEY/Nvj3gOoE13n9137bWzCRCh+EhauoUKl/XPQXIx/mncYdIYnFo/fqwia6s= Received: by 10.64.150.8 with SMTP id x8mr1087174qbd; Thu, 01 Dec 2005 11:40:59 -0800 (PST) Received: by 10.65.180.14 with HTTP; Thu, 1 Dec 2005 11:40:59 -0800 (PST) Message-ID: Date: Thu, 1 Dec 2005 14:40:59 -0500 From: Jaime Casanova To: Michael Riess Subject: Re: 15,000 tables Cc: pgsql-performance@postgresql.org In-Reply-To: MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline References: <60psogzqui.fsf@dba2.int.libertyrms.com> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.028 required=5 tests=[AWL=0.028] X-Spam-Score: 0.028 X-Spam-Level: X-Archive-Number: 200512/41 X-Sequence-Number: 15862 On 12/1/05, 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. > > > > >>> BTW, are you using some kind of weird ERP? I have one that treat > >>> informix as a fool and don't let me get all of informix potential... > >>> maybe the same is in your case... > >> No. Our database contains tables for we content management > >> systems. The server hosts approx. 500 cms applications, and each of > >> them has approx. 30 tables. > >> > >> That's why I'm asking if it was better to have 500 databases with 30 > >> tables each. In previous Postgres versions this led to even worse > >> performance ... > > > > This has the feeling of fitting with Alan Perlis' dictum below... > > > > Supposing you have 500 databases, each with 30 tables, each with 4 > > indices, then you'll find you have, on disk... > > > > # of files =3D 500 x 30 x 5 =3D 75000 files > > > > If each is regularly being accessed, that's bits of 75000 files > > getting shoved through OS and shared memory caches. Oh, yes, and > > you'll also have regular participation of some of the pg_catalog > > files, with ~500 instances of THOSE, multiplied some number of ways... > > > > Not all of the tables are frequently accessed. In fact I would estimate > that only 20% are actually used ... but there is no way to determine if > or when a table will be used. I thought about a way to "swap out" tables > which have not been used for a couple of days ... maybe I'll do just > that. But it would be cumbersome ... I had hoped that an unused table > does not hurt performance. But of course the internal tables which > contain the meta info get too large. > > > An application with 15000 frequently accessed tables doesn't strike me > > as being something that can possibly turn out well. You have, in > > effect, more tables than (arguably) bloated ERP systems like SAP R/3; > > it only has a few thousand tables, and since many are module-specific, > > and nobody ever implements *all* the modules, it is likely only a few > > hundred that are "hot spots." No 15000 there.. > > I think that my systems confirms with the 80/20 rule ... > . > How many disks do you have i imagine you can put tables forming one logical database in a tablespace and have tables spread on various disks... -- Atentamente, Jaime Casanova (DBA: DataBase Aniquilator ;)