Archivar tablas MySQL en ClickHouse

En esta publicación de blog, hablaré sobre cómo archivar tablas MySQL en ClickHouse para su almacenamiento y análisis.

¿Por qué archivar?

Los discos duros son baratos hoy en día, pero almacenar una gran cantidad de datos en MySQL no es práctico y puede causar todo tipo de cuellos de botella en el rendimiento. Por nombrar solo algunos problemas:

  1. Cuanto mayor sea la tabla y el índice, más lento será el rendimiento de todas las operaciones (lectura y escritura)
  2. La copia de seguridad y la restauración de terabytes de datos es más desafiante, y si necesitamos redundancia (replicación esclava, agrupamiento, etc.) almacenaremos todos los datos N veces.

La respuesta es archivar datos antiguos. Archivar no significa necesariamente que los datos se eliminarán de forma permanente. En su lugar, los datos archivados se pueden almacenar a largo plazo (es decir, AWS S3) o cargar en una base de datos especial que está optimizada para almacenamiento (con compresión) y generación de informes. Los datos están entonces disponibles.

De hecho, hay varios casos de uso:

  • A veces, los datos solo deben almacenarse (es decir, con fines normativos), pero no deben estar fácilmente disponibles (no son datos de «cara del cliente»).
  • Los datos pueden ser útiles para la depuración o la investigación (es decir, registros de aplicaciones o acceso)
  • En algunos casos, los datos deben estar disponibles para el cliente (es decir, informes históricos o transacciones bancarias de los últimos seis años).

En todos estos casos, podemos sacar los datos más antiguos de MySQL y cargarlos en una solución de «grandes datos». Incluso si los datos van a estar disponibles, podemos moverlos del servidor MySQL principal a otro sistema. En esta publicación de blog, analizaré el archivo de tablas MySQL en ClickHouse para almacenamiento a largo plazo y consultas en tiempo real.

¿Cómo archivo?

Digamos que tenemos una tableta 650G que realiza un seguimiento del historial de todas las transacciones y queremos comenzar a archivar. ¿Cómo podemos abordar esto?

Primero, necesitamos dividir esta tabla en «viejo» y «nuevo». Suponga que la tabla no está particionada (las tablas divididas son mucho más fáciles de manejar). Por ejemplo, si tenemos datos de 2008 (hasta diez años) pero solo necesitamos almacenar los datos de los últimos dos meses en el entorno MySQL principal, eliminar los datos antiguos sería un desafío. Entonces, en lugar de eliminar el 99% de los datos de una tabla enorme, podemos crear una nueva tabla y cargar en ella los datos más recientes. Luego cambie el nombre (intercambie) las tablas. El proceso puede verse así:

  1. CREAR TABLA transacciones_nuevas LIKE transacciones
  2. INSERTAR EN transacciones_nuevas SELECCIONAR * DESDE transacciones DONDE trx_date> ahora () – intervalo de 2 meses
  3. RENOMBRAR TABLA transacciones A transacciones_antiguas, transacciones_nuevas A transacciones

Segundo, necesitamos transferir la transacción_antiguo a ClickHouse. Esto es simple: podemos canalizar datos de MySQL a ClickHouse directamente. Para demostrar esto, utilizo el proyecto Wikipedia: Estadísticas (un registro real de todas las consultas en las páginas de Wikipedia).

Cree una tabla en ClickHouse:

Tenga en cuenta que he usado el nuevo Partición personalizada de ClickHouse. No necesita crear una columna de datos separada para asignar la tabla en MySQL a la misma estructura de tabla en ClickHouse.

Ahora puedo «canalizar» datos directamente desde MySQL a ClickHouse:

Tercero, necesitamos establecer un proceso de archivo constante para que los datos se eliminen de MySQL y se transfieran a ClickHouse. Para ello, podemos utilizar la herramienta «pt-archiver» (parte de Percona Toolkit). En este caso, primero podemos archivarlo en un archivo y luego cargar el archivo en ClickHouse. Aquí hay un ejemplo:

Elimine datos de MySQL y cárguelos en un archivo (tsv):

Sube el archivo a ClickHouse:

La versión más reciente de pt-archiver también puede usar un formato CSV:

¿Cuanto más rápido?

De hecho, es mucho más rápido en ClickHouse. Incluso las consultas que se basan en escaneos de índice pueden ser mucho más lentas en MySQL en comparación con ClickHouse.

Por ejemplo, en MySQL solo contar el número de filas para un año puede tomar hasta 34 segundos (escaneo de índice):

0.00

ES

/

s

.

)

Tamaño del disco En mi blog anterior sobre la comparación de ClickHouse con Apache Spark en MariaDB, también comparé el tamaño del disco. Por lo general, podemos esperar una disminución de 10x a 5x en el tamaño del disco en ClickHouse para la compresión. Wikipedia: Estadísticas, por ejemplo, contiene URI actuales, que pueden ser bastante grandes debido al nombre del artículo/frase de búsqueda. Esto se puede entender muy bien. Si usamos solo números enteros o usamos hash MD5 / SHA1 en lugar de almacenar URI reales, podemos esperar una compresión mucho menor (es decir, 3x). Incluso con una relación de compresión de 3x, sigue siendo bastante bueno como almacenamiento a largo plazo. Conclusión A medida que los datos en MySQL continúan creciendo, el rendimiento de todas las consultas seguirá disminuyendo. Por lo general, las consultas que inicialmente tomaban milisegundos ahora pueden tomar segundos (o más). Se necesitan muchos cambios (código, MySQL, etc.) para hacerlo más rápido. El objetivo principal del archivado de datos es aumentar el rendimiento («hacer que MySQL vuelva a ser rápido»), reducir los costos y mejorar la facilidad de mantenimiento (copia de seguridad/restauración, clonación de esclavos de replicación, etc.). El almacenamiento de ClickHouse le permite conservar datos antiguos y ponerlos a disposición para informes.

Author: Ing. Luis

A lo largo de conocer Windows y otros sistemas operativos me eh encontrado con diversos tipos de error, ahora brindo soluciones según mi experiencia-

Deja un comentario