TLDR

Comencé a eliminar filas de una tabla masiva de InnoDB hace aproximadamente 8 horas, cuando verifiqué hace 45 minutos, se eliminaron aproximadamente 80 millones de filas, cuando acabo de verificar hace unos minutos, el recuento regresó a cuando comencé, pero las filas todavía se están eliminando - ¿por qué?

El problema. Mi equipo heredó una aplicación hace unas semanas, una aplicación interna confiable ( por lo que no es un showtopper ) que actualiza los informes de una fuente de datos interna. El desarrollador inicial hizo un buen trabajo, con una excepción: no limpió los registros antiguos en una tabla en particular cuando había nuevos datos disponibles, por lo que con el tiempo la tabla DB creció a 500 millones de registros (cada actualización quincenal agrega algunos millones de filas a esta mesa).

Decidimos limpiar la tabla (MySql InnoDb) y agregar algo de lógica al script de actualización para evitar que esto vuelva a suceder.

Primer paso: ejecuté un comando de eliminación simple que está tomando horas, comencé esta tarde y lo dejaría correr toda la noche. Mi SQL es esencialmente:

delete from the_enormous_table where batch_id != most_recent_batch_id

Hace unos 45 minutos, la secuencia de comandos había eliminado unos 80 millones de registros, pero cuando volví a verificar hace unos minutos, el recuento de filas parecía haberse restablecido a donde había comenzado, pero aún continúa eliminándose.

No soy un DBA, pero obviamente he hecho cosas como esta antes, nunca con un conjunto de datos tan masivo. Creo que nunca he tenido que lidiar con los matices del bloqueo de InnoDB, etc., o los he olvidado a lo largo de los años.

¿Cuál es el problema aquí? ¿Necesito dividir esto en conjuntos de eliminación más pequeños?

Si ese es el caso, puedo recrear la tabla con el último conjunto de datos y luego deshacerme de la tabla anterior.

0
picus 24 jun. 2020 a las 08:13

2 respuestas

La mejor respuesta

Para grandes DELETEs, hay mejores formas.

  • Eliminar toda la tabla - TRUNCATE.

  • Reemplazar toda la tabla: cree una nueva tabla y luego use RENAME.

  • Eliminar una gran parte de una tabla: recorra la tabla con PRIMARY KEY para ver fragmentos de 1000 filas a la vez.

  • Eliminación regular de filas "antiguas" - PARTITION BY RANGE.

Detalles: http://mysql.rjweb.org/doc.php/deletebig

(El comentario de Karwin explicó el problema del título: los recuentos de filas rebotando para InnoDB).

1
Rick James 24 jun. 2020 a las 23:14

TRUNCATE la tabla al comienzo de cada ejecución; Es una forma más rápida de deshacerse de grandes cantidades de datos. Puede emitir un truncamiento ahora mismo, o soltar la tabla y volver a crearla si lo desea, para resolver su problema inmediato de que necesita eliminar 500M de registros

1
Caius Jard 24 jun. 2020 a las 05:16