Tuesday, May 13, 2008

Re: [PERFORM] RAID controllers for Postgresql on large setups

PFC writes:

> You say that like you don't mind having PCI in a server whose job is to
> perform massive query over large data sets.

I am in my 4th week at a new job. Trying to figure what I am working with.
From what I see I will likely get as much improvement from new hardware as
from re-doing some of the database design. Can't get everything done at
once, not to mention I have to redo one machine sooner rather than later so
I need to prioritize.

>In fact for bulk IO a box with 2 SATA drives would be just as fast as
> your monster RAID, lol.

I am working on setting up a standard test based on the type of operations
that the company does. This will give me a beter idea. Specially I will work
with the developers to make sure the queries I create for the benchmark are
representative of the workload.

>Adding more drives will help random reads/writes but do nothing for
> throughput since the tiny PCI pipe is choking.

Understood, but right now I have to use the hardware they already have. Just
trying to make the most of it. I believe another server is due in some
months so then I can better plan.

In your opinion if we get a new machine with PCI-e, at how many spindles
will the SCSI random access superiority start to be less notable? Specially
given the low number of connections we usually have running against these
machines.

>If you mean doing large COPY or inserting/updating lots of rows using one
> SQL statement, you are going to need disk bandwidth.

We are using one single SQL statement.

> http://tweakers.net/reviews/557/17/comparison-of-nine-serial-ata-raid-5-adapters-pagina-17.html

I have heard great stories about Areca controllers. That is definitely one
in my list to research and consider.

> However RAID5 will choke and burn on small random writes, which will come
> from UPDATing random rows in a large table, updating indexes, etc. Since
> you are doing this apparently, RAID5 is therefore NOT advised !

I thought I read a while back in this list that as the number of drives
increased that RAID 5 was less bad. Say an external enclosure with 20+
drives.


>Have you considered Bizgres ?

Yes. In my todo list, to check it further. I have also considered Greenplums
may DB offering that has clustering, but when I initially mentioned it there
was some reluctance because of cost. Also will look into Enterprise DB.

Right now I am trying to learn usage patterns, what DBs need to be
re-designed and what hardware I have to work with. Not to mention learning
what all these tables are. Also need to make time to research/get a good
ER-diagram tool and document all these DBs. :(

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

No comments: