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@xxxxxxxxx>:
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@xxxxxxxxxxxx>:
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
