optimización - ¿hay una manera de acelerar la indexación de las tablas Mysql para que el rendimiento general no se vea afectado?

optimizar base de datos mysql

Question

Necesito cargar un conjunto de datos grande en una base de datos de producción.

Se deben cargar 15 archivos cada uno e insertarlos en una tabla. Cada uno es de unos 500 Mb .

Tengo dos columnas de identificación que necesitan ser indexadas. Si cargo los archivos con índices en su lugar, la carga tarda aproximadamente 3 horas. Si elimino los índices, cargo el infile local de datos y luego vuelvo a agregar los índices, toda la operación toma aproximadamente 30 minutos.

El problema es que la capacidad de respuesta de la base de datos recibe un gran golpe al indexar los datos recién importados. ¿Hay alguna manera de hacer que la indexación se ejecute con una "baja prioridad" para que otras consultas sigan obteniendo una velocidad del 95-100% y la clase de indexación de chugs al fondo?

Estoy usando Amazon RDS , por lo que no tengo la opción de simplemente cargar en un servidor diferente y luego copiar sobre los archivos de la tabla.

Agregando una recompensa a esto, ya que todavía quiero ver si hay una manera de obtener un buen rendimiento al indexar en un cuadro específico.

Answer 1

¿Has intentado subir tu configuración de índice para la importación? Eso puede aumentar significativamente el rendimiento de importación. sort_buffer_size es para cualquier tipo de tabla, myisam_sort_buffer_size es para tablas MyISAM. innodb_buffer_pool_size es una especie de "caché de claves" para Innodb. Aumente esos para la importación dependiendo de su tipo de tabla. Lo que intenta hacer es evitar la clasificación de archivos durante la creación del índice.

Es posible que pueda obtener su tiempo de importación / índice de 10 a 15 minutos o menos. No es la regulación, pero acortará significativamente el período de impacto.

O, si está utilizando tablas MyISAM, ¿tal vez una tabla MERGE es una opción? Cree una nueva tabla, realice la importación, luego agregue la nueva tabla a la tabla MERGE. No habrá impacto en la base de datos durante la importación. Aparte del servidor que realiza una tarea.

Answer 2

Bueno, nunca encontré una manera de estrangular, pero encontré una manera de aliviar mi problema. La solución fue única para mi problema, pero la publicaré en caso de que alguien más la encuentre útil.

Escribí una clase llamada CautiousIndexer .

1. Primero almacené la declaración de creación de tabla para recrear la estructura de la tabla sin índices. Almacené una serie de bases de datos de esclavos de lectura, pasé por ellas cambiando el nombre de la tabla con los datos no indexados para prevent_indexing_($name) .
2. Luego ejecuté la declaración de creación de tabla solo en los esclavos. Esto efectivamente hizo que los datos se apartaran de la indexación de las declaraciones que ocurrirían en el maestro.
3. Luego ejecuté la consulta de índice contra el maestro. Los esclavos de lectura no tuvieron impacto en el rendimiento mientras el maestro estaba indexando porque las tablas recién creadas estaban vacías.
4. Cuando el maestro terminó de indexar, saqué a uno de los esclavos de la rotación de producción, dejé caer la tabla vacía, moví la tabla completa de nuevo en su lugar y luego indiqué la tabla en el esclavo fuera de producción.
5. Cuando terminó, lo puse de nuevo en producción y repetí el procedimiento de indexación de esclavos en los esclavos restantes.
6. Cuando todos los esclavos fueron indexados, puse la mesa en producción.

Esto todavía estaba bien en términos de eficiencia, pero durante la indexación en el servidor maestro, el rendimiento de escritura se desaceleró de manera inaceptable. Sigo buscando una manera de indexar con la regulación.

Answer 3

Esta no es una solución exacta que está buscando, pero puede crear una segunda instancia de mysqld como esclavo en esta única casilla y redirigir las consultas SELECT a medida que sea necesario. Existe un Proxy MySQL que puede ayudarlo a lograr esto sin tener que volver a escribir las aplicaciones de cliente.

También puede recopilar algunas ideas del uso de MySQL por parte de FriendFeed . Almacenan los índices reales en otras tablas y los utilizan para la búsqueda. Si almacena una copia de sus datos en otra tabla, incluso en otro servidor y ejecuta índices allí, podrá acceder a los datos maestros lo antes posible a toda velocidad y obtener consultas más rápidas más adelante utilizando otro servidor.

Es como si agregase índices en un esclavo para consultas de tipo de búsqueda y ejecutara solo búsquedas de clave principal en el maestro.

Answer 4

Puede deshabilitar cualquier índice no único mientras inserta y volver a habilitarlos una vez que finalice. Echa un vistazo a deshabilitar teclas / habilitar teclas. Pero funciona solo para índices no únicos.

También puede acelerar las inserciones si usa sentencias de inserción de valores múltiples (insertar en la tabla (...) valores (...), (...), (...) ...

Por cierto, el archivo de datos de carga parece ser la manera más rápida de insertar muchos datos en mysql.

Answer 5

Una buena solución para esto es un script que realiza una actualización sucesiva. Usted aplicaría el índice a cada esclavo de manera no replicante. Una ilustración aproximada:

for host in $hosts
do
    mysql -h $host -e "STOP SLAVE;/
      SET sql_log_bin=0;/
      FLUSH TABLE t;/
      ALTER TABLE t ADD INDEX a (b,c);/
      SET sql_log_bin=1;/
      START SLAVE;"
done

Al desactivar la replicación, se debe reducir la cantidad de actividad del disco y aumentar la velocidad de la operación de indexación. Si tiene requisitos de retraso de la base de datos para sus esclavos, es posible que desee desagrupar por completo el esclavo e incluir lógica para volver a agrupar el esclavo cuando se reanude cero segundos de retraso.

Answer 6

Una idea que no se haya intentado antes tampoco es sobre la regulación del índice, lo que si hace una tabla de respaldo y la actualiza de la forma que mencionó tiene un período de tiempo más corto y luego convierte / renombra las tablas. Te animo a escribir mis pensamientos porque necesitas saber una manera.