Re: Parallel copy

On Fri, Sep 11, 2020 at 3:49 AM Greg Nancarrow <gregn4422@gmail.com> wrote:
>
> I couldn't use the original machine from which I obtained the previous
> results, but ended up using a 4-core CentOS7 VM, which showed a
> similar pattern in the performance results for this test case.
> I obtained the following results from loading a 2GB CSV file (1000000
> rows, 4 indexes):
>
> Copy Type            Duration (s)          Load factor
> ===============================================
> Normal Copy        190.891                -
>
> Parallel Copy
> (#workers)
> 1                            210.947               0.90
>
Hi Greg,

I tried to recreate the test case(attached) and I didn't find much
difference with the custom postgresql.config file.
Test case: 250000 tuples, 4 indexes(composite indexes with 10
columns), 3.7GB, 100 columns(as suggested by you and all the
varchar(255) columns are having 255 characters), exec time in sec.

With custom postgresql.conf[1], removed and recreated the data
directory after every run(I couldn't perform the OS page cache flush
due to some reasons. So, chose this recreation of data dir way, for
testing purpose):
 HEAD: 129.547, 128.624, 128.890
 Patch: 0 workers - 130.213, 131.298, 130.555
 Patch: 1 worker - 127.757, 125.560, 128.275

With default postgresql.conf, removed and recreated the data directory
after every run:
 HEAD: 138.276, 150.472, 153.304
 Patch: 0 workers - 162.468,  149.423, 159.137
 Patch: 1 worker - 136.055, 144.250, 137.916

Few questions:
 1. Was the run performed with default postgresql.conf file? If not,
what are the changed configurations?
 2. Are the readings for normal copy(190.891sec, mentioned by you
above) taken on HEAD or with patch, 0 workers? How much is the runtime
with your test case on HEAD(Without patch) and 0 workers(With patch)?
 3. Was the run performed on release build?
 4. Were the readings taken on multiple runs(say 3 or 4 times)?

[1] - Postgres configuration used for above testing:
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

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