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: ce163be0-06f7-8cc6-f407-cee62235c561@catalyst.net.nz
(view: Whole thread, Raw)
In response to: Re: Why HDD performance is better than SSD in this case  (Neto pr)
Responses: 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, )

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