Re: Add primary key/unique constraint using prefix columns of an index

On Wed, May 23, 2012 at 11:05 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Wed, May 23, 2012 at 2:00 PM, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:
>>
>> I'm running some tests where I mix the work load of pgbench by doing
>> "TPC-B (sort of)" transaction mixed in with a variable number of
>> SELECT-only transactions, at a ratio varying between 1:0 to 1:10.
>>
>> It is often said that the default pgbench is an unrealistically
>> write-heavy workload.  So mixing in some SELECT-only is probably only
>> going to improve its real-world alignment.  In fact I wondering if it
>> would make sense to add a feature to pgbench to make such admixture
>> easy to do, rather than the current pain of creating multiple sql
>> files, specifying a bunch of -f switches in various ratios, and
>> remembering to always specify the correct -s flag.
>>
>> From preliminary test it looks like an index on (aid, abalance) wins
>> at all ratios from 1:1 to 1:10, and at 1:0 it is mostly a toss up.  I
>> still want to do a few overnight runs to see how the decay in the
>> visibility map, and perhaps autovacuum kicking in or failing to kick
>> in, effect things.
>
> Interesting!  If that holds up under more careful testing, it would be
> a great result.

I'm using -s2000 with 2GB of RAM (meaning there is little opportunity
for write-combining on pgbench_accounts table or the leaf pages of its
index, as they generally are written to disk before a second update is
expected).

The comparisons are between the normal PK index, which gets HOT
updates, and the (aid, abalance) index which can't be HOT updated but
can benefit from IOS.

The IOS performance is much more variable than the HOT performance,
measured as TPS over 30 second windows. The oscillations for IOS seem
to be associated with the 5 minute checkpoint interval. Turning off
FPW doesn't smooth things out any, against my expectations.

On a workload of only TPC-B, HOT just barely wins on average (81.9 to
77.8 TPS, averaged over 50 windows of 30 seconds) but clearly wins on
if you look for the best worst-case or similar (75.2 to 48.3 TPS for
the 10th percentile performance on the same data)

If you mix in one -S transaction for each TPC-B transaction, than the
IOS start to win on average and at the 10th percentile, but still
loses on the absolute worst 30 second window.

At 2 -S per 1 TPC-B, then the IOS wins on all measures. However, at
finer windows than 30 TPS it very well might still lose the
worst-case--I never recorded individual latencies.

So it is probably kind of a toss-up, depending on how much weight you
put on worst-case latency. But once you get up to 5 or 10 -S per
TPC-B, then the IOS becomes more clearly a win.

Over longer time frames, the IOS performance degrades as the vm
degrades. I never ran it long enough for autovacuum to kick in, as
that would take many days. I also never ran it long enough for it to
degrade enough so the IOS stopped winning at higher -S ratios, it was
just winning by less.

I'm not really sure what to make of this, except it would probably be
good to have a way to set vm bits without having to do a vacuum.

Cheers,

Jeff