Re: HDD vs SSD without explanation - Mailing list pgsql-performance

2018-01-14 15:59 GMT-08:00 Neto pr <netopr9@gmail.com>:
> Thanks for the reply.
> I'll try upload the execution plan with Explain (analyse, buffer) for
> website:  https://explain.depesz.com/
>

Below is a new execution plan, with Analyze, BUFFERS. This time,
without changing anything in the configuration of the DBMS, I just
rebooted the DBMS, the time of 16 minutes was obtained, against the 26
minutes of another execution. But it still has not managed to exceed
the execution time in HDD SAS 15Krpm.
I was not able to upload to the site, because I'm saving the execution
plan in the database, and when I retrieve it, it loses the line
breaks, and the dxxxx site does not allow uploading.


------------------- Execution Plan with Buffers  executed on SSD
Stores.---------------------------------------------------------------------------------------------

Finalize GroupAggregate  (cost=15822228.33..15980046.69 rows=60150
width=66) (actual time=969248.287..973686.679 rows=175 loops=1)  Group
Key: nation.n_name, (date_part(_year_::text,
(orders.o_orderdate)::timestamp without time zone))  Buffers: shared
hit=1327602 read=2305013, temp read=1183857 written=1180940  ->
Gather Merge  (cost=15822228.33..15977791.06 rows=120300 width=66)
(actual time=969222.164..973685.582 rows=525 loops=1)        Workers
Planned: 2        Workers Launched: 2        Buffers: shared
hit=1327602 read=2305013, temp read=1183857 written=1180940        ->
Partial GroupAggregate  (cost=15821228.31..15962905.44 rows=60150
width=66) (actual time=941985.137..946403.344 rows=175 loops=3)
      Group Key: nation.n_name, (date_part(_year_::text,
(orders.o_orderdate)::timestamp without time zone))
Buffers: shared hit=3773802 read=7120852, temp read=3550293
written=3541542              ->  Sort  (cost=15821228.31..15838806.37
rows=7031225 width=57) (actual time=941954.595..943119.850
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:
320784kB                    Buffers: shared hit=3773802 read=7120852,
temp read=3550293 written=3541542                    ->  Hash Join
(cost=4708859.28..14719466.13 rows=7031225 width=57) (actual
time=619996.638..933725.615 rows=4344197 loops=3)
    Hash Cond: (lineitem.l_suppkey = supplier.s_suppkey)
           Buffers: shared hit=3773732 read=7120852, temp read=3220697
written=3211409                          ->  Hash Join
(cost=4683017.71..14434606.65 rows=7071075 width=43) (actual
time=579893.395..926348.061 rows=4344197 loops=3)
          Hash Cond: (lineitem.l_orderkey = orders.o_orderkey)
                       Buffers: shared hit=3758207 read=7108695, temp
read=3114271 written=3105025                                ->  Hash
Join  (cost=1993687.71..11297331.33 rows=7071075 width=47) (actual
time=79741.803..805259.856 rows=4344197 loops=3)
               Hash Cond: ((lineitem.l_suppkey = partsupp.ps_suppkey)
AND (lineitem.l_partkey = partsupp.ps_partkey))
              Buffers: shared hit=1754251 read=5797780, temp
read=2369849 written=2366741                                      ->
Hash Join  (cost=273201.71..9157213.44 rows=7071075 width=45) (actual
time=5363.078..672302.517 rows=4344197 loops=3)
                    Hash Cond: (lineitem.l_partkey = part.p_partkey)
                                         Buffers: shared hit=325918
read=5027133, temp read=1742658 written=1742616
                    ->  Parallel Seq Scan on lineitem
(cost=0.00..5861333.20 rows=100005120 width=41) (actual
time=0.129..536226.436 rows=80004097 loops=3)
                        Buffers: shared hit=2 read=4861280
                               ->  Hash  (cost=263921.00..263921.00
rows=565657 width=4) (actual time=5362.100..5362.100 rows=434469
loops=3)                                                  Buckets:
131072  Batches: 8  Memory Usage: 2933kB
                   Buffers: shared hit=325910 read=165853, temp
written=3327                                                  ->  Seq
Scan on part  (cost=0.00..263921.00 rows=565657 width=4) (actual
time=0.025..5279.959 rows=434469 loops=3)
                          Filter: ((p_name)::text ~~ _%orchid%_::text)
                                                       Rows Removed by
Filter: 7565531
Buffers: shared hit=325910 read=165853
     ->  Hash  (cost=1052986.00..1052986.00 rows=32000000 width=22)
(actual time=74231.061..74231.061 rows=32000000 loops=3)
                             Buckets: 65536  Batches: 512  Memory
Usage: 3941kB                                            Buffers:
shared hit=1428311 read=770647, temp written=513846
                        ->  Seq Scan on partsupp
(cost=0.00..1052986.00 rows=32000000 width=22) (actual
time=0.037..66316.652 rows=32000000 loops=3)
                       Buffers: shared hit=1428311 read=770647
                       ->  Hash  (cost=1704955.00..1704955.00
rows=60000000 width=8) (actual time=46310.630..46310.630 rows=60000000
loops=3)                                      Buckets: 131072
Batches: 1024  Memory Usage: 3316kB
  Buffers: shared hit=2003950 read=1310915, temp written=613128
                              ->  Seq Scan on orders
(cost=0.00..1704955.00 rows=60000000 width=8) (actual
time=0.033..34352.493 rows=60000000 loops=3)
                 Buffers: shared hit=2003950 read=1310915
            ->  Hash  (cost=18106.56..18106.56 rows=400000 width=30)
(actual time=226.360..226.360 rows=400000 loops=3)
           Buckets: 65536  Batches: 8  Memory Usage: 3549kB
                    Buffers: shared hit=15437 read=12157, temp
written=6396                                ->  Hash Join
(cost=1.56..18106.56 rows=400000 width=30) (actual time=0.037..145.779
rows=400000 loops=3)                                      Hash Cond:
(supplier.s_nationkey = nation.n_nationkey)
          Buffers: shared hit=15437 read=12157
             ->  Seq Scan on supplier  (cost=0.00..13197.00
rows=400000 width=12) (actual time=0.014..63.768 rows=400000 loops=3)
                                          Buffers: shared hit=15434
read=12157                                      ->  Hash
(cost=1.25..1.25 rows=25 width=30) (actual time=0.015..0.015 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.006..0.008 rows=25 loops=3)
             Buffers: shared hit=3Planning time: 16.668 msExecution
time: 973799.430 ms


------------------------------------------------------------------------------------------------------------------------------------------------

> I'm make an experiment for a scientific research and this is what I
> find strange, explaining better, strange HDD performance far outweigh
> the performance of an SSD.
>
> Do you think that if you run a VACUMM FULL the performance with the
> SSD will be better than a 15Krpm SAS HDD?
>
> Best Regards
> Neto
> <div id="DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2"><br /> <table
> style="border-top: 1px solid #D3D4DE;">
>         <tr>
>       <td style="width: 55px; padding-top: 18px;"><a
> href="https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail"
> target="_blank"><img
> src="https://ipmcdn.avast.com/images/icons/icon-envelope-tick-round-orange-animated-no-repeat-v1.gif"
> alt="" width="46" height="29" style="width: 46px; height: 29px;"
> /></a></td>
>                 <td style="width: 470px; padding-top: 17px; color: #41424e;
> font-size: 13px; font-family: Arial, Helvetica, sans-serif;
> line-height: 18px;">Livre de vírus. <a
> href="https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail"
> target="_blank" style="color: #4453ea;">www.avast.com</a>.              </td>
>         </tr>
> </table>
> <a href="#DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2" width="1" height="1"></a></div>
>
> 2018-01-14 19:40 GMT-02:00 Justin Pryzby <pryzby@telsasoft.com>:
>> On Sun, Jan 14, 2018 at 12:44:00PM -0800, Neto pr wrote:
>>> Dear 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 a server HP Intel Xeon 2.8GHz/4-core - Memory 8GB HDD SAS 320GB
>>> 15 Krpm AND SSD Sansung EVO 500GB.
>>>
>>> I think maybe the execution plan is using more write operations, and so the
>>> HDD SAS 15Krpm has been faster.
>>
>> The query plan is all garbled by mail , could you resend?  Or post a link from
>> https://explain.depesz.com/
>>
>> To see if the query is causing many writes (due to dirty pages, sorts, etc),
>> run with explain(analyze,buffers)
>>
>> But from what I could tell, your problems are here:
>>
>> ->  Parallel Seq Scan on lineitem  (cost=0.00..5861332.93 rows=100005093 width=41) (actual TIME=3.494..842667.110
rows=80004097loops=3) 
>> vs
>> ->  Parallel Seq Scan on lineitem  (cost=0.00..5861333.40 rows=100005140 width=41) (actual TIME=41.805..224438.909
rows=80004097loops=3) 
>>
>> ->  Seq Scan on partsupp (cost=0.00..1052983.08 rows=31999708 width=22) (actual TIME=0.033..228828.149 rows=32000000
loops=3)
>> vs
>> ->  Seq Scan on partsupp  (cost=0.00..1052934.38 rows=31994838 width=22) (actual TIME=0.037..37865.003 rows=32000000
loops=3)
>>
>> Can you reproduce the speed difference using dd ?
>> time sudo dd if=/dev/sdX of=/dev/null bs=1M count=32K skip=$((128*$RANDOM/32)) # set bs to optimal_io_size
>>
>> Or: bonnie++ -f -n0
>>
>> What OS/kernel are you using?  LVM?  filesystem?  I/O scheduler?  partitions?
>> readahead?  blockdev --getra
>>
>> If you're running under linux, maybe you can just send the output of:
>> for a in /sys/block/sdX/queue/*; do echo "$a `cat $a`"; done
>> or: tail
/sys/block/sdX/queue/{minimum_io_size,optimal_io_size,read_ahead_kb,scheduler,rotational,max_sectors_kb,logical_block_size,physical_block_size}
>>
>> Justin