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
