From: Scott Marlowe <smarlowe@g2switchworks.com>
To: Michael Riess <mlriess@gmx.de>
Cc: pgsql-performance@postgresql.org
Subject: Re: 15,000 tables
Date: Thu, 01 Dec 2005 15:15:59 -0600
Message-ID: <1133471756.16010.63.camel@state.g2switchworks.com> (raw)
In-Reply-To: <dmnj8g$1hrt$1@news.hub.org>
References: <dmmujf$2rgd$1@news.hub.org>
<c2d9e70e0512010742p5e5fb61dx84dfb8e332c05352@mail.gmail.com>
<dmn6tm$1n9n$1@news.hub.org>
<60psogzqui.fsf@dba2.int.libertyrms.com>
<dmnj8g$1hrt$1@news.hub.org>
On Thu, 2005-12-01 at 13:34, Michael Riess wrote:
> > Michael Riess <mlriess@gmx.de> writes:
> >>> On 12/1/05, Michael Riess <mlriess@gmx.de> 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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: smarlowe@g2switchworks.com, mlriess@gmx.de
Subject: Re: 15,000 tables
In-Reply-To: <1133471756.16010.63.camel@state.g2switchworks.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox