X-Original-To: pgsql-performance-postgresql.org@postgresql.org Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id 460959FB2EE for ; Tue, 22 Aug 2006 12:27:41 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 36248-01 for ; Tue, 22 Aug 2006 15:27:28 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from wr-out-0506.google.com (wr-out-0506.google.com [64.233.184.232]) by postgresql.org (Postfix) with ESMTP id 452409FA3AD for ; Tue, 22 Aug 2006 12:27:28 -0300 (ADT) Received: by wr-out-0506.google.com with SMTP id i31so409806wra for ; Tue, 22 Aug 2006 08:27:28 -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=OeRYyDZcmmD0CUzNgRAcy5mg96Tzb3ckNEy9toGmx6LXYtwED5P3q3wqmznfbiIclXiRA2Dt89Tt3uKPUMHC7xlA6VEdZLOpRDBFcfWnGFc/wFmCvhWLcPB19CXW6XM8nLFrG6+EY2WpuJ8P3Ps+LAFYR4b85BCMGpg4rsK70Go= Received: by 10.90.118.10 with SMTP id q10mr538441agc; Tue, 22 Aug 2006 08:27:26 -0700 (PDT) Received: by 10.90.79.9 with HTTP; Tue, 22 Aug 2006 08:27:26 -0700 (PDT) Message-ID: <33c6269f0608220827i77f493d6rb321f29f4a44dccb@mail.gmail.com> Date: Tue, 22 Aug 2006 11:27:26 -0400 From: "Alex Turner" To: "Mark Lewis" Subject: Re: How to get higher tps Cc: "Marty Jia" , "Joshua D. Drake" , pgsql-performance@postgresql.org, DBAs , "Rich Wilson" , "Ernest Wurzbach" In-Reply-To: <33c6269f0608220826u1c2f44b3id8c6816a12f81912@mail.gmail.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_6979_14209717.1156260446918" References: <0B9A8C89DCC3AB488A78A4DE0FECDA90019E31E1@SITE3MAIL04.jeeves.ask.info> <1156257109.9657.93.camel@archimedes> <33c6269f0608220826u1c2f44b3id8c6816a12f81912@mail.gmail.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=4.191 tagged_above=0 required=5 tests=HTML_30_40, HTML_MESSAGE, SPF_HELO_SOFTFAIL, SPF_SOFTFAIL X-Spam-Level: **** X-Archive-Number: 200608/341 X-Sequence-Number: 20526 ------=_Part_6979_14209717.1156260446918 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline Oh - and it's usefull to know if you are CPU bound, or IO bound. Check top or vmstat to get an idea of that Alex On 8/22/06, Alex Turner wrote: > > First things first, run a bonnie++ benchmark, and post the numbers. That > will give a good indication of raw IO performance, and is often the first > inidication of problems separate from the DB. We have seen pretty bad > performance from SANs in the past. How many FC lines do you have running to > your server, remember each line is limited to about 200MB/sec, to get good > throughput, you will need multiple connections. > > When you run pgbench, run a iostat also and see what the numbers say. > > Alex. > > > On 8/22/06, Mark Lewis < mark.lewis@mir3.com> wrote: > > > > Well, at least on my test machines running gnome-terminal, my pgbench > > runs tend to get throttled by gnome-terminal's lousy performance to no > > more than 300 tps or so. Running with 2>/dev/null to throw away all the > > detailed logging gives me 2-3x improvement in scores. Caveat: in my > > case the db is on the local machine, so who knows what all the > > interactions are. > > > > Also, when you initialized the pgbench db what scaling factor did you > > use? And does running pgbench with -v improve performance at all? > > > > -- Mark > > > > On Tue, 2006-08-22 at 09:19 -0400, Marty Jia wrote: > > > Joshua, > > > > > > Here is > > > > > > shared_buffers = 80000 > > > fsync = on > > > max_fsm_pages = 350000 > > > max_connections = 1000 > > > work_mem = 65536 > > > effective_cache_size = 610000 > > > random_page_cost = 3 > > > > > > Here is pgbench I used: > > > > > > pgbench -c 10 -t 10000 -d HQDB > > > > > > Thanks > > > > > > Marty > > > > > > -----Original Message----- > > > From: Joshua D. Drake [mailto:jd@commandprompt.com] > > > Sent: Monday, August 21, 2006 6:09 PM > > > To: Marty Jia > > > Cc: pgsql-performance@postgresql.org > > > Subject: Re: [PERFORM] How to get higher tps > > > > > > Marty Jia wrote: > > > > I'm exhausted to try all performance tuning ideas, like following > > > > parameters > > > > > > > > shared_buffers > > > > fsync > > > > max_fsm_pages > > > > max_connections > > > > shared_buffers > > > > work_mem > > > > max_fsm_pages > > > > effective_cache_size > > > > random_page_cost > > > > > > > > I believe all above have right size and values, but I just can not > > get > > > > > > > higher tps more than 300 testd by pgbench > > > > > > What values did you use? > > > > > > > > > > > Here is our hardware > > > > > > > > > > > > Dual Intel Xeon 2.8GHz > > > > 6GB RAM > > > > Linux 2.4 kernel > > > > RedHat Enterprise Linux AS 3 > > > > 200GB for PGDATA on 3Par, ext3 > > > > 50GB for WAL on 3Par, ext3 > > > > > > > > With PostgreSql 8.1.4 > > > > > > > > We don't have i/o bottle neck. > > > > > > Are you sure? What does iostat say during a pgbench? What parameters > > are > > > you passing to pgbench? > > > > > > Well in theory, upgrading to 2.6 kernel will help as well as making > > your > > > WAL ext2 instead of ext3. > > > > > > > Whatelse I can try to better tps? Someone told me I can should get > > tps > > > > > > > over 1500, it is hard to believe. > > > > > > 1500? Hmmm... I don't know about that, I can get 470tps or so on my > > > measily dual core 3800 with 2gig of ram though. > > > > > > Joshua D. Drake > > > > > > > > > > > > > > Thanks > > > > > > > > Marty > > > > > > > > ---------------------------(end of > > > > broadcast)--------------------------- > > > > TIP 2: Don't 'kill -9' the postmaster > > > > > > > > > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 1: if posting/reading through Usenet, please send an appropriate > > subscribe-nomail command to majordomo@postgresql.org so that your > > message can get through to the mailing list cleanly > > > > ------=_Part_6979_14209717.1156260446918 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline Oh - and it's usefull to know if you are CPU bound, or IO bound.  Check top or vmstat to get an idea of that

