Hi,
On 2022-12-07 13:32:42 -0800, Andres Freund wrote:
> On 2022-12-07 18:14:01 -0300, Fabrízio de Royes Mello wrote:
> > Here we have some numbers about the Aleksander's patch:
>
> Hm. Were they taken in an assertion enabled build or such? Just testing the
> t10000 case on HEAD, I get 0:01.23 elapsed for an unpatched pg_dump in an
> optimized build. And that's on a machine with not all that fast cores.
Comparing the overhead on the server side.
CREATE OR REPLACE FUNCTION exec(v_sql text) RETURNS text LANGUAGE plpgsql AS $$BEGIN EXECUTE v_sql;RETURN
v_sql;END;$$;
Acquire locks in separate statements, three times:
SELECT exec(string_agg(format('LOCK t%s;', i), '')) FROM generate_series(1, 10000) AS i;
1267.909 ms
1116.008 ms
1108.383 ms
Acquire all locks in a single statement, three times:
SELECT exec('LOCK '||string_agg(format('t%s', i), ', ')) FROM generate_series(1, 10000) AS i;
1210.732 ms
1101.390 ms
1105.331 ms
So there's some performance difference that's independent of the avoided
roundtrips - but it's pretty small.
With an artificial delay of 100ms, the perf difference between the batching
patch and not using the batching patch is huge. Huge enough that I don't have
the patience to wait for the non-batched case to complete.
With batching pg_dump -s -h localhost t10000 took 0:16.23 elapsed, without I
cancelled after 603 tables had been locked, which took 2:06.43.
This made me try out using pipeline mode. Seems to work nicely from what I can
tell. The timings are a tad slower than the "manual" batch mode - I think
that's largely due to the extended protocol just being overcomplicated.
Greetings,
Andres Freund