pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Harry Broomhall <harry.broomhall@uk.easynet.net>
To: shridhar_daithankar@myrealbox.com
Cc: harry.broomhall@uk.easynet.net
Cc: pgsql-performance@postgresql.org
Subject: Re: Performance weirdness with/without vacuum analyze
Date: Tue, 21 Oct 2003 15:50:48 +0100 (BST)
Message-ID: <200310211450.PAA15768@haeb.noc.uk.easynet.net> (raw)
In-Reply-To: <3F951FC8.2090906@myrealbox.com>

Shridhar Daithankar writes:

  First - many thanks for your suggestions and pointers to further info.

  I have been trying some of them with some interesting results!

> Harry Broomhall wrote:
>  > #effective_cache_size = 1000    # typically 8KB each
>  > #random_page_cost = 4       # units are one sequential page fetch cost
> 
> You must tune the first one at least. Try 
> http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html to tune these 
> parameters.

  Changing effective_cache_size seemed to have very little effect.  I took it
in steps up to 300MB (the machine has 640MB memory), and the differences
in speed were less than 10%.

[SNIP]
> 
> What happens if you turn off hash joins?

   This makes the non vacuum version about 40% slower, and the vacuum version
to the same speed (i.e. about 4X faster than it had been!).

> Also bump sort memory to something 
> good.. around 16MB and see what difference does it make to performance..


  This was interesting.  Taking it to 10MB made a slight improvement.  Up to
20MB and the vacuum case improved by 5X speed, but the non-vacuum version
slowed down.  Putting it up to 40MB slowed both down again.

  I will need to test with some of the other scripts and functions I have
written, but it looks as if selective use of more sort memory will be
useful.

  Regards,
      Harry.




view thread (7+ messages)  latest in thread

Message-ID: <200310211450.PAA15768@haeb.noc.uk.easynet.net>
Permalink:  ../200310211450.PAA15768@haeb.noc.uk.easynet.net/
Also on:    postgresql.org/message-id/200310211450.PAA15768@haeb.noc.uk.easynet.net

 · 

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 using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: harry.broomhall@uk.easynet.net, shridhar_daithankar@myrealbox.com
  Subject: Re: Performance weirdness with/without vacuum analyze
  In-Reply-To: <200310211450.PAA15768@haeb.noc.uk.easynet.net>

* 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