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