Horton

Re: optimizing pg_upgrade's once-in-each-database steps

Nathan Bossart <nathandbossart@gmail.com>

View thread View on postgr.es
From: Nathan Bossart <nathandbossart@gmail.com>
To: Ilya Gladyshev <ilya.v.gladyshev@gmail.com>
Cc: Daniel Gustafsson <daniel@yesql.se>, Robert Haas <robertmhaas@gmail.com>, Jeff Davis <pgsql@j-davis.com>, PostgreSQL Hackers <pgsql-hackers@postgresql.org>
Date: 2024-08-01T21:41:18Z
Lists: pgsql-hackers

Attachments

On Thu, Aug 01, 2024 at 12:44:35PM -0500, Nathan Bossart wrote:
> On Wed, Jul 31, 2024 at 10:55:33PM +0100, Ilya Gladyshev wrote:
>> I like your idea of parallelizing these checks with async libpq API, thanks
>> for working on it. The patch doesn't apply cleanly on master anymore, but
>> I've rebased locally and taken it for a quick spin with a pg16 instance of
>> 1000 empty databases. Didn't see any regressions with -j 1, there's some
>> speedup with -j 8 (33 sec vs 8 sec for these checks).
> 
> Thanks for taking a look.  I'm hoping to do a round of polishing before
> posting a rebased patch set soon.
> 
>> One thing that I noticed that could be improved is we could start a new
>> connection right away after having run all query callbacks for the current
>> connection in process_slot, instead of just returning and establishing the
>> new connection only on the next iteration of the loop in async_task_run
>> after potentially sleeping on select.
> 
> Yeah, we could just recursively call process_slot() right after freeing the
> slot.  That'd at least allow us to avoid the spinning behavior as we run
> out of databases to process, if nothing else.

Here is a new patch set.  Besides rebasing, I've added the recursive call
to process_slot() mentioned in the quoted text, and I've added quite a bit
of commentary to async.c.

-- 
nathan

Commits

  1. Introduce framework for parallelizing various pg_upgrade tasks.

  2. pg_upgrade: Parallelize WITH OIDS check.

  3. pg_upgrade: Parallelize contrib/isn check.

  4. pg_upgrade: Parallelize data type checks.

  5. pg_upgrade: Parallelize encoding conversion check.

  6. pg_upgrade: Parallelize incompatible polymorphics check.

  7. pg_upgrade: Parallelize postfix operator check.

  8. pg_upgrade: Parallelize retrieving extension updates.

  9. pg_upgrade: Parallelize retrieving loadable libraries.

  10. pg_upgrade: Parallelize retrieving relation information.

  11. pg_upgrade: Parallelize subscription check.

  12. pg_upgrade: Move live_check variable to user_opts.

  13. pg_upgrade: run all data type checks per connection