45 formas de acelerar la base de datos Firebird

Aquí puedes encontrar la lista de consejos de rendimiento para la base de datos Firebird en diferentes áreas: desde el hardware/SO y el ajuste de la configuración de Firebird hasta las 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 los planes de ejecución, la gestión de transacciones y las estadísticas de rendimiento de las 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 del rendimiento de bases de datos.

1. Coloca la base de datos en un SSD

Coloca tu base de datos en un SSD. La unidad SSD proporciona una E/S aleatoria mucho mejor que las unidades tradicionales. La E/S aleatoria es crítica para leer y escribir datos distribuidos a lo largo de un archivo de base de datos grande: la mayoría de las operaciones de base de datos requieren una E/S aleatoria paralela intensiva.

2. Usa RAID 10

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

3. Verifica la BBU

Si estás usando un controlador RAID, verifica que tenga instalada y operativa una Unidad de Batería de Respaldo (BBU): algunos fabricantes no proporcionan BBU por defecto. Sin BBU, el controlador deshabilita 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 estás usando un controlador RAID con BBU instalada (y un servidor con UPS), verifica que su caché esté configurada en write-back (no en 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

Verifica tus unidades en busca de bloques defectuosos y otros problemas de hardware (incluido el sobrecalentamiento). Los problemas de hardware pueden disminuir significativamente el rendimiento de E/S y provocar corrupciones de 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 páginas de buffers

Aumenta el tamaño de la caché de páginas de buffers (parámetro DefaultDBCachePages) respecto a los valores por defecto. 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 páginas de buffers demasiado alto: la sincronización de la caché tiene su costo, y la idea de poner toda la base de datos en RAM ajustando este valor no funcionará. Usa los 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 el ordenamiento. Los valores por defecto son demasiado bajos (8Mb para Classic y 64Mb para SuperServer); usa al menos 64Mb para Classic y 1Gb para SuperServer y SuperClassic. De nuevo, usa los archivos de configuración optimizados del punto #9.

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

Si tienes una actividad intensiva de inserción o actualización (puedes verificarlo con HQbird MonLogger; para más detalles, consulta la página 60 de la HQbird User Guide), 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 hash slots para Classic/SuperClassic

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

13. Usa CPU Affinity 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 determinadas bases de datos.

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

Configura la primera parte del parámetro TempDirectory en firebird.conf en el disco rápido: SSD o unidad RAM. Disminuirá el tiempo de los ordenamientos grandes, por ejemplo, cuando se está restaurando la base de datos.

15. Almacena las copias de seguridad de la base de datos en otra unidad

Almacena las copias de seguridad de la base de datos en una unidad física dedicada (RAID). Separará la E/S de lectura y escritura durante la copia de seguridad, y aumentará la velocidad de la copia de seguridad y disminuirá la carga para la unidad principal. Es especialmente importante cuando se realizan copias de seguridad mientras los usuarios trabajan con la base de datos. Se pueden encontrar más detalles sobre la configuración de hardware para Firebird en " Firebird Hardware Guide".

16. Desactiva los í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 los registros y reactívalos después de la inserción o actualización. La operación de reconstrucción del índice puede ser más rápida que muchas actualizaciones del índice.

17. Usa Global Temporary Tables para inserciones rápidas

Para acelerar las inserciones y actualizaciones, usa Global Temporary Tables para inserciones masivas de grandes conjuntos de registros, y luego transfiere los registros a la tabla permanente. Puede ser muy efectivo insertar registros en una 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 para 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 las UDF por llamadas a funciones integradas

Reemplaza las llamadas a UDF por llamadas a funciones integradas. Se añadieron muchas funciones integradas en las versiones recientes de Firebird, que ofrecen funcionalidad que antes solo estaba disponible en bibliotecas UDF. Reemplaza dichas funciones donde sea posible, ya que las funciones integradas funcionan hasta 3 veces más rápido que las UDF.

20. Usa transacciones de solo lectura para operaciones de lectura

Usa transacciones de solo lectura para operaciones que no modifican registros (es decir, SELECT) 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 deshazte de 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 por la 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 las transacciones activas de larga duración y sus estadísticas de E/S. Además, si estás usando 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 en las que un registro tenga muchas versiones de registro: Firebird funciona mucho más lento con cadenas de registros largas. (para ver cuántas versiones de registro tiene alguna tabla, y cuál es la cadena de registros más larga, puedes usar la herramienta HQbird IBAnalyst, pestaña Tables, ordenar por “Max Version”). Usa la 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 llevar un 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 de inserción/actualización masivas

En el caso de una operación masiva de INSERT/UPDATE/DELETE, no hagas commit de la transacción después de cada cambio (puede ocurrir si estás usando la opción de autocommit en tu driver 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 E/S de lectura/escritura contra la base de datos; por eso los commits frecuentes disminuyen el rendimiento de la base de datos.

25. “Desactiva” los índices si estás usando IN con muchas constantes

Si estás usando la construcción WHERE fieldX IN (Constant1, Constant2,… ConstantN), y hay un índice en fieldX, Firebird usará un índice tantas veces como constantes haya en la lista IN. Deshabilita 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 anidados (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 estás usando 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 las consultas SELECT con las 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), normalmente es suficiente mostrar los 10-100 registros principales. 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, los campos que se mostrarán) como en la cláusula ORDER BY. Firebird combina las columnas de las cláusulas SELECT y ORDER BY/GROUP BY y las ordena en memoria (o, si la memoria no es suficiente, en el disco). Por lo tanto, 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 que se mostrarán 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, las largas 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 el registro completo se lee durante el mismo ciclo de E/S, y si el tamaño del registro es menor que 2/3 del tamaño de la página de la base de datos, el registro completo 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 E/S para leerlos, y muestran la ventaja con la lectura y escritura de cadenas largas.

32. Excluye las columnas BLOB de los SELECT grandes

Excluye las columnas BLOB de los SELECT grandes. Usa una especie de enlace tardío con subconsultas para mostrar selectivamente información de los 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 autoincrementadas 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 uses VARCHAR para las claves

No uses VARCHAR para identificadores a menos que sea realmente necesario: las operaciones con ellos son mucho menos eficaces 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 20 veces más lentas que con enteros.

35. Recalcula las estadísticas de los índices

Recalcula las estadísticas de los índices con regularidad. Actualiza las estadísticas de los índices de las tablas con cambios frecuentes o masivos mediante 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 la programación deseada (normalmente una vez por semana).

36. Usa un pool de conexiones

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

37. Usa la opción LINGER en Firebird 3.0

Si las conexiones a la base de datos son cortas y usas Firebird 3+, usa la opción LINGER para mantener la caché activa durante el tiempo especificado; así se mantendrán en caché las páginas de uso frecuente aunque no haya otras conexiones. Por ejemplo, ALTER DATABASE SET LINGER TO 60 mantendrá la caché durante 60 segundos tras el fin de la última conexión.

38. Usa HASH JOINs

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

39. Marca las funciones PSQL adecuadas 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 se almacenan en caché en el ámbito de la consulta actual.

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

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

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

reemplázalo por

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

41. Usa el modificador -se para gbak

Usa el modificador -se para aumentar la velocidad de copia de seguridad y/o restauración de gbak hasta en 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 los 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 consultar con frecuencia las tablas de monitorización

No ejecutes consultas a las tablas de monitorización de Firebird (MON$) con demasiada frecuencia: tales consultas consumen recursos significativos y pueden disminuir enormemente el rendimiento de la lógica de negocio principal. Recomendamos ejecutar consultas MON$ con una frecuencia no mayor de una vez por minuto. Para la monitorización continua de consultas/transacciones/conexiones de Firebird, usa la herramienta HQbird PerfMon, que admite Trace API (consulta la página 66 de la HQbird User Guide para más detalles).

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

Si ejecutas muchos comandos DML (Update/Insert/Delete) dentro de la misma transacción, Firebird fusiona el undo-log de cada comando con el undo-log de la transacción. Para acelerar las operaciones DML masivas, inicia la transacción con la opción «NO AUTO UNDO», para no fusionar los undo-logs de cada comando con el undo-log de la transacción.

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

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

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 email!