Laboratorio 1. Iterar millones de registros.

Hace algunos días tuve que revisar un requerimiento para generar un archivo de texto a partir de los datos de varias tablas. Al realizar una proyección del número de registros que se iban a obtener bajo ciertas condiciones, se detectó que podría crecer a más de 500 mil registros. Esto supone varias preocupaciones: espacio en disco duro, latencia de la red al descargar el archivo, tiempos de respuesta, consumo de recursos, etc.

Bajo el contexto en el que me encontraba, el tiempo de respuesta de la funcionalidad, no era un tema preocupante, pero sí lo era la cantidad de recursos utilizados por un tiempo prolongado. Por recursos me refiero a memoria RAM y porcentaje de procesamiento en el servidor de aplicaciones.

Para este primer laboratorio puntualmente, expondré cómo abordé el diseño para el tema de la iteración de registros - exagerándolo un poco-, teniendo en cuenta que es requerido hacerlo desde el contenedor de la aplicación y no desde la base de datos, pues existen ciertas condiciones funcionales que, por estándar interno, no deben estar en la Base de Datos.

Observé varios foros con preguntas similares, pero no encontré un enfoque práctico, mas bien teórico de cómo abordar este tipo de problemas y, basado en mis propias convicciones y en lo que he aprendido, considero que para decidir desde la perspectiva de un Arquitecto y para convencer al equipo Técnico, es requerido obtener algunas mediciones y desarrollar un prototipo. Veamos cómo lo conseguí y qué solución tuvo, bajo mis condiciones, el mejor comportamiento.

Materiales requeridos

1 base de datos para realizar el experimento, en PostgreSQL 9.2

create database lab_large_items;

Crear 3 tablas similares y con una llave foránea en común tal que tengan integridad referencia entre ellas.

create table lab1_table1(

fieldpk numeric not null primary key ,

field2 varchar(10) ,

field3 varchar(10) ,

field4 varchar(10) ,

field5 varchar(10) ,

field6 varchar(10)

)

create table lab1_table2(

fieldpk numeric not null primary key ,

fieldfk numeric not null,

field2 varchar(10) ,

field3 varchar(10) ,

field4 varchar(10) ,

field5 varchar(10) ,

field6 varchar(10) ,

constraint fk_l1t1_l1t2_pk foreign key (fieldfk) references lab1_table1(fieldpk)

)

create table lab1_table3(

fieldpk numeric not null primary key ,

fieldfk numeric not null,

field2 varchar(10) ,

field3 varchar(10) ,

field4 varchar(10) ,

field5 varchar(10) ,

field6 varchar(10) ,

constraint fk_l1t1_l1t3_pk foreign key (fieldfk) references lab1_table2(fieldpk)

)

1 prototipo stand alone que permita la conexión a la base de datos. Este puede ser descargado aquí (Archivo / Descargar).

Por mi parte tengo un Mac Book Pro i7 quad core con 8GB de memoria RAM, el cual alberga una máquina virtual de Windows XP con 1 procesador asignado, 2GB de RAM y 20 GB de espacio en disco duro.

1 IDE de elección. En mi caso Eclipse Juno. Cliente de base de datos para PostgreSQL, preferible PGAdmin 3. Máquina virtual de java, 1.6.0.43 en mi máquina y algo de paciencia.

Preparación

Crear una función o procedimiento almacenado, que permitida poblar las tablas fácilmente y de forma exhaustiva [1] (hasta 10 millones de registros por cada 1).

create or replace function populate_tables ()

returns VOID as $$

BEGIN

FOR i IN 1..10000000 LOOP

insert into lab1_table1 values (i, 'the test','the test','the test','the test','the test');

insert into lab1_table2 values (i, 1, 'the test','the test','the test','the test','the test');

insert into lab1_table3 values (i, 1, 'the test','the test','the test','the test','the test');

END LOOP;

END;

$$ language plpgsql

Ejecutar la función en PostgreSQL se hace así

select populate_tables();

Considera muy bien las características del equipo que sirve la Base de Datos antes de ejecutar la función. En mi máquina virtual, la ejecución del anterior script tomó 1'406.693 milisegundos, que en términos castizos se traduce en 23 minutos y ocupó un espacio de 2.2GB en disco duro, aproximadamente.

Cocción

Cuando existe un problema de este tipo, el primer enfoque es la iteración directa. Aunque no parece racional, es lo más cotidiano, pues sucede comúnmente que las tablas no contienen tanta información en sus etapas tempranas luego de la salida a producción y, por falta de proyección, la confianza en cómo la iteración rinde, le resta importancia al problema que puede crecer diariamente.

Para que el problema tenga un matiz que pueda ser encontrado en distintas implementaciones, los registros que vamos a iterar no son los que están en 1 u otra tabla, son, realmente, el resultado de la siguiente consulta:

select count(*) from lab1_table1 t1

join lab1_table2 t2 on (t2.fieldpk = t1.fieldpk)

join lab1_table3 t3 on (t3.fieldpk = t2.fieldpk)

Esto podría generar aun muchos más registros, pero por ahora y con base en la función creada, arrojará 10 millones de filas.

Comencemos con los experimentos.

Experimento 1. Registrar uso de recursos

El primer enfoque es, por tanto, iterar los registros tal como vienen de la base de datos. Todo lo que describiré fue hecho a través de JDBC.

Del prototipo en los materiales, el desarrollo de este experimento se encuentra en la clase main TestQueryWithJDBC. Gracias a la portabilidad que ofrece Java, probé la aplicación en distintas máquinas virtuales y Sistemas operativos y noté que, en ciertos casos sucedía que lograba iterar todos los registros y que la memoria virtual asignada se incrementaba dinámicamente a medida que lo necesitaba, pero durante la documentación de este artículo, sólo me genera java.lang.OutOfMemoryError.

En cualquier caso, la ejecución de la consulta directamente genera un sobrecosto a la memoria RAM, recurso bastante preciado y en algunos casos, limitado. Para obtener la mayor información, indiqué que el máximo de memoria a asignar fueran, en principio, 2GB (-Xmx2g).

Para registrar el comportamiento usé JConsole. La siguiente imagen revela cómo se ocupa la memoria progresivamente hasta que simplemente no consigue reservar más espacio.

-Xmx2g. Genera memory leak ocupando 2GB de RAM

Con esa memoria reservada, no alcance a iterar todos los registros, por tanto aumente la cuota a 4G y obtuve el siguiente resultado

-Xmx4g. Memory leak hasta 4GB de RAM.

Luego de quedarme sin memoria para asignar a la máquina virtual y no lograr acceder a todos los registros, se concluye, como es esperado, que iterar todas las filas de la forma convencional, no es el enfoque adecuado. Por tanto, iniciaré por verificar cuánto demora la paginación y continuaré con otras aproximaciones usando dicha técnica.

Experimento 2. Tiempo de Respuesta de paginación en consulta.

Antes de comenzar a desarrollar un prototipo que me permita paginar desde la aplicación esta cantidad de registros, primero elaboro un experimento que me permita decidir si ésta es una buena aproximación.

Para paginar en PostgreSQL existen 2 palabras clave; limit y offset [2]. La primera le indica al motor cuántos registros debe traer y la segunda desde cuál posición debe comenzar a iterar.

Para el caso del experimento, el experimento consiste de 3 pruebas:

- Obtener los primeros 10 registros y anotar el tiempo

select * from lab1_table1 t1

join lab1_table2 t2 on (t2.fieldpk = t1.fieldpk)

join lab1_table3 t3 on (t3.fieldpk = t2.fieldpk) limit 10

El resultado es muy esperanzador, 142 ms. obtiene aecuadamente los 10 primeros registros de la consulta y se ejecuta con eficiencia.

- Obtener 20 registros a partir de la página 5millones

select * from lab1_table1 t1

join lab1_table2 t2 on (t2.fieldpk = t1.fieldpk)

join lab1_table3 t3 on (t3.fieldpk = t2.fieldpk) limit 10 offset 5000000

En este punto ya comienzo a perder la esperanza en la paginación. Al ejecutar ésta consulta, el motor debe ubicar el registro 5millones y a partir de él retornar 10 filas. Esta tarea le toma 18 segundos y 507 milisegundos al motor.

- Obtener los últimos registros

select * from lab1_table1 t1

join lab1_table2 t2 on (t2.fieldpk = t1.fieldpk)

join lab1_table3 t3 on (t3.fieldpk = t2.fieldpk) limit 10 offset 9999990

para obtener los últimos 10 registros, el motor debe ubicar la fila 9'999.990 lo que le toma en total 38 segundos 24 milisegundos.

