Re: Pg_upgrade speed for many tables

On Mon, Nov 12, 2012 at 10:29:39AM -0800, Jeff Janes wrote:
> On Mon, Nov 5, 2012 at 12:08 PM, Bruce Momjian <bruce(at)momjian(dot)us> 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.
>
> Is turning off synchronous_commit enough? What about turning off fsync?

I did some testing with the attached patch on a magnetic disk with no
BBU that turns off fsync; I got these results:

sync_com=off fsync=off
1 15.90 13.51
1000 26.09 24.56
2000 33.41 31.20
4000 57.39 57.74
8000 102.84 116.28
16000 189.43 207.84

It shows fsync faster for < 4k, and slower for > 4k. Not sure why this
is the cause but perhaps the buffering of the fsync is actually faster
than doing a no-op fsync.

I don't think fsync=off makes sense, except for testing; let me know if
I should test something else.

> When I'm doing a pg_upgrade with thousands of tables, the shutdown
> checkpoint after restoring the dump to the new cluster takes a very
> long time, as the writer drains its operation table by opening and
> individually fsync-ing thousands of files. This takes about 40 ms per
> file, which I assume is a combination of slow lap-top disk drive, and
> a strange deal with ext4 which makes fsyncing a recently created file
> very slow. But even with faster hdd, this would still be a problem
> if it works the same way, with every file needing 4 rotations to be
> fsynced and this happens in serial.

Is this with the current code that does synchronous_commit=off? If not,
can you test to see if this is still a problem?

> Worse, the shutdown only waits for the default of 60 seconds for the
> shutdown to take place before it throws an error and the entire
> pg_upgrade gives up. It seems to me that either the -t setting should
> be increased, or should be an option to pg_upgrade.
>
> My work around was to invoke a system-wide "sync" a couple seconds
> after the 'pg_ctl stop' is initiated. Flushing the files wholesale
> seems to work to make the checkpoint writer rapidly find it has
> nothing to do when it tries to flush them retail.
>
> Anyway, the reason I think turning fsync off might be reasonable is
> that as soon as the new cluster is shut down, pg_upgrade starts
> overwriting most of those just-fsynced file with other files from the
> old cluster, and AFAICT makes no effort to fsync them. So until there
> is a system-wide sync after the pg_upgrade finishes, your new cluster
> is already in mortal danger anyway.

pg_upgrade does a cluster shutdown before overwriting those files.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +