Re: How to get higher tps - Mailing list pgsql-performance

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

10 clients:

[pgsql@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@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@commandprompt.com]
Sent: Tuesday, August 22, 2006 3:38 PM
To: Marty Jia
Cc: Bucky Jordan; Alex Turner; Mark Lewis;
pgsql-performance@postgresql.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@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/