Re: Append with naive multiplexing of FDWs

Hi Hackers,

Sharing the email below from Movead Li, I believe he wanted to share the
benchmarking results as a response to this email thread but it started a
new thread.. Here it is...

"
Hello

I have tested the patch with a partition table with several foreign
partitions living on seperate data nodes. The initial testing was done
with a partition table having 3 foreign partitions, test was done with
variety of scale facters. The seonnd test was with fixed data per data
node but number of data nodes were increased incrementally to see
the peformance impact as more nodes are added to the cluster. The
test three is similar to the initial test but with much huge data and
4 nodes.

The results are summary is given below and test script attached:

*Test ENV*
Parent node:2Core 8G
Child Nodes:2Core 4G

*Test one:*

1.1 The partition struct as below:

[ ptf:(a int, b int, c varchar)]
(Parent node)
| | |
[ptf1] [ptf2] [ptf3]
(Node1) (Node2) (Node3)

The table data is partitioned across nodes, the test is done using a
simple select query and a count aggregate as shown below. The result
is an average of executing each query multiple times to ensure reliable
and consistent results.

①select * from ptf where b = 100;
②select count(*) from ptf;

1.2. Test Results

For ① result:
scalepernode master patched performance
2G 7s 2s 350%
5G 173s 63s 275%
10G 462s 156s 296%
20G 968s 327s 296%
30G 1472s 494s 297%

For ② result:
scalepernode master patched performance
2G 1079s 291s 370%
5G 2688s 741s 362%
10G 4473s 1493s 299%

It takes too long time to test a aggregate so the test was done with a
smaller data size.

1.3. summary

With the table partitioned over 3 nodes, the average performance gain
across variety of scale factors is almost 300%

*Test Two*
2.1 The partition struct as below:

[ ptf:(a int, b int, c varchar)]
(Parent node)
| | |
[ptf1] ... [ptfN]
(Node1) (...) (NodeN)

①select * from ptf
②select * from ptf where b = 100;

This test is done with same size of data per node but table is partitioned
across N number of nodes. Each varation (master or patches) is tested
at-least 3 times to get reliable and consistent results. The purpose of the
test is to see impact on performance as number of data nodes are increased.

2.2 The results

For ① result（scalepernode=2G）:
nodenumber master patched performance
2 432s 180s 240%
3 636s 223s 285%
4 830s 283s 293%
5 1065s 361s 295%
For ② result（scalepernode=10G）:
nodenumber master patched performance
2 281s 140s 201%
3 421s 140s 300%
4 562s 141s 398%
5 702s 141s 497%
6 833s 139s 599%
7 986s 141s 699%
8 1125s 140s 803%

*Test Three*

This test is similar to the [test one] but with much huge data and
4 nodes.

For ① result:
scalepernode master patched performance
100G 6592s 1649s 399%
For ② result:
scalepernode master patched performance
100G 35383 12363 286%
The result show it work well in much huge data.

*Summary*
The patch is pretty good, it works well when there were little data back to
the parent node. The patch doesn’t provide parallel FDW scan, it ensures
that child nodes can send data to parent in parallel but the parent can
only
sequennly process the data from data nodes.

Providing there is no performance degrdation for non FDW append queries,
I would recomend to consider this patch as an interim soluton while we are
waiting for parallel FDW scan.
"

On Thu, Dec 12, 2019 at 5:41 PM Kyotaro Horiguchi <horikyota(dot)ntt(at)gmail(dot)com>
wrote:

> Hello.
>
> I think I can say that this patch doesn't slows non-AsyncAppend,
> non-postgres_fdw scans.
>
>
> At Mon, 9 Dec 2019 12:18:44 -0500, Bruce Momjian <bruce(at)momjian(dot)us> wrote
> in
> > Certainly any overhead on normal queries would be unacceptable.
>
> I took performance numbers on the current shape of the async execution
> patch for the following scan cases.
>
> t0 : single local table (parallel disabled)
> pll : local partitioning (local Append, parallel disabled)
> ft0 : single foreign table
> pf0 : inheritance on 4 foreign tables, single connection
> pf1 : inheritance on 4 foreign tables, 4 connections
> ptf0 : partition on 4 foreign tables, single connection
> ptf1 : partition on 4 foreign tables, 4 connections
>
> The benchmarking system is configured as the follows on a single
> machine.
>
> [ benchmark client ]
> | |
> (localhost:5433) (localhost:5432)
> | |
> +----+ | +------+ |
> | V V V | V
> | [master server] | [async server]
> | V | V
> +--fdw--+ +--fdw--+
>
>
> The patch works roughly in the following steps.
>
> 1. Planner decides how many children out of an append can run
> asynchrnously (called as async-capable.).
>
> 2. While ExecInit if an Append doesn't have an async-capable children,
> ExecAppend that is exactly the same function is set as
> ExecProcNode. Otherwise ExecAppendAsync is used.
>
> If the infrastructure part in the patch causes any degradation, the
> "t0"(scan on local single table) and/or "pll" test (scan on a local
> paritioned table) gets slow.
>
> 3. postgresql_fdw always runs async-capable code path.
>
> If the postgres_fdw part causes degradation, ft0 reflects that.
>
>
> The tables has two integers and the query does sum(a) on all tuples.
>
> With the default fetch_size = 100, number is run time in ms. Each
> number is the average of 14 runs.
>
> master patched gain
> t0 7325 7130 +2.7%
> pll 4558 4484 +1.7%
> ft0 3670 3675 -0.1%
> pf0 2322 1550 +33.3%
> pf1 2367 1475 +37.7%
> ptf0 2517 1624 +35.5%
> ptf1 2343 1497 +36.2%
>
> With larger fetch_size (200) the gain mysteriously decreases for
> sharing single connection cases (pf0, ptf0), but others don't seem
> change so much.
>
> master patched gain
> t0 7212 7252 -0.6%
> pll 4546 4397 +3.3%
> ft0 3712 3731 -0.5%
> pf0 2131 1570 +26.4%
> pf1 1926 1189 +38.3%
> ptf0 2001 1557 +22.2%
> ptf1 1903 1193 +37.4%
>
> FWIW, attached are the test script.
>
> gentblr2.sql: Table creation script.
> testrun.sh : Benchmarking script.
>
>
> regards.
>
> --
> Kyotaro Horiguchi
> NTT Open Source Software Center
>