From: | "Joshua D(dot) Drake" <jd(at)commandprompt(dot)com> |
---|---|
To: | Marty Jia <mjia(at)ask(dot)com> |
Cc: | Bucky Jordan <bjordan(at)lumeta(dot)com>, Alex Turner <armtuk(at)gmail(dot)com>, Mark Lewis <mark(dot)lewis(at)mir3(dot)com>, pgsql-performance(at)postgresql(dot)org, DBAs <DBAs(at)ask(dot)com>, Rich Wilson <richwilson(at)ask(dot)com>, Ernest Wurzbach <EWurzbach(at)ask(dot)com> |
Subject: | Re: How to get higher tps |
Date: | 2006-08-22 19:38:27 |
Message-ID: | 44EB5D33.3030300@commandprompt.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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/
From | Date | Subject | |
---|---|---|---|
Next Message | Bucky Jordan | 2006-08-22 19:49:06 | Re: How to get higher tps |
Previous Message | Marty Jia | 2006-08-22 19:37:40 | Re: How to get higher tps |