Re: Relation extension scalability

On Tue, Mar 8, 2016 at 8:34 PM, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:

> > >> Hmm. Can we drive this off of the heavyweight lock manager's idea of
> > >> how big the relation extension lock wait queue is, instead of adding
> > >> more stuff to PGPROC?
> > >
> > > One idea to make it work without adding additional stuff in PGPROC is
> that
> > > after acquiring relation extension lock, check if there is any
> available
> > > block in fsm, if it founds any block, then release the lock and
> proceed,
> > > else extend the relation by one block and then check lock's wait queue
> size
> > > or number of lock requests (nRequested) and extend the relation
> further in
> > > proportion to wait queue size and then release the lock and proceed.
> Here,
> > > I think we can check for wait queue size even before extending the
> relation
> > > by one block.
>

I have come up with this patch..

If this approach looks fine then I will prepare final patch (more comments,
indentation, and improve some code) and do some long run testing (current
results are 5 mins run).

Idea is same what Robert and Amit suggested up thread.

/* First we try the lock and if get just extend one block, this will give
two benefit ,
1. Single thread performance will not impact by checking lock waiters and
all
2. If we check the waiter in else part it will give time for more waiter to
get collected and will get better estimation of contention*/

TryRelExtLock ()
{
extend one block
}
else
{
RelextLock()
if (recheck the FSM if somebody have added blocks for me)
-- don't extend any block just reuse
else
--we have to extend blocks
-- get the waiter = lock->nRequested
--add extra block to FSM extraBlock = waiter*20;
}

Result looks like this
---------------------------

Test1(COPY)
-----./psql -d postgres -c "COPY (select g.i::text FROM generate_series(1,
10000) g(i)) TO '/tmp/copybinary' WITH BINARY";echo COPY data from
'/tmp/copybinary' WITH BINARY; > copy_script
./pgbench -f copy_script -T 300 -c$ -j$ postgres

Shared Buffer:8GB max_wal_size:10GB Storage:Magnetic Disk WAL:SSD
-----------------------------------------------------------------------------------------------
Client Base multi_extend by 20 page lock_waiter patch(waiter*20)
1 105 157 148
2 217 255 252
4 210 494 442
8 166 702 645
16 145 563 773
32 124 480 957

Note: @1 thread there should not be any improvement, so many be run to run
variance.

Test2(INSERT)
--------./psql -d postgres -c "create table test_data(a int, b text)"./psql
-d postgres -c "insert into test_data
values(generate_series(1,1000),repeat('x', 1024))"./psql -d postgres -c
"create table data (a int, b text)
echo "insert into data select * from test_data;" >> insert_script

shared_buffers=512GB max_wal_size=20GB checkpoint_timeout=10min
./pgbench -c $ -j $ -f insert_script -T 300 postgres

Client Base Multi-extend by 1000 lock_waiter patch(waiter*20)
1 117 118 117

2 111 140 132

4 51 190 134

8 43 254 148

16 40 243 206
32 - - 264

* (waiter*20)-> First process got the lock will find the lock waiters and
add waiter*20 extra blocks.

In next run I will run beyond 32 also, as we can see even at 32 client its
increasing.. so its clear when it see more contentions, adapting to
contention and adding more blocks..

--
Regards,
Dilip Kumar
EnterpriseDB: http://www.enterprisedb.com