Re: [HACKERS] asynchronous execution

From: Kyotaro HORIGUCHI <horiguchi(dot)kyotaro(at)lab(dot)ntt(dot)co(dot)jp>
To: robertmhaas(at)gmail(dot)com
Cc: tgl(at)sss(dot)pgh(dot)pa(dot)us, ah(at)cybertec(dot)at, pgsql-hackers(at)postgresql(dot)org
Subject: Re: [HACKERS] asynchronous execution
Date: 2018-05-11 08:45:20
Message-ID: 20180511.174520.188681124.horiguchi.kyotaro@lab.ntt.co.jp
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hello. This is the new version of $Subject.

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.

# 0003 lacks changes of postgres_fdw.out now.

- 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.

> > The attached PoC patch theoretically has no impact on the normal
> > code paths and just brings gain in async cases.

I performed almost the same test as before but with:

- partition tables
(There should be no difference with inheritance.)

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

100 unreasonably magnifies the lag by context switching on
single poor box and 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 so that it doesn't
sleep (I suppose)...)

- Table size of test B is one tenth of the previous size, the
same to one partition.

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

> 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 : 3033.48 2997.44 -1.2
B: local partitioning : 1405.52 1426.66 1.5
C: single remote table : 8335.50 8463.22 1.5
D: sharding (single con) : 6862.92 6820.97 -0.6
E: sharding (multi con) : 2185.84 6733.63 67.5
F: partition (single con): 6818.13 6741.01 -1.1
G: partition (multi con) : 2150.58 6407.46 66.4

fetch_size = 200
patched(ms) unpatched(ms) gain(%)
A: simple table scan :
B: local partitioning :
C: single remote table :
D: sharding (single con) :
E: sharding (multi con) :
F: partition (single con):
G: partition (multi con) :

fetch_size = 1000
patched(ms) unpatched(ms) gain(%)
A: simple table scan : 3050.31 2980.29 -2.3
B: local partitioning : 1401.34 1419.54 1.3
C: single remote table : 8375.4 8445.27 0.8
D: sharding (single con) : 3935.97 4737.84 16.9
E: sharding (multi con) : 1330.44 4752.87 72.0
F: partition (single con): 3997.63 4747.44 15.8
G: partition (multi con) : 1323.02 4807.72 72.5

Async append doesn't affect 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.

I will repost after filling the blank portion of the tables and
complete regression of the patch next week. Sorry for the
incomplete post.

regards.

--
Kyotaro Horiguchi
NTT Open Source Software Center

Attachment Content-Type Size
0001-Allow-wait-event-set-to-be-registered-to-resource-ow.patch text/x-patch 9.4 KB
0002-core-side-modification.patch text/x-patch 47.8 KB
0003-async-postgres_fdw.patch text/x-patch 48.7 KB

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tatsuo Ishii 2018-05-11 09:12:55 Re: Having query cache in core
Previous Message Amit Langote 2018-05-11 07:19:29 Re: postgres_fdw: Oddity in pushing down inherited UPDATE/DELETE joins to remote servers