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

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

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