X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 1DB1BD1D803 for ; Wed, 14 Apr 2004 11:54:35 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 61524-01 for ; Wed, 14 Apr 2004 11:54:37 -0300 (ADT) Received: from localhost.localdomain (webe.ne.client2.attbi.com [24.61.34.112]) by svr1.postgresql.org (Postfix) with ESMTP id 6FE57D1D069 for ; Wed, 14 Apr 2004 11:54:32 -0300 (ADT) Received: from [172.31.1.141] (klopfer.mazunetworks.com [4.19.249.110]) by localhost.localdomain (8.12.8/8.12.8) with ESMTP id i3EEtsBL005969 for ; Wed, 14 Apr 2004 10:55:54 -0400 Subject: Performance tuning for copy in From: Joe Maldonado To: pgsql-admin@postgresql.org Content-Type: text/plain Message-Id: <1081954461.3941.17.camel@joem> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.5 (1.4.5-7) Date: Wed, 14 Apr 2004 10:54:22 -0400 Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200404/220 X-Sequence-Number: 13193 Hello, Currently I can get no more than 40k records/second to be copied in to postgres. This is also the case when running multiple cocurrent copy in commands from different connections. It seems linear in that 1 process will copy in at 40k/s and 2 will copy in at about 20k/s, etc. I need to know whether I can get better performance from the db with the hardware specified below. Changing from mirrored to stripe RAID is not an option :( at this time as is turning off fsync (which did not work much better). -Joe Maldonado Configuration: PostgreSQL 7.4 configure --libdir=/usr/lib \ --includedir=/usr/include \ --bindir=/usr/bin \ --with-krb5=/usr/kerberos \ --with-openssl=/usr \ --enable-thread-safety \ --enable-debug OS: RedHat 9 Drive Config: sdb1: /pg_clog sdb2: /pg_xlog sdc1: /pg_data (RAID 1) hdparm output shows average of 45MB/s iostat shows fairly constant 10MB/s written to pg_data and 5-8MB/s to WAL. CPU: 2x 2.0G Xeon