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. :(
