pg_dump from v13 is slow

From: Justin Pryzby <pryzby(at)telsasoft(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: pg_dump from v13 is slow
Date: 2020-08-13 22:48:23
Message-ID: 20200813224823.GF4561@telsasoft.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

I ran into this while running pg_upgrade from beta2 to beta3:
$ time sudo -u postgres sh -c 'cd /var/lib/pgsql; /usr/pgsql-13/bin/pg_upgrade -b /usr/pgsql-13b2/bin/ -d ./13b2/data -D ./13/data --link'
real 94m18.335s

This instances has many table partitions, and the production instance uses
tablespaces. Some of our tables are wide. This VM is not idle, but does not
account for being 20x slower.

pg_dump -v --section=pre-data ts |wc
1846659 4697507 59575253
real 39m8.524s

Compare v12 and v13:

|$ /usr/pgsql-12/bin/initdb -D 12
|$ /usr/pgsql-12/bin/postgres -D 12 -c shared_buffers=256MB -c max_locks_per_transaction=128 -c port=5678 -c unix_socket_directories=/tmp&
|$ psql -h /tmp -p 5678 postgres </srv/cdrperfbackup/ts/2020-08-10/pg_dumpall-g
|$ time pg_restore /srv/cdrperfbackup/ts/2020-08-10/pg_dump-section\=pre-data -d postgres -h /tmp -p 5678 --no-tablespaces # --clean --if-exist
| real 4m56.627s
|$ time pg_dump --section=pre-data postgres -h /tmp -p 5678 |wc
| 1823612 4504584 58379810
| real 1m4.452s

|/usr/pgsql-13/bin/initdb -D 13
|/usr/pgsql-13/bin/postgres -D 13 -c shared_buffers=256MB -c max_locks_per_transaction=128 -c port=5678 -c unix_socket_directories=/tmp&
|psql -h /tmp -p 5678 postgres </srv/cdrperfbackup/ts/2020-08-10/pg_dumpall-g
|time pg_restore /srv/cdrperfbackup/ts/2020-08-10/pg_dump-section\=pre-data -d postgres -h /tmp -p 5678 --no-tablespaces # --clean --if-exist
| real 6m49.964s
|$ time pg_dump --section=pre-data postgres -h /tmp -p 5678 |wc
| 1823612 4504584 58379813
| real 19m42.918s

I'm trying to narrow this down, but I'd be very happy for suggestions.

--
Justin

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Alvaro Herrera 2020-08-13 23:28:32 Re: pg_dump from v13 is slow
Previous Message Alvaro Herrera 2020-08-13 21:53:49 Re: [BUG] Error in BRIN summarization