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

Le 17/07/2018 à 16:00, Neto pr a écrit :
> 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

I'm no expert in HP system, but you can have a look at this thread and 
referenced links
For the trim option in Debian, you need to define the mount options of 
your partition, in /etc/fstab, to include "discard" (see 
https://wiki.archlinux.org/index.php/Solid_State_Drive#Continuous_TRIM )

Regards,
Nicolas

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