Insert records not that fast

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

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.

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.

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

thanks! i will look at it

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