Chris, I just finished running some benchmarks on an underpowered server compared to the one I am running in production. My initial tests were run on an ampty database, pg_xlog on the same spindle. Stored procedure execution speed was ~15 ms. I then restored the production database so I would have ~3GB database. Execution time for the stored procedure went up to about 40 ms average (with a miuch higher variance), but with a processing speed of 18 stored procedure calls per second. I them moved pg_xlog to a separate spindle. Execution time went down to about 17 ms Beyond moving pg_xlog to a separate spindle, are there any other things you can think of which may improve the performance? > -----Original Message----- > From: Chris Mair [mailto:chrisnospam@xxxxxxxx] > Sent: Monday, August 07, 2006 4:38 PM > To: Benjamin Krajmalnik > Cc: pgsql-admin@xxxxxxxxxxxxxx > Subject: RE: [ADMIN] Performance tuning question > > > > 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. > > Having pg_xlog on another disk than the data itselft helps a > lot for frequent writes/updates. > > Still it's not so clear to me on *where* exactly your > performance problem is. Is it that 40 msec time you > mentioned? On *average* your machine doesn't seem to be > overloaded at all from reading vmstat's output. Do you need > do have this call terminate in less than 40 msec, even though > average load is no problem? Then you have a responsivness > problem, and not an easy one, I'm afraid :/ FreeBSD (or > Linux) are not real time systems that can guarantee they will > complete something within msecs. > > If this is the case (and I'm a bit guessing here), I'm afraid > you need to buffer data in the client. > > > > 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. > > Does it buffer at all? > > > Worst case, I will virtualize the monitroing agent, but that will > > require quite a bit of work on our side. > > > Bye, Chris. > > -- > > Chris Mair > http://www.1006.org > > >
