You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@trafodion.apache.org by "Selvaganesan Govindarajan (JIRA)" <ji...@apache.org> on 2015/10/20 23:23:27 UTC
[jira] [Commented] (TRAFODION-1546) upsert into table with indexes
performs slower than insert
[ https://issues.apache.org/jira/browse/TRAFODION-1546?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14965799#comment-14965799 ]
Selvaganesan Govindarajan commented on TRAFODION-1546:
------------------------------------------------------
The plan as shown by the RMS for the insert into a table with 2 indexes
LC RC Id PaId ExId Frag TDB Name DOP Dispatches Oper CPU Time Est. Records Used Act. Records Used Details
7 . 8 . 8 0 EX_ROOT 1 1 3 0 0 13607
3 6 7 8 7 0 EX_ONLJ 1 6 20 20 0
4 5 6 7 6 0 EX_UNION 1 6 53 20 0
. . 5 6 5 0 EX_TRAF_VSBB_UPSERT 1 17 1,459 1 10 TRAFODION.SELVA.T12I2|0|902
. . 4 6 4 0 EX_TRAF_VSBB_UPSERT 1 16 3,408 1 10 TRAFODION.SELVA.T12I1|0|682
1 2 3 7 3 0 EX_ONLJ 1 3 23 10 10
. . 2 3 2 0 EX_TRAF_INSERT 1 1 8,558 1 10 TRAFODION.SELVA.T12|0|1090
. . 1 3 1 0 EX_TUPLE_LEAF_TDB 1 1 83 10 10
Upsert plan is
LC RC Id PaId ExId Frag TDB Name DOP Dispatches Oper CPU Time Est. Records Used Act. Records Used Details
11 . 12 . 12 0 EX_ROOT 1 2 14 0 0 31143
1 10 11 12 11 0 EX_TUPLE_FLOW 1 15 37 40 0
2 9 10 11 10 0 EX_ONLJ 1 27 75 40 0
5 8 9 10 9 0 EX_UNION 1 18 29 40 0
6 7 8 9 8 0 EX_UNION 1 47 69 20 0
. . 7 8 7 0 EX_TRAF_VSBB_UPSERT 1 141 2,915 1 10 TRAFODION.SELVA.T12I2|0|920
. . 6 8 6 0 EX_TRAF_VSBB_DELETE 1 49 1,562 1 0 TRAFODION.SELVA.T12I2|0|20
3 4 5 9 5 0 EX_UNION 1 47 91 20 0
. . 4 5 4 0 EX_TRAF_VSBB_UPSERT 1 141 3,112 1 10 TRAFODION.SELVA.T12I1|0|700
. . 3 5 3 0 EX_TRAF_VSBB_DELETE 1 49 5,741 1 0 TRAFODION.SELVA.T12I1|0|20
. . 2 10 2 0 EX_TRAF_MERGE 1 8 17,378 1 10 TRAFODION.SELVA.T12|0|1630
. . 1 11 1 0 EX_TUPLE_LEAF_TDB 1 4 120 10 10
Both the plans use VSBB_UPSERT to insert into the indexes. But, RMS reveals that the vsbb operation is not kicking in based on the number of hbase IOs issued.
Insert pertable stats
Table Name
Records Accessed Records Used HBase/Hive HBase/Hive HBase/Hive IO HBase/Hive IO
Estimated/Actual Estimated/Actual IOs IO MBytes Sum Time Max Time
TRAFODION.SELVA.T12
0 1
0 10 10 0 23,069 23,069
TRAFODION.SELVA.T12I1
0 1
0 10 1 0 16,871 16,871
TRAFODION.SELVA.T12I2
0 1
0 10 1 0 33,544 33,544
Upsert pertable stats
Table Name
Records Accessed Records Used HBase/Hive HBase/Hive HBase/Hive IO HBase/Hive IO
Estimated/Actual Estimated/Actual IOs IO MBytes Sum Time Max Time
TRAFODION.SELVA.T12
0 1
0 10 20 0 43,200 43,200
TRAFODION.SELVA.T12I1
0 1
0 0 10 0 21,500 21,500
TRAFODION.SELVA.T12I1
0 1
0 10 10 0 17,550 17,550
TRAFODION.SELVA.T12I2
0 1
0 0 10 0 1,968 1,968
TRAFODION.SELVA.T12I2
0 1
0 10 10 0 24,098 24,098
In case of insert there was one IO to RS insert the 10 rows into the index, while the upsert took 10 IOs to RS to insert the 10 rows to the index. This needs to be corrected to get better performance with upsert.
> upsert into table with indexes performs slower than insert
> ----------------------------------------------------------
>
> Key: TRAFODION-1546
> URL: https://issues.apache.org/jira/browse/TRAFODION-1546
> Project: Apache Trafodion
> Issue Type: Bug
> Reporter: Selvaganesan Govindarajan
> Original Estimate: 1m
> Remaining Estimate: 1m
>
> The plan for upsert and insert are different, but both plans have vsbb operations enabled for index maintenance. But, it is observed that vsbb feature kicks in for insert while the upsert command inserts one row at a time in the index. Hence upsert command performs slower than the insert command.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)