X-Original-To: pgsql-admin-postgresql.org@postgresql.org Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id 7447A9FB1BF for ; Mon, 7 Aug 2006 05:54:39 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 69241-09 for ; Mon, 7 Aug 2006 08:54:30 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey- Received: from my.endian.it (unknown [62.146.87.34]) by postgresql.org (Postfix) with ESMTP id 3E0A89FB1BE for ; Mon, 7 Aug 2006 05:54:30 -0300 (ADT) Received: from dell.home.lan (host177-104.pool875.interbusiness.it [87.5.104.177]) (authenticated (0 bits)) by my.endian.it (8.11.6/8.11.6) with ESMTP id k77928T28555; Mon, 7 Aug 2006 11:02:08 +0200 Subject: Re: Performance tuning question From: Chris Mair To: Benjamin Krajmalnik Cc: pgsql-admin@postgresql.org In-Reply-To: References: Content-Type: text/plain Date: Mon, 07 Aug 2006 10:54:24 +0200 Message-Id: <1154940864.4342.13.camel@dell.home.lan> Mime-Version: 1.0 X-Mailer: Evolution 2.0.2 (2.0.2-27) Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.135 tagged_above=0 required=5 tests=FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200608/69 X-Sequence-Number: 22613 On Mon, 2006-08-07 at 02:18 -0600, Benjamin Krajmalnik wrote: > I just migrated from PG 8.1.4 Windows to 8.1.4 FreeBSD/i386. Good move :) > 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. A 5->40 ms bump might be completely normal if you go from an empty table to one holding many records. I take it your table has some indexes, probably a primary key. Inserting in such a table is not a constant time operation - I guess it's O(log(n)), meaning it increases like a logarithmic function. > 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 would suppose so too. That'd rule out 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. You should find out, whether you're CPU-bound or disk-bound (likely the latter) - can you send 1 minues worth of output of "vmstat 10"? > 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=16 > > > > 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 Looks like you're not using the box 100%. Probably your client cannot keep up with the server. Are you sure you do have a performance problem at all? Bye, Chris. -- Chris Mair http://www.1006.org