Re: Parallel Inserts in CREATE TABLE AS
От | Bharath Rupireddy |
---|---|
Тема | Re: Parallel Inserts in CREATE TABLE AS |
Дата | |
Msg-id | CALj2ACV-VToW65BE6ndDEB7S_3qhzQ_BUWtw2q6V88iwTwwPSg@mail.gmail.com обсуждение исходный текст |
Ответ на | Re: Parallel Inserts in CREATE TABLE AS (Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>) |
Ответы |
RE: Parallel Inserts in CREATE TABLE AS
("tsunakawa.takay@fujitsu.com" <tsunakawa.takay@fujitsu.com>)
Re: Parallel Inserts in CREATE TABLE AS (Amit Kapila <amit.kapila16@gmail.com>) RE: Parallel Inserts in CREATE TABLE AS ("houzj.fnst@fujitsu.com" <houzj.fnst@fujitsu.com>) |
Список | pgsql-hackers |
On Thu, May 27, 2021 at 9:53 AM Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com> wrote: > > One idea to find this out could be that we have three counters for > > each worker which counts the number of times each worker extended the > > relation in bulk, the number of times each worker extended the > > relation by one block, the number of times each worker gets the page > > from FSM. It might be possible that with this we will be able to > > figure out why there is a difference between your and Hou-San's > > results. > > Yeah, that helps. And also, the time spent in > LockRelationForExtension, ConditionalLockRelationForExtension, > GetPageWithFreeSpace and RelationAddExtraBlocks too can give some > insight. > > My plan is to have a patch with above info added in (which I will > share it here so that others can test and see the results too) and run > the "case 4" where there's a regression seen on my system. I captured below information with the attached patch 0001-test-times-and-block-counts.patch applied on top of CTAS v23 patch set. Testing details are attached in the file named "test". Total time spent in LockRelationForExtension Total time spent in GetPageWithFreeSpace Total time spent in RelationAddExtraBlocks Total number of times extended the relation in bulk Total number of times extended the relation by one block Total number of blocks added in bulk extension Total number of times getting the page from FSM Here is a summary of what I observed: 1) The execution time with 2 workers, without TABLE_INSERT_SKIP_FSM (140 sec) is more than with 0 workers (112 sec) 2) The execution time with 2 workers, with TABLE_INSERT_SKIP_FSM (225 sec) is more than with 2 workers, without TABLE_INSERT_SKIP_FSM (140 sec) 3) Majority of the time is going into waiting for relation extension lock in LockRelationForExtension. With 2 workers, without TABLE_INSERT_SKIP_FSM, out of total execution time 140 sec, the time spent in LockRelationForExtension is ~40 sec and the time spent in RelationAddExtraBlocks is ~20 sec. So, ~60 sec are being spent in these two functions. With 2 workers, with TABLE_INSERT_SKIP_FSM, out of total execution time 225 sec, the time spent in LockRelationForExtension is ~135 sec and the time spent in RelationAddExtraBlocks is 0 sec (because we skip FSM, no bulk extend logic applies). So, most of the time is being spent in LockRelationForExtension. 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. Having said that, I request others to try and see if the same observations (as above) are made on their testing systems for the same use case. If others don't see regression (with just 2 workers) or they observe not much difference with and without TABLE_INSERT_SKIP_FSM. I'm open to changing the parallel inserts in CTAS code to use TABLE_INSERT_SKIP_FSM. In any case, if the observation is that there's a good amount of time being spent in LockRelationForExtension, I'm not sure (at this point) whether we can do something here or just live with it. With Regards, Bharath Rupireddy. EnterpriseDB: http://www.enterprisedb.com
Вложения
В списке pgsql-hackers по дате отправления:
Предыдущее
От: Paul GuoДата:
Сообщение: Re: sync request forward function ForwardSyncRequest() might hang for some time in a corner case?
Следующее
От: Dilip KumarДата:
Сообщение: Re: Move pg_attribute.attcompression to earlier in struct for reduced size?