Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id B7CDE9FB30F for ; Mon, 4 Jun 2007 17:14:22 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 06420-05 for ; Mon, 4 Jun 2007 17:14:16 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from midas.utopiasystems.net (midas.utopiasystems.net [64.74.150.12]) by postgresql.org (Postfix) with ESMTP id BD3B69FA502 for ; Mon, 4 Jun 2007 17:14:17 -0300 (ADT) Received: from 24.62.5.51 ([24.62.5.51]) by midas.utopiasystems.net ([172.16.1.12]) with Microsoft Exchange Server HTTP-DAV ; Mon, 4 Jun 2007 20:14:15 +0000 User-Agent: Microsoft-Entourage/11.3.3.061214 Date: Mon, 04 Jun 2007 16:14:14 -0400 Subject: Re: Thousands of tables versus on table? From: Thomas Andrews To: Y Sidhu CC: Gregory Stark , Mark Lewis , Message-ID: Thread-Topic: [PERFORM] Thousands of tables versus on table? Thread-Index: Acem5OurKhDYLRLYEdyDLwAX8s2kmQ== In-Reply-To: Mime-version: 1.0 Content-type: multipart/alternative; boundary="B_3263818454_56011907" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=1.645 tagged_above=0 required=5 tests=AWL=-0.681, BAYES_50=0.001, FORGED_RCVD_HELO=0.135, HTML_30_40=0.374, HTML_MESSAGE=0.001, RCVD_NUMERIC_HELO=1.5, SARE_MILLIONSOF=0.315 X-Spam-Level: * X-Archive-Number: 200706/44 X-Sequence-Number: 25077 > This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. --B_3263818454_56011907 Content-type: text/plain; charset="ISO-8859-1" Content-transfer-encoding: quoted-printable Linux 2.4.9, if I=B9m reading this right. =3Dthomas On 6/4/07 4:08 PM, "Y Sidhu" wrote: > On 6/4/07, Thomas Andrews wrote: >>=20 >>=20 >>=20 >> On 6/4/07 3:43 PM, "Gregory Stark" wrote: >>=20 >>> > >>> > "Thomas Andrews" < tandrews@soliantconsulting.com >>> > writes: >>> > >>>> >> I guess my real question is, does it ever make sense to create thou= sands 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. Partitioni= ng's >>> main >>> > benefit is in the management of the data. You can drop and load parti= tions >>> in=20 >>> > 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 thousa= nds >>> of=20 >>> > 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 tab= le, >>> you >>> > may be suffering from table bloat. But if you never delete or update >>> records=20 >>> > then that's irrelevant. >>=20 >> 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. >>=20 >>> > >>> > Does reindexing or clustering the table make a marked difference? >>> > >>=20 >> Clustering sounds like it might be a really good solution. How long doe= s a >> cluster command usually take on a table with 50,000,000 records? Is it >> something that can be run daily/weekly? >>=20 >> 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 i= s >> the real killer - the CPU rarely gets higher than 20% or so. >>=20 >> =3Dthomas >>=20 >>=20 >> ---------------------------(end of broadcast)--------------------------- >> TIP 5: don't forget to increase your free space map settings >=20 >=20 > What OS are you running on? >=20 --B_3263818454_56011907 Content-type: text/html; charset="ISO-8859-1" Content-transfer-encoding: quoted-printable Re: [PERFORM] Thousands of tables versus on table? Linux= 2.4.9, if I’m reading this right.

=3Dthomas


On 6/4/07 4:08 PM, "Y Sidhu" <ysidhu@gmail.com> wrote:

On 6/4/07, Thomas Andrews <tandrews@soliantco= nsulting.com> wrote:



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

>
> "Thomas Andrews" < tandrews@soliantconsulting.com <mailto:tandrews@soliantconsulti= ng.com> > writes:
>
>> I guess my real question is, does it ever make sense to create tho= usands of
>> tables like this?
>
> Sometimes. But usually it's not a good idea.
>
> What you're proposing is basically partitioning, though you may not ac= tually
> need to put all the partitions together for your purposes. Partitionin= g's main
> benefit is in the management of the data. You can drop and load partit= ions in
> chunks rather than have to perform large operations on millions of rec= ords.
>
> 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 thousan= ds of
> tables you want to work with.
>
> How often do you update or delete records and how many do you update o= r
> delete? Once per day is a very low frequency for vacuuming a busy tabl= e, you
> may be suffering from table bloat. But if you never delete or update r= ecords
> 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 d= oes a
cluster command usually take on a table with 50,000,000 records?  Is i= t
something that can be run daily/weekly?

I'd rather not post the schema because it's not mine - I'm a consultant. &n= bsp;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.

=3Dthomas


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


What OS are you running on?


--B_3263818454_56011907--