45 formas de acelerar la base de datos Firebird

Aquí puedes encontrar la lista de consejos de rendimiento para bases de datos Firebird en diferentes áreas: desde hardware/SO y ajuste de configuración de Firebird hasta recomendaciones de optimización de SQL. Esta lista no es la referencia completa sobre cómo optimizar Firebird, y asume que comprendes los conceptos básicos del funcionamiento de Firebird, como planes de ejecución, gestión de transacciones y estadísticas de rendimiento de consultas.

Por favor, aplica estos consejos con precaución y verifica su efecto antes de ponerlos en producción.

Nuestra empresa (IBSurgeon) ofrece el servicio integral de optimización de rendimiento de bases de datos.

1. Coloca la base de datos en SSD

Coloca tu base de datos en SSD. La unidad SSD proporciona un IO aleatorio mucho mejor que las unidades tradicionales. El IO aleatorio es crítico para leer y escribir datos distribuidos a través de un archivo de base de datos grande: la mayoría de las operaciones de base de datos requieren IO aleatorio paralelo intensivo.

2. Usa RAID 10

Si usas RAID1 o RAID5, considera RAID10: es 15-25% más rápido.

3. Verifica la BBU

Si usas un controlador RAID, verifica que tenga instalada y operativa la Unidad de Batería de Respaldo (BBU); algunos proveedores no la incluyen por defecto. Sin BBU, el controlador desactiva la caché y el RAID funciona muy lento, incluso más lento que las unidades SATA habituales. Normalmente, puedes verificar el estado de la BBU en la herramienta de configuración del RAID.

4. Configura la caché de escritura en write-back

Si usas un controlador RAID con BBU instalada (y servidor con UPS), verifica que su caché esté configurada en write-back (no write-through). «Write-back» habilita la caché de escritura del controlador.

5. Habilita la caché de lectura

Si usas un controlador RAID, verifica que tenga habilitada la caché de lectura.

6. Verifica el subsistema de discos

Revisa tus unidades en busca de bloques defectuosos y otros problemas de hardware (incluido el sobrecalentamiento). Los problemas de hardware pueden disminuir significativamente el rendimiento del IO y provocar corrupciones en la base de datos.

7. Usa SuperClassic o Classic en Firebird 2.5

Si usas Firebird 2.5 SuperServer con muchas conexiones, intenta usar SuperClassic o Classic; pueden escalar mejor al utilizar todos los núcleos de la CPU.

8. Usa SuperServer 3.0 en Firebird 3

Si usas Classic o SuperClassic en 2.5, considera migrar a Firebird 3.0 SuperServer; ahora puede usar múltiples núcleos y combinarlo con las ventajas de la caché compartida.

9. Aumenta la caché de búferes de páginas

Aumenta el tamaño de la caché de búferes de páginas (parámetro DefaultDBCachePages) desde los valores predeterminados. Para 2.5 SuperServer recomendamos 10000 páginas, para 3.0 SuperServer - 50000 páginas, para Classic y SuperClassic - de 256 a 2048 páginas. Sin embargo, no configures el valor de la caché de búferes de páginas demasiado alto: la sincronización de caché tiene su costo, y la idea de poner toda la base de datos en RAM ajustando este valor no funcionará. Usa archivos de configuración de Firebird preoptimizados aquí: /es/optimized-firebird-configuration/

10. Aumenta el tamaño de memoria para operaciones de ordenamiento

Aumenta el valor del parámetro TempCacheLimit en firebird.conf: especifica el tamaño de la caché del espacio temporal para ordenamiento. Los valores predeterminados son demasiado bajos (8Mb para Classic y 64Mb para SuperServer); usa al menos 64Mb para Classic y 1Gb para SuperServer y SuperClassic. Nuevamente, usa los archivos de configuración optimizados del #9.

11. Configura Forced Writes en OFF (¡con precaución!)

Si tienes actividad intensiva de inserción o actualización (puedes verificarlo con HQbird MonLogger; para más detalles consulta la página 60 de la Guía de Usuario de HQbird), y si tienes UPS y replicación instalados para protegerte de fallos de hardware, considera configurar Forced Writes en OFF; puede aumentar la velocidad de las operaciones de escritura hasta 3 veces.

12. Aumenta el número de slots de hash para Classic/SuperClassic

Aumenta el valor del parámetro LockHashSlots para Classic y SuperClassic desde el valor predeterminado de 1009 a un número primo grande (30011, por ejemplo); esto disminuirá las colas en el mecanismo interno de bloqueo.

13. Usa afinidad de CPU para Super Server 2.5

