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.
