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