Cómo procesar datos estadísticos después de archivar los datos de la tabla de la base de datos

Como DBA de MySQL, se puede decir que nadie no conoce pt-archiver. Como miembro importante del conjunto de pt-toolkit, a menudo puede ayudar fácilmente a los DBA a resolver. Problemas de archivo de datos. Por ejemplo, es suficiente tener una tabla en ejecución. La empresa solo necesita almacenar los datos en ejecución de los últimos tres meses y archivar los datos de hace tres meses. En este momento, pt-archiver puede ayudarlo a completar esta tarea fácilmente. Incluso configurarte para automatizar tareas sin intervención humana.

Como administradores de bases de datos, debemos saber qué está sucediendo y por qué, para poder utilizar la herramienta pt con confianza. Creo que muchos administradores de bases de datos han estudiado los principios de pt-online-schema-change, por lo que hoy planificaremos el trabajo de pt-archiver con más profundidad.

1. Principio de observación

Los nativos tienen sus propios métodos. Abrimos directamente el registro general para observar cómo pt-archiver completa el archivo.

Comando

pt-archiver --source h=127.0.0.1, u=xucl, p=xuclxucl, P=3306, D=xucl, t=t1 --dest h =127.0.0.1, P=3306, u=xucl, p=xuclxucl, D= xucl_archive, t=t1 --progreso 5000 \

--estadísticas --charset=utf8mb4 --limit=10000 - -txn-size 1000 --sleep 30

Opciones comunes

-- analizar

Especifique la herramienta para realizar la operación "ANAlizar TABLA" en la tabla después completar el archivo de datos. Especifíquelo como "--analyze=ds", donde s representa la tabla de origen yd representa la tabla de destino, o se puede especificar por separado.

--ask-pass

Solicita una contraseña en la línea de comando para proteger las contraseñas, siempre que el módulo perl-TermReadKey esté instalado.

--buffer

Especifica que los datos del búfer se vacían en el archivo especificado por la opción "--file" y se vacían al enviarlos.

Solo cuando se confirma la transacción, el archivo especificado por "--file" se vaciará automáticamente y el archivo se vaciará en el disco, lo que significa que el archivo será vaciado por el bloque del sistema operativo. por lo tanto, antes de confirmar la transacción, algunos datos se descargarán implícitamente al disco. De forma predeterminada, el archivo se descarga en el disco después de cada línea de operación.

--Batch delete

Especifica una única instrucción para eliminar bloques en lotes de filas, ejecutando implícitamente la opción "--commit-each".

Utilice una única instrucción DELETE para eliminar las filas de la tabla correspondientes a cada bloque. El método habitual es eliminar las filas una por una mediante la clave principal. La eliminación por lotes puede mejorar en gran medida la velocidad, pero si hay "DÓNDE" complejos. "condiciones, la velocidad se reducirá. Podría ser más lenta.

--[no]bulk-delete-limit (límite de eliminación masiva)

Predeterminado: Sí

Especifique agregar "--" a la declaración que ejecuta las opciones de eliminación masiva de archivos" y "--limit".

--bulk-insert

