Re: How to get higher tps

----------- Here is vmstat

procs memory swap io system
cpu
r b swpd free buff cache si so bi bo in cs us sy
id wa
0 1 15416 18156 73372 4348488 1 1 3 2 4 1 2
1 2 2


----------- Here is iostat

avg-cpu: %user %nice %sys %iowait %idle
11.59 0.00 6.13 10.77 71.50

Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn
sda 2.76 6.88 36.35 16036474 84688320
sda1 0.00 0.01 0.00 30100 1056
sda2 0.27 2.36 1.72 5509296 4017224
sda3 1.85 0.78 21.99 1819850 51242800
sda4 0.00 0.00 0.00 20 0
sda5 0.15 0.49 1.47 1131624 3425672
sda6 0.49 3.14 11.12 7320616 25899088
sda7 0.01 0.09 0.04 219960 102480
sdb 2.75 6.78 36.35 15803532 84688320
sdb1 0.00 0.01 0.00 24322 1056
sdb2 0.27 2.31 1.72 5391682 4017224
sdb3 1.84 0.79 21.99 1836088 51242800
sdb4 0.00 0.00 0.00 20 0
sdb5 0.15 0.49 1.47 1134546 3425672
sdb6 0.49 3.12 11.12 7273816 25899088
sdb7 0.01 0.06 0.04 138138 102480
sdc 0.00 0.00 0.00 632 0
sdd 0.00 0.00 0.00 80 0
sde 0.00 0.00 0.00 80 0
sdf 0.00 0.00 0.00 80 0
sdg 0.00 0.00 0.00 112 0
sdh 0.00 0.00 0.00 112 0
sdi 139.89 680.59 839.42 1585722266 1955771032
sdj 139.72 680.21 835.90 1584829368 1947590800
sdk 139.82 680.30 840.74 1585053608 1958864880
sdl 139.86 680.56 841.26 1585657408 1960079576
sdm 54.80 6.67 891.38 15547618 2076836720
sdn 54.71 6.66 891.35 15509096 2076776352

________________________________

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