Magnus reported that a customer with a million tables was finding
pg_upgrade slow. I had never considered many table to be a problem, but
decided to test it. I created a database with 2k tables like this:
CREATE TABLE test1990 (x SERIAL);
Running the git version of pg_upgrade on that took 203 seconds. Using
synchronous_commit=off dropped the time to 78 seconds. This was tested
on magnetic disks with a write-through cache. (No change on an SSD with
a super-capacitor.)
I don't see anything unsafe about having pg_upgrade use
synchronous_commit=off. I could set it just for the pg_dump reload, but
it seems safe to just use it always. We don't write to the old cluster,
and if pg_upgrade fails, you have to re-initdb the new cluster anyway.
Patch attached. I think it should be applied to 9.2 as well.
--
Bruce Momjian <br...@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
diff --git a/contrib/pg_upgrade/server.c b/contrib/pg_upgrade/server.c
new file mode 100644
index a9f9d85..e64d0c4
*** a/contrib/pg_upgrade/server.c
--- b/contrib/pg_upgrade/server.c
*************** start_postmaster(ClusterInfo *cluster)
*** 207,216 ****
* vacuums can still happen, so we set autovacuum_freeze_max_age to its
* maximum. We assume all datfrozenxid and relfrozen values are less than
* a gap of 2000000000 from the current xid counter, so autovacuum will
! * not touch them.
*/
snprintf(cmd, sizeof(cmd),
! "\"%s/pg_ctl\" -w -l \"%s\" -D \"%s\" -o \"-p %d %s %s%s\" start",
cluster->bindir, SERVER_LOG_FILE, cluster->pgconfig, cluster->port,
(cluster->controldata.cat_ver >=
BINARY_UPGRADE_SERVER_FLAG_CAT_VER) ? "-b" :
--- 207,217 ----
* vacuums can still happen, so we set autovacuum_freeze_max_age to its
* maximum. We assume all datfrozenxid and relfrozen values are less than
* a gap of 2000000000 from the current xid counter, so autovacuum will
! * not touch them. synchronous_commit=off improves object creation speed.
*/
snprintf(cmd, sizeof(cmd),
! "\"%s/pg_ctl\" -w -l \"%s\" -D \"%s\" -o \"-p %d "
! "-c synchronous_commit=off %s %s%s\" start",
cluster->bindir, SERVER_LOG_FILE, cluster->pgconfig, cluster->port,
(cluster->controldata.cat_ver >=
BINARY_UPGRADE_SERVER_FLAG_CAT_VER) ? "-b" :
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