Inserta filas mediante la inserción masiva de bloques utilizando el método LOAD DATA LOCAL INFILE (las opciones "--bulk-delete" y "--commit- se especifican implícitamente " cada")

En lugar de insertar individualmente ejecutando la operación de inserción fila por fila, esto es más rápido que ejecutar la instrucción INSERT en una sola fila.

No realizaremos operaciones de inserción por lotes directamente, sino que crearemos implícitamente una tabla temporal para almacenar las filas (bloques) que deben insertarse en lotes y realizaremos una carga de datos unificada después de completar la inserción de cada bloque en la tabla temporal. Para garantizar la seguridad de los datos, esta opción forzará el uso de la opción "--eliminar por lotes", asegurando efectivamente que la inserción sea completamente exitosa antes de la eliminación.

--channel

Cuando el entorno de replicación maestro-esclavo es de replicación de múltiples fuentes, especifique qué estación maestra archivar. Esto es adecuado para la replicación de múltiples fuentes donde se corresponden varias estaciones maestras. a una estación esclava.

--charset,-A

Especifica el juego de caracteres de conexión.

--[no]check-charset

Predeterminado: Sí

Especifica una verificación para garantizar que la conexión de la base de datos tenga el mismo conjunto de caracteres que el carácter de la tabla. colocar.

--[no]check-columns

Predeterminado: Sí

Especifique la verificación para garantizar que la tabla fuente especificada por las opciones "--source" y "--dest" "La tabla de destino especificada tiene los mismos campos.

No verificará la clasificación y el tipo de campos en la tabla, solo verificará si los campos existen en la tabla de origen y en la tabla de destino. Si los campos son inconsistentes, la herramienta saldrá con un. error. Si desea desactivar esta verificación, especifique "--no-check-columns".

--check-slave-lag

Especifica pausar la operación de archivo después de que el retraso de replicación maestro-esclavo sea mayor que el valor especificado por la opción "--max-lag" . De forma predeterminada, la herramienta verifica todas las dependencias, pero esta opción solo es válida para dependencias específicas (a través del método de conexión DSN).

--check-interval

Predeterminado: 1s

Si también se especifica la opción "--check-slave-lag", el tiempo especificado por este opción Es el tiempo de pausa cuando la herramienta detecta un retraso en la replicación maestro-esclavo. Verifique cada 100 filas.

--columns, -c

Especifique los campos de la tabla que se archivarán. Si hay varios campos, sepárelos con ',' (coma).

--commit-each

Especifica el número de filas que se recuperarán y archivarán por confirmación. Esta opción deshabilita la opción "--txn-size".

Después de cada recuperación y archivado de datos de la tabla, la transacción se confirmará y el archivo especificado por la opción "--sleep" se actualizará antes de la siguiente recuperación y el tiempo de suspensión especificado por la opción "- -file". El tamaño de las transacciones se controla mediante la opción "--limit".

--host, -h

Especifique la dirección IP para conectarse a la base de datos.

-port, -P

Especifique el puerto de la base de datos al que conectarse.

--user, -u

Especifique el usuario de la base de datos conectada.

--contraseña, -p

Especifique la contraseña del usuario de la base de datos conectada.

--socket, -S

Especifica el uso de una conexión de archivo SOCKET.

--databases, -d

Especifique la base de datos a la que conectarse

--source

Especifique la tabla para realizar operaciones de archivado ; Esta opción es obligatoria y se expresa mediante el método DSN.

--dest

Utilice el método DSN para especificar la tabla de destino en la que archivar.

Si no se especifica esta opción, el valor predeterminado es usar la misma tabla que la opción "--source" que especifica la tabla fuente.

--where

Especifica que los datos que se archivarán se especifican mediante una declaración condicional WHERE, y esta opción es una opción obligatoria.

La palabra clave WHERE "no es obligatoria. Si realmente no necesita restringir la condición WHERE, puede especificar "--where 1=1".

--File

Especificar que los datos de la tabla deben archivarse en qué archivo. Utilice un esquema de nomenclatura de formato similar a MySQL DATE_FORMAT()

El contenido del archivo utiliza el mismo formato que la instrucción SELECT INTO OUTFILE en MySQL y la nomenclatura del archivo. las opciones son las siguientes:

Y: año, número, cuatro dígitos

m: mes, número (01..: mes, número (01...12)

d: mes, día, número (01...31))

H: hora (00...23))

i: minuto (00. ..59))

s: segundos (00...59))

segundos (00...59))

D: nombre de la base de datos

t: nombre de la tabla

Ejemplo: --file '/var/log/archive/Y-m-d-D.t'

-- formato-salida

Utilice la opción "-- file "Especifique el formato de salida del contenido del archivo.

El valor predeterminado si no se especifica esta opción es utilizar el carácter de tabulación como separador de campo. Si se especifica la opción, se utiliza ',' (coma) como separador de campos y se utiliza '"' (comillas dobles) para rodear los campos. Ejemplo de uso: '--output-format=dump'.

--for-update

Especifica agregar una cláusula FOR UPDATE a la instrucción SELECT ejecutada para cada archivo. --share-lock

Especifica agregar una cláusula LOCK IN SHARE MODE a cada instrucción SELECT ejecutada por el archivo.

--header

Especifica que el nombre del campo se escribe como encabezado en la primera línea del archivo.

--ignore

Especifica agregar la opción IGNORE a la instrucción INSERT.

