Re: Parallel Inserts in CREATE TABLE AS

On Fri, May 28, 2021 at 6:24 AM tsunakawa(dot)takay(at)fujitsu(dot)com
<tsunakawa(dot)takay(at)fujitsu(dot)com> wrote:
>
> From: Bharath Rupireddy <bharath(dot)rupireddyforpostgres(at)gmail(dot)com>
> > I'm still not sure why the execution time with 0 workers (or serial execution or
> > no parallelism involved) on my testing system is 112 sec compared to 58 sec on
> > Hou-San's system for the same use case. Maybe the testing system I'm using
> > is not of the latest configuration compared to others.
>
> What's the setting of wal_level on your two's systems? I thought it could be that you set it to > minimal, while Hou-san set it to minimal. (I forgot the results of 2 and 4 workers, though.)

Thanks. I was earlier running with default wal_level = replica.

Results on my system, with wal_level = minimal, PSA file
"test_results2" for more details:
Without TABLE_INSERT_SKIP_FSM:
0 workers/serial execution - Time: 61875.255 ms (01:01.875)
2 workers - Time: 89227.379 ms (01:29.227)
4 workers - Time: 81484.876 ms (01:21.485)
With TABLE_INSERT_SKIP_FSM:
0 workers/serial execution - Time: 61279.764 ms (01:01.280)
2 workers - Time: 208620.453 ms (03:28.620)
4 workers - Time: 223737.081 ms (03:43.737)

Results on my system, with wal_level = replica, PSA file
"test_results1" for more details:
Without TABLE_INSERT_SKIP_FSM:
0 workers/serial execution - Time: 112175.273 ms (01:52.175)
2 workers - Time: 140441.158 ms (02:20.441)
4 workers - Time: 141750.577 ms (02:21.751)

With TABLE_INSERT_SKIP_FSM:
0 workers/serial execution - Time: 112637.906 ms (01:52.638)
2 workers - Time: 225358.287 ms (03:45.358)
4 workers - Time: 242172.600 ms (04:02.173)

Results on Hou-san's system:
SERIAL: 58759.213 ms
PARALLEL 2 WORKER [NOT SKIP FSM]: 68390.221 ms [SKIP FSM]: 58633.924 ms
PARALLEL 4 WORKER [NOT SKIP FSM]: 67448.142 ms [SKIP FSM]: 66,960.305 ms

Majority of the time is being spent in LockRelationForExtension,
RelationAddExtraBlocks without TABLE_INSERT_SKIP_FSM and in
LockRelationForExtension with TABLE_INSERT_SKIP_FSM. The observations
made at [1] still hold true with wal_level = minimal.

I request Hou-san to capture the same info with the add-on patch
shared earlier. This would help us to be on the same page. We can
further think on:
1) Why so much time is being spent in LockRelationForExtension?
2) Whether to use TABLE_INSERT_SKIP_FSM or not, in other words,
whether to take advantage of bulk relation extension or not.
3) If bulk relation extension is to be used i.e. without
TABLE_INSERT_SKIP_FSM flag, then whether the blocks being added by one
worker are immediately visible to other workers or not after it
finishes adding all the blocks.

[1] - https://www.postgresql.org/message-id/CALj2ACV-VToW65BE6ndDEB7S_3qhzQ_BUWtw2q6V88iwTwwPSg%40mail.gmail.com

With Regards,
Bharath Rupireddy.
EnterpriseDB: http://www.enterprisedb.com