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:
- Cuanto mayor sea la tabla y el índice, más lento será el rendimiento de todas las operaciones (lectura y escritura)
- 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í:
- CREAR TABLA transacciones_nuevas LIKE transacciones
- INSERTAR EN transacciones_nuevas SELECCIONAR * DESDE transacciones DONDE trx_date> ahora () – intervalo de 2 meses
- 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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 dieciséis 17 |
CREAR MESA wikistatu ( identificación Empezando, dt Fecha y hora, proyecto Cuerda, subproyecto Cuerda, caminar Cuerda, tiros UInt64, cortar UInt64 ) MOTOR = MergeTree ESPARTO POR hasta AAAAMMDD(dt) PEDIDO POR dt OK. 0 póngase en fila en colocar. Pasado: 0.010 segundo. |
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:
|
mysql –próximamente –h anfitrión local wikiestado –NBe «SELECCIONE concat (id, ‘,»‘, dt,’«,»‘, proyecto,’«,»‘subproyecto,’«,»‘caminar«, ‘, visitas,’, ‘, tamaño) DESDE wikiistats» | casa de clics–cliente –D wikiestado –preguntar=«INSERTAR EN FORMATO CSV wikistatus» |
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):
|
punto–archivador –fuente h=anfitrión local,D=wikiestado,t=wikiestado,I=dt –donde «dt <= '2018-01-01 0:00:00'" –Archivo cargar_a_clickhouse.TXT –demasiado–limpiar –límite 100,000 –Progreso=100,000 HORA RUTA CONTAR 2018–01–25T18:19:59 0 0 2018–01–25T18:20:08 8 100,000 2018–01–25T18:20:17 18 200000 2018–01–25T18:20:26 27 300000 2018–01–25T18:20:36 36 400000 2018–01–25T18:20:45 45 500000 2018–01–25T18:20:54 54 600000 2018–01–25T18:21:03 64 700000 2018–01–25T18:21:13 73 800000 2018–01–25T18:21:23 83 900000 2018–01–25T18:21:32 93 1000000 2018–01–25T18:21:42 102 1100000 ... |
Sube el archivo a ClickHouse:
|
gato cargar_a_clickhouse.TXT | casa de clics–cliente –D wikiestado –preguntar=«INSERTAR EN FORMATO TSV wikistatus» |
La versión más reciente de pt-archiver también puede usar un formato CSV:
|
punto–archivador –fuente h=anfitrión local,D=wikitest,t=wikiestado,I=dt –donde «dt <= '2018-01-01 0:00:00'" –Archivo cargar_a_clickhouse.csv –producción–formato CSV –demasiado–limpiar –límite 10000 –Progreso=10000 |
¿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):
|
de wikistatus donde dt entre ‘2017-01-01 00:00:00’ y ‘2017-12-31 00:00:00’; + ———– + | contar | + ———– + | 103161991 | + ———– + 1 fila en conjunto (34.82 seg) mysql> explicar seleccionar contar de wikistatus donde dt entre ‘2017-01-01 00:00:00’ y ‘2017-12-31 00: 00: 00’G ******************* ******** 1. fila **************************** id: 1 select_type: tabla SIMPLE: particiones de wikistatus: NULL tipo: rango posibles_claves: dt clave: dt key_len: 6 ref: NULL fila: 227206802 filtrado: 100.00 Extra: Uso de donde; Usando el índice 1 fila en conjunto, 1 advertencia (0.00 seg) 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 dieciséis 17 18 19 20 |
2122 23 mysql>Seleccionecontar ( * ) sí wikiestado donde dt Entre‘2017-01-01 00:00:00’ y ‘2017-12-31 00:00:00’ ;+ ———– +|contar (* ) | + ———– + | 103161991 | + ———– + 1 póngase en filaen colocar( 34.82 segundo) mysql >expliqueSeleccionecontar ( * ) sí wikiestado donde dt Entre‘2017-01-01 00:00:00’ y ‘2017-12-31 00:00:00’ GRAMO *************************** 1.póngase en fila*************************** identificación:1 seleccione tipo:SIMPLE mesa:wikiestado particiones:NULO escribe: gama clave_posible:dt llave:dt key_len:6 árbitro:NULO póngase en fila:227206802 filtrado:100.00 Extra: Usar donde ; Usar índice 1póngase en fila en colocar ,1 advertencia( |
0.00
|
)En ClickHouse, toma solo 0.062 segundos: 🙂 seleccione la cuenta de wikistatus donde dt entre toDateTime (‘2017-01-01 00:00:00’) y toDateTime (‘2017-12-31 00:00:00’); SELECCIONAR recuentoDESDE wikistatus DONDE (dt> = toDateTime (‘2017-01-01 00:00:00’)) Y (dt <= toDateTime ('2017-12-31 00:00:00')) ┌───count ( ) ─┐ │ 103161991 │ └──────────── 1 fila en conjunto. Aprobado: 0,062 seg. 103,16 millones de archivos procesados, 412,65 MB (1670 millones de archivos/s., 6,68 GB/s.):) Seleccione contar ( * ) síwikiestadodondedt Entre aFechaHora(‘2017-01-01 00:00:00’)y aFechaHora ( ‘2017-12-31 00:00:00’); SELECCIONE contar ( *) SÍwikiestado INDUNI(dt>= aFechaHora (‘2017-01-01 00:00:00’ )) Y(dt<= aFechaHora (‘2017-12-31 00:00:00’)) ┌─── contar ( ) ─┐ ⁇ 103161991 ⁇ └──────────── 1póngase en fila en colocar. Pasado : 0.062 segundo. Procesada 103.16 millonespóngase en fila , 412.65MEGABYTE(1.67mil millones póngase en fila /s.,6.68 |
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.
