Hi,
at work at my company I inherited responsibility for a large PG 8.1 DB,
with a an extreme number of tables (~300000). Surprisingly this is
working quite well, except for maintenance and backup. I am tasked with
finding a way to do dump & restore to 9.3 with as little downtime as
possible.
Using 9.3's pg_dump with -j12 I found out that pg_dump takes 6 hours to
lock tables using a single thread, then does the data dump in 1 more
hour using 12 workers. However if I patch out the explicit LOCK TABLE
statements this only takes 1 hour total. Of course no one else is using
the DB at this time. In a pathological test case scenario in a staging
environment the dump time decreased from 5 hours to 5 minutes.
I've googled the problem and there seem to be more people with similar
problems, so I made this a command line option --no-table-locks and
wrapped it up in as nice a patch against github/master as I can manage
(and I didn't use C for a long time). I hope you find it useful.
regards,
Jürgen Strobel
commit 393d47cf6b5ce77297e52e7fc390aa862fd2c2fd
Author: Jürgen Strobel <juergen+git...@strobel.info>
Date: Fri Mar 7 16:54:22 2014 +0100
Add option --no-table-locks to pg_dump.
diff --git a/doc/src/sgml/ref/pg_dump.sgml b/doc/src/sgml/ref/pg_dump.sgml
index 1f0d4de..5725c46 100644
--- a/doc/src/sgml/ref/pg_dump.sgml
+++ b/doc/src/sgml/ref/pg_dump.sgml
@@ -772,6 +772,25 @@ PostgreSQL documentation
</varlistentry>
<varlistentry>
+ <term><option>--no-table-locks</></term>
+ <listitem>
+ <para>
+ If this option is specified, tables to be dumped will not be locked
explicitly
+ at the start of pg_dump. It implies
<option>--no-synchronized-snapshots</>.
+ This is potentially dangerous and should only be used by experts,
+ and only while there is no other activity in the database.
+ </para>
+ <para>
+ In the presense of an extreme number of tables pg_dump can exhibit
+ bad startup performance because it needs to issue LOCK TABLE
statements for all
+ tables. This is intended as a last resort option for dump and restore
version
+ upgrades, in order to make downtime manageable. In an especially bad
case
+ with ~300000 tables this reduced dump time from 6 to 1 hours.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term><option>--no-tablespaces</option></term>
<listitem>
<para>
diff --git a/src/bin/pg_dump/parallel.c b/src/bin/pg_dump/parallel.c
index 6f2634b..ac05356 100644
--- a/src/bin/pg_dump/parallel.c
+++ b/src/bin/pg_dump/parallel.c
@@ -32,6 +32,9 @@
#define PIPE_READ 0
#define PIPE_WRITE 1
+/* flag for the no-table-locks command-line long option */
+int no_table_locks = 0;
+
/* file-scope variables */
#ifdef WIN32
static unsigned int tMasterThreadId = 0;
@@ -886,7 +889,7 @@ WaitForCommands(ArchiveHandle *AH, int pipefd[2])
* meantime. lockTableNoWait dies in this case to
prevent a
* deadlock.
*/
- if (strcmp(te->desc, "BLOBS") != 0)
+ if (!no_table_locks && strcmp(te->desc, "BLOBS") != 0)
lockTableNoWait(AH, te);
/*
diff --git a/src/bin/pg_dump/parallel.h b/src/bin/pg_dump/parallel.h
index 7a32a9b..f95a6bb 100644
--- a/src/bin/pg_dump/parallel.h
+++ b/src/bin/pg_dump/parallel.h
@@ -70,6 +70,9 @@ extern bool parallel_init_done;
extern DWORD mainThreadId;
#endif
+/* flag for the no-table-locks command-line long option */
+extern int no_table_locks;
+
extern void init_parallel_dump_utils(void);
extern int GetIdleWorker(ParallelState *pstate);
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 17bb846..e1fe047 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -357,6 +357,7 @@ main(int argc, char **argv)
{"use-set-session-authorization", no_argument,
&use_setsessauth, 1},
{"no-security-labels", no_argument, &no_security_labels, 1},
{"no-synchronized-snapshots", no_argument,
&no_synchronized_snapshots, 1},
+ {"no-table-locks", no_argument, &no_table_locks, 1},
{"no-unlogged-table-data", no_argument,
&no_unlogged_table_data, 1},
{NULL, 0, NULL, 0}
@@ -562,6 +563,10 @@ main(int argc, char **argv)
if (column_inserts)
dump_inserts = 1;
+ /* --no-table-locks implies --no_synchronized_snapshots */
+ if (no_table_locks)
+ no_synchronized_snapshots = 1;
+
if (dataOnly && schemaOnly)
{
write_msg(NULL, "options -s/--schema-only and -a/--data-only
cannot be used together\n");
@@ -902,6 +907,7 @@ help(const char *progname)
printf(_(" --inserts dump data as INSERT commands,
rather than COPY\n"));
printf(_(" --no-security-labels do not dump security label
assignments\n"));
printf(_(" --no-synchronized-snapshots do not use synchronized
snapshots in parallel jobs\n"));
+ printf(_(" --no-table-locks do not lock tables before
starting to dump\n"));
printf(_(" --no-tablespaces do not dump tablespace
assignments\n"));
printf(_(" --no-unlogged-table-data do not dump unlogged table
data\n"));
printf(_(" --quote-all-identifiers quote all identifiers, even if
not key words\n"));
@@ -4807,7 +4813,7 @@ getTables(Archive *fout, int *numTables)
* NOTE: it'd be kinda nice to lock other relations too, not
only
* plain tables, but the backend doesn't presently allow that.
*/
- if (tblinfo[i].dobj.dump && tblinfo[i].relkind ==
RELKIND_RELATION)
+ if (!no_table_locks && tblinfo[i].dobj.dump &&
tblinfo[i].relkind == RELKIND_RELATION)
{
resetPQExpBuffer(query);
appendPQExpBuffer(query,
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
