Re: why SSD is slower than HDD SAS 15K ?

[Neto pr]

Dear Merlin
2018-01-15 11:16 GMT-08:00 Merlin Moncure :

> On Mon, Jan 15, 2018 at 7:38 AM, Neto pr  wrote:
> > Hello all,
> > Someone help me analyze the two execution plans below (Explain ANALYZE
> > used), is the  query 9 of TPC-H benchmark [1].
> > I'm using two servers HP Intel Xeon 2.8GHz/4-core - Memory 8GB. O.S.
> > Debian8, using EXT4 filesystem.
> >
> > Server 1
> > - HDD SAS 15 Krpm - 320 GB (Location where O.S. Debian and Postgresql are
> > installed).
> >
> > Server 2
> > - Samsung Evo SSD 500 GB (Location where Postgresql is Installed)
> > - HDD Sata 7500 Krpm - 1TB (Location where O.S Debian is installed)
> >
> > My DBMS parameters presents in postgresql.conf is default, but in SSD I
> have
> > changed random_page_cost = 1.0.
> >
> > I do not understand, because running on an HDD SAS a query used half the
> > time. I explain better, in HDD spends on average 12 minutes the query
> > execution and on SSD spent 26 minutes.
> > I think maybe the execution plan is using more write operations, and so
> the
> > HDD SAS 15Krpm has been faster.
> > I checked that the temporary tablespace pg_default is on the SSD in
> server
> > 2, because when running show temp_tablespaces in psql returns empty,
> will be
> > in the default directory, where I installed the DBMS in:
> > /media/ssd500gb/opt/pgv101norssd/data.
> >
> > Anyway, I always thought that an SSD would be equal or faster, but in the
> > case and four more cases we have here, it lost a lot for the HDDs.
>
> Generally for reading data, yes, but you changed the query plan also.
> To get to the bottom of this let's get SSD performance numbers for
> both plans and HDD performance numbers for both plans.  You're trying
> to measure device performance about are probably measuring the
> relative efficiencies of the generated plans.
>
>
Very good tip. I discovered that my SAS HDD drive has a transfer rate of
12Gb/s versus 6Gb/s of the SSD. Because of that reason the difference in
performance occurred.  See below:

SSD: Samsung 500 GB SATA III 6Gb/s - Model: 850 Evo
http://www.samsung.com/semiconductor/minisite/ssd/product/consumer/850evo/

HDD: HPE 300GB 12G SAS Part-Number: 737261-B21
https://h20195.www2.hpe.com/v2/GetPDF.aspx%2Fc04111744.pdf

I intend to do my experiment, between HDD and SSD, abandon the SAS HDD and
use a SATA HDD, to compare with the SATA SSD.
I will use your strategy to put the OS and DBMS on the same disk, when it
is SSD and separate on the HDD.
Best Regards
Neto

> merlin
>

Re: why SSD is slower than HDD SAS 15K ?

[NTPT]

I bet this is a ssd partition alignment problem there are erase block size
of 3mb and this should be taken in account, when You partition ssd drive, 
creating a raid and filesystem etc...
-- Původní e-mail --
Od: Merlin Moncure <mmonc...@gmail.com>
Komu: Neto pr <netopr...@gmail.com>
Datum: 15. 1. 2018 20:17:17
Předmět: Re: why SSD is slower than HDD SAS 15K ?
"On Mon, Jan 15, 2018 at 7:38 AM, Neto pr <netopr...@gmail.com> wrote:
> Hello all,
> Someone help me analyze the two execution plans below (Explain ANALYZE 
> used), is the query 9 of TPC-H benchmark [1].
> I'm using two servers HP Intel Xeon 2.8GHz/4-core - Memory 8GB. O.S.
> Debian8, using EXT4 filesystem.
>
> Server 1
> - HDD SAS 15 Krpm - 320 GB (Location where O.S. Debian and Postgresql are
> installed).
>
> Server 2
> - Samsung Evo SSD 500 GB (Location where Postgresql is Installed)
> - HDD Sata 7500 Krpm - 1TB (Location where O.S Debian is installed)
>
> My DBMS parameters presents in postgresql.conf is default, but in SSD I 
have
> changed random_page_cost = 1.0.
>
> I do not understand, because running on an HDD SAS a query used half the
> time. I explain better, in HDD spends on average 12 minutes the query 
> execution and on SSD spent 26 minutes.
> I think maybe the execution plan is using more write operations, and so 
the
> HDD SAS 15Krpm has been faster.
> I checked that the temporary tablespace pg_default is on the SSD in server

