Alexey Kovyazin, 26-mayo-2014

Hace algunos días publicamos un artículo sobre nuestras pruebas, dedicado a la relación entre el rendimiento de Firebird y el crecimiento de las bases de datos, donde probamos (entre otras) la base de datos Firebird SQL de 1.7 Terabytes. Aquí puedes encontrar más detalles sobre una base de datos tan grande.

Tablas

En la tabla 1 puedes encontrar la lista de tablas con sus características clave. Estas estadísticas se obtuvieron con gstat -a -r y se interpretaron con nuestra herramienta IBAnalyst.

Tabla	Registros	Longitud de registro, bytes	Páginas de datos	Tamaño de tabla, Mb	Tamaño de índices, Mb	Total, %
ORDER_LINE	6300024797	60.09	38880344	607505.3	50582.3	34
STOCK	2100000000	298.88	43783806	684121.9	15809.61	39
ORDERS	630004141	29.00	2692324	42067.56	4250.38	2
HISTORY	630000434	48.77	3446455	53850.86	0.00	3
CUSTOMER	630000000	577.52	24226054	378532.0	8675.78	21
NEW_ORDER	188999981	13.00	623764	9746.31	1260.84	1
DISTRICT	210000	103.92	1860	29.06	1.27	~0
ITEM	100000	82.73	756	11.81	0.52	~0
WAREHOUSE	21000	97.90	179	2.80	0.11	~0

Tabla 1. Tablas y sus parámetros principales en la base de datos Firebird SQL de 1.7 Terabytes

Como puedes ver, la tabla más grande es ORDER_LINE - contiene más de 6 mil millones de registros. Su tamaño es de aproximadamente 600 Gb y hay 50 Gb de índices para esta tabla. Esta tabla ocupa el 34% de la base de datos.

La tabla STOCK también es muy grande - contiene aproximadamente 2 mil millones de registros. Aunque el número de registros es menor que en la tabla ORDER_LINE, STOCK ocupa ~680 Gb en la base de datos (39%), porque la longitud de registro en STOCK es de 298.88 bytes frente a solo 60.09 bytes en ORDER_LINE. En consecuencia, el tamaño de los índices asociados para STOCK es solo de 15 Gb.

La tercera tabla más grande, CUSTOMER, tiene una longitud de registro aún mayor - 577.52 bytes, y ocupa 378 Gb (21%) con solo 630 millones de registros.

Índices

No hay muchos índices en esta base de datos, ya que está diseñada solo para pruebas - la mayoría de las tablas tienen solo 1 índice - la clave primaria. En aplicaciones del mundo real, los desarrolladores crean muchos índices para atender solicitudes específicas de los usuarios, pero aquí los índices se concentran en las inserciones y actualizaciones más rápidas de los datos - como resultado, el tamaño de los índices es solo del 5-10% del tamaño de la tabla, mientras que normalmente es del 30-50% (si tienes un tamaño de índices superior al 50% del tamaño de la tabla, considera rediseñar el esquema de la base de datos, o quizás eliminar índices inútiles o ineficaces).

En la tabla 2 puedes encontrar la lista de todos los índices en esta base de datos y sus características clave, tomadas de las estadísticas de gstat y analizadas por IBAnalyst:

Índice	Tabla	Profundidad	Claves #	Longitud de clave, bytes	# de claves únicas	Tamaño, Mb
ORDER_LINE_PK	ORDER_LINE	4	6300024797	1.41	6300024797	50582.34
STOCK_PK	STOCK	4	2100000000	1.00	2100000000	15809.61
ORDERS_PK	ORDERS	3	630004141	1.01	630004141	4250.38
CUSTOMER_LAST	CUSTOMER	3	630000000	0.00	1000	4029.64
CUSTOMER_PK	CUSTOMER	3	630000000	1.01	188999981	1260.84
NEW_ORDER_PK	NEW_ORDER	3	188999981	1.01	188999981	1260.84
DISTRICT_PK	DISTRICT	2	210000	1.37	210000	1.27
ITEM_PK	ITEM	2	100000	1.00	100000	0.52
WAREHOUSE_PK	WAREHOUSE	2	21000	1.00	21000	0.11

