Re: n00b RAID + wal hot standby question - Mailing list pgsql-general

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@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Anton Melser
Sent: Monday, December 04, 2006 4:11 PM
To: pgsql-general@postgresql.org
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