Re: Further pg_upgrade analysis for many tables

On Thu, Nov 29, 2012 at 12:59:19PM -0500, Bruce Momjian wrote:
> I have polished up the patch (attached) and it is ready for application
> to 9.3.

Applied.

---------------------------------------------------------------------------

> Since there is no pg_dump/pg_restore pipe parallelism, I had the old
> cluster create per-database dump files, so I don't need to have the old
> and new clusters running at the same time, which would have required two
> port numbers and make shared memory exhaustion more likely.
>
> We now create a dump file per database, so thousands of database dump
> files might cause a performance problem.
>
> This also adds status output so you can see the database names as their
> schemas are dumped and restored. This was requested by users.
>
> I retained custom mode for pg_dump because it is measurably faster than
> text mode (not sure why, psql overhead?):
>
> git -Fc -Fp
> 1 11.04 11.08 11.02
> 1000 22.37 19.68 21.64
> 2000 32.39 28.62 31.40
> 4000 56.18 48.53 51.15
> 8000 105.15 81.23 91.84
> 16000 227.64 156.72 177.79
> 32000 542.80 323.19 371.81
> 64000 1711.77 789.17 865.03
>
> Text dump files are slightly easier to debug, but probably not by much.
>
> Single-transaction restores were recommended to me over a year ago (by
> Magnus?), but I wanted to get pg_upgrade rock-solid before doing
> optimization, and now is the right time to optimize.
>
> One risk of single-transaction restores is max_locks_per_transaction
> exhaustion, but you will need to increase that on the old cluster for
> pg_dump anyway because that is done a single transaction, so the only
> new thing is that the new cluster might also need to adjust
> max_locks_per_transaction.
>
> I was able to remove split_old_dump() because pg_dumpall now produces a
> full global restore file and we do database dumps separately.

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

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