Re: How to get higher tps

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(at)gmail(dot)com]
> Sent: Tuesday, August 22, 2006 11:27 AM
> To: Mark Lewis
> Cc: Marty Jia; Joshua D. Drake; pgsql-performance(at)postgresql(dot)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(at)gmail(dot)com <mailto:armtuk(at)gmail(dot)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(dot)lewis(at)mir3(dot)com
> <mailto:mark(dot)lewis(at)mir3(dot)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(at)commandprompt(dot)com]
> > Sent: Monday, August 21, 2006 6:09 PM
> > To: Marty Jia
> > Cc: pgsql-performance(at)postgresql(dot)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(at)postgresql(dot)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/