Pg_upgrade speed for many tables
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 <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
Attachments:
sync_off.difftext/x-diff; charset=us-asciiDownload+5-5
Bruce Momjian <bruce@momjian.us> writes:
Magnus reported that a customer with a million tables was finding
pg_upgrade slow.
You sure there's not an O(N^2) issue in there somewhere?
I don't see anything unsafe about having pg_upgrade use
synchronous_commit=off.
No objection, but this seems unlikely to be better than linear speedup,
with a not-terribly-large constant factor.
BTW, does pg_upgrade run pg_restore in --single-transaction mode?
That would probably make synchronous_commit moot, at least for that
step.
regards, tom lane
On Mon, Nov 5, 2012 at 9:14 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Bruce Momjian <bruce@momjian.us> writes:
Magnus reported that a customer with a million tables was finding
pg_upgrade slow.You sure there's not an O(N^2) issue in there somewhere?
I don't see anything unsafe about having pg_upgrade use
synchronous_commit=off.No objection, but this seems unlikely to be better than linear speedup,
with a not-terribly-large constant factor.BTW, does pg_upgrade run pg_restore in --single-transaction mode?
That would probably make synchronous_commit moot, at least for that
step.
It doesn't use pg_restore at all - it uses the dump from pg_dumpall, which
you can't reload with pg_restore.
--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/
Magnus Hagander <magnus@hagander.net> writes:
On Mon, Nov 5, 2012 at 9:14 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
BTW, does pg_upgrade run pg_restore in --single-transaction mode?
That would probably make synchronous_commit moot, at least for that
step.
It doesn't use pg_restore at all - it uses the dump from pg_dumpall, which
you can't reload with pg_restore.
Sorry, I should've said psql --single-transaction. Although that isn't
going to work either given the presence of \connect commands in the
script. I wonder whether pg_dumpall ought to have some sort of "one
transaction per database please" option.
regards, tom lane
On Mon, Nov 5, 2012 at 03:30:32PM -0500, Tom Lane wrote:
Magnus Hagander <magnus@hagander.net> writes:
On Mon, Nov 5, 2012 at 9:14 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
BTW, does pg_upgrade run pg_restore in --single-transaction mode?
That would probably make synchronous_commit moot, at least for that
step.It doesn't use pg_restore at all - it uses the dump from pg_dumpall, which
you can't reload with pg_restore.Sorry, I should've said psql --single-transaction. Although that isn't
going to work either given the presence of \connect commands in the
script. I wonder whether pg_dumpall ought to have some sort of "one
transaction per database please" option.
pg_dumpall is already doing lots of gymnastics with SQL, and pg_upgrade
splits the output file into db/user creation and object creation, so I
am hesitant to add anything more in there.
I was surprised by the scale of the performance improvement, but a
simple table creation test confirmed that improvement, irregardless of
pg_upgrade. Perhaps we should suggest synchronous_commit=off for
pg_dumpall restores, particularly when using --schema-only.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
On Mon, Nov 5, 2012 at 9:49 PM, Bruce Momjian <bruce@momjian.us> wrote:
On Mon, Nov 5, 2012 at 03:30:32PM -0500, Tom Lane wrote:
Magnus Hagander <magnus@hagander.net> writes:
On Mon, Nov 5, 2012 at 9:14 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
BTW, does pg_upgrade run pg_restore in --single-transaction mode?
That would probably make synchronous_commit moot, at least for that
step.It doesn't use pg_restore at all - it uses the dump from pg_dumpall,
which
you can't reload with pg_restore.
Sorry, I should've said psql --single-transaction. Although that isn't
going to work either given the presence of \connect commands in the
script. I wonder whether pg_dumpall ought to have some sort of "one
transaction per database please" option.pg_dumpall is already doing lots of gymnastics with SQL, and pg_upgrade
splits the output file into db/user creation and object creation, so I
am hesitant to add anything more in there.
What about running pg_dump in a loop instead of pg_dumpall?
--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/
On Mon, Nov 5, 2012 at 10:01:22PM +0100, Magnus Hagander wrote:
On Mon, Nov 5, 2012 at 9:49 PM, Bruce Momjian <bruce@momjian.us> wrote:
On Mon, Nov 5, 2012 at 03:30:32PM -0500, Tom Lane wrote:
Magnus Hagander <magnus@hagander.net> writes:
On Mon, Nov 5, 2012 at 9:14 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
BTW, does pg_upgrade run pg_restore in --single-transaction mode?
That would probably make synchronous_commit moot, at least for that
step.It doesn't use pg_restore at all - it uses the dump from pg_dumpall,
which
you can't reload with pg_restore.
Sorry, I should've said psql --single-transaction. Although that isn't
going to work either given the presence of \connect commands in the
script. I wonder whether pg_dumpall ought to have some sort of "one
transaction per database please" option.pg_dumpall is already doing lots of gymnastics with SQL, and pg_upgrade
splits the output file into db/user creation and object creation, so I
am hesitant to add anything more in there.What about running pg_dump in a loop instead of pg_dumpall?
Well, I could cetainly do pg_dumpall --globals-only, and then I have to
create a pg_dump file for every database, and then add the \connect in
there; it just seemed easier to use pg_dumpall, though the file split
thing is certainly something I would like to get rid of.
I think I used pg_dumpall because it was an existing tool that I assumed
would be maintained to dump a full cluster.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
On Mon, Nov 5, 2012 at 12:49 PM, Bruce Momjian <bruce@momjian.us> wrote:
On Mon, Nov 5, 2012 at 03:30:32PM -0500, Tom Lane wrote:
Magnus Hagander <magnus@hagander.net> writes:
On Mon, Nov 5, 2012 at 9:14 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
BTW, does pg_upgrade run pg_restore in --single-transaction mode?
That would probably make synchronous_commit moot, at least for that
step.It doesn't use pg_restore at all - it uses the dump from pg_dumpall, which
you can't reload with pg_restore.Sorry, I should've said psql --single-transaction. Although that isn't
going to work either given the presence of \connect commands in the
script. I wonder whether pg_dumpall ought to have some sort of "one
transaction per database please" option.pg_dumpall is already doing lots of gymnastics with SQL, and pg_upgrade
splits the output file into db/user creation and object creation, so I
am hesitant to add anything more in there.I was surprised by the scale of the performance improvement, but a
simple table creation test confirmed that improvement, irregardless of
pg_upgrade. Perhaps we should suggest synchronous_commit=off for
pg_dumpall restores, particularly when using --schema-only.
Or have options for pg_dump and pg_restore to insert "set
synchronous_commit=off" into the SQL stream?
Cheers,
Jeff
On Mon, Nov 5, 2012 at 01:07:45PM -0800, Jeff Janes wrote:
On Mon, Nov 5, 2012 at 12:49 PM, Bruce Momjian <bruce@momjian.us> wrote:
On Mon, Nov 5, 2012 at 03:30:32PM -0500, Tom Lane wrote:
Magnus Hagander <magnus@hagander.net> writes:
On Mon, Nov 5, 2012 at 9:14 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
BTW, does pg_upgrade run pg_restore in --single-transaction mode?
That would probably make synchronous_commit moot, at least for that
step.It doesn't use pg_restore at all - it uses the dump from pg_dumpall, which
you can't reload with pg_restore.Sorry, I should've said psql --single-transaction. Although that isn't
going to work either given the presence of \connect commands in the
script. I wonder whether pg_dumpall ought to have some sort of "one
transaction per database please" option.pg_dumpall is already doing lots of gymnastics with SQL, and pg_upgrade
splits the output file into db/user creation and object creation, so I
am hesitant to add anything more in there.I was surprised by the scale of the performance improvement, but a
simple table creation test confirmed that improvement, irregardless of
pg_upgrade. Perhaps we should suggest synchronous_commit=off for
pg_dumpall restores, particularly when using --schema-only.Or have options for pg_dump and pg_restore to insert "set
synchronous_commit=off" into the SQL stream?
You can already do that with PGOPTIONS:
PGOPTIONS="-c synchronous_commit=off" pg_restore ...
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
On Mon, Nov 5, 2012 at 4:07 PM, Jeff Janes <jeff.janes@gmail.com> wrote:
Or have options for pg_dump and pg_restore to insert "set
synchronous_commit=off" into the SQL stream?
It would be kind of neat if we had a command that would force all
previously-asynchronous commits to complete. It seems likely that
very, very few people would care about intermediate pg_dump states, so
we could do the whole dump asynchronously and then do "FORCE ALL
COMMITS;" or whatever at the end.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
On Mon, Nov 5, 2012 at 04:14:47PM -0500, Robert Haas wrote:
On Mon, Nov 5, 2012 at 4:07 PM, Jeff Janes <jeff.janes@gmail.com> wrote:
Or have options for pg_dump and pg_restore to insert "set
synchronous_commit=off" into the SQL stream?It would be kind of neat if we had a command that would force all
previously-asynchronous commits to complete. It seems likely that
very, very few people would care about intermediate pg_dump states, so
we could do the whole dump asynchronously and then do "FORCE ALL
COMMITS;" or whatever at the end.
Actually, I had assumed that a session disconnection forced a WAL fsync
flush, but now I doubt that. Seems only server shutdown does that, or a
checkpoint. Would this work?
SET synchronous_commit=on;
CREATE TABLE dummy(x int);
DROP TABLE dummy;
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
On Mon, Nov 5, 2012 at 1:14 PM, Robert Haas <robertmhaas@gmail.com> wrote:
On Mon, Nov 5, 2012 at 4:07 PM, Jeff Janes <jeff.janes@gmail.com> wrote:
Or have options for pg_dump and pg_restore to insert "set
synchronous_commit=off" into the SQL stream?It would be kind of neat if we had a command that would force all
previously-asynchronous commits to complete. It seems likely that
very, very few people would care about intermediate pg_dump states, so
we could do the whole dump asynchronously and then do "FORCE ALL
COMMITS;" or whatever at the end.
Yeah, I was wondering what a fool-proof way of doing that would be,
without implementing a new feature. Turning synchronous_commits back
on and then doing and committing a transaction guaranteed to generate
WAL would do it.
Would a simple 'select pg_switch_xlog();' always accomplish the desired flush?
Cheers,
Jeff
On Mon, Nov 5, 2012 at 01:23:58PM -0800, Jeff Janes wrote:
On Mon, Nov 5, 2012 at 1:14 PM, Robert Haas <robertmhaas@gmail.com> wrote:
On Mon, Nov 5, 2012 at 4:07 PM, Jeff Janes <jeff.janes@gmail.com> wrote:
Or have options for pg_dump and pg_restore to insert "set
synchronous_commit=off" into the SQL stream?It would be kind of neat if we had a command that would force all
previously-asynchronous commits to complete. It seems likely that
very, very few people would care about intermediate pg_dump states, so
we could do the whole dump asynchronously and then do "FORCE ALL
COMMITS;" or whatever at the end.Yeah, I was wondering what a fool-proof way of doing that would be,
without implementing a new feature. Turning synchronous_commits back
on and then doing and committing a transaction guaranteed to generate
WAL would do it.Would a simple 'select pg_switch_xlog();' always accomplish the desired flush?
That could generate a lot of WAL files if used regularly. :-( Does
SELECT txid_current() generate WAL? I think it does.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
Bruce Momjian escribió:
On Mon, Nov 5, 2012 at 04:14:47PM -0500, Robert Haas wrote:
On Mon, Nov 5, 2012 at 4:07 PM, Jeff Janes <jeff.janes@gmail.com> wrote:
Or have options for pg_dump and pg_restore to insert "set
synchronous_commit=off" into the SQL stream?It would be kind of neat if we had a command that would force all
previously-asynchronous commits to complete. It seems likely that
very, very few people would care about intermediate pg_dump states, so
we could do the whole dump asynchronously and then do "FORCE ALL
COMMITS;" or whatever at the end.Actually, I had assumed that a session disconnection forced a WAL fsync
flush, but now I doubt that. Seems only server shutdown does that, or a
checkpoint. Would this work?SET synchronous_commit=on;
CREATE TABLE dummy(x int);
DROP TABLE dummy;
AFAIR any transaction that modifies catalogs gets sync commit forcibly,
regardless of the setting. And sync commit means you get to wait for
all previous transactions to be flushed as well. So simply creating a
temp table ought to do the trick ...
--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
On Mon, Nov 5, 2012 at 06:33:16PM -0300, Alvaro Herrera wrote:
Bruce Momjian escribi�:
On Mon, Nov 5, 2012 at 04:14:47PM -0500, Robert Haas wrote:
On Mon, Nov 5, 2012 at 4:07 PM, Jeff Janes <jeff.janes@gmail.com> wrote:
Or have options for pg_dump and pg_restore to insert "set
synchronous_commit=off" into the SQL stream?It would be kind of neat if we had a command that would force all
previously-asynchronous commits to complete. It seems likely that
very, very few people would care about intermediate pg_dump states, so
we could do the whole dump asynchronously and then do "FORCE ALL
COMMITS;" or whatever at the end.Actually, I had assumed that a session disconnection forced a WAL fsync
flush, but now I doubt that. Seems only server shutdown does that, or a
checkpoint. Would this work?SET synchronous_commit=on;
CREATE TABLE dummy(x int);
DROP TABLE dummy;AFAIR any transaction that modifies catalogs gets sync commit forcibly,
regardless of the setting. And sync commit means you get to wait for
Uh, I am not seeing that my testing because I was only doing CREATE
TABLE and it was affected by the synchronous_commit value.
all previous transactions to be flushed as well. So simply creating a
temp table ought to do the trick ...
I don't think TEMP tables write to WAL, for performance reasons.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
On Mon, Nov 5, 2012 at 4:33 PM, Alvaro Herrera <alvherre@2ndquadrant.com> wrote:
AFAIR any transaction that modifies catalogs gets sync commit forcibly,
regardless of the setting. And sync commit means you get to wait for
all previous transactions to be flushed as well. So simply creating a
temp table ought to do the trick ...
I don't think there's a carve-out for system tables ... but creating a
temp table with synchronous_commit=on will certainly do the trick.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
On Mon, Nov 5, 2012 at 04:39:27PM -0500, Robert Haas wrote:
On Mon, Nov 5, 2012 at 4:33 PM, Alvaro Herrera <alvherre@2ndquadrant.com> wrote:
AFAIR any transaction that modifies catalogs gets sync commit forcibly,
regardless of the setting. And sync commit means you get to wait for
all previous transactions to be flushed as well. So simply creating a
temp table ought to do the trick ...I don't think there's a carve-out for system tables ... but creating a
temp table with synchronous_commit=on will certainly do the trick.
What is a temp table writing to WAL? The pg_class/pg_attribute changes?
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
On Mon, Nov 5, 2012 at 4:42 PM, Bruce Momjian <bruce@momjian.us> wrote:
On Mon, Nov 5, 2012 at 04:39:27PM -0500, Robert Haas wrote:
On Mon, Nov 5, 2012 at 4:33 PM, Alvaro Herrera <alvherre@2ndquadrant.com> wrote:
AFAIR any transaction that modifies catalogs gets sync commit forcibly,
regardless of the setting. And sync commit means you get to wait for
all previous transactions to be flushed as well. So simply creating a
temp table ought to do the trick ...I don't think there's a carve-out for system tables ... but creating a
temp table with synchronous_commit=on will certainly do the trick.What is a temp table writing to WAL? The pg_class/pg_attribute changes?
Yes.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
On Mon, Nov 05, 2012 at 04:42:56PM -0500, Bruce Momjian wrote:
On Mon, Nov 5, 2012 at 04:39:27PM -0500, Robert Haas wrote:
On Mon, Nov 5, 2012 at 4:33 PM, Alvaro Herrera <alvherre@2ndquadrant.com> wrote:
AFAIR any transaction that modifies catalogs gets sync commit forcibly,
regardless of the setting. And sync commit means you get to wait for
all previous transactions to be flushed as well. So simply creating a
temp table ought to do the trick ...
SET synchronous_commit = on;
SELECT txid_current();
Should be enough.
I don't think there's a carve-out for system tables ... but creating a
temp table with synchronous_commit=on will certainly do the trick.What is a temp table writing to WAL? The pg_class/pg_attribute changes?
Yes.
Andres
On Mon, Nov 5, 2012 at 1:39 PM, Robert Haas <robertmhaas@gmail.com> wrote:
On Mon, Nov 5, 2012 at 4:33 PM, Alvaro Herrera <alvherre@2ndquadrant.com> wrote:
AFAIR any transaction that modifies catalogs gets sync commit forcibly,
regardless of the setting. And sync commit means you get to wait for
all previous transactions to be flushed as well. So simply creating a
temp table ought to do the trick ...I don't think there's a carve-out for system tables ... but creating a
temp table with synchronous_commit=on will certainly do the trick.
But that seems like something that might be optimized away in the
future (for example, so that temp tables can be used on hot standbys)
resulting in action-at-a-distance breakage.
Is txid_current() more fundamental, i.e. less likely to change?
Cheers,
Jeff
