PG writes a lot to the disk

From: "Laurent Raufaste" <analogue(at)glop(dot)org>
To: pgsql-performance(at)postgresql(dot)org
Subject: PG writes a lot to the disk
Date: 2008-03-19 11:18:16
Message-ID: 669dc9710803190418g45002e87k19cb742579c1eeac@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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/>

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Mark Steben 2008-03-19 13:22:33 Re: question on TRUNCATE vs VACUUM FULL
Previous Message Ron Mayer 2008-03-19 01:17:22 Re: TB-sized databases