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

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:
>>
>>
>
>