[
https://issues.apache.org/jira/browse/TRAFODION-2216?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15486356#comment-15486356
]
zhangliang commented on TRAFODION-2216:
---------------------------------------
[~suresh_subbiah] Yes, the test run on a small VM, and today I tried sevral
times on the same VM, this didn't occur again, even if I copied a large table
with 50 million rows. Maybe it is related with the netstat?
Also, with loadcmd=UL, it became much more fast.
[centos@gyvm-17 odb_test]$ ./odb64luo -u zale:trafodion -p zale:traf123 -d
orac:traf -cp
src=BIG_TABLE2:tgt=TRAFODION.ODB_COPY.big_table2:rows=5000:truncate:parallel=5:loadcmd=UL
-v
Connected to Oracle
odb [2016-09-13 11:57:04]: starting ODBC connection(s)... 0 >1 >2 3 >4 >5 6 >7
>8 9 >10 >11 12 >13 >14
Connected to Trafodion
odb: Now truncating target table (DELETE FROM TRAFODION.ODB_COPY.big_table2)
[1.0.0]--- 0 row(s) deleted in 0.269s (prep 0.235s, exec 0.034s, fetch
0.000s/0.000s)
[0] odb [Ocopy(10210)] - SOURCE statement: SELECT * FROM BIG_TABLE2 WHERE
MOD(ORA_HASH(ROWID), 5) = 0
[6] odb [Ocopy(10210)] - SOURCE statement: SELECT * FROM BIG_TABLE2 WHERE
MOD(ORA_HASH(ROWID), 5) = 2
[3] odb [Ocopy(10210)] - SOURCE statement: SELECT * FROM BIG_TABLE2 WHERE
MOD(ORA_HASH(ROWID), 5) = 1
[9] odb [Ocopy(10210)] - SOURCE statement: SELECT * FROM BIG_TABLE2 WHERE
MOD(ORA_HASH(ROWID), 5) = 3
[12] odb [Ocopy(10210)] - SOURCE statement: SELECT * FROM BIG_TABLE2 WHERE
MOD(ORA_HASH(ROWID), 5) = 4
[3] odb [Ocopy(10552)] - TARGET statement: UPSERT USING LOAD INTO
TRAFODION.ODB_COPY.big_table2 VALUES (?,?)
[6] odb [Ocopy(10552)] - TARGET statement: UPSERT USING LOAD INTO
TRAFODION.ODB_COPY.big_table2 VALUES (?,?)
[0] odb [Ocopy(10552)] - TARGET statement: UPSERT USING LOAD INTO
TRAFODION.ODB_COPY.big_table2 VALUES (?,?)
[7] 5,000 records copied [commit]
[9] odb [Ocopy(10552)] - TARGET statement: UPSERT USING LOAD INTO
TRAFODION.ODB_COPY.big_table2 VALUES (?,?)
[4] 5,000 records copied [commit]
[12] odb [Ocopy(10552)] - TARGET statement: UPSERT USING LOAD INTO
TRAFODION.ODB_COPY.big_table2 VALUES (?,?)
[5] 5,000 records copied [commit]
[7] 10,000 records copied [commit]
[8] 5,000 records copied [commit]
[13] 5,000 records copied [commit]
[1] 5,000 records copied [commit]
[7] 15,000 records copied [commit]
[14] 5,000 records copied [commit]
[5] 10,000 records copied [commit]
...
[14] 4,820,000 records copied [commit]
odb: thread 8 closing connection...
odb: thread 7 is ending...
[13] 5,180,473 records copied [commit]
odb: thread 14 closing connection...
odb: thread 8 is ending...
odb: thread 6 closing connection...
odb: thread 14 is ending...
odb: thread 6 is ending...
odb: thread 13 closing connection...
[12] odb version 1.1.0 Copy statistics:
[0] Source: BIG_TABLE2
[0] Target: TRAFODION.ODB_COPY.big_table2
[0] Total number of columns: 2
[0] ODBC row size: 45 B (data) + 16 B (len ind)
[0] Rowset size: 5,000
[0] Rowset buffer size: 297.85 KiB
[0] Pre-copy time: 20.278 s (00:00:20.278)
[0] Copy time: 2,560.764 s (00:42:40.764)
[0] Total records copied: 50,000,000 (19.525 krec/s)
[0] Copy throughput (ODBC): 0.838 MiB/s (2.946 GiB/h)
[0] Total/Wait cycles: 1,999/327
[0>1] 5,232,564 records copied in 2,553.823
(00:42:33.823 s)
[0>2] 4,760,000 records copied in 2,552.445
(00:42:32.445 s)
[3] Total/Wait cycles: 2,001/305
[3>4] 5,169,364 records copied in 2,545.078
(00:42:25.078 s)
[3>5] 4,835,000 records copied in 2,545.078
(00:42:25.078 s)
[6] Total/Wait cycles: 2,001/330
[6>7] 5,335,000 records copied in 2,563.730
(00:42:43.730 s)
[6>8] 4,665,318 records copied in 2,563.975
(00:42:43.975 s)
[9] Total/Wait cycles: 2,001/329
[9>10] 5,200,000 records copied in 2,555.452
(00:42:35.452 s)
[9>11] 4,802,281 records copied in 2,556.331
(00:42:36.331 s)
[12] Total/Wait cycles: 2,001/298
[12>13] 5,180,473 records copied in 2,560.764
(00:42:40.764 s)
[12>14] 4,820,000 records copied in 2,560.747
(00:42:40.747 s)
odb: thread 13 is ending...
odb: thread 12 closing connection...
odb: thread 12 is ending...
odb [2016-09-13 12:40:05]: exiting. Session Elapsed time 2,581.057 seconds
(00:43:01.057)
> sometimes odb cannot copy all rows when the table is large
> ----------------------------------------------------------
>
> Key: TRAFODION-2216
> URL: https://issues.apache.org/jira/browse/TRAFODION-2216
> Project: Apache Trafodion
> Issue Type: Bug
> Components: db-utility-odb
> Reporter: zhangliang
>
> When I copy a table with 100000 rows from oracle to trafodion with odb,
> sometimes only part of the table is copied successfully.
> The proper log like this:
> [centos@gyvm-17 odb_test]$ ./odb64luo -u zale:trafodion -p zale:traf123 -d
> orac:traf -cp
> src=big_table3:tgt=TRAFODION.ODB_COPY.big_table3_esg:rows=5000:truncate:parallel=3
> -v
> Connected to Oracle
> odb [2016-09-12 11:08:45]: starting ODBC connection(s)... 0 >1 >2 3 >4 >5 6
> >7 >8
> Connected to Trafodion
> odb: Now truncating target table (DELETE FROM
> TRAFODION.ODB_COPY.big_table3_esg)
> [1.0.0]--- 0 row(s) deleted in 0.790s (prep 0.705s, exec 0.085s, fetch
> 0.000s/0.000s)
> [0] odb [Ocopy(10210)] - SOURCE statement: SELECT * FROM BIG_TABLE3 WHERE
> MOD(ORA_HASH(ROWID), 3) = 0
> [3] odb [Ocopy(10210)] - SOURCE statement: SELECT * FROM BIG_TABLE3 WHERE
> MOD(ORA_HASH(ROWID), 3) = 1
> [6] odb [Ocopy(10210)] - SOURCE statement: SELECT * FROM BIG_TABLE3 WHERE
> MOD(ORA_HASH(ROWID), 3) = 2
> [0] odb [Ocopy(10552)] - TARGET statement: INSERT INTO
> TRAFODION.ODB_COPY.big_table3_esg VALUES (?,?)
> [3] odb [Ocopy(10552)] - TARGET statement: INSERT INTO
> TRAFODION.ODB_COPY.big_table3_esg VALUES (?,?)
> [1] 5,000 records copied [commit]
> [6] odb [Ocopy(10552)] - TARGET statement: INSERT INTO
> TRAFODION.ODB_COPY.big_table3_esg VALUES (?,?)
> [4] 5,000 records copied [commit]
> [7] 5,000 records copied [commit]
> [1] 10,000 records copied [commit]
> [4] 10,000 records copied [commit]
> [8] 5,000 records copied [commit]
> [2] 5,000 records copied [commit]
> [7] 10,000 records copied [commit]
> [4] 15,000 records copied [commit]
> [5] 5,000 records copied [commit]
> [8] 10,000 records copied [commit]
> [2] 10,000 records copied [commit]
> [1] 15,000 records copied [commit]
> [4] 20,000 records copied [commit]
> [7] 15,000 records copied [commit]
> [2] 15,000 records copied [commit]
> [5] 10,000 records copied [commit]
> [8] 15,000 records copied [commit]
> [1] 18,330 records copied [commit]
> [4] 23,230 records copied [commit]
> [7] 18,440 records copied [commit]
> odb: thread 2 closing connection...
> odb: thread 2 is ending...
> odb: thread 1 closing connection...
> odb: thread 0 closing connection...
> odb: thread 0 is ending...
> odb: thread 1 is ending...
> odb: thread 8 closing connection...
> odb: thread 8 is ending...
> odb: thread 5 closing connection...
> odb: thread 7 closing connection...
> odb: thread 4 closing connection...
> [3] odb version 1.1.0 Copy statistics:
> [0] Source: BIG_TABLE3
> [0] Target: TRAFODION.ODB_COPY.big_table3_esg
> [0] Total number of columns: 2
> [0] ODBC row size: 45 B (data) + 16 B (len ind)
> [0] Rowset size: 5,000
> [0] Rowset buffer size: 297.85 KiB
> [0] Pre-copy time: 17.100 s (00:00:17.100)
> [0] Copy time: 103.320 s (00:01:43.320)
> [0] Total records copied: 100,000 (0.968 krec/s)
> [0] Copy throughput (ODBC): 0.042 MiB/s (0.146 GiB/h)
> [0] Total/Wait cycles: 7/1
> [0>1] 18,330 records copied in 104.553 (00:01:44.553
> s)
> [0>2] 15,000 records copied in 104.548 (00:01:44.548
> s)
> [3] Total/Wait cycles: 7/1
> [3>4] 23,230 records copied in 103.320 (00:01:43.320
> s)
> [3>5] 10,000 records copied in 103.320 (00:01:43.320
> s)
> [6] Total/Wait cycles: 7/0
> [6>7] 18,440 records copied in 95.039 (00:01:35.039 s)
> [6>8] 15,000 records copied in 95.036 (00:01:35.036 s)
> odb: thread 5 is ending...
> odb: thread 6 closing connection...
> odb: thread 6 is ending...
> odb: thread 4 is ending...
> odb: thread 7 is ending...
> odb: thread 3 closing connection...
> odb: thread 3 is ending...
> odb [2016-09-12 11:10:45]: exiting. Session Elapsed time 120.437 seconds
> (00:02:00.437)
> The failed like this (just copied 83440 of 100000):
> [centos@gyvm-17 odb_test]$ ./odb64luo -u zale:trafodion -p zale:traf123 -d
> orac:traf -cp
> src=big_table3:tgt=TRAFODION.ODB_COPY.big_table3:rows=5000:truncate:parallel=3
> -v
> Connected to Oracle
> odb [2016-09-12 10:54:27]: starting ODBC connection(s)... 0 >1 >2 3 >4 >5 6
> >7 >8
> Connected to Trafodion
> odb: Now truncating target table (DELETE FROM TRAFODION.ODB_COPY.big_table3)
> [4.0.0]--- 0 row(s) deleted in 0.959s (prep 0.855s, exec 0.104s, fetch
> 0.000s/0.000s)
> [3] odb [Ocopy(10210)] - SOURCE statement: SELECT * FROM BIG_TABLE3 WHERE
> MOD(ORA_HASH(ROWID), 3) = 1
> [6] odb [Ocopy(10210)] - SOURCE statement: SELECT * FROM BIG_TABLE3 WHERE
> MOD(ORA_HASH(ROWID), 3) = 2
> [0] odb [Ocopy(10210)] - SOURCE statement: SELECT * FROM BIG_TABLE3 WHERE
> MOD(ORA_HASH(ROWID), 3) = 0
> [6] odb [Ocopy(10552)] - TARGET statement: INSERT INTO
> TRAFODION.ODB_COPY.big_table3 VALUES (?,?)
> [0] odb [Ocopy(10552)] - TARGET statement: INSERT INTO
> TRAFODION.ODB_COPY.big_table3 VALUES (?,?)
> [7] 5,000 records copied [commit]
> [3] odb [Ocopy(10552)] - TARGET statement: INSERT INTO
> TRAFODION.ODB_COPY.big_table3 VALUES (?,?)
> [1] 5,000 records copied [commit]
> [7] 10,000 records copied [commit]
> [8] 5,000 records copied [commit]
> [4] 5,000 records copied [commit]
> [5] 5,000 records copied [commit]
> [1] 10,000 records copied [commit]
> [7] 15,000 records copied [commit]
> [4] 10,000 records copied [commit]
> [8] 10,000 records copied [commit]
> [5] 10,000 records copied [commit]
> [1] 15,000 records copied [commit]
> [2] 5,000 records copied [commit]
> [7] 20,000 records copied [commit]
> odb: thread 7 closing connection...
> odb: thread 7 is ending...
> [1] 20,000 records copied [commit]
> [8] 13,440 records copied [commit]
> odb: thread 8 closing connection...
> odb: thread 8 is ending...
> [4] 15,000 records copied [commit]
> [5] odb [Oloadbuff(9420)] - Error (State: 25000, Native -8606)
> [Trafodion ODBC Driver][Trafodion Database] SQL ERROR:*** ERROR[8606]
> Transaction subsystem DTM returned error 82 on a commit transaction.
> [2016-09-12 10:56:47]
> odb: thread 6 closing connection...
> odb: thread 6 is ending...
> [1] odb [Oloadbuff(9420)] - Error (State: 08S02, Native 101)
> [Trafodion ODBC Driver] TRANSPORT LAYER ERROR. Platform: UNIX, Transport:
> TCPIP, Api: SQLEXECUTE2, Error type: DRIVER, Process:
> TCP:192.168.1.21/23408:ODBC, Operation: DO_WRITE_READ, function: SEND, error:
> 32, error_detail: 0, error text:Broken pipe.
> [1] 25,000 records copied [commit]
> [5] 10,000 records copied [commit]
> odb: thread 5 closing connection...
> odb: thread 5 is ending...
> [2] odb [Oloadbuff(9420)] - Error (State: 08S01, Native 98)
> [Trafodion ODBC Driver] COMMUNICATION LINK FAILURE. THE SERVER TIMED OUT OR
> DISAPPEARED. Platform: UNIX, Transport: TCPIP, Api: SQLEXECUTE2, Error type:
> DRIVER, Process: TCP:192.168.1.21/23406:ODBC, Operation: DO_WRITE_READ,
> function: RECV, error: 104, error_detail: 0, error text:recv() returned zero
> bytes.
> [2] 5,000 records copied [commit]
> [4] odb [Oloadbuff(9420)] - Error (State: 08S01, Native 98)
> [Trafodion ODBC Driver] COMMUNICATION LINK FAILURE. THE SERVER TIMED OUT OR
> DISAPPEARED. Platform: UNIX, Transport: TCPIP, Api: SQLEXECUTE2, Error type:
> DRIVER, Process: TCP:192.168.1.21/23405:ODBC, Operation: DO_WRITE_READ,
> function: RECV, error: 104, error_detail: 0, error text:recv() returned zero
> bytes.
> [4] odb [Oloadbuff(9465)] - Unable to get row counts from driver
> [4] odb(9466) [2016-09-12 10:56:58] - [unixODBC][Driver Manager]Function
> sequence error (State: HY010 Native Err: 0)
> [4] odb(9466) [2016-09-12 10:56:58] - [Trafodion ODBC Driver] COMMUNICATION
> LINK FAILURE. THE SERVER TIMED OUT OR DISAPPEARED. Platform: UNIX, Transport:
> TCPIP, Api: SQLEXECUTE2, Error type: DRIVER, Process:
> TCP:192.168.1.21/23405:ODBC, Operation: DO_WRITE_READ, function: RECV, error:
> 104, error_detail: 0, error text:recv() returned zero bytes. (State: 08S01
> Native Err: 98)
> [4] 15,000 records copied [commit]
> odb: thread 4 closing connection...
> odb: thread 4 is ending...
> odb: thread 1 closing connection...
> odb: thread 2 closing connection...
> odb: thread 1 is ending...
> odb: thread 0 closing connection...
> odb: thread 0 is ending...
> [3] odb version 1.1.0 Copy statistics:
> [0] Source: BIG_TABLE3
> [0] Target: TRAFODION.ODB_COPY.big_table3
> [0] Total number of columns: 2
> [0] ODBC row size: 45 B (data) + 16 B (len ind)
> [0] Rowset size: 5,000
> [0] Rowset buffer size: 297.85 KiB
> [0] Pre-copy time: 35.118 s (00:00:35.118)
> [0] Copy time: 116.107 s (00:01:56.107)
> [0] Total records copied: 88,440 (0.762 krec/s)
> [0] Copy throughput (ODBC): 0.033 MiB/s (0.115 GiB/h)
> [0] Total/Wait cycles: 7/1
> [0>1] 25,000 records copied in 127.207 (00:02:07.207
> s)
> [0>2] 5,000 records copied in 127.207 (00:02:07.207 s)
> [3] Total/Wait cycles: 7/2
> [3>4] 15,000 records copied in 116.105 (00:01:56.105
> s)
> [3>5] 10,000 records copied in 116.100 (00:01:56.100
> s)
> [6] Total/Wait cycles: 7/0
> [6>7] 20,000 records copied in 86.977 (00:01:26.977 s)
> [6>8] 13,440 records copied in 101.714 (00:01:41.714
> s)
> odb: thread 2 is ending...
> odb: thread 3 closing connection...
> odb: thread 3 is ending...
> odb [2016-09-12 10:56:58]: exiting. Session Elapsed time 151.228 seconds
> (00:02:31.228)
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
