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 3B9059FB206 for ; Mon, 7 Aug 2006 05:19:00 -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 42054-10 for ; Mon, 7 Aug 2006 05:18:53 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from stash.illumen.com (unknown [64.207.29.137]) by postgresql.org (Postfix) with ESMTP id BDDDE9FB1C0 for ; Mon, 7 Aug 2006 05:18:53 -0300 (ADT) Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Subject: Performance tuning question X-MimeOLE: Produced By Microsoft Exchange V6.5 Date: Mon, 7 Aug 2006 02:18:51 -0600 Message-ID: X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: Performance tuning question Thread-Index: Aca5zpQvGFiUNl3eSAG/8IxfL3bqmwAEbxYwAAT2JMA= From: "Benjamin Krajmalnik" To: X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.039 tagged_above=0 required=5 tests=AWL, SPF_PASS X-Spam-Level: X-Archive-Number: 200608/68 X-Sequence-Number: 22612 I just migrated from PG 8.1.4 Windows to 8.1.4 FreeBSD/i386. All of the data insertion to the database is done via a stored procedure call. I did some benchmarking, and on an empty database the execution time of the stored procedure was about 5 ms on average. This was done running via EMS SQL Manager. Now that the database is populated (and it has about 3GB of data, and having the data inserted directly by the monitoring application via ODBC) the execution speed of the stored procedure has gone to above 40 ms. These are the values as reported by logging the data. I assume that the pg_log log is showing the actual execution speed at the server, and it is not including the ODBC overhead. I need some guidance on which parameters to tune. There are 2 tables constantly being updated, and one constantly being inserted to. The 2 being updated are about 170MB, while the one bing inserted to is aout 2 GB maximum. =20 The data server is a 3Gz P4 with 1 GB RAM and SATA RAID-1. Right now it is configured as follows: On a 1GB box, I have shared meory at 256M, 25000 shared buffers, 2000 temp buffers, and work_mem/maintenance_work_mem both set to 128000. I have checkpoint_segments set to 30, wal_buffers=3D16 An analysis via top shows: last pid: 57423; load averages: 0.59, 0.66, 0.63 up 0+11:22:44 01:42:39 62 processes: 1 running, 61 sleeping CPU states: 22.9% user, 0.0% nice, 7.3% system, 5.4% interrupt, 64.4% idle Mem: 140M Active, 480M Inact, 132M Wired, 31M Cache, 110M Buf, 4608K Free Swap: 2005M Total, 188K Used, 2004M Free Any assistance will be deeply appreciated.