Pivotal Knowledge Base

Follow

Writable External Table with Hash Distribution Shows Slow Performance

Environment

 Product  Version
 Pivotal HDB  2.0.x / 2.1.0 / 2.1.1
 OS  RHEL 6.x

Symptom

When migrating from HAWQ 1.3.x to Pivotal HDB 2.0.x or Pivotal HDB 2.1.1, writable external table with Hash distribution show some performance issues.

Steps to reproduce

1. Create an internal table & populate it:

gpadmin=# CREATE TABLE tbl1 (a int, b text) DISTRIBUTED BY (a);
CREATE TABLE
gpadmin=# INSERT INTO tbl1 VALUES (generate_series(1,1000),'aaa');
INSERT 0 1000
gpadmin=# INSERT INTO tbl1 VALUES (generate_series(1,10000),'bbb');
INSERT 0 10000
gpadmin=# INSERT INTO tbl1 VALUES (generate_series(1,100000),'bbc');
INSERT 0 100000
gpadmin=# INSERT INTO tbl1 VALUES (generate_series(1,1000000),'bdbc');
INSERT 0 1000000
gpadmin=# INSERT INTO tbl1 VALUES (generate_series(1,1000000),'bdddbc');
INSERT 0 1000000

2. Create two writable external tables - One for Hash distribution, and the other one is for Random distribution:

gpadmin=# CREATE WRITABLE EXTERNAL TABLE ext_tbl1
gpadmin-# ( LIKE tbl1 )
gpadmin-# LOCATION ('gpfdist://172.28.21.190/tbl1.csv')
gpadmin-# FORMAT 'CSV' (DELIMITER ',')
gpadmin-# DISTRIBUTED BY (a);
CREATE EXTERNAL TABLE
gpadmin=#
gpadmin=# CREATE WRITABLE EXTERNAL TABLE ext_tbl1_random
gpadmin-# ( LIKE tbl1 )
gpadmin-# LOCATION ('gpfdist://172.28.21.190/tbl1.csv')
gpadmin-# FORMAT 'CSV' (DELIMITER ',')
gpadmin-# DISTRIBUTED RANDOMLY;
CREATE EXTERNAL TABLE

3. Check the timings against both tables:

gpadmin=# \timing
Timing is on.

-- First for the writable external table with Hash distribution
gpadmin=# INSERT INTO ext_tbl1 SELECT * from tbl1 ;
INSERT 0 2111000
Time: 5111.551 ms
gpadmin=# INSERT INTO ext_tbl1 SELECT * from tbl1 ;
INSERT 0 2111000
Time: 6447.045 ms
gpadmin=# INSERT INTO ext_tbl1 SELECT * from tbl1 ;
INSERT 0 2111000
Time: 5037.442 ms

-- Second for the writable external table with Random distribution
gpadmin=# INSERT INTO ext_tbl1_random SELECT * from tbl1 ;
INSERT 0 2111000
Time: 3468.034 ms
gpadmin=# INSERT INTO ext_tbl1_random SELECT * from tbl1 ;
INSERT 0 2111000
Time: 2174.876 ms
gpadmin=# INSERT INTO ext_tbl1_random SELECT * from tbl1 ;
INSERT 0 2111000
Time: 2728.428 ms

4. Run explain analyze against both queries:

gpadmin=# explain analyze INSERT INTO ext_tbl1 SELECT * from tbl1 ;

                                                           QUERY PLAN

------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------
Insert  (cost=0.00..165516.60 rows=2111000 width=9)
   Rows out:  Avg 2111000.0 rows x 1 workers.  Max/Last(seg0:hdw3/seg0:hdw3) 2111000/2111000 rows with 8.601/8.601 ms to first row, 5292/5292 ms to en
d, start offset by 33/33 ms.
   Executor memory:  1K bytes.
   ->  Result  (cost=0.00..594.73 rows=2111000 width=20)
         Rows out:  Avg 2111000.0 rows x 1 workers.  Max/Last(seg0:hdw3/seg0:hdw3) 2111000/2111000 rows with 6.688/6.688 ms to first row, 2057/2057 ms
to end, start offset by 33/33 ms.
         ->  Redistribute Motion 1:1  (slice1; segments: 1)  (cost=0.00..552.51 rows=2111000 width=9)
               Hash Key: tbl1.a
               Rows out:  Avg 2111000.0 rows x 1 workers at destination.  Max/Last(seg0:hdw3/seg0:hdw3) 2111000/2111000 rows with 6.684/6.684 ms to fi
rst row, 1364/1364 ms to end, start offset by 33/33 ms.
               ->  Table Scan on tbl1  (cost=0.00..457.70 rows=2111000 width=9)
                     Rows out:  Avg 2111000.0 rows x 1 workers.  Max/Last(seg0:hdw3/seg0:hdw3) 2111000/2111000 rows with 6.221/6.221 ms to first row,
396/396 ms to end, start offset by 33/33 ms.
Slice statistics:
   (slice0)    Executor memory: 293K bytes (seg0:hdw3).
   (slice1)    Executor memory: 303K bytes (seg0:hdw3).
Statement statistics:
   Memory used: 262144K bytes
Settings:  default_hash_table_bucket_number=18
Optimizer status: PQO version 1.638
Dispatcher statistics:
   executors used(total/cached/new connection): (2/2/0); dispatcher time(total/connection/dispatch data): (0.246 ms/0.000 ms/0.068 ms).
   dispatch data time(max/min/avg): (0.046 ms/0.019 ms/0.033 ms); consume executor data time(max/min/avg): (0.023 ms/0.014 ms/0.018 ms); free executor