Si usas SuperServer 2.5, configura el parámetro CPUAffinity con un valor igual al número de bases de datos en uso: SuperServer en 2.5 puede usar diferentes núcleos de CPU para procesar solicitudes de ciertas bases de datos.

14. Usa una unidad rápida para el espacio temporal

Configura la primera parte del parámetro TempDirectory en firebird.conf hacia un disco rápido: SSD o unidad RAM. Esto disminuirá el tiempo de ordenamientos grandes, por ejemplo, cuando se restaura la base de datos.

15. Almacena las copias de seguridad en otra unidad

Almacena las copias de seguridad de la base de datos en una unidad física dedicada (RAID). Esto separará el IO de lectura y escritura durante la copia de seguridad, aumentará la velocidad de la copia y disminuirá la carga en la unidad principal. Es especialmente importante cuando se realizan copias de seguridad mientras los usuarios trabajan con la base de datos. Más detalles sobre la configuración de hardware para Firebird se pueden encontrar en la «Guía de Hardware de Firebird».

16. Desactiva índices para inserciones masivas

Si insertas o actualizas muchos registros (más del 25% de la tabla), desactiva los índices de la tabla donde se insertan registros y reactívalos después de la inserción o actualización. La operación de reconstrucción de índices puede ser más rápida que muchas actualizaciones del índice.

17. Usa Tablas Temporales Globales para inserciones rápidas

Para acelerar inserciones y actualizaciones, usa Tablas Temporales Globales para inserciones masivas de grandes conjuntos de registros, y luego transfiere los registros a la tabla permanente. Puede ser muy efectivo insertar registros en GTT, preprocesarlos y luego moverlos a la tabla persistente.

18. Evita índices innecesarios

Usa menos índices para tablas con inserciones y actualizaciones intensivas. Cada índice añade una sobrecarga significativa a las operaciones de inserción, actualización, eliminación y recolección de basura: puede haber 3-4 lecturas y escrituras de páginas adicionales cuando se inserta/actualiza/elimina/limpia un solo registro por cada índice.

19. Reemplaza UDFs con llamadas a funciones integradas

Reemplaza las llamadas a UDF con llamadas a funciones integradas. Muchas funciones integradas se agregaron en versiones recientes de Firebird, que ofrecen funcionalidad previamente disponible solo en bibliotecas UDF. Reemplaza dichas funciones cuando sea posible, ya que las funciones integradas funcionan hasta 3 veces más rápido que las UDFs.

20. Usa transacciones de solo lectura para operaciones de lectura

Usa transacciones de solo lectura para operaciones que no cambian registros (es decir, SELECTs) con modo de aislamiento = read committed. Dichas transacciones no retienen versiones de registros de la recolección de basura y pueden ejecutarse indefinidamente: no afectan el rendimiento de la base de datos.

21. Usa transacciones de escritura cortas y elimina TODAS las de larga duración

Usa transacciones de escritura cortas (para operaciones INSERT/UPDATE/DELETE).

Cuanto más corta sea la transacción de escritura, mejor. Las transacciones cortas retienen proporcionalmente menos versiones de registros de la recolección de basura que las de larga duración. Desafortunadamente, incluso una sola transacción de larga duración (dejada abierta desde una herramienta de desarrollo, por ejemplo) puede arruinar el buen efecto de todas las demás transacciones de escritura cortas. Por eso necesitas monitorear las transacciones de larga duración y corregir los lugares apropiados en el código fuente. Usa la herramienta HQbird DataGuard para recibir alertas sobre la transacción activa más antigua en la base de datos Firebird (qué aplicaciones la iniciaron, qué dirección IP, la marca de tiempo de su inicio), y la herramienta HQbird MonLogger para ver la lista completa de transacciones activas de larga duración y sus estadísticas de IO. Además, si usas componentes/bibliotecas de acceso a bases de datos que pueden almacenar en caché conjuntos de registros, usa actualizaciones en caché.

22. Evita cadenas de registros largas

Evita situaciones donde un registro tiene muchas versiones: Firebird trabaja mucho más lento con cadenas de registros largas. (Para ver cuántas versiones de registros tienen algunas tablas y cuál es la cadena de registros más larga, puedes usar la herramienta HQbird IBAnalyst, pestaña Tables, ordena por «Max Version»). Usa una combinación de inserciones y eliminación programada de registros antiguos en lugar de múltiples actualizaciones del mismo registro.

23. Usa PREPARE correctamente

Usa sentencias preparadas para ejecutar consultas SQL donde solo cambian los parámetros; por ejemplo, haz prepare antes del bucle de dichas consultas. Prepare puede tomar tiempo significativo (especialmente para tablas grandes), y preparar la consulta solo una vez aumentará enormemente el rendimiento general.