Como en los programas de cocina, hice otras pruebas adicionales a estas y con base en los datos de los tiempos de respuesta, proyecté la siguiente gráfica que resume cómo se incrementa el tiempo de respuesta a medida que se paginan nuevos resultados:

El tiempo incrementa cada vez que, al paginar, se aumenta el índice de offset.

Básicamente la gráfica se traduce en que, al incremente la paginación, el tiempo de respuesta se incrementa casi linealmente. Viendo que no era una posibilidad simplemente paginar la consulta -no quería ocupar recursos por más de 10 minutos-, debí recurrir a una segunda estrategia: Paginar en una sola tabla o tabla intermedia.

Experimento 3. Tiempo de respuesta, paginación a partir de una tabla intermedia.

Es evidente que el tiempo que gasta la paginación, se debe, principalmente, a la ejecución de la consulta. Evitar que el motor de base de datos consulte cada vez que pagina, es vital para reducir el tiempo total. Pero, ¿Cómo lograrlo?

Existe una técnica empleada para ubicar los resultados de una consulta en una tabla sin mayores tiempos de retraso.

Este experimento consta de 2 pasos.

Primero. Crear una tabla en donde ubicar los resultados de la consulta.

create table test6488_largeitems (

t1fieldpk numeric,

t1field2 varchar(10) ,

t1field3 varchar(10) ,

t1field4 varchar(10) ,

t1field5 varchar(10) ,

t1field6 varchar(10),

t2fieldpk numeric ,

t2fieldfk numeric ,

t2field2 varchar(10) ,

t2field3 varchar(10) ,

t2field4 varchar(10) ,

t2field5 varchar(10) ,

t2field6 varchar(10) ,

t3fieldpk numeric ,

t3fieldfk numeric ,

t3field2 varchar(10) ,

t3field3 varchar(10) ,

t3field4 varchar(10) ,

t3field5 varchar(10) ,

t3field6 varchar(10)

)

Segundo. Ejecutar la consulta e insertar en la tabla.

insert into test6488_largeitems

select * from lab1_table1 t1

join lab1_table2 t2 on (t2.fieldpk = t1.fieldpk)

join lab1_table3 t3 on (t3.fieldpk = t2.fieldpk)

Si bien no es posible reducir el tiempo que toma la consulta ejecutarse, es viable reducir el tiempo que toma la paginación pues, recordemos, el objetivo es el uso razonable de los recursos. La ejecución del script le toma 147 segundos y 941 milisegundos.

La acción final de este experimento consiste en ejecutar la paginación para la consulta, pero resulta que, aunque los tiempos disminuyen drásticamente, aún siguen siendo bastante altos.

Obtener 10 registros a partir de la fila 9999990 millones tomará, en este caso, 20 segundos (a diferencia de los 38 segundos que se obtuvieron en el Experimento 2).

Buscando opciones, decidí emplear una técnica más eficiente para paginar que la que ofrece Postgresql. En Oracle, el tema es distinto, pero por ahora me concentro en esta base de datos. La técnica consiste en usar una llave primaria que permita numerar cada fila, a lo que denominé, paginación manual.

Experimento 4. Uso de paginación manual

Usando una secuencia temporal y una llave primaria para la tabla intermedia, es posible asignar un identificador único a cada registro y además, permite numerar cada fila para paginar de una manera más eficiente a la ofrecida por el motor.

Para este experimento, modifiqué la tabla test6488_largeitems (incluí una llave primaria) y construí una función que me permite ejecutar estos pasos: Crear la secuencia, Ejecutar la consulta e insertar en la tabla intermedia.

Estos son los nuevos participantes:

drop table test6488_largeitems ;
create table test6488_largeitems (
counterrow numeric ,
t1fieldpk numeric,
t1field2 varchar(10) ,
t1field3 varchar(10) ,
t1field4 varchar(10) ,
t1field5 varchar(10) ,
t1field6 varchar(10),
t2fieldpk numeric ,
t2fieldfk numeric ,
t2field2 varchar(10) ,
t2field3 varchar(10) ,
t2field4 varchar(10) ,
t2field5 varchar(10) ,
t2field6 varchar(10) ,
t3fieldpk numeric ,
t3fieldfk numeric ,
t3field2 varchar(10) ,
t3field3 varchar(10) ,
t3field4 varchar(10) ,
t3field5 varchar(10) ,
t3field6 varchar(10)
);

create or replace function load_records ()

returns VOID as $$

BEGIN

