Re: Why HDD performance is better than SSD in this case

And perhaps more interesting:

Re-running query 9 against the (single) HDD setup *but* with pgsql_tmp 
symlinked to the 2x SSD RAID0: 15 minutes

I'm thinking that you have inadvertently configured your HDD test in 
this way (you get 9 minutes because you have 2x HDDs). Essentially most 
of the time taken for this query is in writing and reading files for 
sorting/hashing, so where pgsql_tmp is located hugely influences the 
overall time.

regards

Mark


On 20/07/18 12:33, Mark Kirkwood wrote:
> 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
>>
>>
>>
>> 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:
>>>
>>>
>>
>>
>
>