Re: Why HDD performance is better than SSD in this case - Mailing list pgsql-performance

Sorry.. I replied in the wrong message before ...
follows my response.
-------------

Thanks all, but I still have not figured it out.
This is really strange because the tests were done on the same machine
(I use  HP ML110 Proliant 8gb RAM - Xeon 2.8 ghz processor (4
cores), and POSTGRESQL 10.1.
- Only the mentioned query running at the time of the test.
- I repeated the query 7 times and did not change the results.
- Before running each batch of 7 executions, I discarded the Operating
System cache and restarted DBMS like this:
(echo 3> / proc / sys / vm / drop_caches;

discs:
- 2 units of Samsung Evo SSD 500 GB (mounted on ZERO RAID)
- 2 SATA 7500 Krpm HDD units - 1TB (mounted on ZERO RAID)

- The Operating System and the Postgresql DBMS are installed on the SSD disk.

Best Regards
[ ]`s Neto

2018-07-17 1:08 GMT-07:00 Fabio Pardi <f.pardi@portavita.eu>:
> 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
>>
>