How to increase gather motion performance

[George De]

Hi,

First of all, sorry for my English)

 

So, i have one phycal host managed by Centos 7.5 with installed GP (5.11.1) on it. Host specs:

1. CPU: i7 6800k@4GHz 6 cores +HT(SMT)
2. Mem: 16GB@3.2GHz
3. Local storage: 1 x nvme ssd, 6 x sata hdd@7200rpm

GP master installed on ssd and 6 segments, each installed on separate hdd. Interconnect represented by loopback interface. No VMs used, all on baremetal.

Test data consists of financial market quotes. I created append-only table with 120 millions rows (6 gb raw data). Table had 6 columns with pseudo id (ai) for distributing across segments, timestamp for some 'where' clause and 4 columns with quotes.

My workloads are truly simple, like 'select all' or 'select all where...'.

The first time when i execute explain analyze command for 'select all' query i see that GP reading data from disks (70-80 mb/s on each disk from iotop utility), cache them and send to master (300-350 mb/s from bmon utility), total time was 20-22 seconds. Next time executing previous command i had similar results, exclude disk activity (data was in cache). All queries used GPORCA and had same query plan with table scan and finally gather motion (all rows) .

Also, if it is neccessary all queries executes from psql on localhost.

So, please help me understanding this situations:

1. Why i had similar total time results both on cached and reading from disk data provided by explain analyze command.
2. Why performance on gather motion node, when segments send data to master are so slow (300-350 mb/s with bandwidth of loopback interface arround 70 gbit/s). So i am confused of this, because if i use two separate hosts (master host and segment host) with 10 gbe nics i can see same situation (300-350 mb/s between hosts) - i think it can't be so..

In another words, help me determine what can be the reason of bottlenecks or maybe on what i must first looking to solve this.

I trying to use different os params and GP config settings but in most situations i had decreasing perfomance or get same results.

 

[Faisal Ali]

Could you please share the

 

• Explain plan
• Definition of the tables on the query.
• and the query

Thanks

[George De]

Hi, thanks for your interest.

I had misunderstanding phrase 'Definition of the tables on the query' - what it means, but maybe i provide next what you need, if not, please explain me what kind of information you need.

test=# \d+ test1_eurrub_comp0

                                                               Append-Only Columnar Table "public.test1_eurrub_comp0"

 Column   |           Type             |                           Modifiers                            | Storage | Compression Type | Compression Level | Block Size

-----------------+-------------------------------------------+-----------------------------------------------------------------------------------------+-----------+------------------+-------------------+------------+-------------

 id        | integer                     | not null default nextval('test1_eurrub_comp0_id_seq'::regclass) | plain   | none            | 0                | 32768     |

 timestamp | timestamp without time zone | not null                                                        | plain   | none             | 0               | 32768     |

 ask       | double precision            | not null                                                        | plain   | none            | 0                | 32768     |

 bid       | double precision            | not null                                                        | plain   | none            | 0                | 32768     |

 askvol    | double precision            | not null                                                        | plain   | none            | 0                | 32768     |

 bidvol    | double precision            | not null                                                        | plain   | none            | 0                | 32768     |

Checksum: t

Has OIDs: no

Options: appendonly=true, orientation=column

Distributed by: (id)

test=> select count(*) from test1_eurrub_comp0;

  count  

-----------

 117363948

(1 row)

test=# select pg_size_pretty(pg_relation_size('test1_eurrub_comp0'));

 pg_size_pretty

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

 4931 MB

(1 row)

This is the best result that i can get (data was cached):

test=> explain analyze select * from test1_eurrub_comp0;

                                                       QUERY PLAN                                                        

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

 Gather Motion 6:1 (slice1; segments: 6) (cost=0.00..17958.52 rows=117363952 width=44)

  Rows out: 117363948 rows at destination with 1.048 ms to first row, 16336 ms to end.

  -> Table Scan on test1_eurrub_comp0 (cost=0.00..1054.99 rows=19560659 width=44)

        Rows out: Avg 19560658.0 rows x 6 workers. Max 19561007 rows (seg4) with 0.981 ms to first row, 1943 ms to end.

 Slice statistics:

  (slice0)   Executor memory: 322K bytes.

  (slice1)   Executor memory: 797K bytes avg x 6 workers, 797K bytes max (seg0).

 Statement statistics:

  Memory used: 128000K bytes

 Optimizer status: PQO version 3.1.0

 Total runtime: 19985.224 ms

(11 rows)