X-Original-To: pgsql-admin-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 3F5049FB2EF for ; Wed, 9 Aug 2006 21:27:13 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 20742-07 for ; Wed, 9 Aug 2006 21:27:08 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.175]) by postgresql.org (Postfix) with ESMTP id B7D859FB295 for ; Wed, 9 Aug 2006 21:27:08 -0300 (ADT) Received: by ug-out-1314.google.com with SMTP id m3so304758ugc for ; Wed, 09 Aug 2006 17:27:07 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=LsGj54g4qbGiE44oTTgysm0oNwsNjbR6WhRPvgMRZoxnTEDxVfUwNkqIZOziVWgwVzKVuWJW1D31I+l7LQ1Jh7wXjnRY0GsY/ihRwCgnolerh0rHJqxZ5oYbeoZLlbZDLrpxAku9d/lWf08rpXq6yXcPOifGRZdoY0pyYgVe6r0= Received: by 10.67.22.2 with SMTP id z2mr1787229ugi; Wed, 09 Aug 2006 17:27:07 -0700 (PDT) Received: by 10.66.234.17 with HTTP; Wed, 9 Aug 2006 17:27:07 -0700 (PDT) Message-ID: <1c66bda80608091727s73e38ed6h2d52c5bb27d026b9@mail.gmail.com> Date: Thu, 10 Aug 2006 12:27:07 +1200 From: adey To: "Benjamin Krajmalnik" Subject: Re: Performance tuning question Cc: "Chris Mair" , pgsql-admin@postgresql.org In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_59231_11233759.1155169627034" References: X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.622 tagged_above=0 required=5 tests=AWL, DNS_FROM_RFC_ABUSE, HTML_40_50, HTML_MESSAGE, SPF_PASS X-Spam-Level: X-Archive-Number: 200608/121 X-Sequence-Number: 22664 ------=_Part_59231_11233759.1155169627034 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline A vacuum full analyze might help. On 8/8/06, Benjamin Krajmalnik 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 > ------=_Part_59231_11233759.1155169627034 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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

------=_Part_59231_11233759.1155169627034--