pg_upgrade slowness for databases with many tables
От | Stefan Seifert |
---|---|
Тема | pg_upgrade slowness for databases with many tables |
Дата | |
Msg-id | 3559239.xQRrTQ2jGQ@sphinx обсуждение исходный текст |
Ответы |
Re: pg_upgrade slowness for databases with many tables
|
Список | 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
В списке pgsql-bugs по дате отправления: