Re: pg_dump without explicit table locking

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

On Mon, Mar 17, 2014 at 7:52 AM, Jürgen Strobel <juergen+pg(at)strobel(dot)info> wrote:
> 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.

Fascinating report. Whether we use your patch or not, that's
interesting to know about. Please add your patch here so we don't
forget about it:

https://commitfest.postgresql.org/action/commitfest_view/open

See also https://wiki.postgresql.org/wiki/Submitting_a_Patch

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

2014-03-17 12:52 GMT+01:00 Jürgen Strobel <juergen+pg(at)strobel(dot)info>:

>
> 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.
>

Joe Conway sent me a tip so commit eeb6f37d89fc60c6449ca12ef9e914
91069369cb significantly decrease a time necessary for locking. So it can
help to.

I am not sure, if missing lock is fully correct. In same situation I though
about some form of database level lock. So you can get a protected access
by one statement.

Regards

Pavel Stehule

>
> regards,
> Jürgen Strobel
>
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>
>

Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> writes:
> 2014-03-17 12:52 GMT+01:00 Jrgen Strobel <juergen+pg(at)strobel(dot)info>:
>> 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.

> Joe Conway sent me a tip so commit eeb6f37d89fc60c6449ca12ef9e914
> 91069369cb significantly decrease a time necessary for locking. So it can
> help to.

Indeed. I think there's zero chance that we'd accept the patch as
proposed. If there's still a performance problem in HEAD, we'd look
for some other way to improve matters more.

