2018-07-19 21:33 GMT-03:00 Mark Kirkwood <mark.kirkwood@xxxxxxxxxxxxxxx>: > FWIW: > > re-running query 9 using the SSD setup as 2x crucial M550 RAID0: 10 minutes. > > > > On 20/07/18 11:30, Mark Kirkwood wrote: >> >> One more thought on this: >> >> Query 9 does a lot pf sorting to disk - so there will be writes for that >> and all the reads for the table scans. Thus the location of your instance's >> pgsql_tmp directory(s) will significantly influence results. >> >> I'm wondering if in your HDD test the pgsql_tmp on the *SSD's* is being >> used. This would make the HDDs look faster (obviously - as they only need to >> do reads now). You can check this with iostat while the HDD test is being >> run, there should be *no* activity on the SSDs...if there is you have just >> found one reason for the results being quicker than it should be. >> >> FWIW: I had a play with this: ran two version 10.4 instances, one on a >> single 7200 rpm HDD, one on a (ahem slow) Intel 600p NVME. Running query 9 >> on the scale 40 databases I get: >> >> - SSD 30 minutes >> >> - HDD 70 minutes >> >> No I'm running these on an a Intel i7 3.4 Ghz 16 GB RAM setup. Also both >> postgres instances have default config apart from random_page_cost. >> >> Comparing my results with yours - the SSD one is consistent...if I had two >> SSDs in RAID0 I might halve the time (I might try this). However my HDD >> result is not at all like yours (mine makes more sense to be fair...would >> expect HDD to be slower in general). >> >> Cheers (thanks for an interesting puzzle)! >> >> Mark >> Mark, This query 9 is very hard, see my results for other queries (attached - test with secondary index and without secondary index - only primary keys), the SSD always wins in performance. Only for this query that he was the loser, so I put this topic in the list. Today I will not be able to check your test information in more detail, but I will return with more information soon. Best Regards Neto >> >> >> On 18/07/18 13:13, Neto pr wrote: >>> >>> >>> Dear Mark >>> To ensure that the test is honest and has the same configuration the >>> O.S. and also DBMS, my O.S. is installed on the SSD and DBMS as well. >>> I have an instance only of DBMS and two database. >>> - a database called tpch40gnorhdd with tablespace on the HDD disk. >>> - a database called tpch40gnorssd with tablespace on the SSD disk. >>> See below: >>> >>> >> >> >
Attachment:
tpch-compare_hdd_ssd.jpg
Description: JPEG image
