Unless you can separate PGDATA and the WAL destination to be on wholly independent physical disks and not just different partitions of the same hardware array, the physical limitations will still be present. I believe the recommended method is to use RAID 5 or RAID 10 data partitions and then use RAID 1 for transaction logs. Additionally, you're supposed to match the stripes size of the arrays to the block sizes of your database, but I can never remember the math involved to do it. Database guides like this are still a bit beyond what I can understand: http://www.lc.leidenuniv.nl/awcourse/oracle/server.920/a96520/hardware.h tm This one is a bit easier: http://www.dbazine.com/oracle/or-articles/ault1 This is the best RAID primer I've seen: http://www.pcguide.com/ref/hdd/perf/raid/index.htm I'm not convinced I/O is your problem, though. High CPU and memory usage is indicative of many different problems, and poor disk I/O is usually not one of them. In a modern system, I'd expect to see poor disk I/O causing *low* CPU usage combined with poor SELECT and awful INSERT/UPDATE/DELETE performance. Maybe it's caching the database state in memory while it's waiting for writing, though. It seems more likely that the database is either pushing more transactions per minute, pushing more complex transactions, dealing with larger queries and result sets, maintaining more indexes, or running more complex pl/SQL procedures, triggers, and constraints. Additionally, if my understanding is right then running with autovacuum disabled and no batch process vacuum strategy on a database with lots of INSERTs and DELETEs is essentially like running without indexes. -- Brandon Aiken CS/IT Systems Engineer -----Original Message----- From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of Anton Melser Sent: Monday, December 04, 2006 4:11 PM To: pgsql-general@xxxxxxxxxxxxxx Subject: [GENERAL] n00b RAID + wal hot standby question Hi, I am just starting at a company and we are inheriting a previously built solution. It looks pretty good but my previous experience with pg is seriously small-time compared with this... I am very new at the job, and don't know what hd config we have but it will be RAID-something I imagine (hey I was working with desktop "servers" before this!). If that is very important I can find out. We seem to be saving our WAL to the same partition as PGDATA, and I notice that we are maxing out a reasonable looking server. The db is not very big (~4gig, 400meg pgdump), and though I can't see any vacuum strategy (autovacuum on a 8.1.4 is disabled), we didn't have as much consistent CPU usage at my old job (with a 6 gig db and MUCH less CPU and RAM, and probably as many connections), and my vacuum strategy was also pitiful! Sure, completely different environments, but I am thinking that WAL replication could be a factor. So my question... being in complete ignorance of how RAID works (the performance details)... would it be better to try and separate the WAL destination from PGDATA? How much of a difference could it make? Should we wait till the customer starts complaining (no explosion in traffic/db size realistic for the foreseeable future...)? Any abuse welcome. Cheers Antoine ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
