On Fri, May 28, 2021 at 6:24 AM tsunakawa.ta...@fujitsu.com <tsunakawa.ta...@fujitsu.com> wrote: > > From: Bharath Rupireddy <bharath.rupireddyforpostg...@gmail.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
test_results1
Description: Binary data
test_results2
Description: Binary data
