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

2018-07-17 10:04 GMT-03:00 Neto pr <netopr9@gmail.com>:
> 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.
>

One more information.
I used default configuration to Postgresql.conf
Only exception is to :
random_page_cost on SSD is 1.1


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