pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: adey <adey11@gmail.com>
To: Abu Mushayeed <amushayeed@hotmail.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: PG slowdown
Date: Thu, 13 Jul 2006 15:31:02 +1200
Message-ID: <1c66bda80607122031m3f5ad5a0jcc8208440e5d6174@mail.gmail.com> (raw)
In-Reply-To: <BAY108-F2233D3FE2D2DDCC2CC28C9D1680@phx.gbl>
References: <BAY108-F2233D3FE2D2DDCC2CC28C9D1680@phx.gbl>

If you turn WAL logging off (via fsynch parameter in .conf), it is my
understanding that you will no longer log database updates, and will
therefore not be able to recover any uncommitted transactions in the event
of failure, (which seems contradictory to database practice).

Gut feel to me says your chckpoint_segments and timeout are too high.


On 7/12/06, Abu Mushayeed <amushayeed@hotmail.com> wrote:
>
> Hello,
>
> My hardware
>
> CPU: Intel 3 GHZ X 4
> Memory: 9 GB
> LINUX
>
> My conf file are as follows:
>
> shared_buffers=10000
> wal_buffers=128
> vacuum_mem = 256000
> sort_mem=32768
> max_fsm_pages = 3000000
> max_fsm_relations = 32768
> checkpoint_segments = 2048
> checkpoint_timeout = 1800
> checkpoint_warning = 300
> effective_cache_size =  262144
> random_page_cost = 2
> default_statistics_target = 250
>
> Some of my queries that used to run within a minute, before I changed the
> "default_statistics_target" from 10 to 250, now runs for 20-30 minutes.
> Some
> queries did see improvement in their run time.
>
> I have two questions:
>
> 1. Why did the other queries slowdown? I tried to tune them, as their is a
> join between two tables and it reads all the rows, I forced it to do a seq
> scan rather than index scan but it quadrupled it run time. Why?
> 2. How can I disable the logging of updates? What are the pitfalls? When
> an
> update or insert happening there are lot of logging going on and the
> system
> writes messages like this "2006-07-11 10:08:54 [2330] LOG:  checkpoints
> are
> occurring too frequently (182 seconds apart)
> HINT:  Consider increasing the configuration parameter
> "checkpoint_segments"."
>
> Thank you for the help.
>
> Abu
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>               http://archives.postgresql.org
>

view thread (3+ messages)  latest in thread

Message-ID: <1c66bda80607122031m3f5ad5a0jcc8208440e5d6174@mail.gmail.com>
Permalink:  ../1c66bda80607122031m3f5ad5a0jcc8208440e5d6174@mail.gmail.com/
Also on:    postgresql.org/message-id/1c66bda80607122031m3f5ad5a0jcc8208440e5d6174@mail.gmail.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-admin@postgresql.org
  Cc: adey11@gmail.com, amushayeed@hotmail.com
  Subject: Re: PG slowdown
  In-Reply-To: <1c66bda80607122031m3f5ad5a0jcc8208440e5d6174@mail.gmail.com>
  References: <BAY108-F2233D3FE2D2DDCC2CC28C9D1680@phx.gbl> <1c66bda80607122031m3f5ad5a0jcc8208440e5d6174@mail.gmail.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