Monday, July 7, 2014

Greenplum's AO(AppendOnly) table's 128 concurrent DML limitation

In Greenplum, if there are more than 128(including 128) DML transactions on one AO(AppendOnly) table, some transactions may fail with "ERROR: could not find segment file to use for inserting into relation aotest (47157). (appendonlywriter.c:758)" .
This is/will be documented limitation of using Greenplum's Append Only table.

Reproduce Test

1. Create AO table

create table aotest (id int,name text) with (APPENDONLY='true');

2.Use my pressure testing tool HAOTPS to run 127 concurrent inserts:

insert into aotest select generate_series(1,1000000),generate_series(1,1000000)::text;
The sample testao.conf for HAOTPS is:
#########   Author: openkb.info
#########   Where : openkb.info
#########   When  : 2011-01
#########   This file is used to feed the HAOTPS java program
#########   beta2 version
#################################################################
#Connection String Part provide the connection string of target database
#################################################################
^^^Connection String Part^^^
#hostname can be the IP of the target host
$hostname = mdw
$port      = 1972
#sid is the database name we want to connect in the GP system
$sid       = openkb
#username and password are used to logon to the target database
$username  = openkb
$password  = openkb
#thread means how many client threads will be started
$thread    = 127
#query_cnt_per_thread means how many queries will be run in one single thread
$query_cnt_per_thread = 1
#################################################################
#SQL part are the SQLs you want to use to generate the load on target
#################################################################
^^^SQL Part^^^
#Totally how many SQLs will be supplied in the file
$sql_count=1
--------------------------
#the percent of executions among all sqls(please remember that the sum of sql_percent should be 100!!!)
$sql_percent=100
#SQL_TEXT, as you know:)
$sql_text=
insert into aotest select generate_series(1,1000000),generate_series(1,1000000)::text
#how many bind variables(please count for the "?" in above sql_text:))
$bind_variables_count=0
#################################################################
#Bind variables sample part will feed the bind var for above SQLs
#################################################################
^^^Bind Variables Sample Part^^^
--------------------------
During executing, all 127 sessions are running without being blocked.
xxx=# select count(*),waiting from pg_stat_activity where current_query~'insert' and current_query!~ 'pg_stat_activity'  group by waiting;
 count | waiting
-------+---------
   127 | f
(1 row)
xxx=# select count(*) from aotest ;
   count   
-----------
 127000000
(1 row)
 
$ java haotps testao.conf
======1.checking the config file testao.conf ......
======2.checking constraint of the config ......
======3.collecting all the bind variable data ......
======4.running 127 threads, each thread with 1 queries......
----------------Result--------------
Average response time(ms) =   59957.992
Average TPS               =   1.02

3. Run 128 concurrent inserts

During executing, some(12) sessions are blocked because of "ShareUpdateExclusiveLock".
xxx=# select count(*),waiting from pg_stat_activity where current_query~'insert' and current_query!~ 'pg_stat_activity'  group by waiting;
 count | waiting
-------+---------
    12 | t
    94 | f
(2 rows)
xxx=# select * from pg_locks where mode='ShareUpdateExclusiveLock';
 locktype | database | relation | page | tuple | transactionid | classid | objid | objsubid | transaction |  pid  |           mode           | granted | mppsessionid | mppiswrit
er | gp_segment_id
----------+----------+----------+------+-------+---------------+---------+-------+----------+-------------+-------+--------------------------+---------+--------------+----------
---+---------------
 relation |    16992 |    47157 |      |       |               |         |       |          |      406796 | 18311 | ShareUpdateExclusiveLock | f       |         1088 | t        
   |            -1
 relation |    16992 |    47157 |      |       |               |         |       |          |      406806 | 18338 | ShareUpdateExclusiveLock | f       |         1093 | t        
   |            -1
 relation |    16992 |    47157 |      |       |               |         |       |          |      406786 | 18291 | ShareUpdateExclusiveLock | f       |         1083 | t        
   |            -1
 relation |    16992 |    47157 |      |       |               |         |       |          |      406802 | 18324 | ShareUpdateExclusiveLock | t       |         1091 | t        
   |            -1
 relation |    16992 |    47157 |      |       |               |         |       |          |      406794 | 18307 | ShareUpdateExclusiveLock | f       |         1087 | t        
   |            -1
 relation |    16992 |    47157 |      |       |               |         |       |          |      406814 | 18368 | ShareUpdateExclusiveLock | f       |         1097 | t        
   |            -1
 relation |    16992 |    47157 |      |       |               |         |       |          |      406822 | 18393 | ShareUpdateExclusiveLock | f       |         1101 | t        
   |            -1
 relation |    16992 |    47157 |      |       |               |         |       |          |      406792 | 18303 | ShareUpdateExclusiveLock | f       |         1086 | t        
   |            -1
(8 rows)
One session failed with error message:
$ java haotps testao.conf
======1.checking the config file testao.conf ......
======2.checking constraint of the config ......
======3.collecting all the bind variable data ......
======4.running 128 threads, each thread with 1 queries......
org.postgresql.util.PSQLException: ERROR: could not find segment file to use for inserting into relation aotest (47157). (appendonlywriter.c:758)
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1608)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1343)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:195)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:471)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:370)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:255)
        at haotps.runsql(haotps.java:146)
        at haotps.run(haotps.java:72)
----------------Result--------------
Average response time(ms) =   58146.617
Average TPS               =   1.25
 
xxx=# select count(*) from aotest ;
   count   
-----------
 127000000
(1 row)
Master log shows:"could not find segment file to use for inserting into relation aotest (47157). (appendonlywriter.c:758)":
2012-04-23 10:53:18.741165 CST,"xxx","xxx",p19090,th2044097024,"172.28.8.250","55692",2012-04-23 10:53:18 CST,407038,con1209,cmd2,seg-1,,dx1226,x407038,sx1,"ERROR","XX000","could not find segment file to use for inserting into relation aotest (47157). (appendonlywriter.c:758)",,,,,,"
insert into aotest select generate_series(1,1000000),generate_series(1,1000000)::text",0,,"appendonlywriter.c",758,"Stack trace:
1    0xa59f45 postgres errstart + 0x595
2    0x57e4e6 postgres SetSegnoForWrite + 0x576
3    0x57ee42 postgres assignPerRelSegno + 0xa2
4    0x6e1584 postgres <symbol not found> + 0x6e1584
5    0x6e27f2 postgres ExecutorStart + 0x192
6    0x8fe4fb postgres <symbol not found> + 0x8fe4fb
7    0x900e16 postgres PortalRun + 0xa86
8    0x8f81b2 postgres PostgresMain + 0x21b2
9    0x863e11 postgres <symbol not found> + 0x863e11
10   0x86b4e5 postgres PostmasterMain + 0x1845
11   0x772bca postgres main + 0x4da
12   0x3b07c1d994 libc.so.6 __libc_start_main + 0xf4
13   0x47bf49 postgres <symbol not found> + 0x47bf49
"
Do not panic, this is "expected"&documented behavior.

No comments:

Post a Comment

Popular Posts