Re: 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> 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> 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
> >
>
>