Merlin,
The problem is disk. I've got a WD Caviar. hdparm says it does 44MB/sec
(I ran that in single user mode so there was nothing interfering). A WD
Caviar SE SATA in one of my servers at home gets 56MB/sec on a quiescent
system at runlevel 3. What kind of values does hdparm give for a SATA
Raptor?
I think my Dell Precision 650 has SATA on the motherboard. The boss says
I can order one drive, so what should I get? How much faster is RAID 0+1
than a single drive?
Aside from size, I can't see much difference between these drives (WD
Raptors at NewEgg):
http://www.newegg.com/Product/ProductList.asp?DEPA=0&type=&Description=raptor&Submit=ENE&Ntk=all&N=0&minPrice=&maxPrice=&Go.x=0&Go.y=0
CLUSTER certainly helped. Each of the following queries would have
returned roughly 50,000 records. Note that selecting a single record
from blast_result using an index is plenty fast ( ~ 50 ms), so my
primary concern is pulling back larger subsets of data.
It appears that count(*) on a CLUSTERed table uses the index (as opposed
to the old way of doing a sequential scan). Count on the table after
CLUSTER appears to be a *lot* faster, maybe almost 100x. I know we
shouldn't count, but we've been too lazy to keep the record counts in
another table, and our customers occasionally want to know how many
records are in a certain subset.
Before CLUSTER:
explain analyze select * from blast_result where si_fk=11843253;
Total runtime: 16334.539 ms
explain analyze select * from blast_result where si_fk=11843248;
Total runtime: 31406.999 ms
explain analyze select * from blast_result where si_fk=11218929;
Total runtime: 15319.440 ms
After CLUSTER and vacuum analyze:
explain analyze select * from blast_result where si_fk=11843253;
Total runtime: 2343.893 ms
explain analyze select * from blast_result where si_fk=11843248;
Total runtime: 2158.395 ms
explain analyze select * from blast_result where si_fk=11218929;
Total runtime: 1880.586 ms
explain analyze select * from blast_result where si_fk=11843250;
Total runtime: 2085.253 ms
Thanks,
Tom
Are your data structures normalized? Performance problems queying a
single giganto table is usually (but not necessirly in your case) a
sign of a poorly designed table structure.
otherwise it's pretty clear you get the most bang for the buck with
hardware. consider upping ram and/or buying better disks. you could
buy cheap sata controller and 4 raptors in raid 0+1 configuration for
<1000$ and you will feel like you have supercomputer relative to what
you have now :)
merlin
--
Tom Laudeman
twl8n@xxxxxxxxxxxx
(434) 924-2456
http://www.people.virginia.edu/~twl8n/
http://laudeman.com/
