X-Original-To: pgsql-performance@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 5385D475C45 for ; Fri, 21 Feb 2003 10:34: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 05702-03 for ; Fri, 21 Feb 2003 10:34:05 -0500 (EST) Received: from maverick.digital-ics.com (maverick.digital-ics.com [63.239.101.196]) by postgresql.org (Postfix) with ESMTP id 117E74758FE for ; Fri, 21 Feb 2003 10:32:33 -0500 (EST) Received: from digital-ics.com (gremlin.ics.digital-ics.com [192.168.2.52]) by maverick.digital-ics.com (8.12.6/8.12.6) with ESMTP id h1LFWbhm000889 for ; Fri, 21 Feb 2003 10:32:37 -0500 (EST) Message-ID: <3E564695.7030906@digital-ics.com> Date: Fri, 21 Feb 2003 10:32:37 -0500 From: Kevin White User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.3a) Gecko/20021212 X-Accept-Language: en-us, en MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: Really bad insert performance: what did I do wrong? Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new X-Archive-Number: 200302/151 X-Sequence-Number: 1207 I've use PostgreSQL for some pretty amazing things, and was a proponent of using it here. I set up 7.3.1 on my development RedHat 8 box, and it was fine. I need to load about 700,000 rows into one table, the rows only having 6 columns, and the load on my box happens in just a couple of minutes (there's some calculation while the data is loading, and that time was acceptable to me). My box, however, isn't a production server, so I attempted to create the database again on a Sun Blade: SunOS trident 5.8 Generic_108528-17 sun4u sparc SUNW,UltraAX-i2 Status of processor 0 as of: 02/21/03 10:10:10 Processor has been on-line since 01/13/03 13:53:51. The sparcv9 processor operates at 500 MHz, and has a sparcv9 floating point processor. It isn't the world's fastest box, but should be fast enough for this. However... It took almost 2 days to load the table on this box. The postgresql process was constantly eating up all of the CPU time it could get, while loading, at times, less than 1 row a second. Now, I KNOW something is wrong, and it probably isn't postgres. :) I turned off the index on the table (there was only one, on three fields) and made the whole load happen in a transaction. The 2 day result was after those changes were made. I compiled postgres myself, using the gcc 3.2.2 package from sunfreeware. Any thoughts? I know that postgres should be more than capable of loading this data quickly on this box...I figured it could possibly take twice as long...and I thought my problem would be i/o related, not CPU. Thanks! Kevin