Tabla 2. Índices de la base de datos Firebird de 1.7 Tb

Como puedes ver, todos los índices tienen un tamaño de clave muy pequeño - el mayor es 1.41, lo que significa que el índice está muy comprimido de manera efectiva - por ejemplo, la clave primaria de ORDER_LINE contiene 6 mil millones de claves en 50 Gb de espacio. Este es un muy buen resultado.

Hay 2 índices que tienen profundidad = 4. Esto significa que el motor necesita realizar 4 lecturas de páginas de índice para encontrar el valor solicitado. Se recomienda que la profundidad del índice no sea superior a 3, y si es mayor, se debe aumentar el tamaño de página de la base de datos. Sin embargo, ya tenemos el tamaño máximo de página en esta base de datos (16 Kb), así que debemos vivir con ello.

Calentamiento

Antes de continuar con las consultas, realicemos un “calentamiento”. Cuando la base de datos es grande, los datos del sistema asociados con las tablas también son grandes, y Firebird tarda algún tiempo en almacenar en caché las páginas del sistema necesarias. Por ejemplo, la tabla ORDER_LINE tiene 10110 páginas de puntero. El calentamiento es necesario para cualquier base de datos grande.

Sin calentamiento, la primera ejecución de consultas tomará significativamente más tiempo que con calentamiento.

Para calentar la caché de la base de datos, realicemos una serie de consultas simples para cargar los datos del sistema en la caché:

select first 1 * from ORDER_LINE; select first 1 * from STOCK; select first 1 * from customer; select first 1 * from ORDERS; select first 1 * from ITEM;

Esto es suficiente para un esquema de datos tan simple. Para bases de datos grandes y complejas con cientos de tablas, el esquema de calentamiento podría ser mucho más complejo y debe desarrollarse cuidadosamente.

Como resultado, el consumo de memoria del proceso de Firebird (Firebird SuperServer 2.5.2 64 bits) crece:

Figura 1. Calentamiento de la caché de la base de datos

Consultas

Después del calentamiento, podemos ejecutar varias consultas típicas para estimar cómo trabajarán los usuarios con una base de datos Firebird tan grande: cuáles serán los tiempos de respuesta para consultas típicas y operaciones de lectura.

