pg_upgrade slowness for databases with many tables

From: Stefan Seifert <nine(at)detonation(dot)org>
To: pgsql-bugs(at)postgresql(dot)org
Subject: pg_upgrade slowness for databases with many tables
Date: 2015-05-22 16:10:53
Message-ID: 3559239.xQRrTQ2jGQ@sphinx
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

Hi,

upgrading a database containing > 90000 tables and about the same number of
views and indexes with pg_upgrade takes several hours. I've started the
upgrade more than five hours ago and the "Creating dump of database schemas"
step is still not finished.

ps xaf output:

60709 pts/10 S+ 0:00 | \_ sudo -u postgres pg_upgrade -d
/var/lib/pgsql/data.intern -D /var/lib/pgsql/data -b /usr/lib/postgresql92/bin
-B /usr/lib/postgresql94/bin -j12 -k
60710 pts/10 S+ 0:00 | \_ pg_upgrade -d
/var/lib/pgsql/data.intern -D /var/lib/pgsql/data -b /usr/lib/postgresql92/bin
-B /usr/lib/postgresql94/bin -j12 -k
60806 pts/10 S+ 0:00 | \_ pg_upgrade -d
/var/lib/pgsql/data.intern -D /var/lib/pgsql/data -b /usr/lib/postgresql92/bin
-B /usr/lib/postgresql94/bin -j12 -k
60809 pts/10 S+ 0:00 | \_ sh -c
"/usr/lib/postgresql94/bin/pg_dump" --host "/data/pgsql" --port 50432 --
username "postgres" --schema-only --quote-all-identifiers --binary-upgrade --
format=custom --file="pg_upgrade_dump_16428.custom" "zms" >>
"pg_upgrade_dump_16428.log" 2>&1
60815 pts/10 S+ 3:52 | \_
/usr/lib/postgresql94/bin/pg_dump --host /data/pgsql --port 50432 --username
postgres --schema-only --quote-all-identifiers --binary-upgrade --
format=custom --file=pg_upgrade_dump_16428.custom zms

60719 pts/10 S+ 0:00 /usr/lib/postgresql92/bin/postgres -D
/var/lib/pgsql/data.intern -p 50432 -b -c listen_addresses= -c
unix_socket_permissions=0700 -c unix_socket_directory=/data/pgsql
60720 ? Ss 0:00 \_ postgres: logger process
60722 ? Ss 0:00 \_ postgres: checkpointer process
60723 ? Ss 0:08 \_ postgres: writer process
60724 ? Ss 0:00 \_ postgres: wal writer process
60725 ? Ss 0:04 \_ postgres: stats collector process
60817 ? Rs 334:12 \_ postgres: postgres zms [local] SELECT

The database is version 9.2, pg_upgrade is version 9.4.1. In a discussion at
lwn.net I've been told, that 9.4.1 already contains fixes for inefficiencies
and that it shouldn't be so slow anymore: http://lwn.net/Articles/645600/

What can I do to help improve pg_dump/pg_upgrade for my use case?

Regards,
Stefan

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message lr 2015-05-22 18:13:34 BUG #13334: PostGIS 2.2 crash in topology (array_contain_compare)
Previous Message Fabien COELHO 2015-05-22 09:33:19 Re: BUG #12379: pgbench should hint to pgbench -i