pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: adey <adey11@gmail.com>
To: Benjamin Krajmalnik <kraj@illumen.com>
Cc: Chris Mair <chrisnospam@1006.org>
Cc: pgsql-admin@postgresql.org
Subject: Re: Performance tuning question
Date: Thu, 10 Aug 2006 12:27:07 +1200
Message-ID: <1c66bda80608091727s73e38ed6h2d52c5bb27d026b9@mail.gmail.com> (raw)
In-Reply-To: <BF337097BDD9D849A2F4B818DDB279872525A6@stash.stackdump.local>
References: <BF337097BDD9D849A2F4B818DDB279872525A6@stash.stackdump.local>

A vacuum full analyze might help.

On 8/8/06, Benjamin Krajmalnik <kraj@illumen.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@1006.org]
> > Sent: Monday, August 07, 2006 4:38 PM
> > To: Benjamin Krajmalnik
> > Cc: pgsql-admin@postgresql.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
>

view thread (7+ messages)

Message-ID: <1c66bda80608091727s73e38ed6h2d52c5bb27d026b9@mail.gmail.com>
Permalink:  ../1c66bda80608091727s73e38ed6h2d52c5bb27d026b9@mail.gmail.com/
Also on:    postgresql.org/message-id/1c66bda80608091727s73e38ed6h2d52c5bb27d026b9@mail.gmail.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: adey11@gmail.com, kraj@illumen.com, chrisnospam@1006.org
  Subject: Re: Performance tuning question
  In-Reply-To: <1c66bda80608091727s73e38ed6h2d52c5bb27d026b9@mail.gmail.com>
  References: <BF337097BDD9D849A2F4B818DDB279872525A6@stash.stackdump.local> <1c66bda80608091727s73e38ed6h2d52c5bb27d026b9@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox