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

2018-07-17 10:55 GMT-03:00 Fabio Pardi <f.pardi@portavita.eu>:
> If you have a RAID cache, i would disable it, since we are only focusing
> on the disks. Cache can give you inconsistent data (even it looks like
> is not the case here).
>
> Also, we can do a step backward, and exclude postgres from the picture
> for the moment.
>
> try to perform a dd test in reading from disk, and let us know.
>
> like:
>
> - create big_enough_file
> - empty OS cache
> - dd if=big_enough_file of=/dev/null
>
> and post the results for both disks.
>
> Also i think it makes not much sense testing on RAID 0. I would start
> performing tests on a single disk, bypassing RAID (or, as mentioned, at
> least disabling cache).
>

But in my case, both the 2 SSDs and the 2 HDDs are in RAID ZERO.
This way it would not be a valid test ? Because the 2 environments are
in RAID ZERO.



> The findings should narrow the focus
>
>
> regards,
>
> fabio pardi
>
> On 07/17/2018 03:19 PM, Neto pr wrote:
>> 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
>>>>>
>>>>