18 votos

Construir una tabla de MySQL fila de 1.000 M

Esta pregunta es reenviada de Stack Overflow basada en una sugerencia en los comentarios, pido disculpas por la duplicación.

Preguntas

Pregunta 1: como el tamaño de la tabla de base de datos se hace más grande ¿cómo puedo ajustar MySQL para aumentar la velocidad de los DATOS de la CARGA de este argumento de la llamada?

Pregunta 2: ¿con un clúster de equipos para cargar diferentes archivos csv, mejorar el rendimiento o matar? (este es mi bench-marking tarea para mañana utilizando los datos de la carga y de las inserciones masivas)

Objetivo

Estamos probando diferentes combinaciones de características de los detectores y la agrupación de los parámetros para la búsqueda de imágenes, como resultado tenemos que ser capaces de construir y grandes bases de datos de manera oportuna.

Máquina Info

La máquina dispone de 256 gigas de ram y hay otras 2 máquinas disponibles con la misma cantidad de ram si hay una manera de mejorar el tiempo de creación por la distribución de la base de datos?

Esquema De La Tabla

el esquema de la tabla se ve como

+---------------+------------------+------+-----+---------+----------------+
| 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       |                |
+---------------+------------------+------+-----+---------+----------------+

creado con

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;

Benchmarking hasta ahora

El primer paso fue comparar las inserciones masivas vs la carga de un archivo binario en una mesa vacía.

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

Dada la diferencia en el rendimiento que he ido con la carga de los datos desde un archivo binario archivo csv, en primer lugar me cargan los archivos binarios que contienen 100K, 1 M, 20 M, 200M de filas utilizando la llamada a continuación.


He matado a los 200 m fila de archivo binario (~3 GB archivo csv) de la carga después de 2 horas.

Así que me fui corriendo un script para crear la tabla e insertar diferentes números de filas de un archivo binario, a continuación, colocar la tabla, vea el siguiente gráfico.

enter image description here

Tomó alrededor de 7 segundos para insertar 1 millón de filas del archivo binario. Siguiente decidí punto de referencia la inserción de 1 millón de filas en un tiempo para ver si iba a ser un cuello de botella en un determinado tamaño de base de datos. Una vez que la Base de datos de golpear aproximadamente 59M filas de la media hora de insertar reducido a aproximadamente 5,000/segundo

enter image description here

Configuración global key_buffer_size = 4294967296 mejora de las velocidades ligeramente para la inserción de pequeños archivos binarios. El siguiente gráfico muestra las velocidades para diferentes números de filas

enter image description here

Sin embargo, para la inserción de 1 millón de filas no mejorar el rendimiento.

filas: 1.000.000 de tiempo: 0:04:13.761428 inserciones/seg: 3,940

vs para una base de datos vacía

filas: 1.000.000 de tiempo: 0:00:6.339295 inserciones/seg: 315,492

Actualización

Hacer la carga de datos usando la siguiente secuencia vs simplemente usando el comando load data


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

Por lo que este se ve muy prometedor en términos de tamaño de base de datos que se generan, pero las otras opciones no parecen afectar el rendimiento de la carga de datos de este argumento de la llamada.

Luego traté de cargar varios archivos de diferentes máquinas, pero el load data infile comando bloquea la tabla, debido al gran tamaño de los archivos, por lo que el resto de máquinas para el tiempo de espera con

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

Aumentar el número de filas en un archivo binario

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;

Solución: Precomputing la identificación de fuera de MySQL en lugar de usar el auto increment

Creación de la tabla con

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

con el SQL

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

enter image description here

Obtención de la secuencia de comandos pre-calcular los índices parece haber eliminado el impacto en el rendimiento como la base de datos crece en tamaño.

Actualización 2 - uso de tablas de la memoria

Aproximadamente 3 veces más rápido, sin tomar en cuenta el costo de mover una tabla en memoria a disco basado en la tabla.

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;

por la carga de los datos en una memoria basada en tabla y, a continuación, copiarlo a un disco basado en la tabla en trozos tenía una sobrecarga de 10 min 59.71 sec para copiar 107,356,741 filas con la consulta

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

que hace aproximadamente 15 minutos para cargar 100M de filas, que es aproximadamente la misma directamente en la inserción en un disco basado en la tabla.

4voto

MagicAndi Puntos 10128

Buena pregunta - bien explicado.

¿cómo puedo ajustar MySQL para aumentar la velocidad de los DATOS de la CARGA de este argumento de la llamada?

Ya tienes un alto(ish) configuración de la clave de búfer - pero, ¿es suficiente? Estoy asumiendo que esta es una instalación de 64 bits (si no, entonces la primera cosa que usted necesita hacer es actualizar) y no se ejecuta en MSNT. Eche un vistazo a la salida de mysqltuner.pl después de correr un par de pruebas.

Con el fin de utilizar la caché para un mejor efecto, usted puede encontrar beneficios en la formación de lotes/pre-ordenar los datos de entrada (versiones más recientes de la 'ordenar' comando tiene un montón de funcionalidades para la clasificación de grandes conjuntos de datos). También si genera los números de IDENTIFICACIÓN fuera de MySQL, entonces puede ser más eficiente.

con un clúster de equipos para cargar diferentes archivos csv

Suponiendo que (de nuevo) que desea tener el conjunto de salida se comportan como una sola tabla, a continuación, los únicos beneficios que obtendrás son por distribuir el trabajo de clasificación y generación de ids que no necesita más bases de datos. OTOH el uso de un clúster de base de datos, usted va a tener problemas con la disputa (que no debería ver de otra manera que como los problemas de rendimiento).

Si usted puede fragmento de los datos y manejar los conjuntos de datos resultantes de forma independiente, entonces sí, usted recibirá beneficios de rendimiento -, pero esto no niega la necesidad de sintonizar cada nodo.

Comprobar que tienes al menos 4 Gb para el sort_buffer_size.

Más allá de eso, el factor limitante en el rendimiento es todo sobre el disco I/O. Hay un montón de maneras de abordar esto - pero usted probablemente debería estar considerando un espejo de un conjunto de rayas conjuntos de datos en unidades de estado sólido para un rendimiento óptimo.

1voto

Tom Puntos 720
  • Considere la posibilidad de su factor limitante. Es casi seguro que un único subproceso de procesamiento de la CPU.
  • Ya has decidido que load data... es más rápido que el de inserción, a fin de utilizar que.
  • Ya has determinado que los archivos muy grandes (por número de fila) ralentizar las cosas mucho; desea romper en pedazos.
  • No utilizar la superposición de las claves principales, la cola de al menos N*CPU conjuntos, usando no más de un millón de filas... probablemente menos (benchmark).
  • El uso secuencial de bloques de claves primarias en cada archivo.

Si quieres ser realmente fantástico, puede crear un multi-threaded programa para alimentar un solo archivo a una colección de canalizaciones con nombre y gestionar la inserción de los casos.

En resumen, no ajustar MySQL para este tanto como usted ajustar su carga de trabajo a MySQL.

-1voto

No recuerdo exactamente el syntacx pero si es inno db puede desactivar comprobación clave extranjera.

También puede crear el índice después de la importación, es puede ser realmente un aumento del rendimiento.

EnMiMaquinaFunciona.com

EnMiMaquinaFunciona es una comunidad de administradores de sistemas en la que puedes resolver tus problemas y dudas.
Puedes consultar las preguntas de otros sysadmin, hacer tus propias preguntas o resolver las de los demás.

Powered by: