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.