Re: How to get higher tps

Here is, it's first time I got tps > 400

10 clients:

[pgsql(at)prdhqdb2:/pgsql/database]pgbench -c 10 -t 10000 -v -d pgbench
2>/dev/null
pghost: pgport: (null) nclients: 10 nxacts: 10000 dbName: pgbench
transaction type: TPC-B (sort of)
scaling factor: 1
number of clients: 10
number of transactions per client: 10000
number of transactions actually processed: 100000/100000
tps = 413.022562 (including connections establishing)
tps = 413.125733 (excluding connections establishing)

20 clients:

[pgsql(at)prdhqdb2:/pgsql/database]pgbench -c 20 -t 10000 -v -d pgbench
2>/dev/null
pghost: pgport: (null) nclients: 20 nxacts: 10000 dbName: pgbench
transaction type: TPC-B (sort of)
scaling factor: 1
number of clients: 20
number of transactions per client: 10000
number of transactions actually processed: 200000/200000
tps = 220.759983 (including connections establishing)
tps = 220.790077 (excluding connections establishing)

-----Original Message-----
From: Joshua D. Drake [mailto:jd(at)commandprompt(dot)com]
Sent: Tuesday, August 22, 2006 3:38 PM
To: Marty Jia
Cc: Bucky Jordan; Alex Turner; Mark Lewis;
pgsql-performance(at)postgresql(dot)org; DBAs; Rich Wilson; Ernest Wurzbach
Subject: Re: [PERFORM] How to get higher tps

Marty Jia wrote:
> Bucky
>
> My best result is around 380. I believe your hardware is more
> efficient, because no matter how I change the conf parameters, no
> improvement can be obtained. I even turned fsync off.

Do you stay constant if you use 40 clients versus 20?

>
> What is your values for the following parameters?
>
> shared_buffers = 80000
> max_fsm_pages = 350000
> max_connections = 1000
> work_mem = 65536
> effective_cache_size = 610000
> random_page_cost = 3
>
> Thanks
> Marty
>
> -----Original Message-----
> From: Bucky Jordan [mailto:bjordan(at)lumeta(dot)com]
> Sent: Tuesday, August 22, 2006 3:23 PM
> To: Joshua D. Drake; Marty Jia
> Cc: Alex Turner; Mark Lewis; pgsql-performance(at)postgresql(dot)org; DBAs;
> Rich Wilson; Ernest Wurzbach
> Subject: RE: [PERFORM] How to get higher tps
>
> Marty,
>
> Here's pgbench results from a stock FreeBSD 6.1 amd64/PG 8.1.4 install

> on a Dell Poweredge 2950 with 8gb ram, 2x3.0 dual-core woodcrest (4MB
> cache/socket) with 6x300GB 10k SAS drives:
>
> pgbench -c 10 -t 10000 -d bench 2>/dev/null
> pghost: pgport: (null) nclients: 10 nxacts: 10000 dbName: bench
> `transaction type: TPC-B (sort of) scaling factor: 20 number of
clients:
> 10 number of transactions per client: 10000 number of transactions
> actually processed: 100000/100000 tps = 561.056729 (including
> connections establishing) tps = 561.127760 (excluding connections
> establishing)
>
> Here's some iostat samples during the test:
> tty mfid0 da0 cd0
> cpu
> tin tout KB/t tps MB/s KB/t tps MB/s KB/t tps MB/s us ni sy
in
> id
> 6 77 16.01 1642 25.67 0.00 0 0.00 0.00 0 0.00 3 0 8
> 2 87
> 8 157 17.48 3541 60.43 0.00 0 0.00 0.00 0 0.00 24 0 28
> 4 43
> 5 673 17.66 2287 39.44 0.00 0 0.00 0.00 0 0.00 10 0 13
> 2 75
> 6 2818 16.37 2733 43.68 0.00 0 0.00 0.00 0 0.00 17 0 23
> 3 56
> 1 765 18.05 2401 42.32 0.00 0 0.00 0.00 0 0.00 15 0 17
> 3 65
>
> Note- the above was with no tuning to the kernel or postgresql.conf.
>
> Now for my question- it seems that I've still got quite a bit of
> headroom on the hardware I'm running the above tests on, since I know
> the array will pump out > 200 MB/s (dd, bonnie++ numbers), and CPU
> appears mostly idle. This would indicate I should be able to get some
> significantly better numbers with postgresql.conf tweaks correct?
>
> I guess the other problem is ensuring that we're not testing RAM
> speeds, since most of the data is probably in memory (BSD io buffers)?

> Although, for the initial run, that doesn't seem to be the case, since

> subsequent runs without rebuilding the benchmark db are slightly not
> believable (i.e. 1,200 going up to >2,500 tps over 5 back-to-back
> runs). So, as long as I re-initialize the benchdb before each run, it
> should be a realistic test, right?
>
> Thanks,
>
> Bucky
> -----Original Message-----
> From: pgsql-performance-owner(at)postgresql(dot)org
> [mailto:pgsql-performance-owner(at)postgresql(dot)org] On Behalf Of Joshua D.
> Drake
> Sent: Tuesday, August 22, 2006 12:16 PM
> To: Marty Jia
> Cc: Alex Turner; Mark Lewis; pgsql-performance(at)postgresql(dot)org; DBAs;
> Rich Wilson; Ernest Wurzbach
> Subject: Re: [PERFORM] 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/