Re: Tuning to speed select
От | Tom Laudeman |
---|---|
Тема | Re: Tuning to speed select |
Дата | |
Msg-id | 44DC9058.8070203@virginia.edu обсуждение исходный текст |
Ответ на | Re: Tuning to speed select ("Merlin Moncure" <mmoncure@gmail.com>) |
Ответы |
Re: Tuning to speed select
("Merlin Moncure" <mmoncure@gmail.com>)
Re: Tuning to speed select (Roman Neuhauser <neuhauser@sigpipe.cz>) |
Список | pgsql-general |
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@virginia.edu (434) 924-2456 http://www.people.virginia.edu/~twl8n/ http://laudeman.com/
В списке pgsql-general по дате отправления: