Mysql – the fastest way to insert data into MySQL memory table

insertmemoryMySQLmysql-5.7

I need to insert 10M+ records into a MySQL memory table.

A naive approach was to pipe records using shell, e.g.

$ tail -10 ~/Desktop/test.sql
INSERT INTO `test` (`a`, `b`) VALUES (4, 5);
INSERT INTO `test` (`a`, `b`) VALUES (5, 6);
INSERT INTO `test` (`a`, `b`) VALUES (6, 7);
INSERT INTO `test` (`a`, `b`) VALUES (6, 7);
INSERT INTO `test` (`a`, `b`) VALUES (1, 2);
INSERT INTO `test` (`a`, `b`) VALUES (2, 3);
INSERT INTO `test` (`a`, `b`) VALUES (3, 4);
INSERT INTO `test` (`a`, `b`) VALUES (4, 5);
INSERT INTO `test` (`a`, `b`) VALUES (5, 6);
INSERT INTO `test` (`a`, `b`) VALUES (6, 7);
$ mysql -u root -proot test < ~/Desktop/test.sql

The table itself is simple:

CREATE TABLE `test` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MEMORY DEFAULT CHARSET=utf8;

In essence, the question is what are some lower level ways of inserting data to MySQL?

Best Answer

You need to batch the inserts. This is done by means of extended inserts.

In your question, you can take the row and make them a single insert as follows:

INSERT INTO `test` (`a`, `b`) VALUES
(4, 5),(5, 6),(6, 7),(6, 7),(1, 2),(2, 3),(3, 4),(4, 5),(5, 6),(6, 7);

Why do it this way ?

When you insert into a MEMORY table, there is still some mild disk I/O to check the table's existence by hitting the table .frm file. I wrote about this 5 years ago (I am using the MEMORY storage engine but MySQL still writes to my disk...Why?)

Your question's example does 10 inserts. That's 10 disk reads to the .frm file and 10 full table locks (the lock granularity for the MEMORY storage engine is a full table lock).

Changing it into a single extended insert, the .frm is read only once and the table locked once.

SUGGESTION

In your case, if test.sql has 10 million rows, there will 10 million tables locks and 10 million access of the .frm file.

You should setup test.sql to batch like 10,000 rows in a single insert. Don't worry, mysqldumps create extended insert like this all the time.

That way, you will have 1000 inserts instead of 10,000,000.

GIVE IT A TRY !!!

Related Question