El blog de Luis Medel
Si alguna vez has trabajado con grandes volúmenes de datos (y cuando me refiero a grandes, me refiero a millones de registros generados de manera más o menos periódica) te habrás encontrado con el eterno problema: procesos que hace unas semanas tardaban pocas horas en finalizar, comienzan a ser más y más pesados hasta el punto de “tumbar” el servidor donde corren. Si no puedo ampliar mis máquinas, ¿cómo escalo?
Si eres un DBA no vas a descubrir nada nuevo en esta entrada. Por el contrario, si eres desarrollador, administras tus propios servidores y crees que estás en condiciones de generar un volumen importante de datos para explotarlos en un datawarehouse, puede que te interese lo que vas a leer a continuación.
Hay muchas situaciones que nos pueden obligar a generar una cantidad enfermiza de datos (ojo, datos!=información, pero sobre eso ya escribiré en un futuro). Supongamos que montamos un datawarehouse para analizar el tráfico de la Biblioteca Cervantes Virtual.
Comenzamos con la creación de una tabla en la que se guardarán los logs de acceso para poder extraer información útil aplicando minería de datos. Teniendo en cuenta que esta web sirve más de un millón de páginas al mes, es evidente que cada mes tendremos más de un millón de registros extra en dicha tabla. Un par de años más tarde estaríamos hablando de más de 25 millones de registros en una sola tabla. No importa lo óptimos que sean nuestros índices. No importa lo potente que sea el servidor ni qué gestor de bases de datos estemos utilizando. Sencillamente es una cantidad difícil de manejar
A grandes rasgos, el particionado de tablas consiste en dividir un conjunto lógico de registros (una gran tabla) en pequeñas tablas almacenadas de manera independiente. Dicho de otro modo, se trata de “segmentar” la información en base a algún criterio (generalmente discriminando por los campos que formen parte de la PK).
La primera vez que vi el particionado en funcionamiento fue en un datawarehouse gobernado por un Señor Oracle. Gracias a esta técnica podía trabajar con tablas con incrementos de 7 u 8 millones de registros mensuales de manera cómoda y rápida.
Volviendo al ejemplo anterior, si tras dos años de recogida de información de acceso queremos obtener todos los REFERER distintos del mes de Octubre de 2008, lanzaremos una consulta similar a ésta…
select distinct referer
from logs_acceso
where fecha between '01/10/2008' and '31/10/2008'
…nos haremos un café (probablemente dos o tres) y abriremos un buen libro para disfrutar de la tarde.
En vez de trabajar con un conjunto tan grande de registros, podemos particionar la tabla en base al mes y el año en el que se realiza la visita.
Aplicando este criterio de particionado obtendríamos un total 24 tablas en las que se repartirían todos los registros de la tabla original en función del valor del campo “fecha”.
Dependiendo del motor que estemos utilizando el funcionamiento interno puede variar pero a grandes rasgos, lo que obtenemos es un puñado de tablas de la siguiente manera:
Una tabla para Enero de 2006, otra para Febrero y así sucesivamente. Es decir, una tabla por cada mes y año que tengamos.
Pero no te asustes ya que estas tablas estarán mapeadas de manera transparente sobre la tabla original y para tí es como si no existieran. Podemos lanzar consultas de manera indiscriminada sobre la tabla original sin necesidad de tener en cuenta el particionado.
Si lanzásemos la consulta anterior…
select distinct referer
from logs_acceso
where fecha between '01/10/2008' and '31/10/2008'
…obtendríamos el resultado mucho más rápido ya que el optimizador de consultas, al encontrarse el campo “fecha” en la cláusula WHERE, filtraría únicamente los registros de la partición correspondiente.
Gracias a la técnica de particionado el tamaño de los índices se reduce considerablemente, con lo que aquellas partes más utilizadas pueden caber en memoria.
Pero no sólo te va a permitir mejorar el rendimiento de las consultas. Los procesos de carga y actualización masivos también se pueden acelerar en gran medida trabajando sobre una tabla y añadiéndola posteriormente al “conjunto de particiones”.
Los procesos de borrado masivos también pueden ser tan sencillos como lanzar DROP TABLE sobre las particiones a eliminar.
Y por último, aprovechando que en la mayoría de gestores de bases de datos modernos cada tabla puede residir en un tablespace distinto, podemos alojar las particiones más utilizadas en discos más modernos y rápidos y dejar aquellas particiones menos usadas en discos más lentos y baratos.
Como he mencionado antes, cada motor de bases de datos lo implementa a su modo y extiende la sintaxis del lenguaje a su manera. Esto es así porque el estándar SQL no trata el concepto de partición.
Oracle permite habilitar el particionado directamente sobre las tablas y admite algún “azúcar sintáctico” en las consultas, MySQL parece que ofrece la misma facilidad que Oracle (aunque nunca he usado MySQL en un datawarehouse), PostgreSQL lo hace a través de herencia de tablas (un método que me gusta bastante) y SQL Server lo hace a “su” manera.
No soy ni mucho menos un experto en la materia y en la red vas a encontrar información mucho más útil al respecto pero al menos espero haberte abierto alguna puerta.
Si estás interesado en aplicar el particionado te recomiendo que leas la documentación de tu motor de bases de datos para aprender cómo se habilita y cómo se administra. Por supuesto, no tengo inconveniente en ayudarte en todo lo que pueda :)
Algunos de los que me leeis trabajáis con bases de datos medianas/grandes. Lo sé, no os escondáis :)
¿Habéis hecho ya uso de ésta u otra técnica para optimizarlas?
Imagen de cabecera cortesía de teejayhanton.
No hay comentarios