From: | senor <frio_cervesa(at)hotmail(dot)com> |
---|---|
To: | "pgsql-general(at)lists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Optimize pg_dump schema-only |
Date: | 2019-04-28 20:21:40 |
Message-ID: | BYAPR01MB3701AF2EFA014CC16EA14119F7380@BYAPR01MB3701.prod.exchangelabs.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi All,
I'm looking for advice for optimizing the pg_dump portion of "pg_upgrade
--link". Since this schema only dump can't take advantage of parallel
processing with jobs I'm looking for any preparation or configuration
settings that can improve speed.
9.2 to 9.6
CentOS 6/64bit
512GB
I see only one CPU of 32 doing anything and it's often at 100%. Disk IO
is minimal. Memory use varies but always plenty to spare.
During upgrade I'm running:
Only the upgrade - no other services
work_mem = 50MB
maintenance_work_mem = 2048MB
shared_buffers = 30GB
max_locks_per_transaction = 4096
autovacuum = off
autovacuum_freeze_max_age = 1500000000 #Had previous issues with
vacuum (to prevent wrap)
Truthfully, I thought I had increased work_mem until starting this
email. But increasing it is just a guess unless I get advice to do so
here. I'm at a knowledge level where I can only guess at the relevance
of vacuum, analyze or any other preparatory actions I can complete
before taking postgres offline for upgrade. My feeling is that the
bottleneck is the backend and not pg_dump. School me on that if needed
please.
Any advice and explanation is appreciated.
- Senor
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2019-04-28 20:48:42 | Re: Optimize pg_dump schema-only |
Previous Message | Adrian Klaver | 2019-04-27 14:11:16 | Re: Missing pg_config on SuSE SLES 12 for PostgreSQL 10 |