Re: [HACKERS] asynchronous execution

This gets further refactoring.

At Fri, 11 May 2018 17:45:20 +0900 (Tokyo Standard Time), Kyotaro HORIGUCHI <horiguchi(dot)kyotaro(at)lab(dot)ntt(dot)co(dot)jp> wrote in <20180511(dot)174520(dot)188681124(dot)horiguchi(dot)kyotaro(at)lab(dot)ntt(dot)co(dot)jp>
> But, this is not just a rebased version. On the way fixing
> serious conflicts, I refactored patch and I believe this becomes
> way readable than the previous shape.
>
> - Waiting queue manipulation is moved into new functions. It had
> a bug that the same node can be inserted in the queue more than
> once and it is fixed.
>
> - postgresIterateForeginScan had somewhat a tricky strcuture to
> merge similar procedures thus it cannot be said easy-to-read at
> all. Now it is far simpler and straight-forward looking.
>
> - Still this works only on Append/ForeignScan.

I performed almost the same test (again) as before but with some
new things:

- partition tables (There should be no difference with
inheritance and it actually looks so.)

- added test for fetch_size of 200 and 1000 as long as 100.

Fetch size of 100 seems unreasonably magnifies the lag by
context switching on single poor box for the test D/F
below. They became faster by about twice by *adding* a small
delay (1000 times of clock_gettime()(*1)) just before
epoll_wait. Things would be different on separate machines but
I'm not sure it really is. I don't find the exact cause nor how
to avoid it.

*1: The reason for the function is that I found at first that the
queries get way faster by just prefixing by "explain
analyze"..

Async append (theoretically) no longer affects non-async path at
all so B is expected to get no degradation. It seems within
error.

C and F are the gain when all foreign tables share one connection
and D and G are the gain when every foreign tables has dedicate
connection.

(previous numbers)
> patched(ms) unpatched(ms) gain(%)
> A: simple table scan : 3562.32 3444.81 -3.4
> B: local partitioning : 1451.25 1604.38 9.5
> C: single remote table : 8818.92 9297.76 5.1
> D: sharding (single con) : 5966.14 6646.73 10.2
> E: sharding (multi con) : 1802.25 6515.49 72.3

fetch_size = 100
patched(ms) unpatched(ms) gain(%)
A: simple table scan : 3065.82 3046.82 -0.62
B: local partitioning : 1393.98 1378.00 -1.16
C: single remote table : 8499.73 8595.66 1.12
D: sharding (single con) : 9267.85 9251.59 -0.18
E: sharding (multi con) : 2567.02 9295.22 72.38
F: partition (single con): 9241.08 9060.19 -2.00
G: partition (multi con) : 2548.86 9419.18 72.94

fetch_size = 200
patched(ms) unpatched(ms) gain(%)
A: simple table scan : 3067.08 2999.23 -2.3
B: local partitioning : 1392.07 1384.49 -0.5
C: single remote table : 8521.72 8505.48 -0.2
D: sharding (single con) : 6752.81 7076.02 4.6
E: sharding (multi con) : 1958.2 7188.02 72.8
F: partition (single con): 6756.72 7000.72 3.5
G: partition (multi con) : 1969.8 7228.85 72.8

fetch_size = 1000
patched(ms) unpatched(ms) gain(%)
A: simple table scan : 4547.44 4519.34 -0.62
B: local partitioning : 2880.66 2739.43 -5.16
C: single remote table : 8448.04 8572.15 1.45
D: sharding (single con) : 2405.01 5919.31 59.37
E: sharding (multi con) : 1872.15 5963.04 68.60
F: partition (single con): 2369.08 5960.81 60.26
G: partition (multi con) : 1854.69 5893.65 68.53

regards.

--
Kyotaro Horiguchi
NTT Open Source Software Center