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

2018-07-17 10:44 GMT-03:00 Nicolas Charles <nicolas.charles@normation.com>:
> Hi Neto,
>
> You should list the SSD model also - there are pleinty of Samsung EVO drives
> - and they are not professional grade.
>
> Among the the possible issues, the most likely (from my point of view) are:
>
> - TRIM command doesn't go through the RAID (which is really likely) - so the
> SSD controller think it's full, and keep pushing blocks around to level
> wear, causing massive perf degradation - please check this config on you
> RAID driver/adapter
>
> - TRIM is not configured on the OS level for the SSD
>
> - Partitions is not correctly aligned on the SSD blocks
>
>
> Without so little details on your system, we can only try to guess the real
> issues
>

Thank you Nicolas, for your tips.
I believe your assumption is right.

This SSD really is not professional, even if Samsung's advertisement
says yes. If I have to buy another SSD I will prefer INTEL SSDs.

I had a previous problem with it (Sansung EVO) as it lost in
performance to a SAS HDD, but however, the SAS HDD was a 12 Gb/s
transfer rate and the SSD was 6 Gb/s.

But now I tested against an HDD (7200 RPM) that has the same transfer
rate as the SSD 6 Gb/sec. and could not lose in performance.

Maybe it's the unconfigured trim.

Could you give me some help on how I could check if my RAID is
configured for this, I use Hardware RAID using HP software (HP Storage
Provider on boot).
And on Debian 8 Operating System, how could I check the TRIM configuration ?

Best
[]'s Neto
>
> Nicolas
>
> Nicolas CHARLES
>
> Le 17/07/2018 à 15:19, Neto pr a écrit :
>>
>> 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
>>>>>
>