From jakablaszlo@sofasoft.ro Fri Feb 21 07:45:47 2003 X-Original-To: pgsql-performance@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 2F641475B8F for ; Fri, 21 Feb 2003 02:45:47 -0500 (EST) Received: from postgresql.org ([64.49.215.8]) by localhost (postgresql.org [64.49.215.8:10024]) (amavisd-new) with SMTP id 38253-03 for ; Fri, 21 Feb 2003 02:45:36 -0500 (EST) Received: from saturn.hiddenservers.com (saturn.hiddenservers.com [209.123.207.12]) by postgresql.org (Postfix) with ESMTP id AE0CA475C85 for ; Fri, 21 Feb 2003 02:43:03 -0500 (EST) Received: from [213.154.101.33] (helo=new) by saturn.hiddenservers.com with smtp (Exim 3.36 #1) id 18m7q2-0001Xr-00 for pgsql-performance@postgresql.org; Fri, 21 Feb 2003 02:43:18 -0500 Message-ID: <004501c2d97d$98927670$0900000a@new> From: "Jakab Laszlo" To: Subject: performance issues for processing more then 150000 records / day. Date: Fri, 21 Feb 2003 09:48:16 +0200 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0042_01C2D98E.5B627DC0" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.00.2919.6700 X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2919.6700 X-AntiAbuse: This header was added to track abuse, please include it with any abuse report X-AntiAbuse: Primary Hostname - saturn.hiddenservers.com X-AntiAbuse: Original Domain - postgresql.org X-AntiAbuse: Originator/Caller UID/GID - [0 0] / [0 0] X-AntiAbuse: Sender Address Domain - sofasoft.ro X-Virus-Scanned: by amavisd-new X-Archive-Number: 200302/143 X-Sequence-Number: 1199 This is a multi-part message in MIME format. ------=_NextPart_000_0042_01C2D98E.5B627DC0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hello, We would like to use PostgreSQL for one project. This project would need to handle some of 150000 - 200000 (thousands record= s) per day. Is there somebody having experience with Postgresql in this kind of environ= ment. Can anybody advice us regarding specific PostgreSQL issues for such kind of= datamanipulation ? Best Regards, Jaki ------=_NextPart_000_0042_01C2D98E.5B627DC0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Hello,
 
We would like to use PostgreSQL for one=20 project.
This project would need to handle some of = 150000 -=20 200000 (thousands records) per day.
 
Is there somebody having experience with P= ostgresql=20 in this kind of environment.
Can anybody advice us regarding specific P= ostgreSQL=20 issues for such kind of datamanipulation ?
 
Best Regards,
Jaki
------=_NextPart_000_0042_01C2D98E.5B627DC0-- From chriskl@familyhealth.com.au Fri Feb 21 08:04:51 2003 X-Original-To: pgsql-performance@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 82AB0474E4F for ; Fri, 21 Feb 2003 03:04:51 -0500 (EST) Received: from postgresql.org ([64.49.215.8]) by localhost (postgresql.org [64.49.215.8:10024]) (amavisd-new) with SMTP id 39084-05 for ; Fri, 21 Feb 2003 03:04:40 -0500 (EST) Received: from houston.familyhealth.com.au (unknown [203.59.48.253]) by postgresql.org (Postfix) with ESMTP id D5742474E61 for ; Fri, 21 Feb 2003 03:04:36 -0500 (EST) Received: (from root@localhost) by houston.familyhealth.com.au (8.11.6/8.11.6) id h1L84aA55003 for pgsql-performance@postgresql.org; Fri, 21 Feb 2003 16:04:36 +0800 (WST) (envelope-from chriskl@familyhealth.com.au) Received: from mariner (mariner.internal [192.168.0.101]) by houston.familyhealth.com.au (8.11.6/8.9.3) with SMTP id h1L84X754913; Fri, 21 Feb 2003 16:04:33 +0800 (WST) Message-ID: <0a6901c2d97f$ea1369d0$6500a8c0@fhp.internal> From: "Christopher Kings-Lynne" To: "Jakab Laszlo" , References: <004501c2d97d$98927670$0900000a@new> Subject: Re: performance issues for processing more then 150000 records / day. Date: Fri, 21 Feb 2003 16:04:52 +0800 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0A66_01C2D9C2.F8169EB0" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2720.3000 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000 X-scanner: scanned by Inflex 0.1.5c - (http://www.inflex.co.za/) X-Virus-Scanned: by amavisd-new X-Archive-Number: 200302/144 X-Sequence-Number: 1200 This is a multi-part message in MIME format. ------=_NextPart_000_0A66_01C2D9C2.F8169EB0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi Jakab, I don't see why postgresql wouldn't be able to handle that - it's a relativ= ely small amount of data. When you say 150000 records/day, do you mean: 1. 150000 inserts/day (ie. database grows constantly, quickly) 2. 150000 updates/deletes/day 3. 150000 transactions/select/day If you give us a little more information, we'll be able to help you tune yo= ur postgres for your application. Regards, Chris ----- Original Message -----=20 From: Jakab Laszlo=20 To: pgsql-performance@postgresql.org=20 Sent: Friday, February 21, 2003 3:48 PM Subject: [PERFORM] performance issues for processing more then 150000 rec= ords / day. Hello, We would like to use PostgreSQL for one project. This project would need to handle some of 150000 - 200000 (thousands reco= rds) per day. Is there somebody having experience with Postgresql in this kind of envir= onment. Can anybody advice us regarding specific PostgreSQL issues for such kind = of datamanipulation ? Best Regards, Jaki ------=_NextPart_000_0A66_01C2D9C2.F8169EB0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Hi Jakab,
 
I don't see why postgresql wouldn't be abl= e to=20 handle that - it's a relatively small amount of data.
 
When you say 150000 records/day, do you=20 mean:
 
1. 150000 inserts/day (ie. database grows= =20 constantly, quickly)
2. 150000 updates/deletes/day
3. 150000 transactions/select/day
 
If you give us a little more information, = we'll be=20 able to help you tune your postgres for your application.
 
Regards,
 
Chris
 
----- Original Message -----
Fro= m:=20 Jakab=20 Laszlo
To: pgsql-performance@postgr= esql.org=20
Sent: Friday, February 21, 2003 3:= 48=20 PM
Subject: [PERFORM] performance iss= ues for=20 processing more then 150000 records / day.

Hello,
 
We would like to use PostgreSQL for one= =20 project.
This project would need to handle some o= f 150000=20 - 200000 (thousands records) per day.
 
Is there somebody having experience with= =20 Postgresql in this kind of environment.
Can anybody advice us regarding specific= =20 PostgreSQL issues for such kind of datamanipulation ?
 
Best Regards,
Jaki
------=_NextPart_000_0A66_01C2D9C2.F8169EB0-- From jakablaszlo@sofasoft.ro Fri Feb 21 08:30:16 2003 X-Original-To: pgsql-performance@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id D0789475AEC for ; Fri, 21 Feb 2003 03:30:16 -0500 (EST) Received: from postgresql.org ([64.49.215.8]) by localhost (postgresql.org [64.49.215.8:10024]) (amavisd-new) with SMTP id 48857-03 for ; Fri, 21 Feb 2003 03:30:05 -0500 (EST) Received: from saturn.hiddenservers.com (saturn.hiddenservers.com [209.123.207.12]) by postgresql.org (Postfix) with ESMTP id A6BAA474E61 for ; Fri, 21 Feb 2003 03:28:45 -0500 (EST) Received: from [213.154.101.33] (helo=new) by saturn.hiddenservers.com with smtp (Exim 3.36 #1) id 18m8Y5-0000vv-00; Fri, 21 Feb 2003 03:28:49 -0500 Message-ID: <007c01c2d983$f3d60d70$0900000a@new> From: "Jakab Laszlo" To: "Christopher Kings-Lynne" , References: <004501c2d97d$98927670$0900000a@new> <0a6901c2d97f$ea1369d0$6500a8c0@fhp.internal> Subject: Re: performance issues for processing more then 150000 records / day. Date: Fri, 21 Feb 2003 10:33:42 +0200 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0079_01C2D994.B49FE480" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.00.2919.6700 X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2919.6700 X-AntiAbuse: This header was added to track abuse, please include it with any abuse report X-AntiAbuse: Primary Hostname - saturn.hiddenservers.com X-AntiAbuse: Original Domain - postgresql.org X-AntiAbuse: Originator/Caller UID/GID - [0 0] / [0 0] X-AntiAbuse: Sender Address Domain - sofasoft.ro X-Virus-Scanned: by amavisd-new X-Archive-Number: 200302/145 X-Sequence-Number: 1201 This is a multi-part message in MIME format. ------=_NextPart_000_0079_01C2D994.B49FE480 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hello Chris, I mean here 150000 inserts/day ( quickly grows constantly )... - with tran= sactions and on the same table ... maybe after one significant amount of ti= me we can move the records of one year to one archive table ... And some 2-3 millions of selects / day ...=20 I would like to know also some hardware related advice. thanks, Jaki ----- Original Message -----=20 From: Christopher Kings-Lynne=20 To: Jakab Laszlo ; pgsql-performance@postgresql.org=20 Sent: Friday, February 21, 2003 10:04 AM Subject: Re: [PERFORM] performance issues for processing more then 150000= records / day. Hi Jakab, =20=20=20 I don't see why postgresql wouldn't be able to handle that - it's a relat= ively small amount of data. =20=20=20 When you say 150000 records/day, do you mean: =20=20=20 1. 150000 inserts/day (ie. database grows constantly, quickly) 2. 150000 updates/deletes/day 3. 150000 transactions/select/day =20=20=20 If you give us a little more information, we'll be able to help you tune = your postgres for your application. =20=20=20 Regards, =20=20=20 Chris ----- Original Message -----=20 From: Jakab Laszlo=20 To: pgsql-performance@postgresql.org=20 Sent: Friday, February 21, 2003 3:48 PM Subject: [PERFORM] performance issues for processing more then 150000 r= ecords / day. Hello, We would like to use PostgreSQL for one project. This project would need to handle some of 150000 - 200000 (thousands re= cords) per day. Is there somebody having experience with Postgresql in this kind of env= ironment. Can anybody advice us regarding specific PostgreSQL issues for such kin= d of datamanipulation ? Best Regards, Jaki ------=_NextPart_000_0079_01C2D994.B49FE480 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Hello Chris,
 
I mean here 150000 inserts/day ( quickly g= rows=20 constantly )...  - with transactions and on the same table ... maybe a= fter=20 one significant amount of time we can move the records of one year to = one=20 archive table ...
And some 2-3 millions of selects / da= y ...=20
 
I would like to know also some hardware re= lated=20 advice.
 
thanks,
Jaki
----- Original Message -----
Fro= m:=20 Christopher Kings-Lynne
To: J= akab=20 Laszlo ; pgsql-performance@postgresql.org= =20
Sent: Friday, February 21, 2003 10= :04=20 AM
Subject: Re: [PERFORM] performance= issues=20 for processing more then 150000 records / day.

Hi Jakab,
 
I don't see why postgresql wouldn't be a= ble to=20 handle that - it's a relatively small amount of data.
 
When you say 150000 records/day, do you= =20 mean:
 
1. 150000 inserts/day (ie. database grow= s=20 constantly, quickly)
2. 150000 updates/deletes/day
3. 150000 transactions/select/day=
 
If you give us a little more information= , we'll=20 be able to help you tune your postgres for your application.
 
Regards,
 
Chris
 
----- Original Message -----
F= rom:=20 Jakab=20 Laszlo
To: pgsql-performance@postgresql.o= rg=20
Sent: Friday, February 21, 2003 = 3:48=20 PM
Subject: [PERFORM] performance i= ssues=20 for processing more then 150000 records / day.

Hello,
 
We would like to use PostgreSQL for on= e=20 project.
This project would need to handle some= of=20 150000 - 200000 (thousands records) per day.
 
Is there somebody having experience wi= th=20 Postgresql in this kind of environment.
Can anybody advice us regarding specif= ic=20 PostgreSQL issues for such kind of datamanipulation ?
 
Best Regards,
Jaki
------=_NextPart_000_0079_01C2D994.B49FE480-- From shridhar_daithankar@persistent.co.in Fri Feb 21 08:44:33 2003 X-Original-To: pgsql-performance@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 7F1D4475C45 for ; Fri, 21 Feb 2003 03:44:33 -0500 (EST) Received: from postgresql.org ([64.49.215.8]) by localhost (postgresql.org [64.49.215.8:10024]) (amavisd-new) with SMTP id 50184-08 for ; Fri, 21 Feb 2003 03:44:22 -0500 (EST) Received: from www.pspl.co.in (www.pspl.co.in [202.54.11.65]) by postgresql.org (Postfix) with ESMTP id 4F8C547592C for ; Fri, 21 Feb 2003 03:41:45 -0500 (EST) Received: (from root@localhost) by www.pspl.co.in (8.11.6/8.11.6) id h1L8fj425011 for ; Fri, 21 Feb 2003 14:11:45 +0530 Received: from daithan (daithan.intranet.pspl.co.in [192.168.7.161]) by www.pspl.co.in (8.11.6/8.11.0) with ESMTP id h1L8fjt25006 for ; Fri, 21 Feb 2003 14:11:45 +0530 From: "Shridhar Daithankar" To: Date: Fri, 21 Feb 2003 14:12:25 +0530 MIME-Version: 1.0 Subject: Re: performance issues for processing more then 150000 records / day. Reply-To: shridhar_daithankar@persistent.co.in Message-ID: <3E5633C9.27527.439F17A@localhost> In-reply-to: <007c01c2d983$f3d60d70$0900000a@new> X-mailer: Pegasus Mail for Windows (v4.02) Content-type: text/plain; charset=US-ASCII Content-transfer-encoding: 7BIT Content-description: Mail message body X-Virus-Scanned: by amavisd-new X-Archive-Number: 200302/146 X-Sequence-Number: 1202 On 21 Feb 2003 at 10:33, Jakab Laszlo wrote: > > Hello Chris, > > I mean here 150000 inserts/day ( quickly grows constantly )... - with > transactions and on the same table ... maybe after onesignificant amount of > time we can move the records of one year to one archivetable ... > Andsome 2-3 millions of selects / day ... > > I would like to know also some hardware related advice. Use a 64 bit machine with SCSI, preferrably RAID to start with. You can search list archives for similar problems and solutions. HTH Bye Shridhar -- Fog Lamps, n.: Excessively (often obnoxiously) bright lamps mounted on the fronts of automobiles; used on dry, clear nights to indicate that the driver's brain is in a fog. See also "Idiot Lights". From justin@postgresql.org Fri Feb 21 09:14:50 2003 X-Original-To: pgsql-performance@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 4FFDD474E5C for ; Fri, 21 Feb 2003 04:14:50 -0500 (EST) Received: from postgresql.org ([64.49.215.8]) by localhost (postgresql.org [64.49.215.8:10024]) (amavisd-new) with SMTP id 64493-01 for ; Fri, 21 Feb 2003 04:14:39 -0500 (EST) Received: from grunt26.ihug.com.au (grunt26.ihug.com.au [203.109.249.146]) by postgresql.org (Postfix) with ESMTP id 5AEC8474E4F for ; Fri, 21 Feb 2003 04:14:39 -0500 (EST) Received: from p148-tnt1.adl.ihug.com.au (postgresql.org) [203.173.248.148] by grunt26.ihug.com.au with esmtp (Exim 3.35 #1 (Debian)) id 18m9GP-0007hx-00; Fri, 21 Feb 2003 20:14:38 +1100 Message-ID: <3E55EE62.6010909@postgresql.org> Date: Fri, 21 Feb 2003 19:46:18 +1030 From: Justin Clift User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.2.1) Gecko/20021130 X-Accept-Language: en-us, en MIME-Version: 1.0 To: Jakab Laszlo Cc: Christopher Kings-Lynne , pgsql-performance@postgresql.org Subject: Re: performance issues for processing more then 150000 References: <004501c2d97d$98927670$0900000a@new> <0a6901c2d97f$ea1369d0$6500a8c0@fhp.internal> <007c01c2d983$f3d60d70$0900000a@new> In-Reply-To: <007c01c2d983$f3d60d70$0900000a@new> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new X-Archive-Number: 200302/147 X-Sequence-Number: 1203 Jakab Laszlo wrote: > Hello Chris, > > I mean here 150000 inserts/day ( quickly grows constantly )... - with > transactions and on the same table ... maybe after one significant > amount of time we can move the records of one year to one archive table ... > And some 2-3 millions of selects / day ... That's no problem at all, depending on: + How complex are the queries you're intending on running? + How will the data be spread between the tables? + The amount of data per row also makes a difference, if it is extremely large. > I would like to know also some hardware related advice. You're almost definitely going to be needing a SCSI or better RAID array, plus a server with quite a few GB's of ECC memory. If you need to get specific about hardware to the point of knowing exactly what you're needing, you're likely best to pay a good PostgreSQL consultant to study your proposal in depth. Hope this helps. Regards and best wishes, Justin Clift > thanks, > Jaki -- "My grandfather once told me that there are two kinds of people: those who work and those who take the credit. He told me to try to be in the first group; there was less competition there." - Indira Gandhi From jakablaszlo@sofasoft.ro Fri Feb 21 09:54:23 2003 X-Original-To: pgsql-performance@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 31CC747580B; Fri, 21 Feb 2003 04:54:23 -0500 (EST) Received: from postgresql.org ([64.49.215.8]) by localhost (postgresql.org [64.49.215.8:10024]) (amavisd-new) with SMTP id 72194-04; Fri, 21 Feb 2003 04:54:12 -0500 (EST) Received: from saturn.hiddenservers.com (saturn.hiddenservers.com [209.123.207.12]) by postgresql.org (Postfix) with ESMTP id 6B917474E61; Fri, 21 Feb 2003 04:54:12 -0500 (EST) Received: from [213.154.101.33] (helo=new) by saturn.hiddenservers.com with smtp (Exim 3.36 #1) id 18m9sj-0003zr-00; Fri, 21 Feb 2003 04:54:13 -0500 Message-ID: <00e101c2d98f$ea9a32c0$0900000a@new> From: "Jakab Laszlo" To: "Justin Clift" Cc: References: <004501c2d97d$98927670$0900000a@new> <0a6901c2d97f$ea1369d0$6500a8c0@fhp.internal> <007c01c2d983$f3d60d70$0900000a@new> <3E55EE62.6010909@postgresql.org> Subject: Re: performance issues for processing more then 150000 Date: Fri, 21 Feb 2003 11:59:24 +0200 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.00.2919.6700 X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2919.6700 X-AntiAbuse: This header was added to track abuse, please include it with any abuse report X-AntiAbuse: Primary Hostname - saturn.hiddenservers.com X-AntiAbuse: Original Domain - postgresql.org X-AntiAbuse: Originator/Caller UID/GID - [0 0] / [0 0] X-AntiAbuse: Sender Address Domain - sofasoft.ro X-Virus-Scanned: by amavisd-new X-Archive-Number: 200302/148 X-Sequence-Number: 1204 > Jakab Laszlo wrote: > > Hello Chris, > > > > I mean here 150000 inserts/day ( quickly grows constantly )... - with > > transactions and on the same table ... maybe after one significant > > amount of time we can move the records of one year to one archive table ... > > And some 2-3 millions of selects / day ... > > That's no problem at all, depending on: > > + How complex are the queries you're intending on running? > + How will the data be spread between the tables? > > + The amount of data per row also makes a difference, if it is > extremely large. > The main information is a barcode. The big table will have aprox 3 fields (just id type fields). Not so complex there will be mainly about 50 tables with all the info totally normalized and with some around 10-20 tables which make the links between them. Usually the join is starting form one much smaller table and with left join goes trough the big table (barcodes) and maybe also on other tables. And then this table with barcode (this is the bigone with 150000 records/day) linked to other smaller tables. ( 1 bigger with aprox 7000 inserts/day). > > I would like to know also some hardware related advice. > > You're almost definitely going to be needing a SCSI or better RAID > array, plus a server with quite a few GB's of ECC memory. Unfortunatelly the hardware budget should be keept as low as possible. I was thinking is there could be reliable solution based on dual processor and ATA 133 raid mirroring normally with some gigs of memory. Thanks, Jaki From shridhar_daithankar@persistent.co.in Fri Feb 21 10:32:41 2003 X-Original-To: pgsql-performance@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 24A7747580B for ; Fri, 21 Feb 2003 05:32:41 -0500 (EST) Received: from postgresql.org ([64.49.215.8]) by localhost (postgresql.org [64.49.215.8:10024]) (amavisd-new) with SMTP id 76163-06 for ; Fri, 21 Feb 2003 05:32:30 -0500 (EST) Received: from www.pspl.co.in (www.pspl.co.in [202.54.11.65]) by postgresql.org (Postfix) with ESMTP id 8C7324758FE for ; Fri, 21 Feb 2003 05:31:45 -0500 (EST) Received: (from root@localhost) by www.pspl.co.in (8.11.6/8.11.6) id h1LAVlG07198 for ; Fri, 21 Feb 2003 16:01:47 +0530 Received: from daithan (daithan.intranet.pspl.co.in [192.168.7.161]) by www.pspl.co.in (8.11.6/8.11.0) with ESMTP id h1LAVlt07192 for ; Fri, 21 Feb 2003 16:01:47 +0530 From: "Shridhar Daithankar" To: Date: Fri, 21 Feb 2003 16:02:26 +0530 MIME-Version: 1.0 Subject: Re: performance issues for processing more then 150000 Reply-To: shridhar_daithankar@persistent.co.in Message-ID: <3E564D92.8962.49EAB1F@localhost> In-reply-to: <00e101c2d98f$ea9a32c0$0900000a@new> X-mailer: Pegasus Mail for Windows (v4.02) Content-type: text/plain; charset=US-ASCII Content-transfer-encoding: 7BIT Content-description: Mail message body X-Virus-Scanned: by amavisd-new X-Archive-Number: 200302/149 X-Sequence-Number: 1205 On 21 Feb 2003 at 11:59, Jakab Laszlo wrote: > Unfortunatelly the hardware budget should be keept as low as possible. > I was thinking is there could be reliable solution based on dual processor > and ATA 133 raid mirroring normally with some gigs of memory. Gigs of memory are not as much important as much badnwidth you have. For these kind of databases, a gig or two would not make as much difference as much faster disks would do. If you are hell bent on budget, I suggest you write a custom layer that consolidates results of query from two boxes and throw two intel boxes at it. Essentially partitioning the data. If your queries are simple enough to split and consolidate, try it. It might give you best performance.. Bye Shridhar -- Blutarsky's Axiom: Nothing is impossible for the man who will not listen to reason. From josh@agliodbs.com Sun Feb 23 20:46:29 2003 X-Original-To: pgsql-performance@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id C96DD475921; Sun, 23 Feb 2003 15:46:29 -0500 (EST) Received: from postgresql.org ([64.49.215.8]) by localhost (postgresql.org [64.49.215.8:10024]) (amavisd-new) with SMTP id 18958-05; Sun, 23 Feb 2003 15:46:19 -0500 (EST) Received: from davinci.ethosmedia.com (unknown [209.10.40.251]) by postgresql.org (Postfix) with ESMTP id 992D947590C; Sun, 23 Feb 2003 15:44:50 -0500 (EST) Received: from [63.195.55.98] (HELO spooky) by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.0.2) with ESMTP id 2853792; Sun, 23 Feb 2003 12:44:50 -0800 Content-Type: text/plain; charset="iso-8859-1" From: Josh Berkus Organization: Aglio Database Solutions To: "Jakab Laszlo" , "Justin Clift" Subject: Re: performance issues for processing more then 150000 Date: Sun, 23 Feb 2003 12:44:06 -0800 User-Agent: KMail/1.4.3 Cc: References: <004501c2d97d$98927670$0900000a@new> <3E55EE62.6010909@postgresql.org> <00e101c2d98f$ea9a32c0$0900000a@new> In-Reply-To: <00e101c2d98f$ea9a32c0$0900000a@new> MIME-Version: 1.0 Message-Id: <200302231244.06242.josh@agliodbs.com> Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: by amavisd-new X-Archive-Number: 200302/165 X-Sequence-Number: 1221 Jakab, Some simple tips, which is what I thing you were really asking for: Are you adding the records in a single overnight or downtime load batch? I= f=20 so, the fastest way by far is to: 1) disable all triggers and constraints on the table temporarily, and some = or=20 all of the indexes 2) put all the data into a COPY file (tab-delimited text; see COPY docs) 3) load the data through a COPY statement 4) re-enable the trigger and constraints and re-build the indexes The degree to which you need to do 1) and 4) depends on how much punishment= =20 your system can take; start out by dropping and rebuilding just the trigger= s=20 and move up from there until the load finishes in a satisfactory time. If the records are being added on a continuous basis and not in a big batch= ,=20 then take the following precautions: 1) put as many inserts as possible into transaction batches 2) minimize your use of constraints, triggers, and indexes on the tables be= ing=20 loaded 3) consdier using a "buffer table" to hold records about to be loaded while= =20 data integrity checks and similar are performed. > Unfortunatelly the hardware budget should be keept as low as possible. > I was thinking is there could be reliable solution based on dual processor > and ATA 133 raid mirroring normally with some gigs of memory. 1 gig of RAM may be plenty. Your main bottleneck will be your disk channel= .=20=20 If I were setting up your server, I might do something like: 1) buy a motherboard with 4 ATA controllers. 2) put disks like: channel 0: 1 matched pair disks channel 1 + 2: 1 matched quartet of disks channel 3: single ATA disk (for Postgresql, more, smaller disks is almost always better than a few bi= g=20 ones.) (alternately, set up everythin in one big RAID 5 array with at least= 6=20 disks. There is argument about which is better) 3) Format the above as a RAID 1 pair on channel 0 and a RAID 1+0 double pai= r=20 on channel 1 using Linux software RAID 4) Put Linux OS + swap on channel 0. Put the database on channel 1+2. Put= =20 the pg_xlog (the transaction log) on channel 3. Make sure to use a version= =20 of Linux with kernel 2.4.19 or greater! That's just one configuration of several possible, of course, but may serve= =20 your purposes admirably and relatively cheaply. --=20 Josh Berkus Aglio Database Solutions San Francisco