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.
