Horton

Re: Parallel copy

Greg Nancarrow <gregn4422@gmail.com>

View thread
From: Greg Nancarrow <gregn4422@gmail.com>
To: vignesh C <vignesh21@gmail.com>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>, Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>
Date: 2020-08-27T02:33:27Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Allow WaitLatch() to be used without a latch.

  2. Add %P to log_line_prefix for parallel group leader

  3. Include replication origins in SQL functions for commit timestamp

  4. Avoid useless buffer allocations during binary COPY FROM.

Attachments

> I have attached new set of patches with the fixes.
> Thoughts?

Hi Vignesh,

I don't really have any further comments on the code, but would like
to share some results of some Parallel Copy performance tests I ran
(attached).

The tests loaded a 5GB CSV data file into a 100 column table (of
different data types). The following were varied as part of the test:
- Number of workers (1 – 10)
- No indexes / 4-indexes
- Default settings / increased resources (shared_buffers,work_mem, etc.)

(I did not do any partition-related tests as I believe those type of
tests were previously performed)

I built Postgres (latest OSS code) with the latest Parallel Copy patches (v4).
The test system was a 32-core Intel Xeon E5-4650 server with 378GB of RAM.


I observed the following trends:
- For the data file size used, Parallel Copy achieved best performance
using about 9 – 10 workers. Larger data files may benefit from using
more workers. However, I couldn’t really see any better performance,
for example, from using 16 workers on a 10GB CSV data file compared to
using 8 workers. Results may also vary depending on machine
characteristics.
- Parallel Copy with 1 worker ran slower than normal Copy in a couple
of cases (I did question if allowing 1 worker was useful in my patch
review).
- Typical load time improvement (load factor) for Parallel Copy was
between 2x and 3x. Better load factors can be obtained by using larger
data files and/or more indexes.
- Increasing Postgres resources made little or no difference to
Parallel Copy performance when the target table had no indexes.
Increasing Postgres resources improved Parallel Copy performance when
the target table had indexes.

Regards,
Greg Nancarrow
Fujitsu Australia