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 D31D09DCA09 for ; Sat, 28 Jan 2006 13:54:13 -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 17677-10 for ; Sat, 28 Jan 2006 13:54:12 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from mail.mi8.com (d01gw02.mi8.com [63.240.6.46]) by postgresql.org (Postfix) with ESMTP id 055EF9DC9D6 for ; Sat, 28 Jan 2006 13:54:10 -0400 (AST) Received: from 172.16.1.148 by mail.mi8.com with ESMTP (- Welcome to Mi8 Corporation www.Mi8.com (D2)); Sat, 28 Jan 2006 12:53:56 -0500 X-Server-Uuid: 7829E76E-BB9E-4995-8473-3C0929DF7DD1 Received: from MI8NYCMAIL06.Mi8.com ([172.16.1.175]) by D01HOST02.Mi8.com with Microsoft SMTPSVC(6.0.3790.1830); Sat, 28 Jan 2006 12:53:56 -0500 Received: from 69.181.100.71 ([69.181.100.71]) by MI8NYCMAIL06.Mi8.com ( [172.16.1.219]) via Exchange Front-End Server mi8owa.mi8.com ( [172.16.1.105]) with Microsoft Exchange Server HTTP-DAV ; Sat, 28 Jan 2006 17:53:56 +0000 User-Agent: Microsoft-Entourage/11.2.1.051004 Date: Fri, 27 Jan 2006 19:05:04 -0800 Subject: Re: Huge Data sets, simple queries From: "Luke Lonergan" To: "Mike Biamonte" , pgsql-performance@postgresql.org Message-ID: Thread-Topic: [PERFORM] Huge Data sets, simple queries Thread-Index: AcYjqiD+PM+GCn6QRfyvn64Ut4tn7AADYGQY In-Reply-To: <007101c623a9$82207c10$0200a8c0@videobox> MIME-Version: 1.0 X-OriginalArrivalTime: 28 Jan 2006 17:53:56.0787 (UTC) FILETIME=[CF61E830:01C62433] X-WSS-ID: 6FC5703E3306121286-01-01 Content-Type: multipart/alternative; boundary=B_3221286835_1192920 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=1.766 required=5 tests=[AWL=-0.369, DATE_IN_PAST_12_24=0.881, HTML_MESSAGE=0.001, RCVD_NUMERIC_HELO=1.253] X-Spam-Score: 1.766 X-Spam-Level: * X-Archive-Number: 200601/462 X-Sequence-Number: 16940 > 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_3221286835_1192920 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable Sounds like you are running into the limits of your disk subsystem. You ar= e scanning all of the data in the transactions table, so you will be limited by the disk bandwidth you have =AD and using RAID-10, you should divide the number of disk drives by 2 and multiply by their indiividual bandwidth (around 60MB/s) and that=B9s what you can expect in terms of performance. So= , if you have 8 drives, you should expect to get 4 x 60 MB/s =3D 240 MB/s in bandwidth. That means that if you are dealing with 24,000 MB of data in th= e =B3transactions=B2 table, then you will scan it in 100 seconds. With a workload like this, you are in the realm of business intelligence / data warehousing I think. You should check your disk performance, I would expect you=B9ll find it lacking, partly because you are running RAID10, but mostly because I expect you are using a hardware RAID adapter. - Luke On 1/27/06 5:23 PM, "Mike Biamonte" wrote: >=20 >=20 >=20 > Does anyone have any experience with extremely large data sets? > I'm mean hundreds of millions of rows. >=20 > The queries I need to run on my 200 million transactions are relatively > simple: >=20 > select month, count(distinct(cardnum)) count(*), sum(amount) from > transactions group by month; >=20 > This query took 18 hours on PG 8.1 on a Dual Xeon, RHEL3, (2.4 Kernel) wi= th > RAID-10 (15K drives) > and 12 GB Ram. I was expecting it to take about 4 hours - based on some > experience with a > similar dataset on a different machine (RH9, PG7.3 Dual Xeon, 4GB RAM, > Raid-5 10K drives) >=20 > This machine is COMPLETELY devoted to running these relatively simple > queries one at a > time. (No multi-user support needed!) I've been tooling with the vario= us > performance settings: > effective_cache at 5GB, shared_buffers at 2 GB, workmem, sortmem at 1 GB > each. > ( Shared buffers puzzles me a it bit - my instinct says to set it as high= as > possible, > but everything I read says that "too high" can hurt performance.) >=20 > Any ideas for performance tweaking in this kind of application would b= e > greatly appreciated. > We've got indexes on the fields being grouped, and always vacuum analzye > after building them. >=20 > It's difficult to just "try" various ideas because each attempt takes = a > full day to test. Real > experience is needed here! >=20 > Thanks much, >=20 > Mike >=20 >=20 > ---------------------------(end of broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings >=20 >=20 --B_3221286835_1192920 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable Re: [PERFORM] Huge Data sets, simple queries Sound= s like you are running into the limits of your disk subsystem.  You are= scanning all of the data in the transactions table, so you will be limited = by the disk bandwidth you have – and using RAID-10, you should divide = the number of disk drives by 2 and multiply by their indiividual bandwidth (= around 60MB/s) and that’s what you can expect in terms of performance.=  So, if you have 8 drives, you should expect to get 4 x 60 MB/s =3D 240 = MB/s in bandwidth.  That means that if you are dealing with 24,000 MB o= f data in the “transactions” table, then you will scan  it = in 100 seconds.

With a workload like this, you are in the realm of business intelligence / = data warehousing I think.  You should check your disk performance, I wo= uld expect you’ll find it lacking, partly because you are running RAID= 10, but mostly because I expect you are using a hardware RAID adapter.

- Luke


On 1/27/06 5:23 PM, "Mike Biamonte" <mike@dbeat.com> wrote:=




Does anyone have any experience with extremely large data sets?
I'm mean hundreds of millions of rows.

The queries I need to run on my 200 million transactions are relatively
simple:

   select month, count(distinct(cardnum)) count(*), sum(amou= nt) from
transactions group by month;

This query took 18 hours on PG 8.1 on a Dual Xeon, RHEL3, (2.4 Kernel) with=
RAID-10 (15K drives)
and 12 GB Ram.  I was expecting it to take about 4 hours - based on so= me
experience with a
similar dataset on a different machine (RH9, PG7.3 Dual Xeon, 4GB RAM,
Raid-5 10K drives)

  This machine is COMPLETELY devoted to running these relatively = simple
queries one at a
time. (No multi-user support needed!)    I've been tooling w= ith the various
performance settings:
effective_cache at 5GB, shared_buffers at 2 GB, workmem, sortmem at 1 GB each.
( Shared buffers puzzles me a it bit - my instinct says to set it as high a= s
possible,
but everything I read says that "too high" can hurt performance.)=

   Any ideas for performance tweaking in this kind of applic= ation would be
greatly appreciated.
We've got indexes on the fields being grouped, and always vacuum analzye after building them.

   It's difficult to just "try" various ideas beca= use each attempt takes a
full day to test.  Real
experience is needed here!

Thanks much,

Mike


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



 --B_3221286835_1192920--