Insert records not that fast

Help
1

Hi,

I am using MemSQL 6.5, to run a test. Before the test I need to insert about eighty million rows in a MemSQL table. It’s like:

DELIMITER //
CREATE OR REPLACE PROCEDURE initdata(m int)
AS
DECLARE
  counter1 int = 0;
  counter2 int = 0;
BEGIN
   WHILE counter1 < 36000 LOOP
    INSERT INTO TableA;
    WHILE counter2 < 2400 LOOP
        INSERT INTO TableB;
        counter2 += 1;
    END LOOP;
    counter1 += 1;
    ...

There are two ways I tried:

  1. Create a procedure like above, in a file initiate.sql, and run like: memsql < initdata.sql
    The speed is 9K per sec.

  2. Using a Golang program, to create insert segments and send to MemSQL.
    The speed is around 30-40K per sec.

In this case, I need to wait for some time for initialising data. So i would like to know, how can I increase the speed of inserting records? Where can i optimise the process? Many thanks!

Yuan

2

To load data quickly it’s a good idea to use parallel inserts with multiple tuples batched per insert, or write data to csvs and use LOAD DATA.

3

Also, once you upgrade to 6.7 you can use INSERT_ALL() to speed up that stored procedure. You can load MemSQL arrays and then insert the rows in the arrays to a table using a single call. It can speed up this type of thing by a factor of 8 or thereabouts.

4

ok thanks! So how many parallel insert threads do you recommend?

5

thanks! i will look at it

7

Error: Expected nil, but got: &net.OpError**Op:“dial”, Net:“tcp”, Source:net.Addr(nil), Addr:(net.TCPAddr)(0xc000491080), Err:(os.SyscallError)(0xc00047c5a0)

I’m getting this error in github action.
Please help this problem