Consulta	Plan y estadísticas	Descripción
select first 10 w_id, w_name, c_id, c_last<br> from WAREHOUSE, customer<br> where c_w_id = w_id and c_w_id = 10000	PLAN JOIN (WAREHOUSE NATURAL,<br> CUSTOMER INDEX (CUSTOMER_PK))<br> STATISTICS<br> Current memory = 166919744<br> Delta memory = 5616<br> Max memory = 166977488<br> Elapsed time= 0.08 sec<br> Buffers = 10000<br> Reads = 1<br> Writes 0<br> Fetches = 51	Unión de la tabla WAREHOUSE (21000 registros) con CUSTOMER (630 millones de registros), con condición para un almacén específico
select count(*)<br> from WAREHOUSE, customer<br> where c_w_id = w_id and c_w_id = 10000	PLAN JOIN (WAREHOUSE INDEX (WAREHOUSE_PK),<br> CUSTOMER INDEX (CUSTOMER_PK))<br> COUNT<br> ============<br> 30000<br> <br> Current memory = 166918560<br> Delta memory = -1184<br> Max memory = 166977488<br> Elapsed time= 0.16 sec<br> Buffers = 10000<br> Reads = 1175<br> Writes 0<br> Fetches = 60040	Contar registros para la consulta anterior.
SELECT first 10 *<br> FROM ORDER_LINE<br> WHERE OL_W_ID = 10050	PLAN (ORDER_LINE INDEX (ORDER_LINE_PK))<br> STATISTICS<br> Current memory = 167013640<br> Delta memory = 95080<br> Max memory = 167055968<br> Elapsed time= 0.80 sec<br> Buffers = 10000<br> Reads = 10285<br> Writes 0<br> Fetches = 10424	Consulta a la tabla más grande ORDER_LINE (~6 mil millones de registros) con condición para seleccionar registros de order_line para un ID de almacén específico. Dado que la clave primaria ORDER_LINE_PK es compuesta y contiene el ID del almacén (CONSTRAINT ORDER_LINE_PK: Primary key (OL_W_ID, OL_D_ID, OL_O_ID, OL_NUMBER), se utiliza de manera efectiva en esta consulta.
SELECT count(*)<br> FROM ORDER_LINE<br> WHERE OL_W_ID = 10050;	PLAN (ORDER_LINE INDEX (ORDER_LINE_PK))<br> <br> COUNT<br> ============<br> 299509<br> Current memory = 167125464<br> Delta memory = -7160<br> Max memory = 167267976<br> Elapsed time= 3.41 sec<br> Buffers = 10000<br> Reads = 1994<br> Writes 0<br> Fetches = 599170	Contar para la consulta anterior. La segunda consulta con los mismos parámetros será mucho más rápida, pero las consultas con diferentes parámetros muestran un resultado similar.
Select first 10 w_id, w_name, c_id, c_last<br> rom WAREHOUSE, CUSTOMER<br> where c_w_id = w_id and (c_w_id > 8000)<br> and (c_w_id < 10000)	PLAN JOIN (WAREHOUSE INDEX (WAREHOUSE_PK),<br> CUSTOMER INDEX (CUSTOMER_PK))<br> <br> STATISTICS<br> Current memory = 167687664<br> Delta memory = -651888<br> Max memory = 168931400<br> Elapsed time= 0.19 sec<br> Buffers = 10000<br> Reads = 31<br> Writes 0<br> Fetches = 63	Consulta con unión y 2 condiciones.
select first 10 c1.C_ID, c1.C_FIRST, c1.C_LAST, o1.O_ID,<br> o1.O_OL_CNT, i1.I_NAME, i1.I_PRICE, ol1.OL_AMOUNT<br> from customer c1<br> join orders o1 on (c1.c_w_id = o1.O_w_ID<br> and c1.C_D_ID = o1.O_D_ID and c1.C_ID = o1.O_C_ID)<br> join ORDER_LINE ol1 on (ol1.ol_w_id = c1.C_W_ID<br> and ol1.OL_D_ID = c1.C_D_ID<br> and ol1.OL_O_ID = o1.O_ID)<br> join Item i1 on (ol1.OL_I_ID = i1.i_id)<br> where o1.o_d_id = 1 and o1.o_w_id = 1 and c1.c_id = 10;	PLAN JOIN (C1 INDEX (CUSTOMER_PK),<br> O1 INDEX (ORDERS_PK),<br> OL1 INDEX (ORDER_LINE_PK),<br> I1 INDEX (ITEM_PK))<br> <br> STATISTICS<br> Current memory = 166807128<br> Delta memory = 6016<br> Max memory = 166879200<br> Elapsed time= 0.20 sec<br> Buffers = 9999<br> Reads = 1<br> Writes 0<br> Fetches = 4960	Consulta para mostrar detalles de un cliente específico en un almacén y distrito específicos. Demuestra el rendimiento de uniones más complejas.

Resumen

Como puedes ver, la base de datos Firebird de 1.7 Terabytes muestra un rendimiento bastante bueno para consultas de lectura comunes. Si la consulta está bien diseñada y utiliza buenos índices, el rendimiento es bueno incluso en bases de datos muy grandes. Por supuesto, hay puntos problemáticos, como el largo tiempo de recuperación para conjuntos de datos muy grandes y el largo tiempo para operaciones de conteo (ya que Firebird visita todas las páginas para obtener un número exacto de registros para la consulta particular en la transacción específica), pero estos puntos problemáticos son bien conocidos por los desarrolladores experimentados de Firebird, y existen métodos para diseñar la base de datos de manera que se puedan evitar.

Pero, ¿qué hay de la degradación del rendimiento para inserciones y actualizaciones? ¿Es posible compensarla con un ajuste inteligente de la configuración de Firebird?

En el próximo artículo consideraremos las opciones de ajuste para la base de datos Firebird de 1.7 terabytes - impulsaremos nuestro pájaro y lo haremos volar.