From: Benjamin Krajmalnik <kraj@illumen.com>
To: pgsql-admin@postgresql.org
Subject: Performance tuning question
Date: Mon, 7 Aug 2006 02:18:51 -0600
Message-ID: <BF337097BDD9D849A2F4B818DDB279872525A0@stash.stackdump.local> (raw)
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.
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
Any assistance will be deeply appreciated.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-admin@postgresql.org
Cc: kraj@illumen.com
Subject: Re: Performance tuning question
In-Reply-To: <BF337097BDD9D849A2F4B818DDB279872525A0@stash.stackdump.local>
References: <BF337097BDD9D849A2F4B818DDB279872525A0@stash.stackdump.local>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox