Создание таблицы MySQL на 1000 строк

Этот вопрос отправлен с Переполнение стека на основе предложения в комментариях, приносим свои извинения за дублирование.

Вопросы

Вопрос 1: по мере увеличения размера таблицы базы данных, как мне настроить MySQL для увеличения скорости вызова LOAD DATA INFILE?

Вопрос 2: будет ли использование кластера компьютеров для загрузки различных CSV-файлов улучшением производительности или уничтожением? (это моя завтрашняя контрольная задача с использованием данных загрузки и массовых вставок)

Цель

Мы пробуем различные комбинации детекторов функций и параметров кластеризации для поиска изображений, поэтому нам нужно иметь возможность своевременно создавать большие базы данных.

Информация о машине

У машины 256 гигабайт оперативной памяти, и есть еще 2 машины с таким же объемом оперативной памяти, есть ли способ сократить время создания за счет распределения базы данных?

Схема таблицы

схема таблицы выглядит как

+---------------+------------------+------+-----+---------+----------------+
| Field         | Type             | Null | Key | Default | Extra          |
+---------------+------------------+------+-----+---------+----------------+
| match_index   | int(10) unsigned | NO   | PRI | NULL    |                |
| cluster_index | int(10) unsigned | NO   | PRI | NULL    |                |
| id            | int(11)          | NO   | PRI | NULL    | auto_increment |
| tfidf         | float            | NO   |     | 0       |                |
+---------------+------------------+------+-----+---------+----------------+

создан с

CREATE TABLE test 
(
  match_index INT UNSIGNED NOT NULL,
  cluster_index INT UNSIGNED NOT NULL, 
  id INT NOT NULL AUTO_INCREMENT,
  tfidf FLOAT NOT NULL DEFAULT 0,
  UNIQUE KEY (id),
  PRIMARY KEY(cluster_index,match_index,id)
)engine=innodb;

Бенчмаркинг на данный момент

Первым шагом было сравнение массовых вставок и загрузки из двоичного файла в пустую таблицу.

It took:  0:09:12.394571  to do  4,000  inserts with 5,000 rows per insert

It took:  0:03:11.368320  seconds to load 20,000,000 rows from a csv file

Учитывая разницу в производительности, я пошел с загрузкой данных из двоичного файла csv, сначала я загрузил двоичные файлы, содержащие строки 100K, 1M, 20M, 200M, используя вызов ниже.

LOAD DATA INFILE '/mnt/tests/data.csv' INTO TABLE test;

Я убил загрузку двоичного файла с 200 мегабайтами (~ 3 ГБ файла csv) через 2 часа.

Поэтому я запустил сценарий для создания таблицы и вставил разное количество строк из двоичного файла, а затем отбросил таблицу, см. График ниже.

Вставка 1M строк из двоичного файла заняла около 7 секунд. Затем я решил протестировать вставку 1 миллиона строк за раз, чтобы увидеть, не возникнет ли узкое место при конкретном размере базы данных. После того, как база данных поразила примерно 59 миллионов строк, среднее время вставки упало примерно до 5000 в секунду.

Установка глобального key_buffer_size = 4294967296 немного повысила скорость вставки небольших двоичных файлов. На приведенном ниже графике показаны скорости для разного количества строк.

Однако для вставки 1M строк это не улучшило производительность.

строк: 1000000 раз: 0: 04: 13.761428 вставок / сек: 3940

vs для пустой базы данных

строк: 1000000 раз: 0: 00: 6,339295 вставок / сек: 315,492

Обновить

Выполнение загрузки данных с использованием следующей последовательности по сравнению с использованием просто команды загрузки данных

SET autocommit=0;
SET foreign_key_checks=0;
SET unique_checks=0;
LOAD DATA INFILE '/mnt/imagesearch/tests/eggs.csv' INTO TABLE test_ClusterMatches;
SET foreign_key_checks=1;
SET unique_checks=1;
COMMIT;

Таким образом, это выглядит довольно многообещающим с точки зрения размера создаваемой базы данных, но другие параметры, похоже, не влияют на производительность вызова загрузки данных в файл.

Затем я попытался загрузить несколько файлов с разных компьютеров, но команда load data infile блокирует таблицу из-за большого размера файлов, из-за чего другие машины выходят из строя с

ERROR 1205 (HY000) at line 1: Lock wait timeout exceeded; try restarting transaction

Увеличение количества строк в двоичном файле

rows:  10,000,000  seconds rows:  0:01:36.545094  inserts/sec:  103578.541236
rows:  20,000,000  seconds rows:  0:03:14.230782  inserts/sec:  102970.29026
rows:  30,000,000  seconds rows:  0:05:07.792266  inserts/sec:  97468.3359978
rows:  40,000,000  seconds rows:  0:06:53.465898  inserts/sec:  96743.1659866
rows:  50,000,000  seconds rows:  0:08:48.721011  inserts/sec:  94567.8324859
rows:  60,000,000  seconds rows:  0:10:32.888930  inserts/sec:  94803.3646283

Решение: предварительное вычисление идентификатора вне MySQL вместо использования автоматического увеличения

Создание таблицы с

CREATE TABLE test (
  match_index INT UNSIGNED NOT NULL,
  cluster_index INT UNSIGNED NOT NULL, 
  id INT NOT NULL ,
  tfidf FLOAT NOT NULL DEFAULT 0,
  PRIMARY KEY(cluster_index,match_index,id)
)engine=innodb;

с SQL

LOAD DATA INFILE '/mnt/tests/data.csv' INTO TABLE test FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';"

Получение сценария для предварительного вычисления индексов, похоже, устранило снижение производительности по мере роста базы данных.

Обновление 2 - использование таблиц памяти

Примерно в 3 раза быстрее, без учета затрат на перемещение таблицы из оперативной памяти в таблицу на диске.

rows:  0  seconds rows:  0:00:26.661321  inserts/sec:  375075.18851
rows:  10000000  time:  0:00:32.765095  inserts/sec:  305202.83857
rows:  20000000  time:  0:00:38.937946  inserts/sec:  256818.888187
rows:  30000000  time:  0:00:35.170084  inserts/sec:  284332.559456
rows:  40000000  time:  0:00:33.371274  inserts/sec:  299658.922222
rows:  50000000  time:  0:00:39.396904  inserts/sec:  253827.051994
rows:  60000000  time:  0:00:37.719409  inserts/sec:  265115.500617
rows:  70000000  time:  0:00:32.993904  inserts/sec:  303086.291334
rows:  80000000  time:  0:00:33.818471  inserts/sec:  295696.396209
rows:  90000000  time:  0:00:33.534934  inserts/sec:  298196.501594

путем загрузки данных в таблицу на основе памяти и последующего копирования их в таблицу на диске кусками накладные расходы составляли 10 минут 59,71 секунды для копирования 107,356,741 строки с запросом

insert into test Select * from test2;

что дает примерно 15 минут для загрузки 100 миллионов строк, что примерно то же самое, что и прямая вставка их в таблицу на диске.