Chris, Thanks for your assistance. isweb01# vmstat 10 procs memory page disks faults cpu r b w avm fre flt re pi po fr sr ad4 ad6 in sy cs us sy id 1 0 0 648368 47052 10322 0 0 0 7505 136 0 0 839 6241 2114 18 10 71 1 0 0 651392 42464 9823 0 0 0 6624 0 0 0 667 5374 1703 16 10 73 0 0 0 648368 42316 9672 0 0 0 6677 0 0 0 652 5290 1674 16 10 74 1 0 0 650300 39840 6843 0 0 0 4695 0 0 0 866 6123 2217 15 10 76 0 0 0 648388 39540 6913 0 0 0 4808 0 0 0 1279 9694 3367 18 10 72 1 0 0 649764 36780 10528 0 0 0 7337 0 0 0 1182 9207 3127 23 11 66 1 0 0 651372 33180 13763 0 0 0 9392 0 0 0 1129 9458 2950 26 13 61 1 0 0 651452 57444 14711 0 0 0 10087 666 0 0 889 8044 2315 23 13 63 1 0 0 650664 55956 12388 0 0 0 8479 0 0 0 773 6791 2006 20 11 68 2 0 0 649632 55152 10621 0 0 0 7256 0 0 0 805 5811 1985 18 11 71 I have increased the shared memory by 50%, and temp_buffers to 5000, but no noticeable difference in speed. As I mentioned, the system has 2 drives in RAID-1, so pg_xlog is on the same disk. Would moving pg_xlog to a different disk increase the performance? The server I am currently running this on is a temporary server while I rebuild our main data server which is SCSI. Right now I am going to test a few things on a secondary dev server I set (old server with IDE). This one has 2 drives, so I will run some tests with pg_xlog on the same drive and on a separate drive. Also, I will load the data on an empty database as well as a restored database. I really need to find a way to make this faster :( The monitoring agent which we use has a single logging thread, and if the database does not keep up with it it will stall. Worst case, I will virtualize the monitroing agent, but that will require quite a bit of work on our side. > -----Original Message----- > From: Chris Mair [mailto:chrisnospam@xxxxxxxx] > Sent: Monday, August 07, 2006 2:54 AM > To: Benjamin Krajmalnik > Cc: pgsql-admin@xxxxxxxxxxxxxx > Subject: Re: [ADMIN] Performance tuning question > > 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 > > >
