Particionamiento con MySQL, comparativa en tablas MyISAM

Publicado: 
Comentarios:  Ninguno

Índice de contenidos

Introducción y objetivos

En este artículo presento los resultados obtenidos de un conjunto de tests de inserción de datos y de lectura que he realizado sobre MySQL utilizando particionamiento de tablas

Las pruebas las hemos hecho con la versión 5.5.10 de MySQL y usando MyISAM como motor de almacenamiento. La estructura de la base de datos y el código PHP utilizado como base para la realización de los tests son los mismos que los usados para comparar MongoDB contra MySQL.

El objetivo es comprobar si ganamos o perdemos rendimiento y en qué condiciones dividiendo una tabla con decenas de millones de registros correspondientes a logs de acceso en distintas particiones.

Se han comparado las mismas operaciones realizadas sobre una tabla sin particionar y la misma dividida en 12 particiones (particionamiento por fecha, dividiendo por mes). Las operaciones han sido:

  • de escritura simple 10, 20 y 30 millones de registros en bucle uno detrás de otro
  • de escritura concurrente de 1 millón de registros con 50 usuarios simultáneos
  • de inserción masiva 250 millones de registros a partir de un CSV
  • de lectura simple probando varias operaciones: buscar registros por id, contar registros para un mes, contar registros entre entre dos fechas, y obtener totales para un dominio

Para elegir un particionado adecuado debemos entender bien cuales son los patrones de acceso que va a utilizar la aplicación. En nuestro caso serán informes por rangos de fechas, mensuales fundamentalmente, con lo que lo lógico crear particiones para cada mes

Me he basado en los tests realizados por Brooks Johnson y Phillip Tellis, que muestran en las dos siguientes presentaciones

Volumen de los datos

Se ha trabajado con la siguiente cantidad de datos:

  • 70.000 usuarios
  • 70.000 IP’s
  • 1.300.000 dominios
  • Una tabla de registros de log con 250 millones de registros

Ésta última tabla es la realmente importante, ya que es la que se ha definido sin particiones y con particiones para comparar distintas operaciones sobre ella. Esta tabla tiene tres índices, un id autonumérico que sirve de clave principal y dos índices para el campo de dominio y el campo de fecha y hora.

En tamaño en disco el espacio ocupado para la tabla de logs es el siguiente:

  • Tabla sin particionar: 37.303 Mb (29.067 de datos + 8.236 de índices)
  • Tabla particionada: 38.561 Mb (29.067 de datos + 9.494 de índices)

Como podemos observar una tabla particionada ocupa más espacio en disco que la misma sin particionar, en particular los índices (casi un 10% más para los índices que usamos en la tabla de logs)

Parametrización de MySQL

Los tests los haremos en una máquina con 12 Gigas de RAM disponible y en la que la carpeta de archivos temporales (generados a la hora de crear los índices) se ha establecido en una partición aparte para que disponga de espacio.

Siguiendo las recomendaciones de varios blogs y respuestas a preguntas encontradas en Internet (aquí y aquí, por ejemplo) y para aliviar la lentitud de MySQL a la hora de generar los índices cuando el tamaño de éstos supera la memoria RAM de la máquina he establecido en MySQL los siguientes parámetros de configuración:

[mysqld]
general_log = 0
tmpdir  = /users7/mys55_tmp/
bulk_insert_buffer_size = 2G
myisam_sort_buffer_size = 2G
key_buffer_size = 1G
myisam_max_sort_file_size = 39G
[myisamchk]
key_buffer_size = 1G
tmpdir  = /users7/mys55_tmp/

Sintaxis para la creación de las tablas

La estructura de la tabla “NonFTP_Access_log” sin particionar corresponde a la definida en la siguiente consulta SQL CREATE TABLE

CREATE TABLE `NonFTP_Access_log` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `clientip` VARCHAR(15) NOT NULL,
  `user` CHAR(7) NOT NULL,
  `datetime` datetime NOT NULL,
  `method` VARCHAR(10) NOT NULL,
  `protocol` VARCHAR(10) NOT NULL,
  `domain` VARCHAR(255) NOT NULL,
  `uri` VARCHAR(100) NOT NULL,
  `return_code` SMALLINT(5) UNSIGNED NOT NULL,
  `size` INT(10) UNSIGNED NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `domain` (`domain`),
  INDEX `datetime` (`datetime`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

Y con particiones, una para cada mes del 2012 a la siguiente

CREATE TABLE `NonFTP_Access_log` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `clientip` VARCHAR(15) NOT NULL,
  `user` CHAR(7) NOT NULL,
  `datetime` datetime NOT NULL,
  `method` VARCHAR(10) NOT NULL,
  `protocol` VARCHAR(10) NOT NULL,
  `domain` VARCHAR(255) NOT NULL,
  `uri` VARCHAR(100) NOT NULL,
  `return_code` SMALLINT(5) UNSIGNED NOT NULL,
  `size` INT(10) UNSIGNED NOT NULL,
  PRIMARY KEY (`id`, `datetime`),
  INDEX `domain` (`domain`),
  INDEX `datetime` (`datetime`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
partition BY range COLUMNS (datetime)
(
partition p1 VALUES less than ('2012-02-01 00:00:00') engine = myisam,
partition p2 VALUES less than ('2012-03-01 00:00:00') engine = myisam,
partition p3 VALUES less than ('2012-04-01 00:00:00') engine = myisam,
partition p4 VALUES less than ('2012-05-01 00:00:00') engine = myisam,
partition p5 VALUES less than ('2012-06-01 00:00:00') engine = myisam,
partition p6 VALUES less than ('2012-07-01 00:00:00') engine = myisam,
partition p7 VALUES less than ('2012-08-01 00:00:00') engine = myisam,
partition p8 VALUES less than ('2012-09-01 00:00:00') engine = myisam,
partition p9 VALUES less than ('2012-10-01 00:00:00') engine = myisam,
partition p10 VALUES less than ('2012-11-01 00:00:00') engine = myisam,
partition p11 VALUES less than ('2012-12-01 00:00:00') engine = myisam,
partition p12 VALUES less than ('2013-01-01 00:00:00') engine = myisam
);

Para evitar el problema descrito aquí debemos particionar usando columns

Podemos observar que para poder particionar la tabla por rangos de fechas hemos tenido que añadir el campo utilizado (“datetime”) a la clave primaria.

Por otra parte las clases PHP desarrolladas para generar datos aleatorios han sido modificadas, en particular a la clase MySQLRandomElements se le han añadido funcionalidades.

Resultados de los tests

Los tests se han repetido varias veces para comprobar que los resultados son coherentes, descartando si procede los resultados extremos. Los datos mostrados corresponden a los resultados de uno de ellos.

Tests de escritura simple

La prueba ha consistido en insertar 10 millones de registros, luego añadir 20 millones más y en un tercer paso añadir 30 millones. La fecha y hora de cada uno es un valor aleatorio entre el 1 de enero y el 31 de diciembre del 2012.

Inserción de 10 millones20 millones30 millones
Tabla sin particionar2h10m57s4h36m16s7h0m42s
Tabla particionada2h19m55s4h43m23s7h15m22s

El test se ha hecho con un script ejecutado localmente. Las pruebas se han repetido varias veces con resultados similares.

La conclusión es que las inserciones individuales tienen aproximadamente el mismo rendimiento. En tablas particionadas ha habido entre un 2% y un 7% de pérdida, algo normal dado el trabajo extra que supone a nivel gestionar las particiones.

Test de escritura simple 2

En este caso vamos a insertar un millón de registros, añadiéndolos a los 60 millones ya existentes, pero siendo todos de la última semana del año, de forma que todas las inserciones se harán sobre la última partición en el caso de usar la tabla particionada.

El test lo he repetido tres veces, recreando la tabla con los mismos datos y reiniciando el servidor.

Inserción de 1 millón en la semana 52

Tiro 1Tiro 2Tiro 3
Tabla sin particionar13m55s13m51s13m48s
Tabla particionada14m49s14m40s14m19s

Los resultados son los mismos que en el caso anterior. Las inserciones individuales tienen aproximadamente el mismo rendimiento, con una muy ligera pérdida en el caso de tablas particionadas.

Tests de escritura multiusuario (en todo el rango de fechas)

En este caso hemos utilizado la herramienta Apache JMeter lanzando peticiones en red desde mi puesto al servidor MySQL.

He configurado JMeter para generar 50 usuarios virtuales (hilos) en el que cada uno de ellos hace 20.000 peticiones a un script PHP que añade un registro a la tabla (el registro es siempre el mismo, varía la fecha de forma aleatoria entre el 1 de enero y el 31 de diciembre del 2012). Mostramos las medianas.

Escritura de 1 millón de registros (50 usuarios x 20.000)

Tiro 1Tiro 2Tiro 3
Tabla sin particionar112ms108ms115ms
Tabla particionada128ms103ms106ms

Las inserciones individuales multiusuario tienen aproximadamente el mismo rendimiento

Tests de escritura multiusuario 2 (en la última semana)

Este tests es igual al anterior pero las escrituras se realizan con fecha de la última semana de diciembre, para provocar que todas las escrituras se realicen en la última partición.

Este escenario sería más realista, ya que los logs se van grabando con una fecha, la actual, que va creciendo. Mostramos las medianas.

Inserción de 1 millón en la semana 52 (50 usuarios)

Tiro 1Tiro 2Tiro 3
Tabla sin particionar138ms104ms118ms
Tabla particionada126ms127ms131ms

No se observa aumento de rendimiento aunque las inserciones afecten a una sola de las particiones

Tests de inserción masiva

La prueba ha consistido en la inserción de 250 millones de registros a partir de un fichero CSV.

El problema fundamental a la hora de hacer este tipo de operaciones es la generación de los índices, que en el caso de superar la memoria RAM disponible se convierte en una operación terriblemente lenta por parte de MySQL (puede llegar a llevar días).

El test se ha hecho con un script ejecutado localmente repitiendo las pruebas varias veces. El script sigue las recomendaciones de la documentación del producto indicadas en “Bulk Data Loading for MyISAM Tables“http://dev.mysql.com/doc/refman/5.5/en/optimizing-myisam-bulk-data-loading.html y hace lo siguiente:

  • Desactiva los índices en la tabla
  • Carga los datos del fichero CSV
  • Vuelve a activar los índices

La desactivación y activación de los índices en tablas MyISAM no particionadas se debe hacer con la herramienta de consola myisamchk, en vez de usar el comando SQL ALTER TABLE, ya que su rendimiento es mucho mayor

Los resultados obtenidos, comparando el tiempo total de inserción y para varias pruebas son:

Importación de 250 millones de registros desde un CSV

Tiro 1Tiro 2Tiro 3
Sin particionar (índices con myisamchk)2h26m21s2h25m16s2h24m41s
Particionada2h40m50s2h30m8s2h34m21s

La conclusión es que la generación de índices es mucho más rápida en tablas particionadas, ya que hemos conseguido que quepan en memoria lo que antes provocaba una reconstrucción muy lenta por parte de MySQL.
De todas formas, para tablas MyISAM el uso de la herramienta myisamchk permite solventar este problema, con lo que para la carga de datos masiva no ganamos rendimiento al usar tablas particionadas (al menos con MyISAM).

Tests de lectura

En este caso se han desarrollado 3 scripts que hacen las siguientes operaciones simples de lectura:

  • Seleccionar un registro por id, repitiendo la operación 10.000 veces (se ha modificado la estructura de la base de datos para incluir un id autonumérico como clave única)
  • Contar el número de registros para cada mes del año 2012
  • Contar el número de registros entre dos fechas, con más de un mes entre ellas.

Los tests los hemos repetido 5 veces cada uno, para una tabla particionada y sin particionar, parando y arrancando el servidor antes de la ejecución de cada script.

Búsqueda de 10.000 registros por id

Tiro 1Tiro 2Tiro 3
Tabla sin particionar2m20s2m16s2m8s
Tabla particionada4m13s3m42s3m15s

Contar el número de registros para cada mes

Tiro 1Tiro 2Tiro 3
Tabla sin particionar2m44s2m40s2m34s
Tabla particionada3m11s3m2s2m58s

Contar el número de registros entre dos fechas

Tiro 1Tiro 2Tiro 3
Tabla sin particionar1m44s1m38s1m44s
Tabla particionada1m42s1m45s1m45s

Sorprendentemente en los tres casos (incluido la operación de contar registros por mes, que a priori debería ir mejor) los tiempos para la tabla no particionada y particionada son casi equivalentes.

Conclusiones

En este escenario concreto,tabla MyISAM de 250 millones de registros, dividida en 12 particiones, una por mes, NO GANAMOS RENDIMIENTO AL USAR PARTICIONES.

Con lo que, con estos datos, el uso de particiones sería recomendable

  • Para gestionar el espacio en disco de volúmenes enormes de datos, pudiendo dividir una tabla entre varios discos.
  • Si al dividir las tablas conseguimos que los índices entren en memoria aceleraremos operaciones del tipo ALTER TABLE REBUILD, OPTIMIZE, ANALYZE, REPAIR, CHECK ya que podemos hacerlas sobre una partición únicamente
  • Para acelerar el borrado de datos en determinados casos (borrar una partición es mucho más rápido que borrar los registros). En este ejemplo suprimir los registros correspondientes a meses pasados sería más rápido.

Estos resultados CONTRADICEN lo descrito en las presentaciones de Brooks Johnson y Phillip Tellis. El resultado que esperaba era un aumento de rendimiento para:

  • inserciones que afecten a una sola de las particiones
  • operaciones agregadas o de lectura sobre una sola de las particiones
  • inserciones concurrentes (multiusuario)

Enlaces de interés

El trabajo realizado se ha basado fuertemente en los trabajos de Giuseppe Maxia, Brooks Johnson y Phillip Tellis, mostrado en las siguientes presentaciones:


Etiquetas: , ,

Comentarios

Actualmente no hay comentarios a este artículo.

Añadir comentarios ...

Escribe debajo tu comentario. Los campos marcados con * son obligatorios. Tienes que previsualizar tu comentario antes de enviarlo definitivamente.