Re: Performance tuning question

A vacuum full analyze might help.

On 8/8/06, Benjamin Krajmalnik <kraj(at)illumen(dot)com> wrote:
>
> 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
> >
> >
> >
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>