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 3F9BE9FB364 for ; Tue, 22 Aug 2006 13:16:19 -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 66900-01 for ; Tue, 22 Aug 2006 16:15:59 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey- Received: from lists.commandprompt.com (host-130.commandprompt.net [207.173.203.130]) by postgresql.org (Postfix) with ESMTP id 2CADE9FB337 for ; Tue, 22 Aug 2006 13:15:58 -0300 (ADT) Received: from [192.168.1.50] (or-67-76-146-141.sta.embarqhsd.net [67.76.146.141]) (authenticated bits=0) by lists.commandprompt.com (8.13.7/8.13.6) with ESMTP id k7MGFkwu009509 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=NO); Tue, 22 Aug 2006 09:15:47 -0700 Message-ID: <44EB2DB0.60707@commandprompt.com> Date: Tue, 22 Aug 2006 09:15:44 -0700 From: "Joshua D. Drake" Organization: Command Prompt, Inc. User-Agent: Thunderbird 1.5.0.5 (X11/20060728) MIME-Version: 1.0 To: Marty Jia CC: Alex Turner , Mark Lewis , pgsql-performance@postgresql.org, DBAs , Rich Wilson , Ernest Wurzbach Subject: Re: How to get higher tps References: <0B9A8C89DCC3AB488A78A4DE0FECDA90019E32A7@SITE3MAIL04.jeeves.ask.info> In-Reply-To: <0B9A8C89DCC3AB488A78A4DE0FECDA90019E32A7@SITE3MAIL04.jeeves.ask.info> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: ClamAV version 0.88.3, clamav-milter version 0.88.3 on projects.commandprompt.com X-Virus-Status: Clean X-Greylist: Sender succeded SMTP AUTH authentication, not delayed by milter-greylist-1.6 (lists.commandprompt.com [192.168.2.159]); Tue, 22 Aug 2006 09:15:52 -0700 (PDT) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=3.951 tagged_above=0 required=5 tests=FORGED_RCVD_HELO, SPF_HELO_SOFTFAIL, SPF_SOFTFAIL X-Spam-Level: *** X-Archive-Number: 200608/347 X-Sequence-Number: 20532 Marty Jia wrote: > Here is iostat when running pgbench: > > avg-cpu: %user %nice %sys %iowait %idle > 26.17 0.00 8.25 23.17 42.42 You are are a little io bound and fairly cpu bound. I would be curious if your performance goes down if you increase the number of connections you are using. Joshua D. Drake > > Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn > sda 0.00 0.00 0.00 0 0 > sda1 0.00 0.00 0.00 0 0 > sda2 0.00 0.00 0.00 0 0 > sda3 0.00 0.00 0.00 0 0 > sda4 0.00 0.00 0.00 0 0 > sda5 0.00 0.00 0.00 0 0 > sda6 0.00 0.00 0.00 0 0 > sda7 0.00 0.00 0.00 0 0 > sdb 0.00 0.00 0.00 0 0 > sdb1 0.00 0.00 0.00 0 0 > sdb2 0.00 0.00 0.00 0 0 > sdb3 0.00 0.00 0.00 0 0 > sdb4 0.00 0.00 0.00 0 0 > sdb5 0.00 0.00 0.00 0 0 > sdb6 0.00 0.00 0.00 0 0 > sdb7 0.00 0.00 0.00 0 0 > sdc 0.00 0.00 0.00 0 0 > sdd 0.00 0.00 0.00 0 0 > sde 0.00 0.00 0.00 0 0 > sdf 0.00 0.00 0.00 0 0 > sdg 0.00 0.00 0.00 0 0 > sdh 0.00 0.00 0.00 0 0 > sdi 40.33 0.00 413.33 0 1240 > sdj 34.33 0.00 394.67 0 1184 > sdk 36.00 0.00 410.67 0 1232 > sdl 37.00 0.00 429.33 0 1288 > sdm 375.00 0.00 3120.00 0 9360 > sdn 378.33 0.00 3120.00 0 9360 > > ________________________________ > > From: Alex Turner [mailto:armtuk@gmail.com] > Sent: Tuesday, August 22, 2006 11:27 AM > To: Mark Lewis > Cc: Marty Jia; Joshua D. Drake; pgsql-performance@postgresql.org; DBAs; > Rich Wilson; Ernest Wurzbach > Subject: Re: [PERFORM] How to get higher tps > > > 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 > > > > > -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/