Re: spinlocks on powerpc - Mailing list pgsql-hackers

Robert Haas <robertmhaas@gmail.com> writes:
> I'm unconvinced by these numbers.  There is a measurable change but it
> is pretty small.  The Itanium changes resulted in an enormous gain at
> higher concurrency levels.

Yeah, that was my problem with it also: I couldn't measure enough gain
to convince me it was a real effect, and not an artifact of the specific
machine being tested.  It occurred to me though that we already know that
pgbench itself is a bottleneck in tests like this, and there's an easy
way to take it out of the picture: move the selects into a plpgsql
function that iterates multiple times per client query.  The attached
testing script reduces the client interaction costs by a thousandfold
compared to plain "pgbench -S", and also takes parse/plan time out of
the loop, so that it becomes easier to see the effects of contention.
With this script, I still see a loss of 1% or so from adding the
unlocked test in TAS_SPIN at moderate contention levels, but there's a
very clear jump when the machine is saturated.  So this convinces me
that Manabu-san's results are reproducible, and I've committed the
TAS_SPIN addition.

git head as of this morning, on 8-core IBM 8406-71Y:

pgbench -c 1 -j 1 -f bench.script -T 300 bench        tps = 50.142878 (including connections establishing)
pgbench -c 2 -j 1 -f bench.script -T 300 bench        tps = 97.179234 (including connections establishing)
pgbench -c 8 -j 4 -f bench.script -T 300 bench        tps = 341.731982 (including connections establishing)
pgbench -c 16 -j 8 -f bench.script -T 300 bench        tps = 402.114111 (including connections establishing)
pgbench -c 32 -j 16 -f bench.script -T 300 bench    tps = 371.338156 (including connections establishing)
pgbench -c 64 -j 32 -f bench.script -T 300 bench    tps = 359.785087 (including connections establishing)
pgbench -c 96 -j 48 -f bench.script -T 300 bench    tps = 363.879950 (including connections establishing)
pgbench -c 128 -j 64 -f bench.script -T 300 bench    tps = 376.794934 (including connections establishing)

after re-adding TAS_SPIN macro:

pgbench -c 1 -j 1 -f bench.script -T 300 bench        tps = 50.182676 (including connections establishing)
pgbench -c 2 -j 1 -f bench.script -T 300 bench        tps = 96.751910 (including connections establishing)
pgbench -c 8 -j 4 -f bench.script -T 300 bench        tps = 327.108510 (including connections establishing)
pgbench -c 16 -j 8 -f bench.script -T 300 bench        tps = 395.425611 (including connections establishing)
pgbench -c 32 -j 16 -f bench.script -T 300 bench    tps = 444.291852 (including connections establishing)
pgbench -c 64 -j 32 -f bench.script -T 300 bench    tps = 486.151168 (including connections establishing)
pgbench -c 96 -j 48 -f bench.script -T 300 bench    tps = 496.379981 (including connections establishing)
pgbench -c 128 -j 64 -f bench.script -T 300 bench    tps = 494.058124 (including connections establishing)


> For Itanium, I was able to find some fairly official-looking
> documentation that said "this is how you should do it".  It would be
> nice to find something similar for PPC64, instead of testing every
> machine and reinventing the wheel ourselves.

You are aware that our spinlock code is pretty much verbatim from the
PPC ISA spec, no?  The issue here is that the "official documentation"
has been a moving target over the decades the ISA has been in existence.

            regards, tom lane

#! /bin/sh

psql bench <<"EOF"
create or replace function bench(scale int, reps int) returns void
language plpgsql
as $$
declare
  naccounts int := 100000 * scale;
  v_aid int;
  v_abalance int;
begin
  for i in 1 .. reps loop
    v_aid := round(random() * naccounts + 1);
    SELECT abalance INTO v_abalance FROM pgbench_accounts WHERE aid = v_aid;
  end loop;
end;
$$;
EOF

cat >bench.script <<EOF
select bench(:scale, 1000);
EOF

# warm the caches a bit
pgbench -c 1 -j 1 -f bench.script -T 30 bench >/dev/null

echo pgbench -c 1 -j 1 -f bench.script -T 300 bench
pgbench -c 1 -j 1 -f bench.script -T 300 bench | grep including
echo pgbench -c 2 -j 1 -f bench.script -T 300 bench
pgbench -c 2 -j 1 -f bench.script -T 300 bench | grep including
echo pgbench -c 8 -j 4 -f bench.script -T 300 bench
pgbench -c 8 -j 4 -f bench.script -T 300 bench | grep including
echo pgbench -c 16 -j 8 -f bench.script -T 300 bench
pgbench -c 16 -j 8 -f bench.script -T 300 bench | grep including
echo pgbench -c 32 -j 16 -f bench.script -T 300 bench
pgbench -c 32 -j 16 -f bench.script -T 300 bench | grep including
echo pgbench -c 64 -j 32 -f bench.script -T 300 bench
pgbench -c 64 -j 32 -f bench.script -T 300 bench | grep including
echo pgbench -c 96 -j 48 -f bench.script -T 300 bench
pgbench -c 96 -j 48 -f bench.script -T 300 bench | grep including
echo pgbench -c 128 -j 64 -f bench.script -T 300 bench
pgbench -c 128 -j 64 -f bench.script -T 300 bench | grep including