pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Hannu Krosing <hannu@tm.ee>
To: Steve Wolfe <nw@codon.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Question on hardware & server capacity
Date: 03 Jan 2003 03:07:34 +0500
Message-ID: <1041545254.2176.28.camel@rh72.home.ee> (raw)
In-Reply-To: <008701c2b286$4c9c2ae0$88693fd1@WEASEL>
References: <008701c2b286$4c9c2ae0$88693fd1@WEASEL>

Steve Wolfe kirjutas N, 02.01.2003 kell 22:42:
>   Well, our current database server is getting tremendously loaded, and
> right now there isn't a clear-cut choice as to an upgrade path - at least
> not within the commodity hardware market.

Have you optimized your queries to max ?

Often one or two of the queries take most of resources and starve
others.

>   The machine is a dual AthlonMP 2000, with 2 gigs of RAM.  the loads on
> the machine are getting out of hand, and performance is noticeably slowed.
> 'top' shows the CPU's as being anywhere from 30% to 50% idle, with (on
> average) 5-10 postmasters in the "non-idle" state.  'vmstat' shows bi/bo
> pegged at zero (copious quantities of disk cache, fsync turned off),

Could there be some unnecessary trashing between OS and PG caches ? 
How could this be detected ?

> interrupts fluctuating between 200 and 1,000 per second (avg. is approx
> 400), context switches between 1300 and 4500 (avg. is approx 2300).  I
> logged some queries, and found that in an average second, the machine
> forks off 10 new backends, and responds to 50 selects and 3 updates.

What are the average times for query responses ?

Will running the same queries (the ones from the logs) serially run
faster/slower/at the same speed ?

Do you have some triggers on updates - I have occasionally found them to
be real performance killers.

Also - if memory bandwidth is the issue, you could tweak the parameters
so that PG will prefer index scans more often - there are rumors that
under heavy loads it is often better to use more index scans due to
possible smaller memory/buffer use, even if they would be slower for
only one or two backends.

>   My feelings are that the machine is being swamped by both the number of
> context switches and the I/O, most likely the memory bandwidth.  I'm
> working on implementing some connection pooling to reduce the number of
> new backends forked off, but there's not much I can do about the sheer
> volume (or cost) of queries.

You could try to replicate the updates (one master - multiple slaves)
and distribute the selects. I guess this is what current postgreSQL
state-of-the-art already lets you do with reasonable effort.

> Now, if quad-Hammers were here, I'd simply throw hardware at it.
> Unfortunately, they're not.

Yes, it's BAD if your business grows faster than Moores law ;-p

>   So far, about the only commodity-level answer
> I can think of would be a dual P4 Xeon, with the 533 MHz bus, and
> dual-channel DDR memory.  That would give each processor approximately
> double the memory bandwidth over what we're currently running.
> 
>   I'm fairly sure that would at least help lower the load, but I'm not
> sure by how much.  If anyone has run testing under similar platforms, I'd
> love to hear of the performance difference. 

How big is the dataset ? What kinds of queries ?

I could perhaps run some quick tests on quad Xeon 1.40GHz , 2GB before
this box goes to production sometime early next week. It is a RedHat
AS2.1 box with rh-postgresql-7.2.3-1_as21.

# hdparm -tT /dev/sda

/dev/sda:
 Timing buffer-cache reads:   128 MB in  0.39 seconds =328.21 MB/sec
 Timing buffered disk reads:  64 MB in  1.97 seconds = 32.49 MB/sec

>  If this is going to chop the
> loads in half, I'll do it.  If it's only going to improve it by 10% or so,
> I'm not going to waste the money.

-- 
Hannu Krosing <hannu@tm.ee>



view thread (8+ messages)  latest in thread

Message-ID: <1041545254.2176.28.camel@rh72.home.ee>
Permalink:  ../1041545254.2176.28.camel@rh72.home.ee/
Also on:    postgresql.org/message-id/1041545254.2176.28.camel@rh72.home.ee

 · 

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: hannu@tm.ee, nw@codon.com
  Subject: Re: Question on hardware & server capacity
  In-Reply-To: <1041545254.2176.28.camel@rh72.home.ee>
  References: <008701c2b286$4c9c2ae0$88693fd1@WEASEL> <1041545254.2176.28.camel@rh72.home.ee>

* 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