Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 51DF29FAC9B for ; Mon, 4 Jun 2007 17:08:45 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 08509-03 for ; Mon, 4 Jun 2007 17:08:36 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from an-out-0708.google.com (an-out-0708.google.com [209.85.132.244]) by postgresql.org (Postfix) with ESMTP id 10FD09FA4AE for ; Mon, 4 Jun 2007 17:08:39 -0300 (ADT) Received: by an-out-0708.google.com with SMTP id c36so346228ana for ; Mon, 04 Jun 2007 13:08:38 -0700 (PDT) DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=iSJ8D1Wio8MUOVcJ/PzNLnH7slIyT2eke0+FQVH9rhaXrgzUPp4sxID23UA8zmoNYK8hTWprmC4TRU6QN5MA+l8kfKZQKLZ2s5BrYca4Hw5UUBM6gBzQr62L6Tn0FD5TKERjwzfs/hC5f2YLIVclfP4zi0NcYdVRdQz3N5SvlSs= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=h8mxyujzR9j+HKSYavbuMgvCzhtEftZgOXmP9M9RGz2nE/qMk2rsGiCSs5ita0BC4XfBzeGPafRyhe0jKgpNpJ3ce4IrR5o7JWYVJtTxH3TBiYcbgkzAB+2TIa5XK6LKOTN7wicweBOoQggTa5weFb0zwL//1DgfIvLCf4ld9uQ= Received: by 10.100.126.2 with SMTP id y2mr2786062anc.1180987718095; Mon, 04 Jun 2007 13:08:38 -0700 (PDT) Received: by 10.100.4.16 with HTTP; Mon, 4 Jun 2007 13:08:38 -0700 (PDT) Message-ID: Date: Mon, 4 Jun 2007 13:08:38 -0700 From: "Y Sidhu" To: "Thomas Andrews" Subject: Re: Thousands of tables versus on table? Cc: "Gregory Stark" , "Mark Lewis" , pgsql-performance@postgresql.org In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_9370_23972497.1180987718044" References: <877iqjmro5.fsf@oxford.xeocode.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200706/43 X-Sequence-Number: 25076 ------=_Part_9370_23972497.1180987718044 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline On 6/4/07, Thomas Andrews wrote: > > > > > On 6/4/07 3:43 PM, "Gregory Stark" wrote: > > > > > "Thomas Andrews" writes: > > > >> I guess my real question is, does it ever make sense to create > thousands of > >> tables like this? > > > > Sometimes. But usually it's not a good idea. > > > > What you're proposing is basically partitioning, though you may not > actually > > need to put all the partitions together for your purposes. > Partitioning's main > > benefit is in the management of the data. You can drop and load > partitions in > > chunks rather than have to perform large operations on millions of > records. > > > > Postgres doesn't really get any faster by breaking the tables up like > that. In > > fact it probably gets slower as it has to look up which of the thousands > of > > tables you want to work with. > > > > How often do you update or delete records and how many do you update or > > delete? Once per day is a very low frequency for vacuuming a busy table, > you > > may be suffering from table bloat. But if you never delete or update > records > > then that's irrelevant. > > It looks like the most inserts that have occurred in a day is about 2000. > The responders table has 1.3 million records, the responses table has 50 > million records. Most of the inserts are in the responses table. > > > > > Does reindexing or clustering the table make a marked difference? > > > > Clustering sounds like it might be a really good solution. How long does > a > cluster command usually take on a table with 50,000,000 records? Is it > something that can be run daily/weekly? > > I'd rather not post the schema because it's not mine - I'm a > consultant. I > can tell you our vacuum every night is taking 2 hours and that disk IO is > the real killer - the CPU rarely gets higher than 20% or so. > > =thomas > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings > What OS are you running on? -- Yudhvir Singh Sidhu 408 375 3134 cell ------=_Part_9370_23972497.1180987718044 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline On 6/4/07, Thomas Andrews <tandrews@soliantconsulting.com> wrote:



On 6/4/07 3:43 PM, "Gregory Stark" <stark@enterprisedb.com> wrote:

>
> "Thomas Andrews" < tandrews@soliantconsulting.com> writes:
>
>> I guess my real question is, does it ever make sense to create thousands of
>> tables like this?
>
> Sometimes. But usually it's not a good idea.
>
> What you're proposing is basically partitioning, though you may not actually
> need to put all the partitions together for your purposes. Partitioning's main
> benefit is in the management of the data. You can drop and load partitions in
> chunks rather than have to perform large operations on millions of records.
>
> Postgres doesn't really get any faster by breaking the tables up like that. In
> fact it probably gets slower as it has to look up which of the thousands of
> tables you want to work with.
>
> How often do you update or delete records and how many do you update or
> delete? Once per day is a very low frequency for vacuuming a busy table, you
> may be suffering from table bloat. But if you never delete or update records
> then that's irrelevant.

It looks like the most inserts that have occurred in a day is about 2000.
The responders table has 1.3 million records, the responses table has 50
million records.  Most of the inserts are in the responses table.

>
> Does reindexing or clustering the table make a marked difference?
>

Clustering sounds like it might be a really good solution.  How long does a
cluster command usually take on a table with 50,000,000 records?  Is it
something that can be run daily/weekly?

I'd rather not post the schema because it's not mine - I'm a consultant.  I
can tell you our vacuum every night is taking 2 hours and that disk IO is
the real killer - the CPU rarely gets higher than 20% or so.

=thomas


---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings


What OS are you running on?


--
Yudhvir Singh Sidhu
408 375 3134 cell ------=_Part_9370_23972497.1180987718044--