24. No hagas COMMIT con demasiada frecuencia durante operaciones masivas de inserción/actualización

En el caso de operaciones masivas INSERT/UPDATE/DELETE, no hagas commit de la transacción después de cada cambio (puede suceder si usas la opción de auto commit en tu controlador de base de datos): haz commit de las transacciones al menos después de 1000 operaciones o más. Cada commit de transacción ejecuta varias operaciones de IO de lectura/escritura contra la base de datos, por eso los commits frecuentes disminuyen el rendimiento de la base de datos.

25. «Desactiva» índices si usas IN con muchas constantes

Si usas la construcción WHERE fieldX IN (Constant1, Constant2,… ConstantN), y hay un índice en fieldX, Firebird usará el índice tantas veces como constantes haya en la lista IN. Desactiva la búsqueda por índice convirtiendo fieldX en una expresión +0: WHERE fieldX+0 IN (Constant1, Constant2,… ConstantN), o, para cadenas, usa fieldX||''

26. Reemplaza IN con JOIN

Evita usar consultas con WHERE IN anidado (SELECT… WHERE IN (SELECT.. WHERE IN() )), puede confundir al optimizador de Firebird. Transforma los IN anidados en joins.

27. Usa LEFT JOIN de la manera correcta

Si usas LEFT OUTER joins, coloca explícitamente las tablas en el join desde la más pequeña hasta la más grande.

28. Limita la obtención de consultas SELECT

Siempre intenta limitar la salida grande de consultas SELECT con cláusulas FIRST… SKIP o ROWS. Si la consulta no está diseñada específicamente como un informe (que requiere que todos los registros se impriman/exporten), generalmente es suficiente mostrar los primeros 10-100 registros. Obtén solo los registros necesarios.

29. Especifica menos columnas en SELECT con ORDER BY/GROUP BY

Reduce el número de columnas y su ancho total en consultas con ORDER BY/GROUP BY tanto en la parte SELECT (es decir, campos a mostrar) como en la cláusula ORDER BY. Firebird fusiona las columnas de SELECT y las cláusulas ORDER BY/GROUP BY y las ordena en memoria (o, si no hay suficiente memoria, en disco). Entonces, si hay un VARCHAR largo en SELECT, el tamaño de los archivos de ordenamiento puede ser realmente grande (muchos gigabytes). Reducir el número de campos solo a aquellos que deben ordenarse y hacer un join tardío con los campos grandes a mostrar puede aumentar enormemente (x3-x10) la velocidad de una consulta con ORDER BY/GROUP BY.

30. Usa tablas derivadas para optimizar SELECT con ORDER BY/GROUP BY

Otra forma de optimizar una consulta SQL con ordenamiento es usar tablas derivadas para evitar operaciones de ordenamiento innecesarias. En lugar de

SELECT FIELD_KEY, FIELD1, FIELD2, ... FIELD_NFROM TORDER BY FIELD2

usa la siguiente modificación:

SELECT T.FIELD_KEY, T.FIELD1, T.FIELD2, ... T.FIELD_NFROM (SELECT FIELD_KEY FROM T ORDER BY FIELD2) T2JOIN T ON T.FIELD_KEY = T2.FIELD_KEY

31. Almacena cadenas cortas en VARCHAR y las grandes en BLOBs

Para almacenar datos de caracteres cortos, usa VARCHARs; para almacenar textos largos, usa BLOBs. Los Varchars son más rápidos para piezas pequeñas de datos porque se almacenan en el registro, y todo el registro se lee durante el mismo ciclo de IO, y si el tamaño del registro es menor que 2/3 del tamaño de página de la base de datos, todo el registro se almacena en la misma página de la base de datos. Los BLOBs se almacenan fuera del registro y requieren una ronda adicional de IO para leerlos, y muestran su ventaja al leer y escribir cadenas largas.

32. Excluye columnas BLOB de los SELECTs grandes

Excluye las columnas BLOB de los SELECTs grandes. Usa una especie de enlace tardío con subconsultas para mostrar selectivamente información de BLOBs (por ejemplo, mostrar el contenido del documento).

33. Usa BIGINT para claves primarias y únicas

Usa el tipo BIGINT para claves primarias y únicas autoincrementales y para identificadores de todo tipo. Las operaciones con BIGINT son las más rápidas, y BIGINT tiene suficiente capacidad para almacenar casi todos los rangos de datos.

34. No utilices VARCHAR para claves

No utilices VARCHAR para identificadores a menos que sea realmente necesario: las operaciones con ellos son mucho menos eficientes que con columnas enteras. Evita especialmente los GUID como identificadores, ya que debido a la distribución aleatoria de los valores GUID, las operaciones INSERT/UPDATE con claves primarias/únicas GUID pueden ser hasta 20 veces más lentas que con enteros.