time(max/min/avg): (0.000 ms/0.000 ms/0.000 ms).
Data locality statistics:
   data locality ratio: 1.000; virtual segment number: 1; different host number: 1; virtual segment number per host(avg/min/max): (1/1/1); segment siz
e(avg/min/max): (46024648.000 B/46024648 B/46024648 B); segment size with penalty(avg/min/max): (46024648.000 B/46024648 B/46024648 B); continuity(avg
/min/max): (1.000/1.000/1.000); DFS metadatacache: 0.238 ms; resource allocation: 0.892 ms; datalocality calculation: 0.147 ms.
Total runtime: 5524.745 ms
(23 rows)
gpadmin=# explain analyze INSERT INTO ext_tbl1_random SELECT * from tbl1 ;

                                               QUERY PLAN

------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------
Insert  (cost=0.00..9602.42 rows=117278 width=9)
   Rows out:  Avg 117277.8 rows x 18 workers.  Max/Last(seg7:hdw2/seg2:hdw1) 117742/117118 rows with 54/54 ms to first row, 2084/2135 ms to end, start
offset by 4.001/12 ms.
   Executor memory:  1K bytes avg, 1K bytes max (seg17:hdw2).
   ->  Result  (cost=0.00..440.10 rows=117278 width=20)
         Rows out:  Avg 117277.8 rows x 18 workers.  Max/Last(seg7:hdw2/seg0:hdw1) 117742/117576 rows with 21/6.728 ms to first row, 179/246 ms to end
, start offset by 4.002/15 ms.
         ->  Redistribute Motion 18:18  (slice1; segments: 18)  (cost=0.00..437.75 rows=117278 width=9)
               Rows out:  Avg 117277.8 rows x 18 workers at destination.  Max/Last(seg7:hdw2/seg15:hdw3) 117742/117398 rows with 21/14 ms to first row
, 113/174 ms to end, start offset by 4.002/21 ms.
               ->  Table Scan on tbl1  (cost=0.00..432.48 rows=117278 width=9)
                     Rows out:  Avg 117277.8 rows x 18 workers.  Max/Last(seg4:hdw1/seg2:hdw1) 117494/117440 rows with 41/43 ms to first row, 65/102 m
s to end, start offset by 21/29 ms.
Slice statistics:
   (slice0)    Executor memory: 369K bytes avg x 18 workers, 369K bytes max (seg17:hdw3).
   (slice1)    Executor memory: 591K bytes avg x 18 workers, 591K bytes max (seg17:hdw3).
Statement statistics:
   Memory used: 262144K bytes
Settings:  default_hash_table_bucket_number=18
Optimizer status: PQO version 1.638
Dispatcher statistics:
   executors used(total/cached/new connection): (36/36/0); dispatcher time(total/connection/dispatch data): (2.110 ms/0.000 ms/1.406 ms).
   dispatch data time(max/min/avg): (0.137 ms/0.015 ms/0.035 ms); consume executor data time(max/min/avg): (0.216 ms/0.006 ms/0.021 ms); free executor
time(max/min/avg): (0.000 ms/0.000 ms/0.000 ms).
Data locality statistics:
   data locality ratio: 1.000; virtual segment number: 18; different host number: 3; virtual segment number per host(avg/min/max): (6/6/6); segment si
ze(avg/min/max): (2556924.889 B/2551520 B/2561512 B); segment size with penalty(avg/min/max): (0.000 B/0 B/0 B); continuity(avg/min/max): (1.000/1.000
/1.000); DFS metadatacache: 0.261 ms; resource allocation: 1.095 ms; datalocality calculation: 0.138 ms.
Total runtime: 2185.380 ms
(22 rows)

5. The same query in HDB 1.3 looks like this:

gpadmin=# explain analyze INSERT INTO ext_tbl1 SELECT * from tbl1 ;
                                                         QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
Insert  (cost=0.00..451.84 rows=167 width=8)
   Rows out:  Avg 166.7 rows x 6 workers.  Max 181 rows (seg4) with 19 ms to first row, 20 ms to end, start offset by 50 ms.
   Executor memory:  1K bytes avg, 1K bytes max (seg0).
   ->  Result  (cost=0.00..431.01 rows=167 width=32)
         Rows out:  Avg 166.7 rows x 6 workers.  Max 181 rows (seg4) with 14 ms to end, start offset by 50 ms.
         ->  Table Scan on tbl1  (cost=0.00..431.00 rows=167 width=8)
               Rows out:  Avg 166.7 rows x 6 workers.  Max 181 rows (seg4) with 14 ms to end, start offset by 50 ms.
Slice statistics:
   (slice0)    Executor memory: 280K bytes avg x 6 workers, 280K bytes max (seg0).
Statement statistics:
   Memory used: 128000K bytes
Settings:  optimizer=on
Total runtime: 74.607 ms
(13 rows)

Cause 

This performance issue is caused by a code defect when only one segment is used (vs. multiple) for the writing operation. This was reported under software defect: GPSQL-3330.

Resolution

This issue will be fixed in the future releases of HAWQ (higher that 2.1.1). Please review the release notes to confirm that this issue has been resolved.  

Workaround

Use Random distribution table instead of Hash distribution, for example:

gpadmin=# CREATE WRITABLE EXTERNAL TABLE ext_tbl1_random
gpadmin-# ( LIKE tbl1 )
gpadmin-# LOCATION ('gpfdist://172.28.21.190/tbl1.csv')
gpadmin-# FORMAT 'CSV' (DELIMITER ',')
gpadmin-# DISTRIBUTED RANDOMLY;

 

 

Comments

Powered by Zendesk