From: Steve Wolfe <nw@codon.com>
To: pgsql-performance@postgresql.org
Subject: Re: Question on hardware & server capacity
Date: Fri, 3 Jan 2003 10:31:54 -0700
Message-ID: <012b01c2b34e$05e267e0$88693fd1@WEASEL> (raw)
References: <008701c2b286$4c9c2ae0$88693fd1@WEASEL>
<1041545254.2176.28.camel@rh72.home.ee>
> Have you optimized your queries to max ?
>
> Often one or two of the queries take most of resources and starve
> others.
I did log a good number of queries and analyze them, and 69% of the
queries issued are from one particular application, and they consume 78%
of the total "cost". The developper is looking into optimizations, but it
doesn't look like there's going to be any low-hanging fruit. It's simply
a complicated and frequently-used app.
> Could there be some unnecessary trashing between OS and PG caches ?
> How could this be detected ?
The machine generally has a minimum of a hundred megs free, unused
memory, so I'm not terribly worried about memory thrashing. I've
increased the various tuneable parameters (buffer blocks, sort mem, etc.)
to the point where performance increases stopped, then I doubled them all
for good measure. I've already decided that the next machine will have at
least 4 gigs of RAM, just because RAM's cheap, and having too much is a
Good Thing.
> Do you have some triggers on updates - I have occasionally found them to
> be real performance killers.
There are a few triggers, but not many - and the number of updates is
extremely low relative to the number of inserts.
> Yes, it's BAD if your business grows faster than Moores law ;-p
.. unfortunately, that's been the case. Each year we've done slightly
more than double the traffic of the previous year - and at the same time,
as we unify all of our various data sources, the new applications that we
develop tend to make greater and greater demands on the database server.
There is always the option of the "big iron", but your
cost-per-transaction shoots through the roof. Paying a 10x premium can
really hurt. : )
> How big is the dataset ? What kinds of queries ?
our ~postgres/data/base is currently 3.4 gigs.
> 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.
I'd appreciate that!
steve
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: nw@codon.com
Subject: Re: Question on hardware & server capacity
In-Reply-To: <012b01c2b34e$05e267e0$88693fd1@WEASEL>
References: <008701c2b286$4c9c2ae0$88693fd1@WEASEL> <1041545254.2176.28.camel@rh72.home.ee> <012b01c2b34e$05e267e0$88693fd1@WEASEL>
* 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