35. Recalcula las estadísticas de índices

Recalcula las estadísticas de índices regularmente. Actualiza las estadísticas de índices para las tablas con cambios frecuentes o masivos con el comando SET STATISTICS; esto permite que el optimizador de Firebird elija mejores planes SQL. HQbird Firebird DataGuard puede realizar este recálculo de estadísticas de índices automáticamente según el cronograma deseado (generalmente una vez por semana).

36. Utiliza un pool de conexiones

Si las conexiones a la base de datos Firebird son cortas (típico en sitios web), utiliza un pool de conexiones; por ejemplo, en PHP usa la función ibase_pconnect en lugar de ibase_connect.

37. Utiliza la opción LINGER en Firebird 3.0

Si las conexiones a la base de datos son cortas y estás usando Firebird 3+, utiliza la opción LINGER para mantener la caché activa durante el tiempo especificado; esto mantendrá las páginas de uso frecuente en la caché incluso si no hay otras conexiones. Por ejemplo, ALTER DATABASE SET LINGER TO 60 mantendrá la caché durante 60 segundos después del final de la última conexión.

38. Utiliza HASH JOINs

En Firebird 3.0, al unir tablas grandes y pequeñas, HASH JOIN puede ser mucho más rápido que la unión normal que utiliza «bucle anidado» con índice. Para que el optimizador de Firebird use HASH join, utiliza +0 en la condición de unión: T1 JOIN T2 ON T1.FIELD1+0 = T2.FIELD2+0. ¡Verifica el resultado de la optimización antes de ponerlo en producción!

39. Marca las funciones PSQL apropiadas como DETERMINISTIC

Marca tus funciones PSQL (en Firebird 3+) que no tienen parámetros y devuelven valores constantes con la palabra clave DETERMINISTIC. Las funciones deterministas se calculan y almacenan en caché dentro del ámbito de la consulta actual.

40. Utiliza funciones analíticas (de ventana) en Firebird 3.0

Si estás ejecutando un SELECT con salida simultánea de alguna columna y su función agregada, utiliza funciones de ventana (analíticas): es más rápido que una subconsulta o 2 consultas. Por ejemplo:

Select id, department, salary, salary / (select sum(salary) from employee) percentagefrom employee

reemplázalo con

Select id, department, salary, salary / sum(salary) OVER () percentage from employee

41. Utiliza el interruptor -se para gbak

Utiliza el interruptor -se para aumentar la velocidad de copia de seguridad y/o restauración de gbak hasta un 20%, por ejemplo

gbak -b -g -se service_mgr c:\db\data.fdb e:\backup\data.fbk

42. WHERE CURRENT OF

La forma más rápida de procesar registros obtenidos por el cursor en PSQL es la cláusula ‘where current of <>’. Es más rápida que ‘where rb$db_key = :v_db_key’ y mucho más rápida que la búsqueda con una clave primaria o única.

43. Evita consultas frecuentes a tablas de monitoreo

No ejecutes consultas a las tablas de monitoreo de Firebird (MON$) con demasiada frecuencia: estas consultas consumen recursos significativos y pueden disminuir en gran medida el rendimiento de la lógica de negocio principal. Recomendamos ejecutar consultas MON$ no más de una vez por minuto. Para el monitoreo continuo de consultas/transacciones/conexiones de Firebird, utiliza la herramienta HQbird PerfMon que admite Trace API (consulta la página 66 de la Guía del usuario de HQbird para más detalles).

44. Utiliza la opción NO_AUTO_UNDO para inserciones/actualizaciones masivas

Si estás ejecutando muchos comandos DML (Update/Insert/Delete) dentro del marco de la misma transacción, Firebird fusiona el registro de deshacer (undo-log) de cada comando con el de la transacción. Para acelerar operaciones DML masivas, inicia la transacción con la opción «NO AUTO UNDO», para no fusionar los registros de deshacer de cada comando con el de la transacción.

45. No utilices autenticación SRP en Firebird 3 si no la necesitas

No utilices autenticación de usuarios SRP (Firebird 3.0+) si realmente no la necesitas: la conexión con autenticación SRP se establece más lentamente que la conexión regular.

En lugar de un resumen

La optimización del rendimiento requiere considerar múltiples factores y puede ser realmente complicada. Si has probado todo lo anterior, considera contratar un servicio profesional de optimización del rendimiento de bases de datos.

Contáctanos

¿Tienes alguna pregunta? No dudes en contactarnos por correo electrónico.