Subject: Re: Why HDD performance is better than SSD in this case
To: Neto pr <netopr9@gmail.com>, Fabio Pardi <f.pardi@portavita.eu>
Cc: pgsql-performance@lists.postgresql.org
References: 
 <CA+wPC0PsWacZpNX2OfRSSauUg9iNa7tReJU0oTLnM7K8PFFt6A@mail.gmail.com>
 <01bbbc88-2a47-f078-ea40-ab2c1acafa1f@portavita.eu>
 <CA+wPC0M=e5ZXd1NF1nz6mYfmPgys89vB4A74GmxnnK+KpA4_XA@mail.gmail.com>
 <CA+wPC0MVVpJqHf5D79Od1mMGAeATEiZWaEwoFRr5q6n_EO+F1w@mail.gmail.com>
From: Nicolas Charles <nicolas.charles@normation.com>
Message-ID: <bc55990f-e1ac-10c1-8a6d-2d4aef9e9ab7@normation.com>
Date: Tue, 17 Jul 2018 15:44:28 +0200
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:52.0) Gecko/20100101
 Thunderbird/52.8.0
MIME-Version: 1.0
In-Reply-To: 
 <CA+wPC0MVVpJqHf5D79Od1mMGAeATEiZWaEwoFRr5q6n_EO+F1w@mail.gmail.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit
Content-Language: fr
Precedence: bulk

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