From: Joshua D. Drake <jd@commandprompt.com>
To: Marty Jia <mjia@ask.com>
Cc: Bucky Jordan <bjordan@lumeta.com>
Cc: Alex Turner <armtuk@gmail.com>
Cc: Mark Lewis <mark.lewis@mir3.com>
Cc: pgsql-performance@postgresql.org, DBAs <DBAs@ask.com>
Cc: Rich Wilson <richwilson@ask.com>
Cc: Ernest Wurzbach <EWurzbach@ask.com>
Subject: Re: How to get higher tps
Date: Tue, 22 Aug 2006 12:38:27 -0700
Message-ID: <44EB5D33.3030300@commandprompt.com> (raw)
In-Reply-To: <0B9A8C89DCC3AB488A78A4DE0FECDA90019E33E4@SITE3MAIL04.jeeves.ask.info>
References: <0B9A8C89DCC3AB488A78A4DE0FECDA90019E33E4@SITE3MAIL04.jeeves.ask.info>
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@lumeta.com]
> Sent: Tuesday, August 22, 2006 3:23 PM
> To: Joshua D. Drake; Marty Jia
> Cc: Alex Turner; Mark Lewis; pgsql-performance@postgresql.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@postgresql.org
> [mailto:pgsql-performance-owner@postgresql.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@postgresql.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@gmail.com]
>> Sent: Tuesday, August 22, 2006 11:27 AM
>> To: Mark Lewis
>> Cc: Marty Jia; Joshua D. Drake; pgsql-performance@postgresql.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@gmail.com <mailto:armtuk@gmail.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.lewis@mir3.com
>> <mailto:mark.lewis@mir3.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@commandprompt.com]
>> > Sent: Monday, August 21, 2006 6:09 PM
>> > To: Marty Jia
>> > Cc: pgsql-performance@postgresql.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@postgresql.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/
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: jd@commandprompt.com, mjia@ask.com, bjordan@lumeta.com, armtuk@gmail.com, mark.lewis@mir3.com, DBAs@ask.com, richwilson@ask.com, EWurzbach@ask.com
Subject: Re: How to get higher tps
In-Reply-To: <44EB5D33.3030300@commandprompt.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox