Re: Query execution in Perl TAP tests needs work

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