On Mon, Nov 5, 2012 at 03:08:17PM -0500, Bruce Momjian wrote: > 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.
Modified patch attached and applied to head and 9.2. I decided to use synchronous_commit=off only on the new cluster, just in case we ever do make a modification of the old cluster. -- 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..49d4c8f *** a/contrib/pg_upgrade/server.c --- b/contrib/pg_upgrade/server.c *************** start_postmaster(ClusterInfo *cluster) *** 208,220 **** * 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" : ! "-c autovacuum=off -c autovacuum_freeze_max_age=2000000000", cluster->pgopts ? cluster->pgopts : "", socket_string); /* --- 208,225 ---- * 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, and we only + * modify the new cluster, so only use it there. If there is a crash, + * the new cluster has to be recreated anyway. */ snprintf(cmd, sizeof(cmd), ! "\"%s/pg_ctl\" -w -l \"%s\" -D \"%s\" -o \"-p %d%s%s%s%s\" start", cluster->bindir, SERVER_LOG_FILE, cluster->pgconfig, cluster->port, (cluster->controldata.cat_ver >= ! BINARY_UPGRADE_SERVER_FLAG_CAT_VER) ? " -b" : ! " -c autovacuum=off -c autovacuum_freeze_max_age=2000000000", ! (cluster == &new_cluster) ? " -c synchronous_commit=off" : "", cluster->pgopts ? cluster->pgopts : "", socket_string); /*
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