> 2, because when running show temp_tablespaces in psql returns empty, will
be
> in the default directory, where I installed the DBMS in:
> /media/ssd500gb/opt/pgv101norssd/data.
>
> Anyway, I always thought that an SSD would be equal or faster, but in the
> case and four more cases we have here, it lost a lot for the HDDs.

Generally for reading data, yes, but you changed the query plan also.
To get to the bottom of this let's get SSD performance numbers for
both plans and HDD performance numbers for both plans. You're trying
to measure device performance about are probably measuring the
relative efficiencies of the generated plans.

merlin

"

Re: why SSD is slower than HDD SAS 15K ?

[Merlin Moncure]

On Mon, Jan 15, 2018 at 7:38 AM, Neto pr  wrote:
> Hello all,
> Someone help me analyze the two execution plans below (Explain ANALYZE
> used), is the  query 9 of TPC-H benchmark [1].
> I'm using two servers HP Intel Xeon 2.8GHz/4-core - Memory 8GB. O.S.
> Debian8, using EXT4 filesystem.
>
> Server 1
> - HDD SAS 15 Krpm - 320 GB (Location where O.S. Debian and Postgresql are
> installed).
>
> Server 2
> - Samsung Evo SSD 500 GB (Location where Postgresql is Installed)
> - HDD Sata 7500 Krpm - 1TB (Location where O.S Debian is installed)
>
> My DBMS parameters presents in postgresql.conf is default, but in SSD I have
> changed random_page_cost = 1.0.
>
> I do not understand, because running on an HDD SAS a query used half the
> time. I explain better, in HDD spends on average 12 minutes the query
> execution and on SSD spent 26 minutes.
> I think maybe the execution plan is using more write operations, and so the
> HDD SAS 15Krpm has been faster.
> I checked that the temporary tablespace pg_default is on the SSD in server
> 2, because when running show temp_tablespaces in psql returns empty, will be
> in the default directory, where I installed the DBMS in:
> /media/ssd500gb/opt/pgv101norssd/data.
>
> Anyway, I always thought that an SSD would be equal or faster, but in the
> case and four more cases we have here, it lost a lot for the HDDs.

Generally for reading data, yes, but you changed the query plan also.
To get to the bottom of this let's get SSD performance numbers for
both plans and HDD performance numbers for both plans.  You're trying
to measure device performance about are probably measuring the
relative efficiencies of the generated plans.

merlin

Re: why SSD is slower than HDD SAS 15K ?

[Neto pr]

2018-01-15 9:13 GMT-08:00 Vick Khera :

> Try random page cost 1.1. Way back when I started using SSD we had a
> discussion here and came to the conclusion that it should be ever so
> slightly higher than sequential page cost.
>
>
Very good tip, I'm running the query with random_page_cost = 1.1, but
notice that there are no secondary indexes on my database.
The test you were doing is to first run the query without index, and then
create an index to check the performance improvement.
But what I reported that having problem, is that the execution of the query
without index in a SAS HDD is being much faster, than the query (without
index) in the SSD, and I found this very strange, see below:
- Query execution Time on SSD - Average: 26min 54 seconds
- Query execution Time on HDD SAS 15K - Average: 12 minutes 48 seconds


