On 2013-10-15 08:49:26 -0400, Robert Haas wrote: > On Mon, Oct 14, 2013 at 5:07 PM, Andres Freund <and...@2ndquadrant.com> wrote: > > So, see the attatched benchmark skript. I've always done using a disk > > bound and a memory bound (using eatmydata, preventing fsyncs) run. > > > > * unpatched run, wal_level = hot_standby, eatmydata > > * unpatched run, wal_level = hot_standby > > > > * patched run, wal_level = hot_standby, eatmydata > > * patched run, wal_level = hot_standby > > > > * patched run, wal_level = logical, eatmydata > > * patched run, wal_level = logical > > > > Based on those results, there's no difference above noise for > > wal_level=hot_standby, with or without the patch. With wal_level=logical > > there's a measurable increase in wal traffic (~12-17%), but no > > performance decrease above noise. > > > > From my POV that's ok, those are really crazy catalog workloads. > > Any increase in WAL traffic will translate into a performance hit once > the I/O channel becomes saturated, but I agree those numbers don't > sound terrible for that faily-brutal test case.
Well, the parallel workloads were fsync saturated although probably not throughput, that's why I added them. But yes, it's not the same as a throughput saturated IO channel. Probably the worst case real-world workload is one that uses lots and lots of ON COMMIT DROP temporary tables. > Actually, I was more concerned about the hit on non-catalog workloads. > pgbench isn't a > good test because the key column is so narrow; but suppose we have a > table like (a text, b integer, c text) where (a, c) is the primary key > and those strings are typically pretty long - say just short enough > that we can still index the column. It'd be worth testing both > workloads where the primary key doesn't change (so the only overhead > is figuring out that we need not log it) and those where it does > (where we're double-logging most of the tuple). I assume the latter > has to produce a significant hit to WAL volume, and I don't think > there's much we can do about that; but the former had better be nearly > free. Ah, ok. Then I misunderstood you. Is there a specific overhead you are "afraid" of in the pkey-doesn't-change scenario? The changed wal logging (buffer in a separate rdata entry) or the check whether the primary key has changed? The only way I have been able to measure differences in that scenario was to load a table with a low fillfactor and wide tuples, checkpoint, and then update lots of rows. On wal_level=logical that will result in full-page-images and tuple data being logged which can be noticeable if you have really large tuples, even if the pkey doesn't change. We could optimize that by not actually logging the tuple data in that case but just include the tid so we could extract things from the Bkp block ourselves. But that will complicate the code and doesn't yet seem warranted. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