drop sequence if exists temp_seq;

create temp sequence temp_seq;

insert into test6488_largeitems

SELECT linea.*

FROM

(

select nextval('temp_seq') as ROWNUM,* from lab1_table1 t1

join lab1_table2 t2 on (t2.fieldpk = t1.fieldpk)

join lab1_table3 t3 on (t3.fieldpk = t2.fieldpk)

) linea;

END;

$$ language plpgsql;

De esta manera, cada iteración se hará de la siguiente forma:

select * from test6488_largeitems where counterrow >= ? and counterrow <= ?

Por ejemplo, para obtener 25 registros, a partir de la fila 9millones, la consulta se vería así:

select * from test6488_largeitems where counterrow >= 9000000 and counterrow <= 9025000

Esto toma, sorprendentemente, 250 ms, aproximadamente.

Decidí que la paginación la haré de a 25 mil registros, basado en el tipo de datos y la cantidad de registros, puedo inferir que ocupará en promedio 100Mb. Esto es una aplicación aceptable a un patrón de Arquitectura denominado Adquisición Parcial [3].

Teniendo estos datos en mente y contemplando que la paginación en este caso dio un gran resultado, inicio la construcción del segundo prototipo para comprobar los resultados que he planteado como esperados, a saber: hasta 100MB de RAM ocupada por paginación y demora de 250ms por cada carga parcial, lo que supone entre 2 y 3 minutos para los 10 millones de registros.

Prototipo.

Realmente desarrollé 2 prototipos para esta prueba, el primero que demuestra la desventaja de iterar todas las filas y el que finalmente contiene el enfoque que utilicé, empleando adquisición parcial soportado en una Lista de carga perezosa [4] que hace más elegante la solución. Esta prueba puede ser ejecutada empleando la clase main TestLazyListQProcedureWithJDBC.

El siguiente es el resultado, visto a través de JConsole, al ejecutar la iteración de todas las filas.

257 segundos y 611 milisegundos (4min), para obtener 10 millones de registros.

La ejecución del prototipo revela uso eficiente de recursos (Memoria RAM).

Resumen general de uso de recursos, principalmente Memoria virtual y procesador.

Este es el modelo de clases que representa la solución planteada y que soporta el prototipo ejecutado.

Diagrama de Clase, estándar UML que representa el diseño del prototipo. Interviene la implementación de una lista de carga perezosa o Lazy List.

La aplicación se encuentra dentro del proyecto descargable y describo brevemente sus responsabilidades a continuación.

Elemento	Responsabilidad
TestLazyListQProcedureWithJDBC	Representa el cliente de la aplicación. Accede a la fachada, obtiene e itera los registros para resumir el tiempo y el total de filas
CargaRegistrosFachada	Fachada que expone los servicios disponibles para el prototipo. En este caso sólo existe uno; cargarRegistros, la primera carga parcial, es decir, los primeros 25 mil registros.
LargeRecordsLazyList	Implementa la lista de carga perezosa. Extiende de AbstracList y define, como su método más importante, get(int). Este se encarga de proporcionar los registros que ha cargado sin que la operación de paginación sea evidente para el cliente.
CargadorParcialResultados	Interfaz que desacopla la Lista de carga perezosa y el cliente de la implementación que se encarga de acceder al repositorio e implementar la carga parcial.
CargaParcialJDBCTest	Implementa la primera carga parcial, invocando la función que realiza la preparación de la tabla intermedia y las siguientes cargas a través de la paginación manual, indicando un índice desde y otro hasta.

Aclaro que probé muchas otras aproximaciones, pero para evitar extenderme, describí las que guiaron mi decisión. ¿Existe otra solución más adecuada? Espero conocer en algún momento la retroalimentación de sus experiencias.

Referencias

http://www.postgresql.org/docs/8.4/static/plpgsql-control-structures.html Estructuras de control para la manipulación de datos e iteración.
http://www.postgresql.org/docs/8.1/static/queries-limit.html. Descripción de LIMIT y OFFSET
http://kircher-schwanninger.de/michael/publications/PartialAcquisition.pdf. Explicación bastante sencilla y acertada del patrón de Arquitectura denominado Adquisición Parcial.
http://www.opensource.apple.com/source/JBoss/JBoss-737/jboss-all/jetty/src/main/org/mortbay/util/LazyList.java. Ejemplo de implementación para una Lista de Carga perezosa.

Theknos

Buscar este blog