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