From: | winston cheung <winston_cheung(at)163(dot)com> |
---|---|
To: | Benjamin Scherrey <scherrey(at)proteus-tech(dot)com>, Neto pr <netopr9(at)gmail(dot)com> |
Cc: | "pgsql-performa(dot)" <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: Why HDD performance is better than SSD in this case |
Date: | 2018-07-17 05:43:52 |
Message-ID: | 96297cfc-eec0-b5ac-ef30-2fc360c52bd1@163.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
Can you show the configuration of postgresql.conf?
Query configuration method:
Select name, setting from pg_settings where name ~ 'buffers|cpu|^enable';
On 2018年07月17日 13:17, Benjamin Scherrey wrote:
> What's the on disk cache size for each drive? The better HDD
> performance problem won't be sustained with large amounts of data and
> several different queries.
>
> - - Ben Scherrey
>
> On Tue, Jul 17, 2018, 12:01 PM Neto pr <netopr9(at)gmail(dot)com
> <mailto:netopr9(at)gmail(dot)com>> 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
>
--
<b>张文升</b> winston<br />
PostgreSQL DBA<br />
From | Date | Subject | |
---|---|---|---|
Next Message | Mark Kirkwood | 2018-07-17 06:07:44 | Re: Why HDD performance is better than SSD in this case |
Previous Message | Benjamin Scherrey | 2018-07-17 05:17:22 | Re: Why HDD performance is better than SSD in this case |