Re: Tuning to speed select

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(at)virginia(dot)edu
(434) 924-2456
http://www.people.virginia.edu/~twl8n/
http://laudeman.com/