DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta;
	h=message-id:date:from:sender:to:subject:mime-version:content-type:content-transfer-encoding:content-disposition:x-google-sender-auth;
	b=jR46lMCfMpGUh3LIe/Vc/XPKMw7CBqAz3IOPsmqf7RR5mOzXiVVBiu4flG9aaVJ3+D+a4PcSpNG6R3l7RKfsnrzY4PAZeM34y5CPqFDtcqbNaT12R/K0kFVqxizPg+aHqYO5kw9N9pfyfOSUxau0GOzfX90JUS8PYMBqStK+ZHA=
Message-ID: <669dc9710803190418g45002e87k19cb742579c1eeac@mail.gmail.com>
Date: Wed, 19 Mar 2008 12:18:16 +0100
From: "Laurent Raufaste" <analogue@glop.org>
To: pgsql-performance@postgresql.org
Subject: PG writes a lot to the disk
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

I have a big PG server dedicated to serve only SELECT queries.
The database is updated permanently using Slony.

The server has 8 Xeon cores running at 3Ghz, 24GB or RAM and the
following disk arrays:
- one RAID1 serving the OS and the pg_xlog
- one RAID5 serving the database and the tables (base directory)
- one RAID5 serving the indexes (indexes have an alternate tablespace)

This server can't take anything, it writes too much.

When I try to  plug it to a client (sending 20
transactions/s) it works fine for like 10 minutes, then start to write
a lot in the pgdata/base directory (where the database files are, not
the index).

It writes so much (3MB/s randomly) that it can't serve the queries anymore, the
load is huge.

In order to locate the problem, I stopped Slony (no updates anymore),
mounted the database and index partitions with the sync option (no FS
write cache), and the problem happens faster, like 2 minutes after
having plugged the client (and the queries) to it.
I can reproduce the problem at will.

I tried to see if some file size were increasing a lot, and found
nothing more than the usual DB increase (DB is constantly updated by
Slony).

What does it writes so much in the base directory ? If it's some
temporary table or anything, how can I locate it so I can fix the
problem ?

Here's the PG memory configuration:
max_connections = 128
shared_buffers = 2GB
temp_buffers = 8MB
work_mem = 96MB
maintenance_work_mem = 4GB
max_stack_depth = 7MB
default_statistics_target = 100
effective_cache_size = 20GB

Thanks a lot for your advices !

-- 
Laurent Raufaste
<http://www.glop.org/>