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

2018-07-19 21:33 GMT-03:00 Mark Kirkwood <mark(dot)kirkwood(at)catalyst(dot)net(dot)nz>:
> 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:
>>>
>>>
>>
>>
>