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

From: Mark Kirkwood
Subject: Re: Why HDD performance is better than SSD in this case
Date: ,
Msg-id: 4ae07b23-912b-e3fb-8e9a-2959c2a64f19@catalyst.net.nz
(view: Whole thread, Raw)
In response to: Re: Why HDD performance is better than SSD in this case  (Mark Kirkwood)
Responses: Re: Why HDD performance is better than SSD in this case  (Neto pr)
Re: Why HDD performance is better than SSD in this case  (Mark Kirkwood)
List: pgsql-performance

Tree view

Why HDD performance is better than SSD in this case  (Neto pr, )
 Re: Why HDD performance is better than SSD in this case  (Benjamin Scherrey, )
  Re: Why HDD performance is better than SSD in this case  (winston cheung, )
 Re: Why HDD performance is better than SSD in this case  (Mark Kirkwood, )
 Re: Why HDD performance is better than SSD in this case  (Robert Zenz, )
 Re: Why HDD performance is better than SSD in this case  (Fabio Pardi, )
  Re: Why HDD performance is better than SSD in this case  (Neto pr, )
   Re: Why HDD performance is better than SSD in this case  (Neto pr, )
    Re: Why HDD performance is better than SSD in this case  (Nicolas Charles, )
     Re: Why HDD performance is better than SSD in this case  (Robert Zenz, )
      Re: Why HDD performance is better than SSD in this case  (Nicolas Charles, )
     Re: Why HDD performance is better than SSD in this case  (Neto pr, )
      Re: Why HDD performance is better than SSD in this case  (Nicolas Charles, )
     Re: Why HDD performance is better than SSD in this case  (Mark Kirkwood, )
    Re: Why HDD performance is better than SSD in this case  (Fabio Pardi, )
     Re: Why HDD performance is better than SSD in this case  (Neto pr, )
      Re: Why HDD performance is better than SSD in this case  (Fabio Pardi, )
       Re: Why HDD performance is better than SSD in this case  (Neto pr, )
        Re: Why HDD performance is better than SSD in this case  (Fabio Pardi, )
   Re: Why HDD performance is better than SSD in this case  (Mark Kirkwood, )
    Re: Why HDD performance is better than SSD in this case  (Neto pr, )
     Re: Why HDD performance is better than SSD in this case  (Neto pr, )
      Re: Why HDD performance is better than SSD in this case  (Nicolas Charles, )
      Re: Why HDD performance is better than SSD in this case  (Imre Samu, )
     Re: Why HDD performance is better than SSD in this case  (Mark Kirkwood, )
     Re: Why HDD performance is better than SSD in this case  (Mark Kirkwood, )
      Re: Why HDD performance is better than SSD in this case  (Mark Kirkwood, )
       Re: Why HDD performance is better than SSD in this case  (Neto pr, )
        Re: Why HDD performance is better than SSD in this case  (Mark Kirkwood, )
       Re: Why HDD performance is better than SSD in this case  (Mark Kirkwood, )
 Re: Why HDD performance is better than SSD in this case  (Jeff Janes, )
 Re: Why HDD performance is better than SSD in this case  (George Neuner, )

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




pgsql-performance by date:

From: Mark Kirkwood
Date:
Subject: Re: Why HDD performance is better than SSD in this case
From: Mark Kirkwood
Date:
Subject: Re: Why HDD performance is better than SSD in this case