(Note that this is only one of assorted O(N^2) behaviors in older versions
of pg_dump; we've gradually stamped them out over time.)

regards, tom lane

On 3/17/14, 8:47 AM, Tom Lane wrote:
> Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> writes:
>> 2014-03-17 12:52 GMT+01:00 Jürgen Strobel <juergen+pg(at)strobel(dot)info>:
>>> 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.
>
>> Joe Conway sent me a tip so commit eeb6f37d89fc60c6449ca12ef9e914
>> 91069369cb significantly decrease a time necessary for locking. So it can
>> help to.
>
> Indeed. I think there's zero chance that we'd accept the patch as
> proposed. If there's still a performance problem in HEAD, we'd look
> for some other way to improve matters more.
>
> (Note that this is only one of assorted O(N^2) behaviors in older versions
> of pg_dump; we've gradually stamped them out over time.)

On that note, it's recommended that when you are taking a backup to restore into a newer version of Postgres you create the dump using the NEWER version of pg_dump, not the old one.
--
Jim C. Nasby, Data Architect jim(at)nasby(dot)net
512.569.9461 (cell) http://jim.nasby.net

Jim Nasby <jim(at)nasby(dot)net> writes:
> On 3/17/14, 8:47 AM, Tom Lane wrote:
>> (Note that this is only one of assorted O(N^2) behaviors in older versions
>> of pg_dump; we've gradually stamped them out over time.)

> On that note, it's recommended that when you are taking a backup to restore into a newer version of Postgres you create the dump using the NEWER version of pg_dump, not the old one.

Right. IIRC, the OP said he *did* use a recent pg_dump ... but this
particular issue got fixed server-side, so the new pg_dump didn't help
against an 8.1 server :-(

regards, tom lane

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 03/17/2014 04:15 PM, Tom Lane wrote:
> Jim Nasby <jim(at)nasby(dot)net> writes:
>> On 3/17/14, 8:47 AM, Tom Lane wrote:
>>> (Note that this is only one of assorted O(N^2) behaviors in
>>> older versions of pg_dump; we've gradually stamped them out
>>> over time.)
>
>> On that note, it's recommended that when you are taking a backup
>> to restore into a newer version of Postgres you create the dump
>> using the NEWER version of pg_dump, not the old one.
>
> Right. IIRC, the OP said he *did* use a recent pg_dump ... but
> this particular issue got fixed server-side, so the new pg_dump
> didn't help against an 8.1 server :-(

Exactly. I backported the patch from 9.3 to 8.4 and saw a schema-only
dump time go from <give-up-and-kill-it-after-5-days> to 1 hour. This
was for a database with about 500k tables.

Joe

- --
Joe Conway
credativ LLC: http://www.credativ.us
Linux, PostgreSQL, and general Open Source
Training, Service, Consulting, & 24x7 Support
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.14 (GNU/Linux)
Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/

iQIcBAEBAgAGBQJTJ4M7AAoJEDfy90M199hlLzUQAIpprlGDB2lAwgaMD9CYiko4
xJKCWPE9FH3zd5D4hoWfzI3rTudP+wr5VHGncJHKFcarVC3RFwlDcwYFZVFbzVfr
IcKxQjiwYJfi30/sjClnXeaqyQCeurolKwkLXiSLbmVvi7edtlYPN4WEBrEojKCS
AUQtVqbtlSWhCMmTWnYAn2uejuXK5wZnOvjQZzhTpTuYsMZ1uKhuAb/ir3/PPiVA
qGax6QLKfJ1SLOlMtLRWTo8jYH82s9QNRNv2kvaiQILXgkmTQ3Z79aYrq5BrCeVL
dn5vHwkGXyYfZ3XuuaomspB9+kUleNsvWWWfBWKJVw129WFbwYqqFF6GX0q1vLzb
+Le2tMmvIUxFAaJle9usS9REa6lyJr5RiLPq+q0DA/oTx/na7O9DdJzfjIzhsKTK
ghRyhUyIafPDPgAMSahh1vWlHdOE9wy+8GYcRKkfLiZKFO24DVknHcDWCFa22uBH
b5BZCV0AgJwWTFcBjpQ/Nz8+0ykGx69B9RswUXtqXoRpak9kvvCbtqCyN15MRUSG
t5Bfr5RPGmL8TFZWKsgGeg8cWRFra3P4WktDB9d1tq8qfcKDPENlEkUEC/bc5D0M
OFoaNoSVCiK2uoFInxtGujFuQxlxtAgbN8PkkCrqHwkU/tmWSzQljaaHx3AKY8iM
Bgr493yOFPDVcESQ1pSa
=PV6q
-----END PGP SIGNATURE-----

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 03/18/2014 07:20 AM, Joe Conway wrote:
> On 03/17/2014 04:15 PM, Tom Lane wrote:
>> Jim Nasby <jim(at)nasby(dot)net> writes:
>>> On 3/17/14, 8:47 AM, Tom Lane wrote:
>>>> (Note that this is only one of assorted O(N^2) behaviors in
>>>> older versions of pg_dump; we've gradually stamped them out
>>>> over time.)
>
>>> On that note, it's recommended that when you are taking a
>>> backup to restore into a newer version of Postgres you create
>>> the dump using the NEWER version of pg_dump, not the old one.
>
>> Right. IIRC, the OP said he *did* use a recent pg_dump ... but
>> this particular issue got fixed server-side, so the new pg_dump
>> didn't help against an 8.1 server :-(
>
> Exactly. I backported the patch from 9.3 to 8.4 and saw a
> schema-only dump time go from <give-up-and-kill-it-after-5-days> to
> 1 hour. This was for a database with about 500k tables.

I wonder if doing large batches of

LOCK TABLE table1, table2, table3, ...

would help, instead of doing individual statements?

- --
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.15 (GNU/Linux)
Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/

iQEcBAEBAgAGBQJTJ5fpAAoJELBXNkqjr+S2XMkH/jhhyET40uuc+zkwpJzW6j7x
o6x2VsaME0PpFUZE5iRL0FDEbHovwznZsQFOPFaCOqZ9vCL1P0JWl6Wf1JNZPJSr
NkwT08PsiOFmtXvnGh5109z3Of6ADGyEKCWHp7msxokzj1whJqzcIbyjAajUq21K
wgnYksH6TUVuutDg6r6PPaNTbmIvcxHwRs58hYi8JUIXdzIyYSlLdPezAo0VjwGw
8GHUQYy4r/hkSDWA4ViZ6ZjDIIgzT46nrTdxkGNPZn4Oz8k6nASnFFZhLfanytQI
YQhqlmIa/jievlZX5Q38snIPE+KYEq3buYK/OmbKuDCfHotC9nNNDCHdop7Qs4c=
=zQoC
-----END PGP SIGNATURE-----

On Mon, Mar 17, 2014 at 5:48 PM, Craig Ringer <craig(at)2ndquadrant(dot)com> wrote:

> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> On 03/18/2014 07:20 AM, Joe Conway wrote:
> > On 03/17/2014 04:15 PM, Tom Lane wrote:
> >> Jim Nasby <jim(at)nasby(dot)net> writes:
> >>> On 3/17/14, 8:47 AM, Tom Lane wrote:
> >>>> (Note that this is only one of assorted O(N^2) behaviors in
> >>>> older versions of pg_dump; we've gradually stamped them out
> >>>> over time.)
> >
> >>> On that note, it's recommended that when you are taking a
> >>> backup to restore into a newer version of Postgres you create
> >>> the dump using the NEWER version of pg_dump, not the old one.
> >
> >> Right. IIRC, the OP said he *did* use a recent pg_dump ... but
> >> this particular issue got fixed server-side, so the new pg_dump
> >> didn't help against an 8.1 server :-(
> >
> > Exactly. I backported the patch from 9.3 to 8.4 and saw a
> > schema-only dump time go from <give-up-and-kill-it-after-5-days> to
> > 1 hour. This was for a database with about 500k tables.
>
> I wonder if doing large batches of
>
> LOCK TABLE table1, table2, table3, ...
>
> would help, instead of doing individual statements?
>

If I recall correctly, someone did submit a patch to do that. It helped
when dumping schema only, but not much when dumping data.

Cheers,

Jeff

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 03/17/2014 05:55 PM, Jeff Janes wrote:
> On Mon, Mar 17, 2014 at 5:48 PM, Craig Ringer
> <craig(at)2ndquadrant(dot)com I wonder if doing large batches of
>
> LOCK TABLE table1, table2, table3, ...
>
> would help, instead of doing individual statements?
>
> If I recall correctly, someone did submit a patch to do that. It
> helped when dumping schema only, but not much when dumping data.

Not surprising at all. The huge time is incurred in taking the locks,
but if you are trying to use pg_upgrade in link mode to speed your
upgrade, you are totally hosed by the time it takes to grab those locks.

This patch applied to 9.3 substantially fixes the issue:
8<-----------------------
commit eeb6f37d89fc60c6449ca12ef9e91491069369cb
Author: Heikki Linnakangas <heikki(dot)linnakangas(at)iki(dot)fi>
Date: Thu Jun 21 15:01:17 2012 +0300

Add a small cache of locks owned by a resource owner in ResourceOwner.
8<-----------------------

On my 8.4 database, with 500,000 tables there were about 2.5 million
locks taken including toast tables and indexes during the schema dump.
Without the patch grabbing locks took many, many days with that many
objects to lock. With a backported version of the patch, one hour.

So if you have a problem due to many tables on an older than 9.3
version of Postgres, this is the direction to head (a custom patch
applied to your old version just long enough to get successfully
upgraded).

Joe

- --
Joe Conway
credativ LLC: http://www.credativ.us
Linux, PostgreSQL, and general Open Source
Training, Service, Consulting, & 24x7 Support
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.14 (GNU/Linux)
Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/

iQIcBAEBAgAGBQJTJ6I1AAoJEDfy90M199hlDsAP/320vLhjKRjaxulBmcESmuze
LUoIOHLS2ZacybTjcMlZSoTBSzf6iXzc3A84ROhQ0c7ASYzWtF/YFkd039FzHz+e
TxtuodZd+CO18f1ZYBR5S7AhXhsA7oviXXdgPhqwb14mIqGAlvblTp9cvMODK+OS
O96NSLe2qe1AvmxwwthcKzhlXBChzoRvT8jXeS5A/G+VfM7UV1HApGmklJE0oe9+
ZaXhxQWGecKqZgkPwfZzIzOz9qQITDb3woi7GxbiXLv8Ds1lgAxPRz26qJB/mKBC
NqxQHViyty79TA8EFV8DrE0g++CUz33rSs1suY5Z1yzsQ7iEFBP1U52BJE5ZdB0J
8Zpz1eLT15fEIuV+64MeXN47U2refJdEjw9Ozx788MgDOu43k9m4+VHjzcH1AO2l
qfp0eqxpIjDpqH4Lu/0DAzl86yEW76tJX+pdieICGOHLdruLS/984gZGtDpjclNE
l/FaliLQQ4Bvqg8tMmmq/dyTxBG+BRmfCBbaBRdtQA762P9Lh7QsL/mGHukwRNVb
M5Ve7i/1HT7ZrazEnMkAotnYMrH5QTy1qTVfjiR0gjXzccdMXSOT8NN/yiWwOq6d
ZRuBvr8Ws+xCDDWwABj8Oh2mKpupy04/87EaTy2+sh6yJaIZPPV+n4ftjF4NF/gP
zBhXxMpFalKDqevkp52Z
=/Nl7
-----END PGP SIGNATURE-----

On 18.03.14 00:15, Tom Lane wrote:
> Jim Nasby <jim(at)nasby(dot)net> writes:
>> On 3/17/14, 8:47 AM, Tom Lane wrote:
>>> (Note that this is only one of assorted O(N^2) behaviors in older versions
>>> of pg_dump; we've gradually stamped them out over time.)
>
>> On that note, it's recommended that when you are taking a backup to restore into a newer version of Postgres you create the dump using the NEWER version of pg_dump, not the old one.
>
> Right. IIRC, the OP said he *did* use a recent pg_dump ... but this
> particular issue got fixed server-side, so the new pg_dump didn't help
> against an 8.1 server :-(

Yes, I did use 9.3's pg_dump against my 8.1 DB initially.

The patch was created against github's master.

-Jürgen

On 18.03.14 02:32, Joe Conway wrote:
> On 03/17/2014 05:55 PM, Jeff Janes wrote:
>> On Mon, Mar 17, 2014 at 5:48 PM, Craig Ringer
>> <craig(at)2ndquadrant(dot)com I wonder if doing large batches of
>
>> LOCK TABLE table1, table2, table3, ...
>
>> would help, instead of doing individual statements?
>
>> If I recall correctly, someone did submit a patch to do that. It
>> helped when dumping schema only, but not much when dumping data.
>
> Not surprising at all. The huge time is incurred in taking the locks,
> but if you are trying to use pg_upgrade in link mode to speed your
> upgrade, you are totally hosed by the time it takes to grab those locks.
>
> This patch applied to 9.3 substantially fixes the issue:
> 8<-----------------------
> commit eeb6f37d89fc60c6449ca12ef9e91491069369cb
> Author: Heikki Linnakangas <heikki(dot)linnakangas(at)iki(dot)fi>
> Date: Thu Jun 21 15:01:17 2012 +0300
>
> Add a small cache of locks owned by a resource owner in ResourceOwner.
> 8<-----------------------
>
> On my 8.4 database, with 500,000 tables there were about 2.5 million
> locks taken including toast tables and indexes during the schema dump.
> Without the patch grabbing locks took many, many days with that many
> objects to lock. With a backported version of the patch, one hour.
>
> So if you have a problem due to many tables on an older than 9.3
> version of Postgres, this is the direction to head (a custom patch
> applied to your old version just long enough to get successfully
> upgraded).
>

In a testing environment I restored my 8.1 DB with 300,000 tables to a
9.3 server (using my patched pg_dump).

Then I ran the original 9.3 pg_dump against the 9.3 DB again, and it
works reasonably well. So I can confirm the server side improvements in
9.3 do to work for my test case.

Still when I finally get around to do this on production I plan to use
my patched pg_dump rather than backporting the server fix to 8.1, as I'd
rather not touch our already-patched-for-something-else 8.1 server.

I can't wait to get my hand on 9.x replication features and other stuff :-)

-Jürgen