Re: Further pg_upgrade analysis for many tables
От | Bruce Momjian |
---|---|
Тема | Re: Further pg_upgrade analysis for many tables |
Дата | |
Msg-id | 20121114223329.GA6753@momjian.us обсуждение исходный текст |
Ответ на | Re: Further pg_upgrade analysis for many tables (Bruce Momjian <bruce@momjian.us>) |
Список | pgsql-hackers |
Patch applied to git head. Thanks Ants Aasma for the analysis that lead to the patch. --------------------------------------------------------------------------- On Tue, Nov 13, 2012 at 07:03:51PM -0500, Bruce Momjian wrote: > On Tue, Nov 13, 2012 at 05:44:54AM +0200, Ants Aasma wrote: > > On Mon, Nov 12, 2012 at 10:59 PM, Bruce Momjian <bruce@momjian.us> wrote: > > > You can see a significant speedup with those loops removed. The 16k > > > case is improved, but still not linear. The 16k dump/restore scale > > > looks fine, so it must be something in pg_upgrade, or in the kernel. > > > > I can confirm the speedup. Profiling results for 9.3 to 9.3 upgrade > > for 8k and 64k tables are attached. pg_upgrade itself is now taking > > negligible time. > > I generated these timings from the attached test script. > > -------------------------- 9.3 ------------------------ > ---- normal ---- -- binary_upgrade -- -- pg_upgrade - > - dmp - - res - - dmp - - res - git patch > 1 0.12 0.07 0.13 0.07 11.06 11.02 > 1000 2.20 2.46 3.57 2.82 19.15 18.61 > 2000 4.51 5.01 8.22 5.80 29.12 26.89 > 4000 8.97 10.88 14.76 12.43 45.87 43.08 > 8000 15.30 24.72 30.57 27.10 100.31 79.75 > 16000 36.14 54.88 62.27 61.69 248.03 167.94 > 32000 55.29 162.20 115.16 179.15 695.05 376.84 > 64000 149.86 716.46 265.77 724.32 2323.73 1122.38 > > You can see the speedup of the patch, particularly for a greater number > of tables, e.g. 2x faster for 64k tables. > > > The 64k profile shows the AtEOXact_RelationCache scaling problem. For > > the 8k profile nothing really pops out as a clear bottleneck. CPU time > > distributes 83.1% to postgres, 4.9% to pg_dump, 7.4% to psql and 0.7% > > to pg_upgrade. > > At 64k I see pg_upgrade taking 12% of the duration time, if I subtract > out the dump/restore times. > > I am attaching an updated pg_upgrade patch, which I believe is ready for > application for 9.3. > > > Postgres time itself breaks down with 10% for shutdown checkpoint and > > 90% for regular running, consisting of 16% parsing, 13% analyze, 20% > > plan, 30% execute, 11% commit (AtEOXact_RelationCache) and 6% network. > > That SVG graph was quite impressive. > > > It looks to me that most benefit could be had from introducing more > > parallelism. Are there any large roadblocks to pipelining the dump and > > restore to have them happen in parallel? > > I talked to Andrew Dustan about parallelization in pg_restore. First, > we currently use pg_dumpall, which isn't in the custom format required > for parallel restore, but if we changed to custom format, create table > isn't done in parallel, only create index/check constraints, and trigger > creation, etc. Not sure if it worth perusing this just for pg_upgrade. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +
В списке pgsql-hackers по дате отправления: