Re: Performance tuning question

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(at)1006(dot)org]
> Sent: Monday, August 07, 2006 4:38 PM
> To: Benjamin Krajmalnik
> Cc: pgsql-admin(at)postgresql(dot)org
> 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
>
>
>