Alex

On 8/22/06, Alex Turner < armtuk@gmail.com> wrote:
First things first, run a bonnie++ benchmark, and post the numbers.  That will give a good indication of raw IO performance, and is often the first inidication of problems separate from the DB.  We have seen pretty bad performance from SANs in the past.  How many FC lines do you have running to your server, remember each line is limited to about 200MB/sec, to get good throughput, you will need multiple connections.

When you run pgbench, run a iostat also and see what the numbers say.

Alex.


On 8/22/06, Mark Lewis < mark.lewis@mir3.com> wrote:
Well, at least on my test machines running gnome-terminal, my pgbench
runs tend to get throttled by gnome-terminal's lousy performance to no
more than 300 tps or so.  Running with 2>/dev/null to throw away all the
detailed logging gives me 2-3x improvement in scores.  Caveat: in my
case the db is on the local machine, so who knows what all the
interactions are.

Also, when you initialized the pgbench db what scaling factor did you
use?  And does running pgbench with -v improve performance at all?

-- Mark

On Tue, 2006-08-22 at 09:19 -0400, Marty Jia wrote:
> Joshua,
>
> Here is
>
> shared_buffers = 80000
> fsync = on
> max_fsm_pages = 350000
> max_connections = 1000
> work_mem = 65536
> effective_cache_size = 610000
> random_page_cost = 3
>
> Here is pgbench I used:
>
> pgbench -c 10 -t 10000 -d HQDB
>
> Thanks
>
> Marty
>
> -----Original Message-----
> From: Joshua D. Drake [mailto:jd@commandprompt.com]
> Sent: Monday, August 21, 2006 6:09 PM
> To: Marty Jia
> Cc: pgsql-performance@postgresql.org
> Subject: Re: [PERFORM] How to get higher tps
>
> Marty Jia wrote:
> > I'm exhausted to try all performance tuning ideas, like following
> > parameters
> >
> > shared_buffers
> > fsync
> > max_fsm_pages
> > max_connections
> > shared_buffers
> > work_mem
> > max_fsm_pages
> > effective_cache_size
> > random_page_cost
> >
> > I believe all above have right size and values, but I just can not get
>
> > higher tps more than 300 testd by pgbench
>
> What values did you use?
>
> >
> > Here is our hardware
> >
> >
> > Dual Intel Xeon 2.8GHz
> > 6GB RAM
> > Linux 2.4 kernel
> > RedHat Enterprise Linux AS 3
> > 200GB for PGDATA on 3Par, ext3
> > 50GB for WAL on 3Par, ext3
> >
> > With PostgreSql 8.1.4
> >
> > We don't have i/o bottle neck.
>
> Are you sure? What does iostat say during a pgbench? What parameters are
> you passing to pgbench?
>
> Well in theory, upgrading to 2.6 kernel will help as well as making your
> WAL ext2 instead of ext3.
>
> > Whatelse I can try to better tps? Someone told me I can should get tps
>
> > over 1500, it is hard to believe.
>
> 1500? Hmmm... I don't know about that, I can get 470tps or so on my
> measily dual core 3800 with 2gig of ram though.
>
> Joshua D. Drake
>
>
> >
> > Thanks
> >
> > Marty
> >
> > ---------------------------(end of
> > broadcast)---------------------------
> > TIP 2: Don't 'kill -9' the postmaster
> >
>
>

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to majordomo@postgresql.org so that your
       message can get through to the mailing list cleanly


------=_Part_6979_14209717.1156260446918--