para - optimizar consultas mysql explain

¡La tabla con 80 millones de registros y la adición de un índice lleva más de 18 horas(o para siempre)! ¿Ahora que?

Question

Una breve recapitulación de lo que sucedió. Estoy trabajando con 71 millones de registros (no mucho comparado con miles de millones de registros procesados ​​por otros). En un thread diferente, alguien sugirió que la configuración actual de mi clúster no es adecuada para mi necesidad. Mi estructura de tabla es:

CREATE TABLE `IPAddresses` (
`id` int(11) unsigned NOT NULL auto_increment,
`ipaddress` bigint(20) unsigned default NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM;

Y agregué los 71 millones de registros y luego hice un:

ALTER TABLE IPAddresses ADD INDEX(ipaddress);

Han pasado 14 horas y la operación todavía no está completa. Al buscar en Google, descubrí que existe un enfoque bien conocido para resolver este problema: Particionado. Entiendo que necesito dividir mi tabla ahora en función de la dirección IP, ¿pero puedo hacerlo sin volver a crear toda la tabla? Quiero decir, a través de una declaración ALTER? En caso afirmativo, existía un requisito que decía que la columna a ser particionada debería ser una clave primaria. Usaré la identificación de este ipaddress para construir una tabla diferente, así que ipaddress no es mi clave principal. ¿Cómo participo mi tabla dado este escenario?

Answer 1

Crear índices con MySQL es lento, pero no tan lento. Con 71 millones de registros, debería tomar un par de minutos, no 14 horas. Los posibles problemas son:

• no ha configurado los tamaños del búfer de clasificación y otras opciones de configuración

mira aquí: http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_myisam_sort_buffer_size

Si intenta generar un índice de 1 GB con un búfer de clasificación de 8 MB, va a tomar muchos pases. Pero si el buffer es más grande que tu caché de CPU, se volverá más lento. Entonces debes probar y ver qué funciona mejor.

• alguien tiene un candado en la mesa
• su sistema IO apesta
• su servidor está intercambiando
• etc

como de costumbre, compruebe iostat, vmstat, logs, etc. Emita una TABLA DE BLOQUEO en su mesa para comprobar si alguien tiene un bloqueo.

FYI en mi escritorio de 64 bits creando un índice en BIGINT aleatorio 10M toma 17s ...

Answer 2

En tu mesa ya ha insertado 71 mil millones de registros. ahora, si desea crear particiones en la columna de la clave principal de su tabla, puede usar la opción alter table. Se da un ejemplo para su referencia.

CREATE TABLE t1 (
id INT,
year_col INT
);
ALTER TABLE t1
PARTITION BY HASH(id)
PARTITIONS 8;

Answer 3

Está utilizando MyISAM, que está en desuso pronto. Una alternativa sería InnoDB.

"InnoDB es un motor de almacenamiento seguro para transacciones (compatible con ACID) para MySQL que tiene capacidades de compromiso, retrotracción y recuperación de fallos para proteger los datos del usuario. InnoDB a nivel de fila (sin escalar a bloqueos de granularidad más gruesos) y no-bloqueo constante de Oracle-style dice que aumenta la concurrencia y el rendimiento multiusuario. InnoDB almacena datos de usuario en índices agrupados para reducir la E / S en consultas comunes basadas en claves primarias. Para mantener la integridad de datos, InnoDB también admite restricciones de integridad referencial FOREIGN KEY. Puede mezclar libremente tablas InnoDB con tablas de otros motores de almacenamiento MySQL, incluso dentro de la misma declaración. "/

http://dev.mysql.com/doc/refman/5.0/en/innodb.html

De acuerdo a:

http://dev.mysql.com/tech-resources/articles/storage-engine/part_1.html

, deberías poder cambiar entre diferentes motores utilizando un simple comando alter que te permite cierta flexibilidad. También establece que cada tabla en su base de datos se puede configurar de forma independiente.

Answer 4

Ok resulta que este problema fue más que un simple crear una tabla, indexarla y olvidar el problema :) Esto es lo que hice por si acaso alguien más enfrenta el mismo problema (he usado un ejemplo de dirección IP pero funciona para otra tipos de datos también):

Problema: su tabla tiene millones de entradas y necesita agregar un índice realmente rápido

Uso: Considere almacenar millones de direcciones IP en una tabla de búsqueda. Agregar las direcciones IP no debería ser un gran problema, pero crear un índice lleva más de 14 horas.

Solución : particione su tabla usando dev.mysql.com/doc/refman/5.1/en/partitioning.html estrategia dev.mysql.com/doc/refman/5.1/en/partitioning.html g de dev.mysql.com/doc/refman/5.1/en/partitioning.html

Caso # 1: cuando la tabla que desea aún no se ha creado

CREATE TABLE IPADDRESSES(
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
ipaddress BIGINT UNSIGNED,
PRIMARY KEY(id, ipaddress)
) ENGINE=MYISAM
PARTITION BY HASH(ipaddress)
PARTITIONS 20;

Caso # 2: cuando la tabla que desea ya está creada. Parece haber una manera de usar ALTER TABLE para hacer esto, pero aún no he encontrado una solución adecuada para esto. En cambio, hay una solución ligeramente ineficiente:

CREATE TABLE IPADDRESSES_TEMP(
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
ipaddress BIGINT UNSIGNED,
PRIMARY KEY(id)
) ENGINE=MYISAM;

Inserta tus direcciones IP en esta tabla. Y luego crea la tabla real con particiones:

CREATE TABLE IPADDRESSES(
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
ipaddress BIGINT UNSIGNED,
PRIMARY KEY(id, ipaddress)
) ENGINE=MYISAM
PARTITION BY HASH(ipaddress)
PARTITIONS 20;

Y finalmente

INSERT INTO IPADDRESSES(ipaddress) SELECT ipaddress FROM IPADDRESSES_TEMP;
DROP TABLE IPADDRESSES_TEMP;
ALTER TABLE IPADDRESSES ADD INDEX(ipaddress)

Y ahí lo tienes ... indizar en la nueva mesa me llevó unas 2 horas en una máquina de 3.2 GHz con 1GB de RAM :) Espero que esto ayude.

Answer 5

Tuve el problema de que quería acelerar mi consulta agregando un índice. La tabla solo tenía aproximadamente 300,000 registros, pero también llevó demasiado tiempo. Cuando revisé los procesos del servidor mysql, resultó que la consulta que intentaba optimizar todavía se estaba ejecutando en segundo plano. ¡4 veces! Después de matar esas consultas, la indexación se hizo en un santiamén. Tal vez el mismo problema se aplica a su situación.