RE: Parallel Inserts in CREATE TABLE AS - Mailing list pgsql-hackers

From houzj.fnst@fujitsu.com
Subject RE: Parallel Inserts in CREATE TABLE AS
Date
Msg-id OS0PR01MB57164384314D67470A04EB8A94229@OS0PR01MB5716.jpnprd01.prod.outlook.com
Whole thread Raw
In response to RE: Parallel Inserts in CREATE TABLE AS  ("tsunakawa.takay@fujitsu.com" <tsunakawa.takay@fujitsu.com>)
List pgsql-hackers
From: Tsunakawa, Takayuki/綱川 貴之 <tsunakawa.takay@fujitsu.com>
Sent: Friday, May 28, 2021 8:55 AM
> To: 'Bharath Rupireddy' <bharath.rupireddyforpostgres@gmail.com>; Hou,
> Zhijie/侯 志杰 <houzj.fnst@fujitsu.com>
> Cc: Amit Kapila <amit.kapila16@gmail.com>; Tang, Haiying/唐 海英
> <tanghy.fnst@fujitsu.com>; PostgreSQL-development
> <pgsql-hackers@postgresql.org>; Zhihong Yu <zyu@yugabyte.com>; Luc
> Vlaming <luc@swarm64.com>; Dilip Kumar <dilipbalaut@gmail.com>;
> vignesh C <vignesh21@gmail.com>
> Subject: RE: Parallel Inserts in CREATE TABLE AS
> 
> From: Bharath Rupireddy <bharath.rupireddyforpostgres@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.)

I think I followed the configuration that Bharath-san mentioned.
It could be the hardware's difference, because I am not using SSD.
I will try to test on SSD to see if there is some difference.

I only change the the following configuration:

shared_buffers = 40GB
max_worker_processes = 32
max_parallel_maintenance_workers = 24
max_parallel_workers = 32
synchronous_commit = off
checkpoint_timeout = 1d
max_wal_size = 24GB
min_wal_size = 15GB
autovacuum = off

Best regards,
houzj

pgsql-hackers by date:

Previous
From: "tsunakawa.takay@fujitsu.com"
Date:
Subject: RE: Parallel Inserts in CREATE TABLE AS
Next
From: Greg Nancarrow
Date:
Subject: Re: Consider parallel for lateral subqueries with limit