On Tue, May 10, 2016 at 5:09 AM, Amit Kapila <amit.kapil...@gmail.com>

> Although, I don't think it is a very good idea to take any performance
> data with WIP patch, still I couldn't resist myself from doing so and below
> are the performance numbers.  To get the performance data, I have dropped
> the primary key constraint on pgbench_accounts and created a hash index on
> aid column as below.
> alter table pgbench_accounts drop constraint pgbench_accounts_pkey;
> create index pgbench_accounts_pkey on pgbench_accounts using hash(aid);

To be rigorously fair, you should probably replace the btree primary key
with a non-unique btree index and use that in the btree comparison case.  I
don't know how much difference that would make, probably none at all for a
read-only case.

> Below data is for read-only pgbench test and is a median of 3 5-min runs.
> The performance tests are executed on a power-8 m/c.

With pgbench -S where everything fits in shared_buffers and the number of
cores I have at my disposal, I am mostly benchmarking interprocess
communication between pgbench and the backend.  I am impressed that you can
detect any difference at all.

For this type of thing, I like to create a server side function for use in

create or replace function pgbench_query(scale integer,size integer)
RETURNS integer AS $$
DECLARE sum integer default 0;
amount integer;
account_id integer;
   SELECT abalance into strict amount FROM pgbench_accounts
      WHERE aid = account_id;
   sum := sum + amount;
return sum;
END $$ LANGUAGE plpgsql;

And then run using a command like this:

pgbench -f <(echo 'select pgbench_query(40,1000)')  -c$j -j$j -T 300

Where the first argument ('40', here) must be manually set to the same
value as the scale-factor.

With 8 cores and 8 clients, the values I get are, for btree, hash-head,
hash-concurrent, hash-concurrent-cache, respectively:


(each transaction involves 1000 select statements)

So I do see that the concurrency patch is quite an improvement.  The cache
patch does not produce a further improvement, which was somewhat surprising
to me (I thought that that patch would really shine in a read-write
workload, but I expected at least improvement in read only)

I've run this was 128MB shared_buffers and scale factor 40.  Not everything
fits in shared_buffers, but quite easily fits in RAM, and there is no
meaningful IO caused by the benchmark.



Reply via email to