pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Martin Foster <martin@ethereal-realms.org>
To: Dennis Björklund <db@zigo.dhs.org>
To: PostgreSQL Performance <pgsql-performance@postgresql.org>
To: PostgreSQL Novice <pgsql-novice@postgresql.org>
Subject: Re: [PERFORM] Extreme high load averages
Date: Wed, 09 Jul 2003 23:37:50 -0600
Message-ID: <3F0CFBAE.6080002@ethereal-realms.org> (raw)
In-Reply-To: <Pine.LNX.4.44.0307071102390.1516-100000@zigo.dhs.org>
References: <Pine.LNX.4.44.0307071102390.1516-100000@zigo.dhs.org>

Dennis Björklund wrote:

> On Sun, 6 Jul 2003, Martin Foster wrote:
> 
> 
>>The processor seems to be purposely sitting there twiddling it's thumbs. 
>>  Which leads me to believe that perhaps the nice levels have to be 
>>changed on the server itself?
> 
> 
> It could also be all the usual things that affect performance. Are your 
> queries using indexes where it should? Do you vacuum analyze after you 
> have updated/inserted a lot of data?
> 
> It could be that some of your queries is not as efficient as it should, 
> like doing a sequenctial scan over a table instead of an index scan. That 
> translates into more IO needed and slower response times. Especially when 
> you have more connections figthing for the available IO.
> 

I actually got a bit more respect for PostgreSQL tonight.  It seems that 
one of my scripts was not committing changes after maintenance was 
conducted.  Meaning that rows that would normally be removed after 
offline archiving was completed were in fact still around.

Normally at any given point in time this table would grow 50K rows 
during a day, be archived that night and then loose rows that were no 
longer needed.    This process, is what allowed MySQL to maintain any 
stability as the size of this table can balloon significantly.

PostgreSQL with tweaking was handling a table with nearly 300K rows. 
That size alone would of dragged the MySQL system down to a near grind, 
and since most of those rows are not needed.   One can imagine that 
queries are needlessly processing rows that should be outright ignored.

This probably explains why row numbering based searches greatly 
accelerated the overall process.

By fixing the script and doing the appropriate full vacuum and re-index, 
the system is behaving much more like it should.  Even if the process 
may seem a bit odd to some.

The reason for removing rows on a daily basis is due to the perishable 
nature of the information.  Since this is a chat site, posts over a day 
old are rarely needed for any reason.   Which is why they are archived 
into dumps in case we really need to retrieve the information itself and 
this gives us the added bonus of smaller backup sizes and smaller 
database sizes.

	Martin Foster
	Creator/Designer Ethereal Realms
	martin@ethereal-realms.org





view thread (22+ messages)  latest in thread

Message-ID: <3F0CFBAE.6080002@ethereal-realms.org>
Permalink:  ../3F0CFBAE.6080002@ethereal-realms.org/
Also on:    postgresql.org/message-id/3F0CFBAE.6080002@ethereal-realms.org

 · 

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: martin@ethereal-realms.org, db@zigo.dhs.org, pgsql-novice@postgresql.org
  Subject: Re: [PERFORM] Extreme high load averages
  In-Reply-To: <3F0CFBAE.6080002@ethereal-realms.org>
  References: <Pine.LNX.4.44.0307071102390.1516-100000@zigo.dhs.org> <3F0CFBAE.6080002@ethereal-realms.org>

* 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