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