Esta questão foi reposta a partir de Stack Overflow com base numa sugestão nos comentários, desculpas pela duplicação.
Perguntas
Pergunta 1: à medida que o tamanho da tabela de banco de dados aumenta, como posso ajustar o MySQL para aumentar a velocidade da chamada LOAD DATA INFILE?
Pergunta 2: usar um cluster de computadores para carregar diferentes arquivos csv, melhorar o desempenho ou eliminá-lo? (esta é a minha tarefa de benchmarking para amanhã usando os dados de carga e inserções em massa)
Meta
Estamos testando diferentes combinações de detectores de recursos e parâmetros de clustering para pesquisa de imagens. Como resultado, precisamos construir bancos de dados grandes e em tempo hábil.
Informações da máquina
A máquina tem 256 gig de ram e existem outras 2 máquinas disponíveis com a mesma quantidade de memória ram, se houver uma maneira de melhorar o tempo de criação distribuindo o banco de dados?
Esquema de tabela
o esquema da tabela se parece com
+---------------+------------------+------+-----+---------+----------------+
| 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 | |
+---------------+------------------+------+-----+---------+----------------+
criado com
%pr_e%
Benchmarking até agora
O primeiro passo foi comparar as inserções em massa versus o carregamento de um arquivo binário em uma tabela vazia.
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;
Dada a diferença no desempenho que tenho feito ao carregar os dados de um arquivo csv binário, primeiro eu carreguei arquivos binários contendo linhas de 100K, 1M, 20M, 200M usando a chamada abaixo.
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
Eu matei o arquivo binário de linha de 200M (~ arquivo de 3GB csv) após 2 horas.
Então, eu corri um script para criar a tabela e insiro diferentes números de linhas de um arquivo binário e, em seguida, descarte a tabela, veja o gráfico abaixo.
Demoroucercade7segundosparainserirlinhasde1Mdoarquivobinário.Emseguida,decidifazerbenchmarkinserindolinhasde1Mporvezparaversehaveriaumgargaloemumdeterminadotamanhodebancodedados.Depoisqueobancodedadosatingiuaproximadamente59milhõesdelinhas,otempomédiodeinserçãocaiuparaaproximadamente5.000/segundo
Configurar o global key_buffer_size = 4294967296 melhorou ligeiramente as velocidades para inserir arquivos binários menores. O gráfico abaixo mostra as velocidades para números diferentes de linhas
Noentanto,parainserirlinhasde1milhão,nãomelhorouodesempenho.
linhas:1.000.000tempo:0:04:13.761428inserções/seg:3.940
vsparaumbancodedadosvazio
linhas:1.000.000tempo:0:00:6.339295inserções/seg:315.492
Atualizar
Fazendoosdadosdecargausandoaseguintesequênciavsusandoapenasocomandoloaddata
LOADDATAINFILE'/mnt/tests/data.csv'INTOTABLEtest;
Então, isso parece bastante promissor em termos do tamanho do banco de dados que está sendo gerado, mas as outras configurações não parecem afetar o desempenho da chamada de infil dados de carga.
Tentei, então, carregar vários arquivos de diferentes máquinas, mas o comando load infile data bloqueia a tabela, devido ao grande tamanho dos arquivos que causam o atraso das outras máquinas com
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;
Aumentando o número de linhas no arquivo binário
ERROR 1205 (HY000) at line 1: Lock wait timeout exceeded; try restarting transaction
Solução: Pré-computando o id fora do MySQL em vez de usar o incremento automático
Construindo a tabela com
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
com o SQL
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;
Aobtençãodoscriptparapré-computarosíndicespareceterremovidoodesempenhoquandoobancodedadosaumentadetamanho.
Atualização2-usandotabelasdememória
Aproximadamente3vezesmaisrápido,semlevaremcontaocustodemoverumatabelanamemóriaparaumatabelabaseadaemdisco.
LOADDATAINFILE'/mnt/tests/data.csv'INTOTABLEtestFIELDSTERMINATEDBY','LINESTERMINATEDBY'\n';"
carregando os dados em uma tabela baseada em memória e, em seguida, copiando-os para uma tabela baseada em disco em pedaços teve uma sobrecarga de 10 min 59,71 segundos para copiar 107,356,741 linhas com a consulta
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
que faz com que aproximadamente 15 minutos carreguem 100 milhões de linhas, o que é aproximadamente o mesmo que inseri-las diretamente em uma tabela baseada em disco.