[MCOL-1381] cpimport for batch insert (INSERT INTO SELECT FROM) does not work Created: 2018-05-03  Updated: 2019-07-10  Resolved: 2019-07-10

Status: Closed
Project: MariaDB ColumnStore
Component/s: MariaDB Server
Affects Version/s: 1.1.3
Fix Version/s: Icebox

Type: Bug Priority: Minor
Reporter: Mihaly Hazag Assignee: Unassigned
Resolution: Not a Bug Votes: 0
Labels: None
Environment:

CentOS 7.4
SoftwareVersion = 1.1.3
SoftwareRelease = 1


 Description   

However the infinidb_use_import_for_batchinsert is ON the INSERT INTO SELECT FROM statement inserts the rows into the table individually with 10 -
12 sec/row speed.
Based o the ColumnStore Batch Insert Mode description I expected that it would work at same speed as the cpimport

MariaDB [data_warehouse]> show variables like '%infinidb_use_import_for_batchinsert%';
 
+-------------------------------------+-------+
 
| Variable_name                       | Value |
 
+-------------------------------------+-------+
 
| infinidb_use_import_for_batchinsert | ON    |
 
+-------------------------------------+-------+
 
1 row in set (0.01 sec)

SQL statement (changed removing sensitive information):

insert into target_db.target_table select * from test_view where d_date = '2018.04.29';

tail -f /var/log/mariadb/columnstore/debug.log (changed removing the sensitive information)

May  3 12:43:57 COLUMNSTORE_UM dmlpackageproc[5861]: 57.110912 |16|9458|0| D 21 CAL0001: Start SQL statement:  insert into test_table select col1, col2, ...coln  from infinidb_vtable.$vtable_16; |target_db|
 
May  3 12:44:10 COLUMNSTORE_UM dmlpackageproc[5861]: 10.184041 |16|9458|0| D 21 CAL0001: End SQL statement
 
May  3 12:44:10 COLUMNSTORE_UM dmlpackageproc[5861]: 10.184187 |0|0|0| W 21 CAL0001: IDB-2025: Data truncated for column 'col1'
 
May  3 12:44:10 COLUMNSTORE_UM dmlpackageproc[5861]: 10.253820 |16|9458|0| D 21 CAL0001: Start SQL statement:  COMMIT
 
May  3 12:44:11 COLUMNSTORE_UM dmlpackageproc[5861]: 11.453470 |16|9458|0| D 21 CAL0001: End SQL statement
 
May  3 12:44:11 COLUMNSTORE_UM dmlpackageproc[5861]: 11.519823 |16|9459|0| D 21 CAL0001: Start SQL statement:  insert into test_table select col1, col2, ...coln  from infinidb_vtable.$vtable_16; |target_db|
 
May  3 12:44:24 COLUMNSTORE_UM dmlpackageproc[5861]: 24.335447 |16|9459|0| D 21 CAL0001: End SQL statement
 
May  3 12:44:24 COLUMNSTORE_UM dmlpackageproc[5861]: 24.444194 |16|9459|0| D 21 CAL0001: Start SQL statement:  COMMIT
 
May  3 12:44:25 COLUMNSTORE_UM dmlpackageproc[5861]: 25.834371 |16|9459|0| D 21 CAL0001: End SQL statement
 
May  3 12:44:25 COLUMNSTORE_UM dmlpackageproc[5861]: 25.947407 |16|9460|0| D 21 CAL0001: Start SQL statement:  insert into test_table select col1, col2, ...coln  from infinidb_vtable.$vtable_16; |target_db|
 
May  3 12:44:37 COLUMNSTORE_UM dmlpackageproc[5861]: 37.863515 |16|9460|0| D 21 CAL0001: End SQL statement
 
May  3 12:44:37 COLUMNSTORE_UM dmlpackageproc[5861]: 37.966467 |16|9460|0| D 21 CAL0001: Start SQL statement:  COMMIT
 
May  3 12:44:39 COLUMNSTORE_UM dmlpackageproc[5861]: 39.198694 |16|9460|0| D 21 CAL0001: End SQL statement
 
May  3 12:44:39 COLUMNSTORE_UM dmlpackageproc[5861]: 39.313278 |16|9461|0| D 21 CAL0001: Start SQL statement:  insert into test_table select col1, col2, ...coln  from infinidb_vtable.$vtable_16; |target_db|



 Comments   
Comment by Andrew Hutchings (Inactive) [ 2018-05-03 ]

I suspect the problem is that you are executing the insert...select as part of a transaction. If you have autocommit turned off or start a transaction before the insert...select we cannot use the cpimport method because we need to record undo log entries just in case you rollback the transaction. This requires disk syncs for every entry in every column which is much slower.

Comment by Mihaly Hazag [ 2018-05-03 ]

I did not use the INSERT INTO ... SELECT in transaction and the autocommit was set ON. The "COMMIT" commands you can see in the debug.log were sent by the system.

MariaDB [(none)]> show variables like '%autocommit%';
 
+---------------+-------+
 
| Variable_name | Value |
 
+---------------+-------+
 
| autocommit    | ON    |
 
+---------------+-------+
 
1 row in set (0.00 sec)

Generated at Thu Feb 08 02:28:19 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.