Upsert into a table with index hangs and hogs system resource
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
Trafodion |
Won't Fix
|
Critical
|
Joanie Cooper |
Bug Description
As shown here, 2 tables: t1 and t2 are identical, except that t2 has an index i2 created for it. 2 identical ‘upsert using load’ queries x1 and x2 are then prepared for t1 and t2 respectively. The query upserts 1000000 rows. Plan x2 includes 2 nested_joins and a trafodion_insert compared to plan x1’s trafodion_load. On either a Hortonworks machine or a Cloudera machine, x1 can generally complete in around 30 seconds on our 4-node systems, but x2 always ‘hangs’. In the latest try, it hanged for 4 hours without coming back. At that point, one of the hbase region servers has used 9.9g virtual memory, 6.6g resident memory, and the CUP usage hovered around 600%-700%. Somewhere along the way, it also started to have trouble with monitors with messages like the followings shown in $SQ_HOME/
02/02/2015-
02/02/2015-
02/03/2015-
02/03/2015-
This is the kind of Trafodion hardening issue regarding overall usability/stability for post-1.0 releases, so the bug report is filed as a critical one. The problem is fairly reproducible on our systems using the v1.0.0 rc3 build. It requires the QA global table g_wisc32.abase, which has 32000000 rows.
-------
Here is the entire script to reproduce it (it requires the QA global table g_wisc32.abase):
log mytest.log clear;
drop schema mytest9 cascade;
create schema mytest9;
set schema mytest9;
showddl trafodion.
showstats for table trafodion.
create table t1
(
UNIQUE1 INT NO DEFAULT NOT NULL NOT DROPPABLE
, UNIQUE2 INT NO DEFAULT NOT NULL NOT DROPPABLE
, TWO INT NO DEFAULT NOT NULL NOT DROPPABLE
, FOUR INT NO DEFAULT NOT NULL NOT DROPPABLE
, TEN INT NO DEFAULT NOT NULL NOT DROPPABLE
, TWENTY INT NO DEFAULT NOT NULL NOT DROPPABLE
, ONEPERCENT INT NO DEFAULT NOT NULL NOT DROPPABLE
, TENPERCENT INT NO DEFAULT NOT NULL NOT DROPPABLE
, TWENTYPERCENT INT NO DEFAULT NOT NULL NOT DROPPABLE
, FIFTYPERCENT INT NO DEFAULT NOT NULL NOT DROPPABLE
, UNIQUE3 INT NO DEFAULT NOT NULL NOT DROPPABLE
, EVENONEPERCENT INT NO DEFAULT NOT NULL NOT DROPPABLE
, ODDONEPERCENT INT NO DEFAULT NOT NULL NOT DROPPABLE
, STRINGU1 CHAR(52) CHARACTER SET ISO88591 COLLATE
DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
, STRINGU2 CHAR(52) CHARACTER SET ISO88591 COLLATE
DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
, STRING4 CHAR(52) CHARACTER SET ISO88591 COLLATE
DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
, PRIMARY KEY (UNIQUE2 ASC)
)
;
create table t2
(
UNIQUE1 INT NO DEFAULT NOT NULL NOT DROPPABLE
, UNIQUE2 INT NO DEFAULT NOT NULL NOT DROPPABLE
, TWO INT NO DEFAULT NOT NULL NOT DROPPABLE
, FOUR INT NO DEFAULT NOT NULL NOT DROPPABLE
, TEN INT NO DEFAULT NOT NULL NOT DROPPABLE
, TWENTY INT NO DEFAULT NOT NULL NOT DROPPABLE
, ONEPERCENT INT NO DEFAULT NOT NULL NOT DROPPABLE
, TENPERCENT INT NO DEFAULT NOT NULL NOT DROPPABLE
, TWENTYPERCENT INT NO DEFAULT NOT NULL NOT DROPPABLE
, FIFTYPERCENT INT NO DEFAULT NOT NULL NOT DROPPABLE
, UNIQUE3 INT NO DEFAULT NOT NULL NOT DROPPABLE
, EVENONEPERCENT INT NO DEFAULT NOT NULL NOT DROPPABLE
, ODDONEPERCENT INT NO DEFAULT NOT NULL NOT DROPPABLE
, STRINGU1 CHAR(52) CHARACTER SET ISO88591 COLLATE
DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
, STRINGU2 CHAR(52) CHARACTER SET ISO88591 COLLATE
DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
, STRING4 CHAR(52) CHARACTER SET ISO88591 COLLATE
DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
, PRIMARY KEY (UNIQUE2 ASC)
)
;
create index i2 on t2 (two, four, unique1, unique3);
showddl t1;
showddl t2;
select count(*) from trafodion.
select count(*) from trafodion.
prepare x1 from upsert using load into t1 select * from trafodion.
prepare x2 from upsert using load into t2 select * from trafodion.
explain options 'f' x1;
explain options 'f' x2;
explain x1;
explain x2;
sh date;
execute x1;
sh date;
drop table t1 cascade;
sh date;
execute x2;
sh date;
drop table t2 cascade;
sh date;
drop schema mytest9;
-------
Here is the execution output up to the point when ‘execute x2’ hanged for 4 hours:
>>drop schema mytest9 cascade;
*** ERROR[1003] Schema TRAFODION.MYTEST9 does not exist.
--- SQL operation failed with errors.
>>create schema mytest9;
--- SQL operation complete.
>>set schema mytest9;
--- SQL operation complete.
>>showddl trafodion.
CREATE TABLE TRAFODION.
(
UNIQUE1 INT NO DEFAULT NOT NULL NOT DROPPABLE
, UNIQUE2 INT NO DEFAULT NOT NULL NOT DROPPABLE
, TWO INT NO DEFAULT NOT NULL NOT DROPPABLE
, FOUR INT NO DEFAULT NOT NULL NOT DROPPABLE
, TEN INT NO DEFAULT NOT NULL NOT DROPPABLE
, TWENTY INT NO DEFAULT NOT NULL NOT DROPPABLE
, ONEPERCENT INT NO DEFAULT NOT NULL NOT DROPPABLE
, TENPERCENT INT NO DEFAULT NOT NULL NOT DROPPABLE
, TWENTYPERCENT INT NO DEFAULT NOT NULL NOT DROPPABLE
, FIFTYPERCENT INT NO DEFAULT NOT NULL NOT DROPPABLE
, UNIQUE3 INT NO DEFAULT NOT NULL NOT DROPPABLE
, EVENONEPERCENT INT NO DEFAULT NOT NULL NOT DROPPABLE
, ODDONEPERCENT INT NO DEFAULT NOT NULL NOT DROPPABLE
, STRINGU1 CHAR(52) CHARACTER SET ISO88591 COLLATE
DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
, STRINGU2 CHAR(52) CHARACTER SET ISO88591 COLLATE
DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
, STRING4 CHAR(52) CHARACTER SET ISO88591 COLLATE
DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
, PRIMARY KEY (UNIQUE2 ASC)
)
SALT USING 8 PARTITIONS
;
CREATE UNIQUE INDEX IXA4 ON TRAFODION.
(
UNIQUE3 ASC
, TWENTYPERCENT ASC
, FIFTYPERCENT ASC
)
;
--- SQL operation complete.
>>showstats for table trafodion.
Histogram data for Table TRAFODION.
Table ID: 533733601026556210
Hist ID # Ints Rowcount UEC Colname(s)
========== ====== =========== =========== =======
2094748496 38 32334424 32334424 UNIQUE1
2094748491 31 32334424 32334424 UNIQUE2
2094748486 2 32334424 2 TWO
2094748481 4 32334424 4 FOUR
2094748476 10 32334424 10 TEN
2094748471 20 32334424 20 TWENTY
2094748466 77 32334424 100 ONEPERCENT
2094748461 10 32334424 10 TENPERCENT
2094748456 5 32334424 5 TWENTYPERCENT
2094748451 2 32334424 2 FIFTYPERCENT
2094748446 38 32334424 32334424 UNIQUE3
2094748441 77 32334424 100 EVENONEPERCENT
2094748436 77 32334424 100 ODDONEPERCENT
2094748431 62 32334424 32334424 STRINGU1
2094748426 62 32334424 32334424 STRINGU2
2094748421 4 32334424 4 STRING4
2094748416 8 32334424 8 "_SALT_"
2094748501 1 32334424 32334424 "_SALT_", UNIQUE2
--- SQL operation complete.
>>
>>create table t1
+> (
+> UNIQUE1 INT NO DEFAULT NOT NULL NOT DROPPABLE
+> , UNIQUE2 INT NO DEFAULT NOT NULL NOT DROPPABLE
+> , TWO INT NO DEFAULT NOT NULL NOT DROPPABLE
+> , FOUR INT NO DEFAULT NOT NULL NOT DROPPABLE
+> , TEN INT NO DEFAULT NOT NULL NOT DROPPABLE
+> , TWENTY INT NO DEFAULT NOT NULL NOT DROPPABLE
+> , ONEPERCENT INT NO DEFAULT NOT NULL NOT DROPPABLE
+> , TENPERCENT INT NO DEFAULT NOT NULL NOT DROPPABLE
+> , TWENTYPERCENT INT NO DEFAULT NOT NULL NOT DROPPABLE
+> , FIFTYPERCENT INT NO DEFAULT NOT NULL NOT DROPPABLE
+> , UNIQUE3 INT NO DEFAULT NOT NULL NOT DROPPABLE
+> , EVENONEPERCENT INT NO DEFAULT NOT NULL NOT DROPPABLE
+> , ODDONEPERCENT INT NO DEFAULT NOT NULL NOT DROPPABLE
+> , STRINGU1 CHAR(52) CHARACTER SET ISO88591 COLLATE
+> DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
+> , STRINGU2 CHAR(52) CHARACTER SET ISO88591 COLLATE
+> DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
+> , STRING4 CHAR(52) CHARACTER SET ISO88591 COLLATE
+> DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
+> , PRIMARY KEY (UNIQUE2 ASC)
+> )
+>;
--- SQL operation complete.
>>
>>create table t2
+> (
+> UNIQUE1 INT NO DEFAULT NOT NULL NOT DROPPABLE
+> , UNIQUE2 INT NO DEFAULT NOT NULL NOT DROPPABLE
+> , TWO INT NO DEFAULT NOT NULL NOT DROPPABLE
+> , FOUR INT NO DEFAULT NOT NULL NOT DROPPABLE
+> , TEN INT NO DEFAULT NOT NULL NOT DROPPABLE
+> , TWENTY INT NO DEFAULT NOT NULL NOT DROPPABLE
+> , ONEPERCENT INT NO DEFAULT NOT NULL NOT DROPPABLE
+> , TENPERCENT INT NO DEFAULT NOT NULL NOT DROPPABLE
+> , TWENTYPERCENT INT NO DEFAULT NOT NULL NOT DROPPABLE
+> , FIFTYPERCENT INT NO DEFAULT NOT NULL NOT DROPPABLE
+> , UNIQUE3 INT NO DEFAULT NOT NULL NOT DROPPABLE
+> , EVENONEPERCENT INT NO DEFAULT NOT NULL NOT DROPPABLE
+> , ODDONEPERCENT INT NO DEFAULT NOT NULL NOT DROPPABLE
+> , STRINGU1 CHAR(52) CHARACTER SET ISO88591 COLLATE
+> DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
+> , STRINGU2 CHAR(52) CHARACTER SET ISO88591 COLLATE
+> DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
+> , STRING4 CHAR(52) CHARACTER SET ISO88591 COLLATE
+> DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
+> , PRIMARY KEY (UNIQUE2 ASC)
+> )
+>;
--- SQL operation complete.
>>
>>create index i2 on t2 (two, four, unique1, unique3);
--- SQL operation complete.
>>
>>showddl t1;
CREATE TABLE TRAFODION.
(
UNIQUE1 INT NO DEFAULT NOT NULL NOT DROPPABLE
, UNIQUE2 INT NO DEFAULT NOT NULL NOT DROPPABLE
, TWO INT NO DEFAULT NOT NULL NOT DROPPABLE
, FOUR INT NO DEFAULT NOT NULL NOT DROPPABLE
, TEN INT NO DEFAULT NOT NULL NOT DROPPABLE
, TWENTY INT NO DEFAULT NOT NULL NOT DROPPABLE
, ONEPERCENT INT NO DEFAULT NOT NULL NOT DROPPABLE
, TENPERCENT INT NO DEFAULT NOT NULL NOT DROPPABLE
, TWENTYPERCENT INT NO DEFAULT NOT NULL NOT DROPPABLE
, FIFTYPERCENT INT NO DEFAULT NOT NULL NOT DROPPABLE
, UNIQUE3 INT NO DEFAULT NOT NULL NOT DROPPABLE
, EVENONEPERCENT INT NO DEFAULT NOT NULL NOT DROPPABLE
, ODDONEPERCENT INT NO DEFAULT NOT NULL NOT DROPPABLE
, STRINGU1 CHAR(52) CHARACTER SET ISO88591 COLLATE
DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
, STRINGU2 CHAR(52) CHARACTER SET ISO88591 COLLATE
DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
, STRING4 CHAR(52) CHARACTER SET ISO88591 COLLATE
DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
, PRIMARY KEY (UNIQUE2 ASC)
)
;
--- SQL operation complete.
>>showddl t2;
CREATE TABLE TRAFODION.
(
UNIQUE1 INT NO DEFAULT NOT NULL NOT DROPPABLE
, UNIQUE2 INT NO DEFAULT NOT NULL NOT DROPPABLE
, TWO INT NO DEFAULT NOT NULL NOT DROPPABLE
, FOUR INT NO DEFAULT NOT NULL NOT DROPPABLE
, TEN INT NO DEFAULT NOT NULL NOT DROPPABLE
, TWENTY INT NO DEFAULT NOT NULL NOT DROPPABLE
, ONEPERCENT INT NO DEFAULT NOT NULL NOT DROPPABLE
, TENPERCENT INT NO DEFAULT NOT NULL NOT DROPPABLE
, TWENTYPERCENT INT NO DEFAULT NOT NULL NOT DROPPABLE
, FIFTYPERCENT INT NO DEFAULT NOT NULL NOT DROPPABLE
, UNIQUE3 INT NO DEFAULT NOT NULL NOT DROPPABLE
, EVENONEPERCENT INT NO DEFAULT NOT NULL NOT DROPPABLE
, ODDONEPERCENT INT NO DEFAULT NOT NULL NOT DROPPABLE
, STRINGU1 CHAR(52) CHARACTER SET ISO88591 COLLATE
DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
, STRINGU2 CHAR(52) CHARACTER SET ISO88591 COLLATE
DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
, STRING4 CHAR(52) CHARACTER SET ISO88591 COLLATE
DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
, PRIMARY KEY (UNIQUE2 ASC)
)
;
CREATE INDEX I2 ON TRAFODION.
(
TWO ASC
, FOUR ASC
, UNIQUE1 ASC
, UNIQUE3 ASC
)
;
--- SQL operation complete.
>>
>>select count(*) from trafodion.
(EXPR)
-------
--- 1 row(s) selected.
>>select count(*) from trafodion.
(EXPR)
-------
--- 1 row(s) selected.
>>
>>prepare x1 from upsert using load into t1 select * from trafodion.
se where unique2 < 1000000;
--- SQL command prepared.
>>prepare x2 from upsert using load into t2 select * from trafodion.
se where unique2 < 1000000;
--- SQL command prepared.
>>explain options 'f' x1;
LC RC OP OPERATOR OPT DESCRIPTION CARD
---- ---- ---- -------
5 . 6 root 1.00E+006
4 . 5 esp_exchange 1:8(hash2) 1.00E+006
2 3 4 tuple_flow 1.00E+006
. . 3 trafodion_load T1 1.00E+000
1 . 2 sort 1.00E+006
. . 1 trafodion_scan ABASE 1.00E+006
--- SQL operation complete.
>>explain options 'f' x2;
LC RC OP OPERATOR OPT DESCRIPTION CARD
---- ---- ---- -------
7 . 8 root x 1.00E+006
6 . 7 esp_exchange 1:8(hash2) 1.00E+006
4 5 6 nested_join 1.00E+006
. . 5 trafodion_insert I2 1.00E+000
2 3 4 nested_join 1.00E+006
. . 3 trafodion_ T2 1.00E+000
1 . 2 sort 1.00E+006
. . 1 trafodion_scan ABASE 1.00E+006
--- SQL operation complete.
>>explain x1;
-------
MODULE_NAME .............. DYNAMICALLY COMPILED
STATEMENT_NAME ........... X1
PLAN_ID .................. 212289671902829625
ROWS_OUT ......... 1,007,717
EST_TOTAL_COST ........... 1.52
STATEMENT ................ upsert using load into t1 select * from
-------
ROOT =======
REQUESTS_IN .............. 1
ROWS_OUT ......... 1,007,717
EST_OPER_COST ............ 0
EST_TOTAL_COST ........... 1.52
DESCRIPTION
max_card_est ........... 1.00772e+06
fragment_id ............ 0
parent_frag ............ (none)
fragment_type .......... master
statement_index ........ 0
affinity_value ......... 0
est_memory_
max_max_cardinal 1,007,717
total_
esp_2_node_map ......... (\NSK:-
xn_access_mode ......... read_only
xn_autoabort_
auto_query_retry ....... enabled
plan_version ....... 2,600
embedded_arkcmp ........ used
IS_SQLCI ............... ON
LDAP_USERNAME
SCHEMA ................. MYTEST9
ObjectUIDs ............. 533733601026556210, 28205037036521192
ESP_EXCHANGE =======
REQUESTS_IN .............. 1
ROWS_OUT ......... 1,007,717
EST_OPER_COST ............ 0.01
EST_TOTAL_COST ........... 1.52
DESCRIPTION
max_card_est ........... 1.00772e+06
fragment_id ............ 2
parent_frag ............ 0
fragment_type .......... esp
buffer_size ........ 5,000
record_length .......... 0
parent_processes ....... 1
child_processes ........ 8
child_
seamonster_query ....... no
seamonster_
TUPLE_FLOW =======
REQUESTS_IN .............. 1
ROWS_OUT ......... 1,007,717
EST_OPER_COST ............ 0.01
EST_TOTAL_COST ........... 1.52
DESCRIPTION
max_card_est ........... 1.00772e+06
fragment_id ............ 2
parent_frag ............ 0
fragment_type .......... esp
join_type .............. inner
join_method ............ in-order nested
parallel_
TRAFODION_LOAD =======
TABLE_NAME ............... TRAFODION.
REQUESTS_IN .............. 1
ROWS_OUT ................. 1
EST_OPER_COST ............ 0
EST_TOTAL_COST ........... 0
DESCRIPTION
max_card_est ........... 1.00772e+06
fragment_id ............ 2
parent_frag ............ 0
fragment_type .......... esp
iud_type ............... trafodion_load TRAFODION.
new_rec_expr ........... (UNIQUE1 assign TRAFODION.
SORT =======
REQUESTS_IN .............. 1
ROWS_OUT ......... 1,007,717
EST_OPER_COST ............ 0.06
EST_TOTAL_COST ........... 1.51
DESCRIPTION
memory_
max_card_est ........... 1.00772e+06
fragment_id ............ 2
parent_frag ............ 0
fragment_type .......... esp
est_memory_
sort_type .............. full
sort_key ............... TRAFODION.
TRAFODION_SCAN =======
TABLE_NAME ............... ABASE
REQUESTS_IN .............. 1
ROWS_OUT ......... 1,007,717
EST_OPER_COST ............ 1.45
EST_TOTAL_COST ........... 1.45
DESCRIPTION
max_card_est ........... 1.00772e+06
fragment_id ............ 2
parent_frag ............ 0
fragment_type .......... esp
scan_type .............. subset scan limited by mdam of table
object_type ............ Trafodion
cache_size ........ 10,000
probes ................. 1
rows_accessed .......... 1.00772e+06
key_columns ............ _SALT_, UNIQUE2
mdam_disjunct .......... (UNIQUE2 < 1000000) and (_SALT_ >=
part_
--- SQL operation complete.
>>explain x2;
-------
MODULE_NAME .............. DYNAMICALLY COMPILED
STATEMENT_NAME ........... X2
PLAN_ID .................. 212289671903453036
ROWS_OUT ......... 1,007,717
EST_TOTAL_COST ........... 1.53
STATEMENT ................ upsert using load into t2 select * from
-------
ROOT =======
REQUESTS_IN .............. 1
ROWS_OUT ......... 1,007,717
EST_OPER_COST ............ 0
EST_TOTAL_COST ........... 1.53
DESCRIPTION
max_card_est ........... 1.00772e+06
fragment_id ............ 0
parent_frag ............ (none)
fragment_type .......... master
statement_index ........ 0
affinity_value ......... 0
est_memory_
max_max_cardinal 1,007,717
total_
esp_2_node_map ......... (\NSK:-
upd_action_
xn_access_mode ......... read_only
xn_autoabort_
auto_query_retry ....... enabled
plan_version ....... 2,600
embedded_arkcmp ........ used
IS_SQLCI ............... ON
LDAP_USERNAME
SCHEMA ................. MYTEST9
ObjectUIDs ............. 533733601026556210, 28205037036521336,
ESP_EXCHANGE =======
REQUESTS_IN .............. 1
ROWS_OUT ......... 1,007,717
EST_OPER_COST ............ 0.01
EST_TOTAL_COST ........... 1.53
DESCRIPTION
max_card_est ........... 1.00772e+06
fragment_id ............ 2
parent_frag ............ 0
fragment_type .......... esp
buffer_size ........ 5,000
record_length .......... 0
parent_processes ....... 1
child_processes ........ 8
child_
seamonster_query ....... no
seamonster_
NESTED_JOIN =======
REQUESTS_IN .............. 1
ROWS_OUT ......... 1,007,717
EST_OPER_COST ............ 0.01
EST_TOTAL_COST ........... 1.53
DESCRIPTION
max_card_est ........... 1.00772e+06
fragment_id ............ 2
parent_frag ............ 0
fragment_type .......... esp
join_type .............. inner
join_method ............ nested
parallel_
TRAFODION_INSERT =======
TABLE_NAME ............... TRAFODION.
REQUESTS_IN .............. 1
ROWS_OUT ................. 1
EST_OPER_COST ............ 0
EST_TOTAL_COST ........... 0
DESCRIPTION
max_card_est ........... 1.00772e+06
fragment_id ............ 2
parent_frag ............ 0
fragment_type .......... esp
iud_type ............... index_trafodion
new_rec_expr ........... ("TWO@" assign TRAFODION.
NESTED_JOIN =======
REQUESTS_IN .............. 1
ROWS_OUT ......... 1,007,717
EST_OPER_COST ............ 0.01
EST_TOTAL_COST ........... 1.52
DESCRIPTION
max_card_est ........... 1.00772e+06
fragment_id ............ 2
parent_frag ............ 0
fragment_type .......... esp
join_type .............. inner
join_method ............ in-order nested
parallel_
TRAFODION_ =======
TABLE_NAME ............... TRAFODION.
REQUESTS_IN .............. 1
ROWS_OUT ................. 1
EST_OPER_COST ............ 0
EST_TOTAL_COST ........... 0
DESCRIPTION
max_card_est ........... 1.00772e+06
fragment_id ............ 2
parent_frag ............ 0
fragment_type .......... esp
iud_type ............... trafodion_ TRAFODION.
new_rec_expr ........... (UNIQUE1 assign TRAFODION.
SORT =======
REQUESTS_IN .............. 1
ROWS_OUT ......... 1,007,717
EST_OPER_COST ............ 0.06
EST_TOTAL_COST ........... 1.51
DESCRIPTION
memory_
max_card_est ........... 1.00772e+06
fragment_id ............ 2
parent_frag ............ 0
fragment_type .......... esp
est_memory_
sort_type .............. full
sort_key ............... TRAFODION.
TRAFODION_SCAN =======
TABLE_NAME ............... ABASE
REQUESTS_IN .............. 1
ROWS_OUT ......... 1,007,717
EST_OPER_COST ............ 1.45
EST_TOTAL_COST ........... 1.45
DESCRIPTION
max_card_est ........... 1.00772e+06
fragment_id ............ 2
parent_frag ............ 0
fragment_type .......... esp
scan_type .............. subset scan limited by mdam of table
object_type ............ Trafodion
cache_size ........ 10,000
probes ................. 1
rows_accessed .......... 1.00772e+06
key_columns ............ _SALT_, UNIQUE2
mdam_disjunct .......... (UNIQUE2 < 1000000) and (_SALT_ >=
part_
--- SQL operation complete.
>>
>>sh date;
>>execute x1;
--- 1000000 row(s) inserted.
>>sh date;
>>drop table t1 cascade;
--- SQL operation complete.
>>sh date;
>>execute x2;
Changed in trafodion: | |
assignee: | nobody → Mike Hanlon (mike-hanlon) |
Changed in trafodion: | |
importance: | High → Critical |
assignee: | Mike Hanlon (mike-hanlon) → Atanu Mishra (atanu-mishra) |
Changed in trafodion: | |
assignee: | Atanu Mishra (atanu-mishra) → Joanie Cooper (joanie-cooper) |
Changed in trafodion: | |
status: | New → In Progress |
Changed in trafodion: | |
status: | In Progress → Fix Committed |
Insert into table with indexes are transactional inserts even if load syntax is used.
They are running into some kind of dtm cache/memory limit due to
the large number of rows being inserted within a transaction.
It is never recommended to insert so many rows within a transaction.
This issue has been seen before and logs can provide more info
on what is happening but there isn't going to be any quick remedy to this.
It ultimately gets stuck on underlying system, region splits, data flush,
memory overflow and other system related issues.
To insert so many rows, one should first insert them
into the table without the index which will use load to insert.
Once that insert is done, one should create the index on it which
will then internally use load to populate the index.
Or one can disable that index, load the table and enable index after
that (disable index support is not yet in). 'upsert using load' can
also do that internally. There may be an unexternalized option to
do that. Khaled can comment on that.
When we have support for incremental loads, that may alleviate loading
a table with incremental data in non-transactional mode.
We can probably give a warning or an error is one tries to do large
inserts on table with index.