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

As I mentioned, I haven't changed the defaults at all yet:
Fsync is still on...

shared_buffers = 1000
max_fsm_pages = 20000
max_connections = 40
work_mem = 1024
effective_cache_size = 1000
random_page_cost = 4

I'm not sure how much the dual core woodcrests and faster memory are
helping my system. Your hardware should *theoretically* have better IO
performance, assuming you're actually making use of the 2x2GB/s FC
interfaces and external RAID.

What do you get if you run the bench back-to-back without rebuilding the
test db? (Say pgbench -c 10 -t 10000 -d bench 2>/dev/null run 5 times in
a row)? Maybe that would put more stress on RAM/CPU?

Seems to me your issue is with an underperforming IO subsystem- as
previously mentioned, you might want to check dd and bonnie++ (v 1.03)
numbers.

time bash -c "(dd if=/dev/zero of=bigfile count=125000 bs=8k && sync)"

I get ~255 mb/s from the above.

Bucky

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

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.

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/



---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

               http://archives.postgresql.org