--limit

Predeterminado: 1

Especifique el número de filas para cada declaración para obtener y archivar la tabla.

--local

Especifica no escribir declaraciones OPTIMIZAR y ANALIZAR en binlog.

--max-lag

Predeterminado: 1 s

Especifique el tiempo de retardo de replicación maestro-esclavo máximo permitido (en segundos). Si el retraso maestro-esclavo excede el valor especificado después de recuperar los datos de cada fila, la operación de archivado se pausará y el tiempo de suspensión se especificará mediante la opción "--check-interval". Una vez finalizado el tiempo de suspensión, se vuelve a comprobar el tiempo de retardo maestro-esclavo, determinado por el valor "Seconds_Behind_Master" de la consulta del esclavo. Si el retraso de replicación maestro-esclavo continúa siendo mayor que el valor especificado en este parámetro, o el esclavo deja de replicarse, la operación espera hasta que se reinicie el esclavo y el retraso es menor que el valor especificado en este parámetro.

--no-delete

Especifica no eliminar los datos de la tabla archivada.

--Optimización

Especifica que la herramienta realiza la operación "OPTIMIZAR TABLA" en la tabla después de completar el archivado de datos. El método de especificación es como "--analyze=ds", donde s representa la tabla de origen yd representa la tabla de destino, o se pueden especificar por separado.

--primary-key-only

Especifica que solo se archivan los campos de clave principal. Esta es una abreviatura de la opción "--columns=primary-key".

Es más eficiente si la operación de archivo de la herramienta es BORRAR, ya que solo es necesario leer un campo de la clave principal, en lugar de todos los campos de la fila.

--progress

Especifica imprimir información de progreso cada varias líneas, imprimiendo el tiempo actual, el tiempo transcurrido y el número de líneas que se archivarán.

--purge

Especifica que se realizará una operación de purga en lugar de una operación de archivado. Esta operación permite ignorar las opciones "--dest" y "--file". Si es solo una operación de limpieza, se puede usar en combinación con la opción "--primary-key-only" para mejorar la eficiencia.

--quiet, -q

Especifica la herramienta de ejecución silenciosa y no genera ninguna información de ejecución.

--replace

Especifique la opción de escritura "--dest" para reescribir la instrucción INSERT en una instrucción REPLACE cuando el destino especificado finalice la tabla.

--Reintentar

Valor predeterminado: 1

Especifique el número de reintentos cuando la operación de archivo encuentra un punto muerto o se agota el tiempo de espera. Cuando el número de reintentos excede el valor especificado por esta opción, la herramienta saldrá con un error.

--run-time

Especifica cuánto tiempo debe ejecutarse la operación de archivo de la herramienta antes de salir. Los sufijos de tiempo permitidos son s=segundos, m=minutos, h=horas y d=días, el valor predeterminado es s si no se especifica.

--[no]safe-auto-increment

Predeterminado: Sí

Especifique no archivar el valor máximo del valor de la columna de incremento automático (AUTO_INCREMENT ) DE ACUERDO.

Esta opción agrega una cláusula WHERE adicional a la purga del archivo para evitar que la herramienta elimine filas con el valor máximo del atributo AUTO_INCREMENT del campo ascendente de una sola columna, de modo que el valor de AUTO_INCREMENT se use después de reiniciar la base de datos. , pero esto puede provocar que las filas con los valores máximos correspondientes para los campos no se puedan archivar ni eliminar.

--set-vars

Predeterminado:

wait_timeout=10000

innodb_lock_wait_timeout=1

lock_wait_timeout= 60

Especifique valores de parámetros al archivar la herramienta. Si hay varios parámetros, sepárelos con ',' (coma). Por ejemplo, "--set-vars=wait_timeout=5000".

---skip-foreign-key-checks

Especifica deshabilitar las comprobaciones de claves externas mediante la instrucción SET FOREIGN_KEY_CHECKS = 0.

--sleep

Especifica cuánto tiempo debe dormir la herramienta antes de recuperar datos archivados mediante una instrucción SELECT. El valor predeterminado es no dormir. Las transacciones no se confirmarán antes de dormir y el archivo especificado por la opción "--file" no se actualizará. Si se especifica la opción "--commit-each", la transacción se confirma y el archivo se vacía antes de dormir.

--Estadísticas

Especifique la herramienta para recopilar e imprimir estadísticas de tiempo de operación.

Las estadísticas de muestra son las siguientes:

'

Comenzó en 2008-07-18T07:18:53 y finalizó en 2008-07-18T07:18 :53

Fuente: D=db, t=table

SELECCIONAR 4

INSERTAR 4

BORRAR 4

Porcentaje de tiempo de recuento de acciones

compromiso 10 0,1079 88,27

SELECCIONAR 5 0,0047 3,87

Eliminar 4 0,0028 2,29

Insertar 4 0,0028 2,28

Otro 0 0.0040 3.29

--txn-size

Predeterminado: 1

Especifique el número de registros que se procesarán para cada transacción. Un valor de 0 deshabilita la funcionalidad de transacción.

--Version

Muestra la versión de la herramienta y sale.

--[no]version-check

Predeterminado: sí

Comprueba las últimas versiones del kit de herramientas Percona, MySQL y otros programas.

--why-quit

Especifica que la herramienta imprime el motivo de salida si sale que no sea debido al número de líneas archivadas completadas.

Esta opción es muy conveniente cuando se realizan tareas de archivado automático junto con la opción "--run-time". La opción "--run-time" se utiliza para determinar si la tarea de archivado se completa dentro del archivo. tiempo especificado. Si también se especifica la opción "--stats", se imprimen todos los motivos de salida.

2. Principios de análisis

Con base en la salida de los registros generales, organizamos las series temporales de la siguiente manera

3. >Ver Parece que no hay ningún problema con el proceso, pero ¿qué pasa si los datos de la tabla original se escanean y los datos antiguos cambian durante el proceso de inserción en la nueva tabla?

Pensando en esto, establecimos un punto de interrupción en la línea 6839 de pt-archiver según el código fuente

Luego, hice las siguientes operaciones en varias ventanas de sesión

p>

Finalmente, el resultado de pt-archiver es el siguiente:

# Hay una actualización de software disponible:

RECUENTO DEL TIEMPO TRANSCURRIDO

2020 -04-08T09: 13:21 0 0

2020-04-08T09: 13:21 0 1

Comenzó en 2020-04-08T09: 13:21, finalizó en 2020 -04-08T09: 13:51

Fuente: A=utf8mb4, D=xucl, P=3306, h=127.0.0.1, p=..., t=t1, u=xucl

Destino: A=utf8mb4, D=xucl_archive, P=3306, h=127.0.0.1, p=..., t=t1, u=xucl

SELECCIONAR 1

INSERTAR 1

ELIMINAR 1

Porcentaje de tiempo de recuento de acciones

suspensión 1 30,0002 99,89

insertar 1 0,0213 0,07

commit 2 0.0080 0.03

seleccionar 2 0.0017 0.01

eliminar 1 0.0005 0.00

otro 0 0.0008 0.00

Obviamente , id=3 Los registros de no están archivados (cambiamos la columna de condición aquí). La columna condicional se cambia aquí, pero en la producción real, podemos cambiar otras columnas, lo que resulta en datos archivados inexactos)

Entonces, ¿cómo solucionamos esta situación?

Obviamente, la base de datos puede evitar que otros programas modifiquen los datos correspondientes agregando un bloqueo exclusivo en la base de datos. pt-archiver realmente nos ha ayudado a considerar esta situación. pt-archiver proporciona dos opciones

--for-update: agregue el modificador FOR UPDATE a la instrucción SELECT

--share-. lock: agregue el modificador LOCK IN SHARE MODE a la instrucción SELECT

Cuarto resumen

pt-archiver como herramienta de archivo es, sin duda, la operación y el mantenimiento diario de MySQL DBA, así como uno de las herramientas más poderosas en el proceso de uso, una vez que domine el método de uso, también podrá comprender sus principios

Durante el proceso de archivado, es mejor bloquear los registros de archivo para evitar datos archivados inexactos

En un entorno maestro-esclavo, es mejor controlar la velocidad de archivado para evitar causar retrasos entre maestro-esclavo

Intente controlar el tamaño del bloque tanto como sea posible, no demasiado grande. Intente controlar el tamaño del bloque tanto como sea posible para evitar provocar grandes transacciones