Re: Relation extension scalability

 

I have measured the performance with insert to see the behavior when it don't use strategy. I have tested multiple option, small tuple, big tuple, data fits in shared buffer and doesn't fit in shared buffer.

Observation:
------------------
 Apart from this test I have also used some tool which can take a many stack traces with some delay.
1. I have observed with base code (when data don't fits in shared buffer) almost all the stack traces are waiting on "LockRelationForExtension" and
many on "FlushBuffer" also (Flushing the dirty buffer).

Total Stack Captured: 204, FlushBuffer: 13, LockRelationForExtension: 187

 (This test run with 8 thread (shared buf 512MB) and after every 5 second stack is captured.)
 
2. If I change shared buf 48GB then Obviously FlushBuffer disappeared but still LockRelationForExtension remains in very high number.

3.Performance of base code in both the cases when Data fits in shared buffers or doesn't fits in shared buffer remain very low and non-scaling(we can see that in below results).

Test--1 (big record insert and Data fits in shared Buffer)
------------------------------------------------------------
setup
--------
./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) with(extend_by_blocks=$variable)" {create table data (a int) for base code}
echo "insert into data select * from test_data;" >> copy_script

test:
-----
 shared_buffers=48GB  max_wal_size=20GB  checkpoint_timeout=10min
./pgbench -c $ -j $ -f copy_script -T 120 postgres


   
client    base    extend_by_block=50    extend_by_block=1000
1    113                115                            118
4    50                  220                            216
8    43                  202                            302


Test--2 (big record insert and Data doesn't fits in shared Buffer)
------------------------------------------------------------------
setup:
-------
./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) with(extend_by_blocks=1000)"
echo "insert into data select * from test_data;" >> copy_script

test:
------
 shared_buffers=512MB  max_wal_size=20GB  checkpoint_timeout=10min
./pgbench -c $ -j $ -f copy_script -T 120 postgres


client    base    extend_by_block=1000
1         125        125
4         49          236
8         41          294
16        39         279


Test--3 (small record insert and Data fits in shared Buffer)
------------------------------------------------------------------
setup:
--------
./psql -d postgres -c "create table test_data(a int)"
./psql -d postgres -c "insert into test_data values(generate_series(1,10000))"
./psql -d postgres -c "create table data (a int) with(extend_by_blocks=20)"
 echo "insert into data select * from test_data;" >> copy_script

test:
-----
 shared_buffers=48GB -c max_wal_size=20GB -c checkpoint_timeout=10min
./pgbench -c $ -j $ -f copy_script -T 120 postgres


client    base    Patch-extend_by_block=20
1          137          143
2          269          250
4          377          443
8         170           690
16        145          745

*All test done with Data on MD and Wal on SSD

Note: Last patch have max limit of extend_by_block=100 so for taking performance with  extend_by_block=1000 i localy changed it.
I will send the modified patch once we finalize on which approach to proceed with.