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

Yeah,

A +1 to telling us the model. In particular the later EVOs use TLC nand 
with a small SLC cache... and when you exhaust the SLC cache the 
performance can be worse than a HDD...


On 18/07/18 01:44, Nicolas Charles wrote:
> 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
>
>
> 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
>>>>>
>
>