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

From: Fabio Pardi
Subject: Re: Why HDD performance is better than SSD in this case
Date: ,
Msg-id: 01bbbc88-2a47-f078-ea40-ab2c1acafa1f@portavita.eu
(view: Whole thread, Raw)
In response to: Why HDD performance is better than SSD in this case  (Neto pr)
Responses: Re: Why HDD performance is better than SSD in this case  (Neto pr)
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, )

As already mentioned by Robert, please let us know if you made sure that
nothing was fished from RAM, over the faster test.

In other words, make sure that all caches are dropped between one test
and another.

Also,to better picture the situation, would be good to know:

- which SSD (brand/model) are you using?
- which HDD?
- how are the disks configured? RAID? or not?
- on which OS?
- what are the mount options? SSD requires tuning
- did you make sure that no other query was running at the time of the
bench?
- are you making a comparison on the same machine?
- is it HW or VM? benchs should better run on bare metal to avoid
results pollution (eg: other VMS on the same hypervisor using the disk,
host caching and so on)
- how many times did you run the tests?
- did you change postgres configuration over tests?
- can you post postgres config?
- what about vacuums or maintenance tasks running in the background?

Also, to benchmark disks i would not use a custom query but pgbench.

Be aware: running benchmarks is a science, therefore needs a scientific
approach :)

regards

fabio pardi



On 07/17/2018 07:00 AM, Neto pr wrote:
> Dear,
> Some of you can help me understand this.
> 
> This query plan is executed in the query below (query 9 of TPC-H
> Benchmark, with scale 40, database with approximately 40 gb).
> 
> The experiment consisted of running the query on a HDD (Raid zero).
> Then the same query is executed on an SSD (Raid Zero).
> 
> Why did the HDD (7200 rpm)  perform better?
> HDD - TIME 9 MINUTES
> SSD - TIME 15 MINUTES
> 
> As far as I know, the SSD has a reading that is 300 times faster than SSD.
> 
> --- Execution  Plans---
> ssd 40g
> https://explain.depesz.com/s/rHkh
> 
> hdd 40g
> https://explain.depesz.com/s/l4sq
> 
> Query ------------------------------------
> 
> select
>     nation,
>     o_year,
>     sum(amount) as sum_profit
> from
>     (
>         select
>             n_name as nation,
>             extract(year from o_orderdate) as o_year,
>             l_extendedprice * (1 - l_discount) - ps_supplycost *
> l_quantity as amount
>         from
>             part,
>             supplier,
>             lineitem,
>             partsupp,
>             orders,
>             nation
>         where
>             s_suppkey = l_suppkey
>             and ps_suppkey = l_suppkey
>             and ps_partkey = l_partkey
>             and p_partkey = l_partkey
>             and o_orderkey = l_orderkey
>             and s_nationkey = n_nationkey
>             and p_name like '%orchid%'
>     ) as profit
> group by
>     nation,
>     o_year
> order by
>     nation,
>     o_year desc
> 



pgsql-performance by date:

From: Neto pr
Date:
Subject: Re: Why HDD performance is better than SSD in this case
From: Nicolas Charles
Date:
Subject: Re: Why HDD performance is better than SSD in this case