Re: logical changeset generation v4 - Heikki's thoughts about the patch state

On 24.01.2013 00:30, Andres Freund wrote:
> Also, while the apply side surely isn't benchmarkable without any being
> submitted, the changeset generation can very well be benchmarked.
>
> A very, very adhoc benchmark:
> -c max_wal_senders=10
> -c max_logical_slots=10 --disabled for anything but logical
> -c wal_level=logical --hot_standby for anything but logical
> -c checkpoint_segments=100
> -c log_checkpoints=on
> -c shared_buffers=512MB
> -c autovacuum=on
> -c log_min_messages=notice
> -c log_line_prefix='[%p %t] '
> -c wal_keep_segments=100
> -c fsync=off
> -c synchronous_commit=off
>
> pgbench -p 5440 -h /tmp -n -M prepared -c 16 -j 16 -T 30
>
> pgbench upstream:
> tps: 22275.941409
> space overhead: 0%
> pgbench logical-submitted
> tps: 16274.603046
> space overhead: 2.1%
> pgbench logical-HEAD (will submit updated version tomorrow or so):
> tps: 20853.341551
> space overhead: 2.3%
> pgbench single plpgsql trigger (INSERT INTO log(data) VALUES(NEW::text))
> tps: 14101.349535
> space overhead: 369%
>
> Note that in the single trigger case nobody consumed the queue while the
> logical version streamed the changes out and stored them to disk.

That makes the space overhead comparison completely worthless, no? I
would expect the trigger-based approach to generate roughly 100% more
WAL, not close to 400%. As long as the queue is drained constantly,
there should be no big difference in the disk space used, except for the
WAL.

> Adding a default NOW() or similar to the tables immediately makes
> logical decoding faster by a factor of about 3 in comparison to the
> above trivial trigger.

Hmm, is that because of the conversion to text? I believe slony also
converts all the values to text in the trigger, because that's simple
and flexible, but if we're trying to compare the performance of logical
changeset generation vs. trigger-based replication in general, we should
choose the most efficient trigger-based scheme to compare with. That
means, don't convert to text. And write the trigger in C.

- Heikki