pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Bill Moran <wmoran@potentialtech.com>
To: matt <matt@ymogen.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: Hardware recommendations to scale to silly load
Date: Wed, 27 Aug 2003 10:17:46 -0400
Message-ID: <3F4CBD8A.4050309@potentialtech.com> (raw)
In-Reply-To: <1061990263.15800.124.camel@gibraltar.mattclark.net>
References: <1061948112.15800.53.camel@gibraltar.mattclark.net>
	<3F4C1364.6080602@potentialtech.com>
	<1061990263.15800.124.camel@gibraltar.mattclark.net>

matt wrote:
>>Are you sure?  Have you tested the overall application to see if possibly
>>you gain more on insert performance than you lose on select performanc?
> 
> Unfortunately dropping any of the indexes results in much worse select
> performance that is not remotely clawed back by the improvement in
> insert performance.

Bummer.  It was just a thought: never assume dropping indexes will hurt
performance.  But, since you've obviously tested ...

> Actually there doesn't really seem to *be* that much improvement in
> insert performance when going from 3 indexes to 2.  I guess indexes must
> be fairly cheap for PG to maintain?

Don't know how "cheap" they are.

I have an app that does large batch updates. I found that if I dropped
the indexes, did the updates and recreated the indexes, it was faster
than doing the updates while the indexes were intact.

It doesn't sound like your app can use that approach, but I thought I'd
throw it out there.

>>It's possible that compiling Postgres manually with proper optimizations
>>could yield some improvements, as well as building a custom kernel in
>>Redhat.
>>
>>Also, you don't mention which filesystem you're using:
>>http://www.potentialtech.com/wmoran/postgresql.php
> 
> Yeah, I can imagine getting 5% extra from a slim kernel and
> super-optimised PG.
> 
> The FS is ext3, metadata journaling (the default), mounted noatime.

ext3 is more reliable than ext2, but it's 1.1x slower.  You can squeeze
a little performance by using Reiser or JFS, if you're not willing to
take the risk of ext2, either way, it's a pretty minor improvement.

Does noatime make much difference on a PostgreSQL database?  I haven't
tested that yet.

>>But if you're in the situation where you have more time than money,
>>you may find that an overall audit of your app is worthwhile.  Consider
>>taking parts that are in perl (for example) and recoding them into C
>>(that is, unless you've already identified that all the bottlenecks are
>>at the PostgreSQL server)
> 
> I can pretty cheaply add more CPU horsepower for the app servers, as
> they scale horizontally, so I can chuck in a couple (or 3, or 4, or ...)
> more dual-cpu boxen with a gig of ram and tell the load balancer about
> them.  The problem with the DB is that that approach simply won't work -
> the box just has to get bigger!

Can you split it onto multiple boxes?  Some database layouts lend themselves
to this, others don't.  Obviously you can't do joins from one server to
another, so you may lose more in multiple queries than you gain by having
multiple servers.  It's worth looking into though.

I know my answers aren't quite the ones you were looking for, but my
experience is that many people try to solve poor application design
by simply throwing bigger hardware at the problem.  It appears as though
you've already done your homework, though.

Hope this has been _some_ help.

-- 
Bill Moran
Potential Technologies
http://www.potentialtech.com




view thread (45+ messages)  latest in thread

Message-ID: <3F4CBD8A.4050309@potentialtech.com>
Permalink:  ../3F4CBD8A.4050309@potentialtech.com/
Also on:    postgresql.org/message-id/3F4CBD8A.4050309@potentialtech.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: wmoran@potentialtech.com, matt@ymogen.net
  Subject: Re: Hardware recommendations to scale to silly load
  In-Reply-To: <3F4CBD8A.4050309@potentialtech.com>
  References: <1061948112.15800.53.camel@gibraltar.mattclark.net> <3F4C1364.6080602@potentialtech.com> <1061990263.15800.124.camel@gibraltar.mattclark.net> <3F4CBD8A.4050309@potentialtech.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