From: Nicolas Charles <nicolas.charles@normation.com>
To: Neto pr <netopr9@gmail.com>
To: Fabio Pardi <f.pardi@portavita.eu>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: Why HDD performance is better than SSD in this case
Date: Tue, 17 Jul 2018 15:44:28 +0200
Message-ID: <bc55990f-e1ac-10c1-8a6d-2d4aef9e9ab7@normation.com> (raw)
In-Reply-To: <CA+wPC0MVVpJqHf5D79Od1mMGAeATEiZWaEwoFRr5q6n_EO+F1w@mail.gmail.com>
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>
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
>>>>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: nicolas.charles@normation.com, netopr9@gmail.com, f.pardi@portavita.eu, pgsql-performance@lists.postgresql.org
Subject: Re: Why HDD performance is better than SSD in this case
In-Reply-To: <bc55990f-e1ac-10c1-8a6d-2d4aef9e9ab7@normation.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox