Feedback-ID: id4a34324:Fastmail
Date: Wed, 30 Aug 2023 15:19:26 -0700
From: Andres Freund <andres@anarazel.de>
To: Thomas Munro <thomas.munro@gmail.com>
Cc: pgsql-hackers <pgsql-hackers@postgresql.org>
Subject: Re: Query execution in Perl TAP tests needs work
Message-ID: <20230830221926.g3cprzh4cgmxnmya@alap3.anarazel.de>
References: <CA+hUKGJoEO33K=ZynsH=xkiEyfBMZjOoqBK+gouBdTGW2-woig@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <CA+hUKGJoEO33K=ZynsH=xkiEyfBMZjOoqBK+gouBdTGW2-woig@mail.gmail.com>
Archived-At: <https://www.postgresql.org/message-id/20230830221926.g3cprzh4cgmxnmya%40alap3.anarazel.de>
Precedence: bulk

Hi,

On 2023-08-28 17:29:56 +1200, Thomas Munro wrote:
> Every time we run a SQL query, we fork a new psql process and a new
> cold backend process.  It's not free on Unix, and quite a lot worse on
> Windows, at around 70ms per query.  Take amcheck/001_verify_heapam for
> example.  It runs 272 subtests firing off a stream of queries, and
> completes in ~51s on Windows (!), and ~6-9s on the various Unixen, on
> CI.

Whoa.

> Here are some timestamps I captured from CI by instrumenting various
> Perl and C bits:
> 
> 0.000s: IPC::Run starts
> 0.023s:   postmaster socket sees connection
> 0.025s:   postmaster has created child process
> 0.033s:     backend starts running main()
> 0.039s:     backend has reattached to shared memory
> 0.043s:     backend connection authorized message
> 0.046s:     backend has executed and logged query
> 0.070s: IPC::Run returns
> 
> I expected process creation to be slow on that OS, but it seems like
> something happening at the end is even slower.  CI shows Windows
> consuming 4 CPUs at 100% for a full 10 minutes to run a test suite
> that finishes in 2-3 minutes everywhere else with the same number of
> CPUs.

It finishes in that time on linux, even with sanitizers enabled...


> As an experiment, I hacked up a not-good-enough-to-share experiment
> where $node->safe_psql() would automatically cache a BackgroundPsql
> object and reuse it, and the times for that test dropped ~51 -> ~9s on
> Windows, and ~7 -> ~2s on the Unixen.  But even that seems non-ideal
> (well it's certainly non-ideal the way I hacked it up anyway...).  I
> suppose there are quite a few ways we could do better:

That's a really impressive win.

Even if we "just" converted some of the safe_psql() cases and converted
poll_query_until() to this, we'd win a lot.


> 1.  Don't fork anything at all: open (and cache) a connection directly
> from Perl.

One advantage of that is that the socket is entirely controlled by perl, so
waiting for IO should be easy...


> 2b.  Maybe give psql or a new libpq-wrapper a new low level stdio/pipe
> protocol that is more fun to talk to from Perl/machines?

That does also seem promising - a good chunk of the complexity around some of
the IPC::Run uses is that we end up parsing psql input/output...

Greetings,

Andres Freund