Re: Query execution in Perl TAP tests needs work

On 2023-08-28 Mo 09:23, Andrew Dunstan wrote:
>
>
> On 2023-08-28 Mo 01:29, Thomas Munro wrote:
>> Hi,
>>
>> 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.
>>
>> 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. Could there be an event handling snafu in IPC::Run or elsewhere
>> nearby? It seems like there must be either a busy loop or a busted
>> sleep/wakeup... somewhere? But even if there's a weird bug here
>> waiting to be discovered and fixed, I guess it'll always be too slow
>> at ~10ms per process spawned, with two processes to spawn, and it's
>> bad enough on Unix.
>>
>> 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:
>>
>> 1. Don't fork anything at all: open (and cache) a connection directly
>> from Perl.
>> 1a. Write xsub or ffi bindings for libpq. Or vendor (parts) of the
>> popular Perl xsub library?
>> 1b. Write our own mini pure-perl pq client module. Or vendor (parts)
>> of some existing one.
>> 2. Use long-lived psql sessions.
>> 2a. Something building on BackgroundPsql.
>> 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?
>>
>> In some other languages one can do FFI pretty easily so we could use
>> the in-tree libpq without extra dependencies:
>>
>>>>> import ctypes
>>>>> libpq = ctypes.cdll.LoadLibrary("/path/to/libpq.so")
>>>>> libpq.PQlibVersion()
>> 170000
>>
>> ... but it seems you can't do either static C bindings or runtime FFI
>> from Perl without adding a new library/package dependency. I'm not
>> much of a Perl hacker so I don't have any particular feeling. What
>> would be best?
>>
>> This message brought to you by the Lorax.
>
> Thanks for raising this. Windows test times have bothered me for ages.
>
> The standard perl DBI library has a connect_cached method. Of course
> we don't want to be dependent on it, especially if we might have
> changed libpq in what we're testing, and it would place a substantial
> new burden on testers like buildfarm owners.
>
> I like the idea of using a pure perl pq implementation, not least
> because it could expand our ability to test things at the protocol
> level. Not sure how much work it would be. I'm willing to help if we
> want to go that way.
>
> Yes you need an external library to use FFI in perl, but there's one
> that's pretty tiny. See <https://metacpan.org/pod/FFI::Library>. There
> is also FFI::Platypus, but it involves building a library. OTOH,
> that's the one that's available standard on my Fedora and Ubuntu
> systems. I haven't tried using either Maybe we could use some logic
> that would use the FFI interface if it's available, and fall back on
> current usage.
>
>
>

I had a brief play with this. Here's how easy it was to wrap libpq in perl:

#!/usr/bin/perl

use strict; use warnings;

use FFI::Platypus;

my $ffi = FFI::Platypus->new(api=>1);
$ffi->lib("inst/lib/libpq.so");

$ffi->type('opaque' => 'PGconn');
$ffi->attach(PQconnectdb => [ 'string' ] => 'PGconn');
$ffi->attach(PQfinish => [ 'PGconn' ] => 'void');

$ffi->type('opaque' => 'PGresult');
$ffi->attach(PQexec => [ 'PGconn', 'string' ] => 'PGresult');
$ffi->attach(PQgetvalue => [ 'PGresult', 'int', 'int' ] => 'string');

my $pgconn = PQconnectdb("dbname=postgres host=/tmp");
my $res = PQexec($pgconn, "select count(*) from pg_class");
my $count = PQgetvalue( $res, 0, 0);

print "count: $count\n";

PQfinish($pgconn);

cheers

andrew

--
Andrew Dunstan
EDB:https://www.enterprisedb.com