Как эффективно вставить очень много строк в memory table MySQL?

Question

[Дмитрий Авилов]

Пользователь присылает запрос, исходя из этого запроса мы должны сохранить, допустим, 500 тысяч записей в таблицу Memory.
Каким образом сделать это максимально быстро?
На текущем железе получилось добиться примерно 70-75 тысяч записей в секунду, используя конструкцию "INSERT INTO `Test_memory` (тут_35_колоночек) VALUES (значения1), (значения2),... (значения1000)". Код для вставки такой:

var settings = {
  'base': *************,
  'draws': 1,
  'packets': 1000,
  'values': 1000
}
var mysql = require('mysql');
var base = mysql.createPool({
  connectionLimit : 100,
  host : settings['base']['host'],
  user : settings['base']['user'],
  password : settings['base']['password'],
  port : settings['base']['port'],
  database : settings['base']['database'],
  debug: false
});

var packetsLeft = settings.draws * settings.packets * settings.values;
var startTime = Date.now();

for (var z = 0; z < settings.draws; z++){
  var draw = Math.floor(Math.random(100000));
  for (var i = 0; i < settings.packets; i++){
    var packet = makePacket(10, '0123456789ABCDEFGHJKLMNPQRSTUVWXYZ');
    var values = [];
    for (var j = 0; j < settings.values; j++){
      var variants = [draw, packet];
      for (var v = 1; v < 16; v++){
        variants.push(Math.random() < 0.5 ? 'home': 'away');
        variants.push(Math.random() < 0.5 ? 'over': 'under');
      }
      variants.push(Math.random() < 0.1 ? 'no': 'yes');
      values.push(variants);
    }
    
    var query = base.query('INSERT INTO `Test_memory` (`draw`, `packet`, `match_1_handicap`, `match_1_total`, `match_2_handicap`, `match_2_total`, `match_3_handicap`, `match_3_total`, `match_4_handicap`, `match_4_total`, `match_5_handicap`, `match_5_total`, `match_6_handicap`, `match_6_total`, `match_7_handicap`, `match_7_total`, `match_8_handicap`, `match_8_total`, `match_9_handicap`, `match_9_total`, `match_10_handicap`, `match_10_total`, `match_11_handicap`, `match_11_total`, `match_12_handicap`, `match_12_total`, `match_13_handicap`, `match_13_total`, `match_14_handicap`, `match_14_total`, `match_15_handicap`, `match_15_total`, `active`) VALUES ? ', [values], function(error, result) {
      if (error){
        console.log('Error: ' + error);
      } else {
        packetsLeft -= settings.values;
        
        if (packetsLeft == 0){
          var finishTime = Date.now();
          console.log('Data inserted in ' + ((finishTime - startTime) / 1000).toFixed(4) + ' seconds. It is ' +((settings.draws * settings.packets * settings.values) / (finishTime - startTime) * 1000).toFixed(2)+ ' rows per second.');
        }
      }
    });
  }
}


function makePacket (number_of_characters, dictionary) {
  return number_of_characters > 0 ? dictionary[(Math.random() * (dictionary.length - 1)).toFixed(0)] + makePacket(number_of_characters - 1, dictionary) : '';
}

Табличка такая:

