Sample Header Ad - 728x90

No speedup using parallel restore with pg_restore

0 votes
1 answer
1022 views
I need a large database like 10TB-15TB in Postgresql for benchmarking. I've created a smaller ~100GB sample databases with pgbench like this:
pgbench -i -s 7000 --no-vacuum exampledb
However, I observe no speedup when doing parallel restore from custom format (created with pg_dump -F c ...). Including restoring from .sql dump as well:
time psql -d pgbench7000 < pgbench7000.sql

SET
SET
SET
SET
SET

set_config
------------
(1 row)

SET
SET
SET
SET
SET
SET

CREATE TABLE
ALTER TABLE

CREATE TABLE
ALTER TABLE

CREATE TABLE
ALTER TABLE

CREATE TABLE
ALTER TABLE

COPY 700000000
COPY 7000
COPY 0
COPY 70000

ALTER TABLE
ALTER TABLE
ALTER TABLE

real        32m1.539s
user        3m28.575s
sys         1m40.125s

####################

time pg_restore -d pgbench7000 pgbench7000.custom
real         20m7.504s
user         2m35.556s
sys          0m35.750s

####################

time pg_restore -j 16 -d pgbench7000 pgbench7000.custom

real         20m56.565s
user         2m57.547s
sys          0m40.096s
I've given Postgresql server generous resources:
max_connections = 1000              
shared_buffers = 94GB  # 25% of system's memory                
work_mem = 512MB                      
maintenance_work_mem = 12GB
effective_io_concurrency = 500
max_worker_processes = 50     
max_parallel_maintenance_workers = 10   
max_parallel_workers_per_gather = 10   
max_parallel_workers = 50
The box is really powerful, it has 374GB of RAM, 72 vCPUs and fast NAS storage mounted over NFS (that's where PG data directory resides). pgbench creates a rather simple database, there are like four tables and only one table is big and occupies most of space. Can this be the reason for lack of speedup? Can anybody tell where's the bottleneck? Or is it just db structure? Environment: Centos 7.9 (that's what I have to use), Postgres 11 installed from project repo.
Asked by LetMeSOThat4U (513 rep)
Feb 17, 2022, 02:16 PM
Last activity: Feb 17, 2022, 10:59 PM