X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 7E959D1B564 for ; Mon, 27 Oct 2003 16:28:23 +0000 (GMT) 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 83121-03 for ; Mon, 27 Oct 2003 12:27:52 -0400 (AST) Received: from mailserv.mobilecohesion.com (picard.mobilecohesion.com [80.4.157.11]) by svr1.postgresql.org (Postfix) with ESMTP id 4003FD1B522 for ; Mon, 27 Oct 2003 12:27:51 -0400 (AST) Received: from pestilence.mobilecohesion.com ([10.12.10.31] helo=pestilence) by mailserv.mobilecohesion.com with esmtp (Exim 4.10) id 1AEADc-0000ZK-00 for pgsql-performance@postgresql.org; Mon, 27 Oct 2003 16:27:48 +0000 Content-Type: text/plain; charset="us-ascii" From: Damien Dougan Organization: Mobile Cohesion To: pgsql-performance@postgresql.org Subject: Very Poor Insert Performance Date: Mon, 27 Oct 2003 16:26:52 +0000 User-Agent: KMail/1.4.1 MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Message-Id: <200310271626.52839.damien.dougan@mobilecohesion.com> X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/678 X-Sequence-Number: 4426 Hi All, We've been experiencing extremely poor batch upload performance on our=20 Postgres 7.3 (and 7.3.4) database, and I've not been able to improve matter= s=20 significantly using any suggestions I've gleamed off the mailing list=20 archives ... so I was wondering if anyone with a bigger brain in this area= =20 could help :) Our batch upload is performing a number of stored procedures to insert data= on=20 the database. Initially, this results in quite good performance, but rapidl= y=20 spirals down to approximately 1 per second after some minutes. I've got a script that generates stored procedure calls to upload test inpu= t=20 data, and the script is capable of inserting BEGIN and END at different=20 intervals, together with VACUUM ANALYZE commands as well. I've tried varying the commit level from every operation, every 5, every 10= ,=20 every 25, every 100 operations (again, each operation is 5 SP calls) withou= t=20 any noticeable improvement. Likewise, I've varied the VACUUM ANALYZE from= =20 every 50 to every 100 operations - again without any measurable improvement. top reports that CPU usage is pretty constant at 99%, and there is=20 approximately 1GB of free physical memory available to the OS (with=20 approximately 1GB of physical memory in use). I've have been running postmaster with switched fsync off. I also tried running with backbuffers of default (64), 128, 256, 512 and ev= en=20 1024. Again, with no measurable change. The typical metrics are (completed operations - each of these require 5 SP= =20 calls): 1 min: 1036 (1036 operations) 2 min: 1426 (390 operations) 3 min: 1756 (330 operations) 4 min: 2026 (270 operations) 5 min: 2266 (240 operations) When left running, its not too long before the code snails to 1 operation p= er=20 second. Has anyone any ideas as to what could be causing the spiraling performance? With approximately 20,000 operations commited in the database, it takes abo= ut=20 1 minute to upload a dump of the database - unfortunately we cannot use the= =20 COPY command to upload brand new data - it really has to go through the=20 Stored Procedures to ensure relationships and data integrity across the=20 schema (it would be very difficult to develop and maintain code to generate= =20 COPY commands for inserting new data). And whilst I appreciate INSERTs are= =20 inherently slower than COPY, I was hoping for something significantly faste= r=20 than the 1 operation/second that things fairly quickly descend to... Thanks for any advice! Damien