Received: from maia.hub.org (maia-2.hub.org [200.46.204.251])
	by mail.postgresql.org (Postfix) with ESMTP id B2C42133655C
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Wed, 27 Oct 2010 15:58:16 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia,
	port 10024) with ESMTP id 44442-04
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Wed, 27 Oct 2010 18:58:09 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from vs137086.vserver.de
	(static-ip-62-75-137-86.inaddr.intergenia.de [62.75.137.86])
	by mail.postgresql.org (Postfix) with ESMTP id EB7FB133616B
	for <pgsql-performance@postgresql.org>;
	Wed, 27 Oct 2010 15:58:08 -0300 (ADT)
Received: (qmail 18298 invoked from network); 27 Oct 2010 20:58:05 +0200
Received: from p57aab8f7.dip.t-dialin.net (HELO a-kretschmer.de)
	(87.170.184.247)
	by static-ip-62-75-137-86.inaddr.intergenia.de with (AES256-SHA
	encrypted) SMTP; 27 Oct 2010 20:58:05 +0200
Received: from kretschmer by a-kretschmer.de with local (Exim 4.69)
	(envelope-from <andreas@a-kretschmer.de>) id 1PBBCG-0004Ee-Ik
	for pgsql-performance@postgresql.org; Wed, 27 Oct 2010 20:58:04 +0200
Date: Wed, 27 Oct 2010 20:58:04 +0200
From: Andreas Kretschmer <akretschmer@spamfence.net>
To: pgsql-performance@postgresql.org
Subject: Re: Massive update, memory usage
Message-ID: <20101027185804.GA15814@tux>
References: <AANLkTimt5WsyiXZFqNkFRWK330JxUEy-288qaS2KX+9f@mail.gmail.com>
	<AANLkTiniK=6ZLjiQJYHnRSO_zsePYjqGf=cm733JG+3R@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <AANLkTiniK=6ZLjiQJYHnRSO_zsePYjqGf=cm733JG+3R@mail.gmail.com>
X-OS: Debian/GNU Linux - weil ich es mir Wert bin!
X-GPG-Fingerprint: EE16 3C01 7B9C 10F7 2C8B  3B86 4DB3 D9EE 7F45 84DA
X-Message-Flag: "Windows" is not the answer. "Windows" is the question and
	the answer is "no"!
X-Lugdd: Gerd Kube
X-Info: My name is root. Just root. And I am licensed to kill -9
User-Agent: Mutt/1.5.18 (2008-05-17)
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.9 tagged_above=-10 required=5 tests=BAYES_00=-1.9
X-Spam-Level: 
X-Archive-Number: 201010/498
X-Sequence-Number: 40916

Trenta sis <trenta.sis@gmail.com> wrote:

> 
> Hi,
> 
> I have a Linux Server (Debian) with Postgres 8.3 and I have problems with a
> massive update, about 400000 updates/inserts.

Updates or Inserts?


> If I execute about 100000 it seems all ok, but when I execute 400000, I have
> the same problem with or without a transaction (I need to do with a
> transaction) increase memory usage and disk usage.
> With a execution of 400.000 inserts/update server begin woring well, but after
> 100 seconds of executions increase usage of RAM, and then Swap and finally all
> RAM and swap are used and execution can't finish.
> I have made some tuning in server, I have modified:
> -shared_buffers 1024 Mb
> -work_mem 512 Mb

Way too high, but that's not the problem here... (i guess, depends on
the real query, see below about explain analyse)

> -effective_cache_size 2048Mb

You have 4GB, but you are defined only 1 GByte for shared_mem and you
have defined only 2GB for shared_mem and os-cache together. What about
the other 2 GByte?


> -random_page_cost 2.0

you have changed the default, why?


> -checkpoint_segments 64
> -wal_buffers 8Mb
> -max_prepared_transaction 100
> -synchronous_commit off
> 
> what is wrong in this configuration to executes this inserts/update?

Hard to guess, can you provide the output generated from 
EXPLAIN ANALYSE <your query>?


> 
> Server has: 4Gb RAM, 3GB Swap and SATA Disk with RAID5

RAID5 isn't a good choise for a database server...


Andreas
-- 
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°