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/> -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
