Thread: why SSD is slower than HDD SAS 15K ?
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=15822228.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=15822228.33..15977791.06 rows=120300 width=66) (actual time=1569767.662..1573968.933 rows=525 loops=1) Workers Planned: 2 Workers Launched: 2 Buffers: 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: 321648kB Buffers: 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=100005120 width=41) (actual time=0.147..712469.002 rows=80004097 loops=3) Buffers: shared hit=482 read=4860800 -> Hash (cost=263921.00..263921.00 rows=565657 width=4) (actual time=24556.402..24556.402 rows=434469 loops=3) Buckets: 131072 Batches: 8 Memory Usage: 2933kB Buffers: shared hit=313796 read=177967, temp written=3327 -> Seq Scan on part (cost=0.00..263921.00 rows=565657 width=4) (actual time=0.073..24418.923 rows=434469 loops=3) Filter: ((p_name)::text ~~ _%orchid%_::text) Rows Removed by Filter: 7565531 Buffers: shared hit=313796 read=177967 -> Hash (cost=1052986.00..1052986.00 rows=32000000 width=22) (actual time=250328.161..250328.161 rows=32000000 loops=3) Buckets: 65536 Batches: 512 Memory Usage: 3941kB Buffers: shared hit=1163809 read=1035149, temp written=513846 -> Seq Scan on partsupp (cost=0.00..1052986.00 rows=32000000 width=22) (actual time=0.042..238352.960 rows=32000000 loops=3) Buffers: shared hit=1163809 read=1035149 -> Hash (cost=1704955.00..1704955.00 rows=60000000 width=8) (actual time=272705.587..272705.587 rows=60000000 loops=3) Buckets: 131072 Batches: 1024 Memory Usage: 3316kB Buffers: shared hit=2025878 read=1288987, temp written=613128 -> Seq Scan on orders (cost=0.00..1704955.00 rows=60000000 width=8) (actual time=0.149..256480.758 rows=60000000 loops=3) Buffers: shared hit=2025878 read=1288987 -> Hash (cost=18106.56..18106.56 rows=400000 width=30) (actual time=597.929..597.929 rows=400000 loops=3) Buckets: 65536 Batches: 8 Memory Usage: 3549kB Buffers: shared hit=18841 read=8753, temp written=6396 -> Hash Join (cost=1.56..18106.56 rows=400000 width=30) (actual time=0.269..518.588 rows=400000 loops=3) Hash Cond: (supplier.s_nationkey = nation.n_nationkey) Buffers: shared hit=18841 read=8753 -> Seq Scan on supplier (cost=0.00..13197.00 rows=400000 width=12) (actual time=0.246..435.109 rows=400000 loops=3) Buffers: shared hit=18838 read=8753 -> Hash (cost=1.25..1.25 rows=25 width=30) (actual time=0.016..0.016 rows=25 loops=3) Buckets: 1024 Batches: 1 Memory Usage: 10kB Buffers: shared hit=3 -> Seq Scan on nation (cost=0.00..1.25 rows=25 width=30) (actual time=0.007..0.010 rows=25 loops=3) Buffers: shared hit=3Planning time: 2.319 msExecution time: 1574019.504 ms
------------------Execution plan (Explain Analyze) on HDD Storage -------------------------------------------------
Finalize GroupAggregate (cost=14.865.093.59..14942715.87 rows=60150 width=66) (actual time=763039.932..767231.344 rows=175 loops=1) Group Key: nation.n_name, (date_part(_year_::text, (orders.o_orderdate)::timestamp without time zone)) -> Gather Merge (cost=14865093.59..14940460.24 rows=120300 width=66) (actual time=763014.187..767230.826 rows=525 loops=1) Workers Planned: 2 Workers Launched: 2 -> Partial GroupAggregate (cost=14864093.57..14925574.61 rows=60150 width=66) (actual time=758405.567..762576.512 rows=175 loops=3) Group Key: nation.n_name, (date_part(_year_::text, (orders.o_orderdate)::timestamp without time zone)) -> Sort (cost=14864093.57..14871647.12 rows=3021421 width=57) (actual time=758348.786..759400.608 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: 324568kB -> Hash Join (cost=4703389.12..14311687.00 rows=3021421 width=57) (actual time=474033.697..736861.120 rows=4344197 loops=3) Hash Cond: (lineitem.l_suppkey = supplier.s_suppkey) -> Hash Join (cost=4677547.56..14173154.89 rows=3030463 width=43) (actual time=420246.635..728731.259 rows=4344197 loops=3) Hash Cond: (lineitem.l_orderkey = orders.o_orderkey) -> Hash Join (cost=1988224.59..11157928.33 rows=3030463 width=47) (actual time=92246.411..545600.522 rows=4344197 loops=3) Hash Cond: ((lineitem.l_suppkey = partsupp.ps_suppkey) AND (lineitem.l_partkey = partsupp.ps_partkey)) -> Hash Join (cost=267897.64..9150646.81 rows=3030463 width=45) (actual time=9247.722..368140.568 rows=4344197 loops=3) Hash Cond: (lineitem.l_partkey = part.p_partkey) -> Parallel Seq Scan on lineitem (cost=0.00..5861333.40 rows=100005140 width=41) (actual time=41.805..224438.909 rows=80004097 loops=3) -> Hash (cost=263920.35..263920.35 rows=242423 width=4) (actual time=9181.407..9181.407 rows=434469 loops=3) Buckets: 131072 (originally 131072) Batches: 8 (originally 4) Memory Usage: 3073kB -> Seq Scan on part (cost=0.00..263920.35 rows=242423 width=4) (actual time=5.608..9027.871 rows=434469 loops=3) Filter: ((p_name)::text ~~ _%orchid%_::text) Rows Removed by Filter: 7565531 -> Hash (cost=1052934.38..1052934.38 rows=31994838 width=22) (actual time=82524.045..82524.045 rows=32000000 loops=3) Buckets: 65536 Batches: 512 Memory Usage: 3941kB -> Seq Scan on partsupp (cost=0.00..1052934.38 rows=31994838 width=22) (actual time=0.037..37865.003 rows=32000000 loops=3) -> Hash (cost=1704952.32..1704952.32 rows=59999732 width=8) (actual time=98182.919..98182.919 rows=60000000 loops=3) Buckets: 131072 Batches: 1024 Memory Usage: 3316kB -> Seq Scan on orders (cost=0.00..1704952.32 rows=59999732 width=8) (actual time=0.042..43977.490 rows=60000000 loops=3) -> Hash (cost=18106.56..18106.56 rows=400000 width=30) (actual time=555.225..555.225 rows=400000 loops=3) Buckets: 65536 Batches: 8 Memory Usage: 3549kB -> Hash Join (cost=1.56..18106.56 rows=400000 width=30) (actual time=1.748..484.203 rows=400000 loops=3) Hash Cond: (supplier.s_nationkey = nation.n_nationkey) -> Seq Scan on supplier (cost=0.00..13197.00 rows=400000 width=12) (actual time=1.718..408.463 rows=400000 loops=3) -> Hash (cost=1.25..1.25 rows=25 width=30) (actual time=0.019..0.019 rows=25 loops=3) Buckets: 1024 Batches: 1 Memory Usage: 10kB -> Seq Scan on nation (cost=0.00..1.25 rows=25 width=30) (actual time=0.007..0.010 rows=25 loops=3)Planning time: 12.145 msExecution time: 767503.736 ms
-- Query SQL ------------------
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
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=15822228.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=15822228.33..15977791.06 rows=120300 width=66) (actual time=1569767.662..1573968.933 rows=525 loops=1) Workers Planned: 2 Workers Launched: 2 Buffers: 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: 321648kB Buffers: 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=100005120 width=41) (actual time=0.147..712469.002 rows=80004097 loops=3) Buffers: shared hit=482 read=4860800 -> Hash (cost=263921.00..263921.00 rows=565657 width=4) (actual time=24556.402..24556.402 rows=434469 loops=3) Buckets: 131072 Batches: 8 Memory Usage: 2933kB Buffers: shared hit=313796 read=177967, temp written=3327 -> Seq Scan on part (cost=0.00..263921.00 rows=565657 width=4) (actual time=0.073..24418.923 rows=434469 loops=3) Filter: ((p_name)::text ~~ _%orchid%_::text) Rows Removed by Filter: 7565531 Buffers: shared hit=313796 read=177967 -> Hash (cost=1052986.00..1052986.00 rows=32000000 width=22) (actual time=250328.161..250328.161 rows=32000000 loops=3) Buckets: 65536 Batches: 512 Memory Usage: 3941kB Buffers: shared hit=1163809 read=1035149, temp written=513846 -> Seq Scan on partsupp (cost=0.00..1052986.00 rows=32000000 width=22) (actual time=0.042..238352.960 rows=32000000 loops=3) Buffers: shared hit=1163809 read=1035149 -> Hash (cost=1704955.00..1704955.00 rows=60000000 width=8) (actual time=272705.587..272705.587 rows=60000000 loops=3) Buckets: 131072 Batches: 1024 Memory Usage: 3316kB Buffers: shared hit=2025878 read=1288987, temp written=613128 -> Seq Scan on orders (cost=0.00..1704955.00 rows=60000000 width=8) (actual time=0.149..256480.758 rows=60000000 loops=3) Buffers: shared hit=2025878 read=1288987 -> Hash (cost=18106.56..18106.56 rows=400000 width=30) (actual time=597.929..597.929 rows=400000 loops=3) Buckets: 65536 Batches: 8 Memory Usage: 3549kB Buffers: shared hit=18841 read=8753, temp written=6396 -> Hash Join (cost=1.56..18106.56 rows=400000 width=30) (actual time=0.269..518.588 rows=400000 loops=3) Hash Cond: (supplier.s_nationkey = nation.n_nationkey) Buffers: shared hit=18841 read=8753 -> Seq Scan on supplier (cost=0.00..13197.00 rows=400000 width=12) (actual time=0.246..435.109 rows=400000 loops=3) Buffers: shared hit=18838 read=8753 -> Hash (cost=1.25..1.25 rows=25 width=30) (actual time=0.016..0.016 rows=25 loops=3) Buckets: 1024 Batches: 1 Memory Usage: 10kB Buffers: shared hit=3 -> Seq Scan on nation (cost=0.00..1.25 rows=25 width=30) (actual time=0.007..0.010 rows=25 loops=3) Buffers: shared hit=3Planning time: 2.319 msExecution time: 1574019.504 ms
------------------Execution plan (Explain Analyze) on HDD Storage -------------------------------------------------
Finalize GroupAggregate (cost=14.865.093.59..14942715.87 rows=60150 width=66) (actual time=763039.932..767231.344 rows=175 loops=1) Group Key: nation.n_name, (date_part(_year_::text, (orders.o_orderdate)::timestamp without time zone)) -> Gather Merge (cost=14865093.59..14940460.24 rows=120300 width=66) (actual time=763014.187..767230.826 rows=525 loops=1) Workers Planned: 2 Workers Launched: 2 -> Partial GroupAggregate (cost=14864093.57..14925574.61 rows=60150 width=66) (actual time=758405.567..762576.512 rows=175 loops=3) Group Key: nation.n_name, (date_part(_year_::text, (orders.o_orderdate)::timestamp without time zone)) -> Sort (cost=14864093.57..14871647.12 rows=3021421 width=57) (actual time=758348.786..759400.608 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: 324568kB -> Hash Join (cost=4703389.12..14311687.00 rows=3021421 width=57) (actual time=474033.697..736861.120 rows=4344197 loops=3) Hash Cond: (lineitem.l_suppkey = supplier.s_suppkey) -> Hash Join (cost=4677547.56..14173154.89 rows=3030463 width=43) (actual time=420246.635..728731.259 rows=4344197 loops=3) Hash Cond: (lineitem.l_orderkey = orders.o_orderkey) -> Hash Join (cost=1988224.59..11157928.33 rows=3030463 width=47) (actual time=92246.411..545600.522 rows=4344197 loops=3) Hash Cond: ((lineitem.l_suppkey = partsupp.ps_suppkey) AND (lineitem.l_partkey = partsupp.ps_partkey)) -> Hash Join (cost=267897.64..9150646.81 rows=3030463 width=45) (actual time=9247.722..368140.568 rows=4344197 loops=3) Hash Cond: (lineitem.l_partkey = part.p_partkey) -> Parallel Seq Scan on lineitem (cost=0.00..5861333.40 rows=100005140 width=41) (actual time=41.805..224438.909 rows=80004097 loops=3) -> Hash (cost=263920.35..263920.35 rows=242423 width=4) (actual time=9181.407..9181.407 rows=434469 loops=3) Buckets: 131072 (originally 131072) Batches: 8 (originally 4) Memory Usage: 3073kB -> Seq Scan on part (cost=0.00..263920.35 rows=242423 width=4) (actual time=5.608..9027.871 rows=434469 loops=3) Filter: ((p_name)::text ~~ _%orchid%_::text) Rows Removed by Filter: 7565531 -> Hash (cost=1052934.38..1052934.38 rows=31994838 width=22) (actual time=82524.045..82524.045 rows=32000000 loops=3) Buckets: 65536 Batches: 512 Memory Usage: 3941kB -> Seq Scan on partsupp (cost=0.00..1052934.38 rows=31994838 width=22) (actual time=0.037..37865.003 rows=32000000 loops=3) -> Hash (cost=1704952.32..1704952.32 rows=59999732 width=8) (actual time=98182.919..98182.919 rows=60000000 loops=3) Buckets: 131072 Batches: 1024 Memory Usage: 3316kB -> Seq Scan on orders (cost=0.00..1704952.32 rows=59999732 width=8) (actual time=0.042..43977.490 rows=60000000 loops=3) -> Hash (cost=18106.56..18106.56 rows=400000 width=30) (actual time=555.225..555.225 rows=400000 loops=3) Buckets: 65536 Batches: 8 Memory Usage: 3549kB -> Hash Join (cost=1.56..18106.56 rows=400000 width=30) (actual time=1.748..484.203 rows=400000 loops=3) Hash Cond: (supplier.s_nationkey = nation.n_nationkey) -> Seq Scan on supplier (cost=0.00..13197.00 rows=400000 width=12) (actual time=1.718..408.463 rows=400000 loops=3) -> Hash (cost=1.25..1.25 rows=25 width=30) (actual time=0.019..0.019 rows=25 loops=3) Buckets: 1024 Batches: 1 Memory Usage: 10kB -> Seq Scan on nation (cost=0.00..1.25 rows=25 width=30) (actual time=0.007..0.010 rows=25 loops=3)Planning time: 12.145 msExecution time: 767503.736 ms
-- Query SQL ------------------
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
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.
2018-01-15 9:13 GMT-08:00 Vick Khera <vivek@khera.org>:
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
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
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
On Mon, Jan 15, 2018 at 7:38 AM, Neto pr <netoprbr9@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
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 <mmoncure@gmail.com>
Komu: Neto pr <netoprbr9@gmail.com>
Datum: 15. 1. 2018 20:17:17
Předmět: Re: why SSD is slower than HDD SAS 15K ?
---------- Původní e-mail ----------
Od: Merlin Moncure <mmoncure@gmail.com>
Komu: Neto pr <netoprbr9@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 <netoprbr9@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
Dear Merlin
2018-01-15 11:16 GMT-08:00 Merlin Moncure <mmoncure@gmail.com>:
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/
HDD: HPE 300GB 12G SAS Part-Number: 737261-B21
https://h20195.www2.hpe.com/
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.
Generally for reading data, yes, but you changed the query plan also.On Mon, Jan 15, 2018 at 7:38 AM, Neto pr <netoprbr9@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.
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/
HDD: HPE 300GB 12G SAS Part-Number: 737261-B21
https://h20195.www2.hpe.com/
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
Dear NTPT
2018-01-15 16:54 GMT-08:00 NTPT <NTPT@seznam.cz>:
That is a good observation. I believe the block size was set by default when I formatted the drive. I use Debian 64bits version 8, and all disks are with ext4 file system. What size block do you suggest for SSD and HDD?
Neto
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...
That is a good observation. I believe the block size was set by default when I formatted the drive. I use Debian 64bits version 8, and all disks are with ext4 file system. What size block do you suggest for SSD and HDD?
Neto
---------- Původní e-mail ----------
Od: Merlin Moncure <mmoncure@gmail.com>
Komu: Neto pr <netoprbr9@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 <netoprbr9@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
it depend of ssd type ie different ssd need diferent alignment. . on samsung evo should be partition aligned to 3072 not default 2048 , to start on erase block bounduary . And fs block should be 8kb (as I remember correctly...)
---------- Původní e-mail ----------
Od: Neto pr <netoprbr9@gmail.com>
Komu: NTPT <NTPT@seznam.cz>, PostgreSQL General <pgsql-general@postgresql.org>
Datum: 16. 1. 2018 2:54:49
Předmět: Re: why SSD is slower than HDD SAS 15K ?
---------- Původní e-mail ----------
Od: Neto pr <netoprbr9@gmail.com>
Komu: NTPT <NTPT@seznam.cz>, PostgreSQL General <pgsql-general@postgresql.org>
Datum: 16. 1. 2018 2:54:49
Předmět: Re: why SSD is slower than HDD SAS 15K ?
Dear NTPT2018-01-15 16:54 GMT-08:00 NTPT <NTPT@seznam.cz>: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...
That is a good observation. I believe the block size was set by default when I formatted the drive. I use Debian 64bits version 8, and all disks are with ext4 file system. What size block do you suggest for SSD and HDD?
Neto---------- Původní e-mail ----------
Od: Merlin Moncure <mmoncure@gmail.com>
Komu: Neto pr <netoprbr9@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 <netoprbr9@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