feature: Improve insert into select performance

[adofsauron]

Insert into... The performance of Select is ideally close to that of the Load Data operation

[adofsauron]

Test with the lineitem table

mysql> select count(1) from lineitem;
+----------+
| count(1) |
+----------+
|  6001215 |
+----------+
1 row in set (0.00 sec)

mysql> select *  into outfile  '/tmp/lineitem.txt' from lineitem;
Query OK, 6001215 rows affected (1 min 20.73 sec)

[root@localhost tmp]# wc -l lineitem.txt 
6001215 lineitem.txt

mysql> load data local infile '/tmp/lineitem.txt' into table lineitem_cp;
Query OK, 6001215 rows affected (2 min 12.48 sec)
Records: 6001215  Deleted: 0  Skipped: 0  Warnings: 0

mysql> insert into lineitem_cp select * from lineitem;
Query OK, 6001215 rows affected (2 min 28.44 sec)
Records: 6001215  Duplicates: 0  Warnings: 0

mysql> insert into lineitem_cp select * from lineitem where l_orderkey > 10;
Query OK, 6001190 rows affected (3 min 5.40 sec)
Records: 6001190  Duplicates: 0  Warnings: 0

[adofsauron]

Compare this to the innodb engine in mysql8

[root@localhost tmp]# mysqld --version
/usr/libexec/mysqld  Ver 8.0.26 for Linux on x86_64 (Source distribution)

mysql> insert into lineitem_cp select * from lineitem;
Query OK, 6001215 rows affected (41.94 sec)
Records: 6001215  Duplicates: 0  Warnings: 0

mysql> insert into lineitem_cp select * from lineitem where l_orderkey > 10;
Query OK, 6001190 rows affected (46.37 sec)
Records: 6001190  Duplicates: 0  Warnings: 0

[adofsauron]

Execution stack

(gdb) bt
#0  Tianmu::handler::ha_tianmu::write_row (this=0x7f29c8d570c0, buf=0x7f29c90f9aa0 " ") at /root/work/stonedb-dev-20230103/storage/tianmu/handler/ha_tianmu.cpp:455
#1  0x0000000001d53b05 in handler::ha_write_row (this=0x7f29c8d570c0, buf=0x7f29c90f9aa0 " ") at /root/work/stonedb-dev-20230103/sql/handler.cc:8189
#2  0x00000000025d064e in write_record (thd=0x7f29c8000bf0, table=0x7f29c8d5bfb0, info=0x7f29c8012360, update=0x7f29c80123d8) at /root/work/stonedb-dev-20230103/sql/sql_insert.cc:1904
#3  0x00000000025d1aa5 in Query_result_insert::send_data (this=0x7f29c8012318, values=...) at /root/work/stonedb-dev-20230103/sql/sql_insert.cc:2304
#4  0x000000000236c79f in end_send (join=0x7f29c90f5d00, qep_tab=0x7f29c90fabd0, end_of_records=false) at /root/work/stonedb-dev-20230103/sql/sql_executor.cc:2936
#5  0x00000000023693c4 in evaluate_join_record (join=0x7f29c90f5d00, qep_tab=0x7f29c90faa58) at /root/work/stonedb-dev-20230103/sql/sql_executor.cc:1652
#6  0x00000000023687f3 in sub_select (join=0x7f29c90f5d00, qep_tab=0x7f29c90faa58, end_of_records=false) at /root/work/stonedb-dev-20230103/sql/sql_executor.cc:1304
#7  0x000000000236804e in do_select (join=0x7f29c90f5d00) at /root/work/stonedb-dev-20230103/sql/sql_executor.cc:957
#8  0x0000000002365fc9 in JOIN::exec (this=0x7f29c90f5d00) at /root/work/stonedb-dev-20230103/sql/sql_executor.cc:206
#9  0x00000000023fe2ff in handle_query (thd=0x7f29c8000bf0, lex=0x7f29c8002f18, result=0x7f29c8012318, added_options=1342177280, removed_options=0, optimize_after_bh=0, free_join_from_bh=0)
    at /root/work/stonedb-dev-20230103/sql/sql_select.cc:195
#10 0x00000000025d4668 in Sql_cmd_insert_select::execute (this=0x7f29c80122a0, thd=0x7f29c8000bf0) at /root/work/stonedb-dev-20230103/sql/sql_insert.cc:3247
#11 0x00000000023aff13 in mysql_execute_command (thd=0x7f29c8000bf0, first_level=true) at /root/work/stonedb-dev-20230103/sql/sql_parse.cc:3640
#12 0x00000000023b5d7f in mysql_parse (thd=0x7f29c8000bf0, parser_state=0x7f2b7d8cef90) at /root/work/stonedb-dev-20230103/sql/sql_parse.cc:5646
#13 0x00000000023aad68 in dispatch_command (thd=0x7f29c8000bf0, com_data=0x7f2b7d8cf730, command=COM_QUERY) at /root/work/stonedb-dev-20230103/sql/sql_parse.cc:1495
#14 0x00000000023a9ba9 in do_command (thd=0x7f29c8000bf0) at /root/work/stonedb-dev-20230103/sql/sql_parse.cc:1034
#15 0x00000000024db375 in handle_connection (arg=0x7c5e650) at /root/work/stonedb-dev-20230103/sql/conn_handler/connection_handler_per_thread.cc:313
#16 0x0000000002bab156 in pfs_spawn_thread (arg=0x7774270) at /root/work/stonedb-dev-20230103/storage/perfschema/pfs.cc:2197
#17 0x00007f2bcdb691ca in start_thread () from /lib64/libpthread.so.0
#18 0x00007f2bcacb9e73 in clone () from /lib64/libc.so.6

[adofsauron]