X-Original-To: pgsql-admin-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 48E5C9FB20B for ; Wed, 23 Aug 2006 11:30:45 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 94788-05 for ; Wed, 23 Aug 2006 11:30:22 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from web50301.mail.yahoo.com (web50301.mail.yahoo.com [206.190.38.55]) by postgresql.org (Postfix) with SMTP id F2A349FB1F2 for ; Wed, 23 Aug 2006 11:30:22 -0300 (ADT) Received: (qmail 98257 invoked by uid 60001); 23 Aug 2006 14:30:17 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=Message-ID:Received:Date:From:Subject:To:MIME-Version:Content-Type:Content-Transfer-Encoding; b=sIXVuPLE4hw9KAHRxTFW9A/FAJSIi/NVzdHpq5EAa2XJhUoEzosd7rIuEd6G5xYx+WiozhIPUgL9RoN5KHB94bKUqwzYNRnZicHDjkkWW2z0ZEuwNf69WsDwR+jGgDA+tSDQCBK81Te204avEUfxEfZFRM5lIUsVDWa/xCmRmH4= ; Message-ID: <20060823143017.98255.qmail@web50301.mail.yahoo.com> Received: from [193.163.1.105] by web50301.mail.yahoo.com via HTTP; Wed, 23 Aug 2006 07:30:17 PDT Date: Wed, 23 Aug 2006 07:30:17 -0700 (PDT) From: Thor Tall Subject: Slow delete/insert. To: pgsql-admin@postgresql.org MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=1.677 tagged_above=0 required=5 tests=AWL X-Spam-Level: * X-Archive-Number: 200608/245 X-Sequence-Number: 22788 I am in the process of converting an old system to a new system where I have chosen to use postgres in stead of a home grow system based on b-trees. The system receives 2650 message a total of 10Mbytes of data per 15 minutes this information have to be store in 4 tables in the database. Some of the messages are updates to existing data and the result is: 1038 records in table 1 size per record 282 bytes 2074 records in table 2 size per record 36 bytes 21488 records in table 3 size per record 60 bytes 25676 records in table 4 size per record 42 bytes. Without indexes this should be something like 2,7Mbytes/15min. The disk speed is as follows taken from hdparm -Tt /dev/hda: Timing buffer-cache reads: 128 MB in 0.89 seconds =143.82 MB/sec Timing buffered disk reads: 64 MB in 1.45 seconds = 44.14 MB/sec My problem is that the postmaster process is using between 70-90% of the CPU time. (seen using top) and the total wall time with nothing else running. It takes 6 minutes to ingest the data with an empty database and 25 minutes (wall time) if all the data is already in the database. The processing is done as follows: 1. Start transaction 2. check if message is in table 1 and if so delete records from table 1(1 row),2(2 rows),3(30 rows),4(50 rows). 3. split the message and insert the date in table 1(1 row),2(2 rows),3(30 rows),4(50 rows) 4. end transaction I need some suggestions so that I can ingest the data faster. with the same computer and disks. We are running redhat 7.2 on the server and I am using the c interface to write the data to the server. Thanks for any help, Thor. __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com