CREATE TABLE `Test_memory` (
	`id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
	`draw` INT(11) UNSIGNED NOT NULL,
	`packet` VARCHAR(10) NOT NULL COLLATE 'utf8_unicode_ci',
	`active` ENUM('yes','no') NOT NULL DEFAULT 'yes' COLLATE 'utf8_unicode_ci',
	`match_1_handicap` ENUM('home','away') NOT NULL COLLATE 'utf8_unicode_ci',
	`match_1_total` ENUM('over','under') NOT NULL COLLATE 'utf8_unicode_ci',
	`match_2_handicap` ENUM('home','away') NOT NULL COLLATE 'utf8_unicode_ci',
	`match_2_total` ENUM('over','under') NOT NULL COLLATE 'utf8_unicode_ci',
	`match_3_handicap` ENUM('home','away') NOT NULL COLLATE 'utf8_unicode_ci',
	`match_3_total` ENUM('over','under') NOT NULL COLLATE 'utf8_unicode_ci',
	`match_4_handicap` ENUM('home','away') NOT NULL COLLATE 'utf8_unicode_ci',
	`match_4_total` ENUM('over','under') NOT NULL COLLATE 'utf8_unicode_ci',
	`match_5_handicap` ENUM('home','away') NOT NULL COLLATE 'utf8_unicode_ci',
	`match_5_total` ENUM('over','under') NOT NULL COLLATE 'utf8_unicode_ci',
	`match_6_handicap` ENUM('home','away') NOT NULL COLLATE 'utf8_unicode_ci',
	`match_6_total` ENUM('over','under') NOT NULL COLLATE 'utf8_unicode_ci',
	`match_7_handicap` ENUM('home','away') NOT NULL COLLATE 'utf8_unicode_ci',
	`match_7_total` ENUM('over','under') NOT NULL COLLATE 'utf8_unicode_ci',
	`match_8_handicap` ENUM('home','away') NOT NULL COLLATE 'utf8_unicode_ci',
	`match_8_total` ENUM('over','under') NOT NULL COLLATE 'utf8_unicode_ci',
	`match_9_handicap` ENUM('home','away') NOT NULL COLLATE 'utf8_unicode_ci',
	`match_9_total` ENUM('over','under') NOT NULL COLLATE 'utf8_unicode_ci',
	`match_10_handicap` ENUM('home','away') NOT NULL COLLATE 'utf8_unicode_ci',
	`match_10_total` ENUM('over','under') NOT NULL COLLATE 'utf8_unicode_ci',
	`match_11_handicap` ENUM('home','away') NOT NULL COLLATE 'utf8_unicode_ci',
	`match_11_total` ENUM('over','under') NOT NULL COLLATE 'utf8_unicode_ci',
	`match_12_handicap` ENUM('home','away') NOT NULL COLLATE 'utf8_unicode_ci',
	`match_12_total` ENUM('over','under') NOT NULL COLLATE 'utf8_unicode_ci',
	`match_13_handicap` ENUM('home','away') NOT NULL COLLATE 'utf8_unicode_ci',
	`match_13_total` ENUM('over','under') NOT NULL COLLATE 'utf8_unicode_ci',
	`match_14_handicap` ENUM('home','away') NOT NULL COLLATE 'utf8_unicode_ci',
	`match_14_total` ENUM('over','under') NOT NULL COLLATE 'utf8_unicode_ci',
	`match_15_handicap` ENUM('home','away') NOT NULL COLLATE 'utf8_unicode_ci',
	`match_15_total` ENUM('over','under') NOT NULL COLLATE 'utf8_unicode_ci',
	PRIMARY KEY (`id`),
	INDEX `Packet` (`draw`, `packet`),
	INDEX `Active packets in draw` (`draw`, `active`)
)
COMMENT='Testing memory tables'
COLLATE='utf8_unicode_ci'
ENGINE=MEMORY
;

Вот такой скрипт выдаёт примерно такие цифры.
Data inserted in 13.0850 seconds. It is 76423.39 rows per second.

Каким способом (изменение настроек сервера, другой вид запроса, что-то ещё) можно кардинально увеличить скорость записи данных? Я понимаю, что сейчас данные генерируются на лету, это тоже занимает какое-то время, но может быть есть более эффективные способы для быстрой вставки большого количества данных, чтобы можно было вставлять несколько сотен тысяч записей в секунду? Или вот эта цифра (70к строк в секунду) это потолок и ощутимо выше на данном стеке (железо, mysql, nodejs) подняться нельзя?

Answer 1

[Ivan Velichko]

Попробуйте сначала сформировать файл с данными, необходимыми для вставки, а затем вызвать LOAD DATA INFILE. Это очень быстрый способ залить большое количество данных в таблицу MySQL. С другой стороны возможно стоит использовать другое хранилище, или вынести эту операцию из пользовательского запроса в асинхронную очередь.

Answer 2

[Леонид Сысолетин]

Для начала смените тип индекса на BTREE, дефолтный HASH быстр на селектах, но инсёрты ему даются хуже. В приницпе, совсем для начала можно попробовать вообще снять все индексы и посмотреть, что будет. Должно получиться близко к максимуму для данной конфигурации.

Дальше имеет смысл думать только после результатов этих действий.

Comments

[Дмитрий Авилов]

Тоже сразу думал, что проблема в индексах, пробовал без индексов тоже, скорость вставки _такая же_ (не уверен, дело ли в том, что индексы в памяти, или же mysql (точнее Percona) как-то хитро их пересчитывает). Кстати, выборка по индексу на подобной таблице с 15 миллионами записей проходит за сотые доли секунды.