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