Re: [HACKERS] pg_dump without explicit table locking
On 18.03.14 00:15, Tom Lane wrote: Jim Nasby j...@nasby.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_dump without explicit table locking
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 cr...@2ndquadrant.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.linnakan...@iki.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_dump without explicit table locking
On Mon, Mar 17, 2014 at 7:52 AM, Jürgen Strobel juergen...@strobel.info wrote: at work at my company I inherited responsibility for a large PG 8.1 DB, with a an extreme number of tables (~30). 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_dump without explicit table locking
2014-03-17 12:52 GMT+01:00 Jürgen Strobel juergen...@strobel.info: Hi, at work at my company I inherited responsibility for a large PG 8.1 DB, with a an extreme number of tables (~30). 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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_dump without explicit table locking
Pavel Stehule pavel.steh...@gmail.com writes: 2014-03-17 12:52 GMT+01:00 Jürgen Strobel juergen...@strobel.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_dump without explicit table locking
On 3/17/14, 8:47 AM, Tom Lane wrote: Pavel Stehule pavel.steh...@gmail.com writes: 2014-03-17 12:52 GMT+01:00 Jürgen Strobel juergen...@strobel.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 j...@nasby.net 512.569.9461 (cell) http://jim.nasby.net -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_dump without explicit table locking
Jim Nasby j...@nasby.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_dump without explicit table locking
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 03/17/2014 04:15 PM, Tom Lane wrote: Jim Nasby j...@nasby.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- -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_dump without explicit table locking
-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 j...@nasby.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- -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_dump without explicit table locking
On Mon, Mar 17, 2014 at 5:48 PM, Craig Ringer cr...@2ndquadrant.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 j...@nasby.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
Re: [HACKERS] pg_dump without explicit table locking
-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 cr...@2ndquadrant.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.linnakan...@iki.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- -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
