From: Laurent Raufaste <analogue@glop.org>
To: pgsql-performance@postgresql.org
Subject: PG writes a lot to the disk
Date: Wed, 19 Mar 2008 12:18:16 +0100
Message-ID: <669dc9710803190418g45002e87k19cb742579c1eeac@mail.gmail.com> (raw)
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/;
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: analogue@glop.org
Subject: Re: PG writes a lot to the disk
In-Reply-To: <669dc9710803190418g45002e87k19cb742579c1eeac@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