> It is very hard to read your query plans (maybe gmail is wrapping them
> funny or you need to use a fixed font on them or share them from
> https://explain.depesz.com), but they do look substantially different. My
> guess is that with the random page cost = sequential page cost you are
> tricking Pg into using more sequential scans than index searches.
>

The problem is that this plan is saved in the database, I have a Java
application that executes 6 times a query and saves the result of Explain
Analyze to a table in my Database. Upon regaining the execution plan, it
loses the line breaks, unfortunately.  I'm checking how to change the java
application, I sent a question in the java forum because I do not know how
to solve this other problem yet:  My question in Java forum:
https://stackoverflow.com/questions/48267819/save-line-break-in-database-in-text-field

Re: why SSD is slower than HDD SAS 15K ?

[Vick Khera]

Try random page cost 1.1. Way back when I started using SSD we had a
discussion here and came to the conclusion that it should be ever so
slightly higher than sequential page cost.

It is very hard to read your query plans (maybe gmail is wrapping them
funny or you need to use a fixed font on them or share them from
https://explain.depesz.com), but they do look substantially different. My
guess is that with the random page cost = sequential page cost you are
tricking Pg into using more sequential scans than index searches.

why SSD is slower than HDD SAS 15K ?

[Neto pr]

Hello all,
Someone help me analyze the two execution plans below (Explain ANALYZE
used), is the  query 9 of TPC-H benchmark [1].
I'm using two servers HP Intel Xeon 2.8GHz/4-core - Memory 8GB. O.S.
Debian8, using EXT4 filesystem.

Server 1
- HDD SAS 15 Krpm - 320 GB (Location where O.S. Debian and Postgresql are
installed).

Server 2
- Samsung Evo SSD 500 GB (Location where Postgresql is Installed)
- HDD Sata 7500 Krpm - 1TB (Location where O.S Debian is installed)

My DBMS parameters presents in postgresql.conf is default, but in SSD I
have changed random_page_cost = 1.0.

I do not understand, because running on an HDD SAS a query used half the
time. I explain better, in HDD spends on average 12 minutes the query
execution and on SSD spent 26 minutes.
I think maybe the execution plan is using more write operations, and so the
HDD SAS 15Krpm has been faster.
I checked that the temporary tablespace pg_default is on the SSD in server
2, because when running show temp_tablespaces in psql returns empty, will
be in the default directory, where I installed the DBMS in:
/media/ssd500gb/opt/pgv101norssd/data.

Anyway, I always thought that an SSD would be equal or faster, but in the
case and four more cases we have here, it lost a lot for the HDDs.

Any help in understanding, is welcome

Best Regards
Neto

- Query execution Time on SSD ---
execution 1: 00:23:29
execution 2: 00:28:38
execution 3: 00:27:32
execution 4: 00:27:54
execution 5: 00:27:35
execution 6: 00:26:19
Average: 26min 54 seconds

Query execution Time on HDD SAS 15K
--
execution 1: 00:12:44
execution 2: 00:12:30
execution 3: 00:12:47
execution 4: 00:13:02
execution 5: 00:13:00
execution 6: 00:12:47
Average: 12 minutes 48 seconds

- EXECUTION PLAN (ANALYZE, BUFFERS) on SSD
Storage--

Finalize GroupAggregate  (cost=1588.33..15980046.69 rows=60150
width=66) (actual time=1569793.025..1573969.614 rows=175 loops=1)  Group
Key: nation.n_name, (date_part(_year_::text,
(orders.o_orderdate)::timestamp without time zone))  Buffers: shared
hit=1237677 read=2399403, temp read=1186697 written=1183781  ->  Gather
Merge  (cost=1588.33..15977791.06 rows=120300 width=66) (actual
time=1569767.662..1573968.933 rows=525 loops=1)Workers Planned:
2Workers Launched: 2Buffers: shared hit=1237677
read=2399403, temp read=1186697 written=1183781->  Partial
GroupAggregate  (cost=15821228.31..15962905.44 rows=60150 width=66) (actual
time=1547834.941..1552040.073 rows=175 loops=3)  Group Key:
nation.n_name, (date_part(_year_::text, (orders.o_orderdate)::timestamp
without time zone))  Buffers: shared hit=3522992 read=7371656,
temp read=3551003 written=3542253  ->  Sort
(cost=15821228.31..15838806.37 rows=7031225 width=57) (actual
time=1547819.849..1548887.629 rows=4344197 loops=3)Sort
Key: nation.n_name, (date_part(_year_::text,
(orders.o_orderdate)::timestamp without time zone)) DESC
Sort Method: external merge  Disk: 321648kBBuffers:
shared hit=3522992 read=7371656, temp read=3551003
written=3542253->  Hash Join
(cost=4708859.28..14719466.13 rows=7031225 width=57) (actual
time=1220169.593..1541279.300 rows=4344197
loops=3)  Hash Cond: (lineitem.l_suppkey =
supplier.s_suppkey)  Buffers: shared hit=3522922
read=7371656, temp read=3220661 written=3211373
->  Hash Join  (cost=4683017.71..14434606.65 rows=7071075 width=43) (actual
time=1142575.564..1535092.395 rows=4344197
loops=3)Hash Cond: (lineitem.l_orderkey =
orders.o_orderkey)Buffers: shared
hit=3503999 read=7362903, temp read=3114233
written=3104987->  Hash Join
(cost=1993687.71..11297331.33 rows=7071075 width=47) (actual
time=275104.573..1213552.106 rows=4344197
loops=3)  Hash Cond:
((lineitem.l_suppkey = partsupp.ps_suppkey) AND (lineitem.l_partkey =
partsupp.ps_partkey))  Buffers: shared
hit=1478115 read=6073916, temp read=2369833
written=2366725  ->  Hash Join
(cost=273201.71..9157213.44 rows=7071075 width=45) (actual
time=24569.390..895992.716 rows=4344197
loops=3)Hash Cond:
(lineitem.l_partkey =
part.p_partkey)Buffers: shared
hit=314284 read=5038767, temp read=1742656
written=1742614->  Parallel Seq
Scan on lineitem  (cost=0.00..5861333.20 rows=15120 width=41) (actual
time=0.147..712469.002 rows=80004097
loops=3)  Buffers: shared
hit=482 read=4860800->  Hash