pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Bob Dusek <redusek@gmail.com>
To: Scott Marlowe <scott.marlowe@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: performance config help
Date: Mon, 11 Jan 2010 12:49:39 -0500
Message-ID: <61039b861001110949n699f0bd1pf3c878fa4c739891@mail.gmail.com> (raw)
In-Reply-To: <dcc563d11001110934n7c97be5cv5801289da116b87e@mail.gmail.com>
References: <61039b861001110544o2c352763q13dcf9d78a156714@mail.gmail.com>
	<dcc563d11001110550s2af6d559l5db495d5e0fc6a57@mail.gmail.com>
	<61039b861001110842g15bb1f26jc50b6e6762e01427@mail.gmail.com>
	<dcc563d11001110934n7c97be5cv5801289da116b87e@mail.gmail.com>

>
>
> > This is to be expected, to some extent, as we would expect some
> perfromance
> > degradation with higher utilization.  But, the hardware doesn't appear to
> be
> > very busy, and that's where we're hoping for some help.
>
> It's likely in io wait.
>
> >> What do the following commands tell you?
> >>
> >> iostat -x 10 (first iteration doesn't count)
> >
> > Here's some iostat output (from the 3rd data point from iostat -x 10)...
> > this was taken while we were processing 256 simultaneous requests.
> >
> >  avg-cpu:  %user   %nice %system %iowait  %steal   %idle
> >           34.29    0.00    7.09    0.03    0.00   58.58
> >
> > Device:         rrqm/s   wrqm/s   r/s   w/s   rsec/s   wsec/s avgrq-sz
> > avgqu-sz   await  svctm  %util
> > sda               0.00   112.20  0.00 133.40     0.00  1964.80    14.73
> > 0.42    3.17   0.04   0.48
> > sda1              0.00     0.00  0.00  0.00     0.00     0.00     0.00
> > 0.00    0.00   0.00   0.00
> > sda2              0.00     0.00  0.00  0.00     0.00     0.00     0.00
> > 0.00    0.00   0.00   0.00
> > sda3              0.00     0.00  0.00  0.00     0.00     0.00     0.00
> > 0.00    0.00   0.00   0.00
> > sda4              0.00     0.00  0.00  0.00     0.00     0.00     0.00
> > 0.00    0.00   0.00   0.00
> > sda5              0.00   112.20  0.00 133.40     0.00  1964.80    14.73
> > 0.42    3.17   0.04   0.48
> > sdb               0.00     0.00  0.00  0.00     0.00     0.00     0.00
> > 0.00    0.00   0.00   0.00
> > dm-0              0.00     0.00  0.00  0.40     0.00     3.20     8.00
> > 0.00    0.00   0.00   0.00
> > dm-1              0.00     0.00  0.00  0.00     0.00     0.00     0.00
> > 0.00    0.00   0.00   0.00
> > dm-2              0.00     0.00  0.00 99.90     0.00   799.20     8.00
> > 0.15    1.50   0.01   0.12
> > dm-3              0.00     0.00  0.00  0.60     0.00     4.80     8.00
> > 0.00    0.33   0.17   0.01
> > dm-4              0.00     0.00  0.00 144.70     0.00  1157.60     8.00
> > 0.46    3.17   0.02   0.35
> > dm-5              0.00     0.00  0.00  0.00     0.00     0.00     0.00
> > 0.00    0.00   0.00   0.00
> >
> > The iowait seems pretty low, doesn't it?
>
> Depends, is that the first iteration of output?  if so, ignore it and
> show me the second and further on.  Same for vmstat...  In fact let
> them run for a minute or two and attach the results...  OTOH, if that
> is the second or later set of output, then you're definitely not IO
> bound, and I don't see why the CPUs are not being better utilized.
>
> I was probably not clear... the output I pasted was from the third
iteration of output from iostat.  And, the vmstat output I pasted was from
the sixth iteration of output

How many concurrent queries are you running when you take these
> measurements?  Can you take them with lower and higher numbers of
> concurrent users and compare the two?  normally I'd be looking for
> context switching taking more and more time in a heavily loaded
> system, but I'm not seeing it in your vmstat numbers either.
>
> We took those measurements with 256 concurrent requests being processed.
So, at most, we have 256 concurrent queries executed by our application.
There aren't other applications using the db in our tests.

We can take some measurements at 40 concurrent requests and see where we
stand.


> What are your settings for
>
> effective_cache_size
>

 effective_cache_size = 24GB


> random_page_cost
>

Using the default...

#random_page_cost = 4.0


> work_mem
>

 work_mem = 64MB


> with your machine and the extra memory, you can probably uptune the
> work_mem to 8 Megs safely if it's at the default of 1MB.  With a
> database that fits in RAM, you can often turn down random_page_cost to
> near 1.0 (1.2 to 1.4 is common for such scenarios.)  And effective
> cache size being larger (in the 20G range) will hint the planner that
> it's likely to find everything it needs in ram somewhere and not on
> the disk.
>

So, we should probably try cranking our random_page_cost value down.  When
we dump our db with "pg_dump --format=t", it's about 15 MB.  We should be
able to keep the thing in memory.

>
> There are several common bottlenecks you can try to tune away from.
> IO doesn't look like a problem for you.  Neither does CPU load.  So,
> then we're left with context switching time and memory to CPU
> bandwidth.  If your CPUs are basically becoming data pumps then the
> speed of your FSB becomes VERY critical, and some older Intel mobos
> didn't have a lot of CPU to Memory bandwidth and adding CPUs made it
> worse, not better.  More modern Intel chipsets have much faster CPU to
> Memory BW, since they're using the same kind of fabric switching that
> AMD uses on highly parallel machines.
>

Each CPU is 2.13 GHz, with 8MB Cache, and the FSB is 1066 MHz.  Does that
bus speed seem slow?

It's hard to go to the money tree  and say "we're only using about half of
your CPUs, but you need to get better ones."


> If your limit is your hardware, then the only solution is a faster
> machine.  It may well be that a machine with dual fast Nehalem
> (2.4GHz+) quad core CPUs will be faster.  Or 4 or 8 AMD CPUs with
> their faster fabric.
>

It sounds like we could spend less money on memory and more on faster hard
drives and faster CPUs.

But, man, that's a tough sell.  This box is a giant, relative to anything
else we've worked with.

view thread (46+ messages)  latest in thread

Message-ID: <61039b861001110949n699f0bd1pf3c878fa4c739891@mail.gmail.com>
Permalink:  ../61039b861001110949n699f0bd1pf3c878fa4c739891@mail.gmail.com/
Also on:    postgresql.org/message-id/61039b861001110949n699f0bd1pf3c878fa4c739891@mail.gmail.com

 · 

reply

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: redusek@gmail.com, scott.marlowe@gmail.com
  Subject: Re: performance config help
  In-Reply-To: <61039b861001110949n699f0bd1pf3c878fa4c739891@mail.gmail.com>
  References: <61039b861001110544o2c352763q13dcf9d78a156714@mail.gmail.com> <dcc563d11001110550s2af6d559l5db495d5e0fc6a57@mail.gmail.com> <61039b861001110842g15bb1f26jc50b6e6762e01427@mail.gmail.com> <dcc563d11001110934n7c97be5cv5801289da116b87e@mail.gmail.com> <61039b861001110949n699f0bd1pf3c878fa4c739891@mail.gmail.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