IBAnalyst: Consejos y trucos

Este texto fue escrito originalmente en 2012, es válido para la versión 1.0 - 2.5, en las versiones 3.0-5.0 hubo muchos cambios que no pudieron reflejarse. Por favor, lea la documentación o contacte con nosotros para soporte: [email protected].

Algunas preguntas que no se responden en las Recomendaciones y/o Ayuda de IBAnalyst:

1. ¿Cómo reconstruir índices en restricciones PRIMARY, FOREIGN o UNIQUE?

R: Para versiones de Firebird 1.0-2.5. Sí, no se puede usar ALTER INDEX xxx INACTIVE/ACTIVE en índices de restricciones. Si ve un índice profundo o fragmentado en esta restricción, puede usar un truco especial (usado por gbak en la restauración):

RDB$INDICES tiene el indicador RDB$INDEX_INACTIVE que es nulo o 0 si el índice está activo (después de CREATE INDEX o ALTER INDEX ACTIVE). 1 significa que el índice está inactivo (después de ALTER INDEX INACTIVE). Pero también hay un valor de 3 que se usa para indicar índices inactivos en restricciones. Entonces, puede establecer RDB$INDEX_INACTIVE=3 para ese índice, hacer COMMIT, y luego devolver el valor a 0 y hacer commit nuevamente: el índice se reconstruirá.

Para Firebird 3.0-5.0 - simplemente haga ALTER INDEX nombreindice ACTIVE

2. Usé todas las recomendaciones de IBAnalyst pero esto no ayuda a acelerar las consultas.

R: Este es un problema aparte, donde IBAnalyst no puede ayudar. Aquí puede haber 2 causas del problema:

1. Los índices tienen estadísticas desactualizadas. Puede actualizar las estadísticas del índice con el comando SET STATISTICS INDEX xxx (ver más detalles http://www.ibase.ru/proc_selectivity/).

2. Simplemente no hay un índice adecuado para alguna condición usada en la consulta.

3. Las consultas son muy complejas, o el optimizador no puede optimizar la consulta, por lo que es necesario refactorizar la consulta.

4. En algunos casos verá “tablas fragmentadas” justo después de la restauración.

Normalmente Firebird e InterBase (sin el parámetro -use_all_space) reservan alrededor del 25% de espacio en las páginas de datos para futuras inserciones, actualizaciones o eliminaciones (para colocar versiones de registros). Pero, con cualquier tamaño de página de base de datos (1, 2, 4 u 8 k), verá aproximadamente un 50% de fragmentación para tablas que tienen un tamaño de registro pequeño (aproximadamente 12-20 bytes, por ejemplo, una tabla con 2 campos enteros tiene un tamaño de registro promedio = 12 bytes).

Esto es normal, considérelo como un número mágico del servidor (o comportamiento).

Entonces, si tiene tablas con registros tan pequeños, puede:

a) ignorar la advertencia de “fragmentada” para esas tablas

b) bajar el “% fragmentada” al 45%, por ejemplo, en el diálogo de Opciones de IBAnalyst.

4. Versiones de registros para una tabla que no debe actualizarse

Si ve versiones de registros en una tabla que no debe actualizarse (por ejemplo, una tabla con algún registro de eventos), no se preocupe, estas versiones son generadas por eliminaciones.

Así sabrá cuántos registros actuales hay en la tabla y cuántos registros fueron eliminados.

Esto es cierto solo si MaxVer = 1. Si es > 1, entonces esta tabla está siendo actualizada por alguna aplicación. Si está realmente seguro de que esta tabla nunca debe actualizarse, es mejor establecer un disparador “before update” con una excepción para encontrar qué aplicación realiza las actualizaciones.

5. Los blobs pueden causar fragmentación de tablas.

El motor almacena los blobs de 3 maneras diferentes:

1. Si el contenido del blob cabe en la página de datos (suficiente espacio libre), se almacenará en esa página de datos cerca de su registro (o versión).

2. Si el contenido del blob no cabe en la página de datos, se almacenará en una página separada.

3. Si en el caso 2 el blob no cabe en una página de datos, se crea una página de punteros para apuntar a las páginas de blob apropiadas.

El caso 1 ocurre dependiendo del tamaño del blob almacenado y del tamaño de página de la base de datos. Por ejemplo, si tenía un tamaño de página de 4K y blobs con tamaño promedio de ~5K, se almacenan no en páginas de datos, sino en páginas de blob adicionales.

Pero si hace una copia de seguridad de su base de datos y la restaura con un tamaño de página de 8K, los blobs cabrán en la página de datos y se almacenarán con los registros, causando alta fragmentación de registros.

IBAnalyst marca estas tablas como Pálidas (columna Registros) y la sugerencia muestra los registros estimados para esa tabla (basados en el conteo de páginas de datos) y el valor de llenado promedio real (%).

Si su consulta lee cualquier campo excepto blobs de esa tabla, el escaneo natural, la unión o la agregación se ejecutarán muy lentamente.

La única solución para evitarlo: crear una tabla adicional (vinculada 1-1 a la tabla original) y mover todas las columnas blob que tengan un tamaño promedio menor que el tamaño de página a esa tabla.

En ese caso, ¡no intente hacer copia de seguridad/restauración con un tamaño de página mayor! Esto hará que los blobs que no cabían en las páginas de datos con el tamaño de página actual se coloquen en las páginas de datos durante la restauración con un tamaño de página mayor. Así, sus tablas con blobs estarán más fragmentadas que antes.

Tampoco se recomienda restaurar con un tamaño de página menor, porque puede disminuir el rendimiento de los índices y las tablas sin blobs.

Tampoco debe intentar cambiar campos blob a campos varchar: los campos varchar siempre se almacenan como parte de un registro, por lo que el registro puede tener 2 o más fragmentos (ser colocado en 2 o más páginas de datos) si no cabe en la página de datos.

p.d. IBAnalyst puede reportar estas tablas “por error”, por ejemplo, una tabla tenía campos blob con datos, pero fueron eliminados de la estructura de la tabla. Desafortunadamente no hay una opción configurable para esa advertencia, porque la calculamos exactamente a partir de los datos reportados por el servidor (estadísticas).

6. Relación entre VerLen y RecLength

a) VerLen >= 90% de RecLength: las versiones que ve en la columna Versión son mayormente eliminaciones de registros. Cuantos más registros se eliminen, menor será RecLength (hasta 0 bytes). También VerLen puede ser mayor que RecLen si actualiza su tabla con datos de cadena más grandes que los almacenados en los registros originales.

b) VerLen <= 80% de RecLength: las versiones son mayormente actualizaciones de registros.

No podemos diferenciar estos casos con más precisión porque las estadísticas muestran el tamaño promedio de registro y versión para toda la tabla, mientras que el número de versiones visibles para transacciones concurrentes puede variar.

7. ¿Por qué IBAnalyst nombra algunos índices como “malos”?

Los índices que tienen un valor de selectividad inferior a 0.01 se marcan como “malos” en IBAnalyst (ver ayuda de la vista Índices). Hay varias causas para nombrar un índice particular como malo:

1. La selectividad de ese índice es inferior a 0.01. Teóricamente el optimizador no debe usar ese índice, pero lo hace si no existen otros índices (para cláusulas where, order by o join, al menos).

2. Tal índice causa una recolección de basura muy lenta. Este problema no existe en InterBase 7.1/7.5, y se corregirá en Firebird 2.0.

3. Este índice hace que el proceso de restauración sea muy lento, y se crea muy lentamente (create/alter index active). Esto se debe a que la cadena de números de registro es grande para una clave de índice.

4. Si este índice se usa en una cláusula where, el uso de memoria dependerá del valor que se busque (tamaño del mapa de bits). Dado que la cadena de registros puede ser grande (muchas duplicaciones de clave), el consumo de memoria también será grande.

5. Si ese índice se usa en “order by”, y hay muchas duplicaciones principalmente en valores de clave inferiores (dependiendo del orden de clasificación del índice), habrá muchas lecturas de páginas de índice, lo que ralentizará la consulta.

Esto se debe a que IBAnalyst no puede ignorar la existencia de tales índices.

El peor caso para un índice es cuando tiene la columna Uniques = 1, es decir, todos los valores para la columna indexada son iguales. Estos índices se enumeran en “Índices inútiles” en la página Resumen.

Por supuesto, para su aplicación tal índice puede ser “bueno”. Por ejemplo, si los registros tienen un indicador de “archivo” en alguna columna, y su aplicación busca por índice en esa columna solo para datos actuales, no archivados. Así, depende de usted si tenemos razón al nombrar ese índice “malo” o no.

8. ¿Qué pasa si un índice “malo” es creado por una restricción de Foreign Key?

Bueno, el párrafo anterior muestra que es mejor eliminar los índices “malos” (si no los usa para buscar claves que tengan menos duplicaciones que otras claves). Pero, si tal índice es creado por una clave externa, solo puede eliminarlo eliminando la clave externa. Eliminar la clave externa deshabilitará la verificación de la restricción de relación, lo que puede ser inaceptable.

Puede reemplazar la FK por disparadores, pero con algunas restricciones. La FK controla las relaciones de registros usando el índice, y el índice “ve” todas las claves para todos los registros independientemente del estado de las transacciones. Pero los disparadores funcionan solo en el contexto de la transacción del cliente. Entonces, al reemplazar la FK por disparadores, debe asegurarse de que:

• Los registros no se eliminarán de la tabla maestra, o se eliminarán en modo “reserva de tabla instantánea”
• La columna usada por la PK en la tabla maestra nunca se modificará. Puede restringir esto con un disparador before update.

Si mantiene estas condiciones, puede eliminar la Foreign Key particular. Por supuesto, no cree un índice manualmente en esa columna.

9. ¿Por qué en la fila de porcentaje de versión de datos solo hay 12 megabytes de datos, pero tengo una base de datos de 140 megabytes?

1. IBAnalyst aquí muestra el volumen de datos “puro”, sin contar otras estructuras de la base de datos (índices, metadatos…) y la fragmentación de páginas.

2. Después de la restauración, InterBase y Firebird dejan algo de espacio libre (15-25%) en las páginas de datos para hacer más rápidas futuras actualizaciones/eliminaciones.

3. Hay un comportamiento específico del servidor cuando deja las páginas de datos fragmentadas en ~50%, si el tamaño de registro de esa tabla es bajo, aproximadamente 11-22 bytes.

10. Cómo mejorar el rendimiento del optimizador en caso de actualizaciones frecuentes

Las estadísticas de índices se almacenan en la columna RDB$INDICES.RDB$STATISTICS, y se actualizan de 3 maneras:

1. SET STATISTICS INDEX

2. ALTER INDEX ACTIVE, o CREATE INDEX …

3. Proceso de restauración (todos los índices se reconstruyen así como “ALTER INDEX ACTIVE”)

El optimizador usa esta información de estadísticas para preparar consultas. Usando los valores de estadísticas, el optimizador puede decidir que un índice es “suficientemente bueno” o “no útil” para recuperar registros.

Si las estadísticas no se actualizaron durante mucho tiempo, el optimizador puede producir un plan deficiente porque los valores de estadísticas existentes no corresponden al estado real de las cosas, ya que los datos de la tabla pueden haber cambiado significativamente (por ejemplo, la cantidad de registros aumentó 5-10 veces, o viceversa, todos los registros fueron eliminados).

Puede reemplazar el plan automático deficiente con un PLAN explícito para una consulta particular, pero este no es un buen enfoque, porque los datos pueden cambiar significativamente después de que se desarrolló el plan.

La forma alternativa (y correcta) es actualizar las estadísticas periódicamente aplicando la declaración SET STATISTICS para todos los índices. Puede programar la ejecución de un script SQL para actualizar las estadísticas usando ISQL o la herramienta lista para usar gidx (solo Windows).

Si tiene algunas tablas con registros diferentes recargados periódicamente, este enfoque no ayudará. Consideremos el ejemplo:

• La tabla A se carga con datos 4-5 veces al día.
• Después del procesamiento de los datos cargados, todos los registros en la tabla A se eliminan.

En este caso, podemos ver 2 valores de estadísticas correctos para los índices en la tabla A: cuando está cargada con datos, y cuando está vacía. Entonces, las estadísticas recalculadas en la tabla cargada serán inútiles cuando la tabla esté vacía, y viceversa.

Para evitar esto, necesita recalcular las estadísticas para los índices en la tabla A solo cuando la tabla esté llena de datos. Lo mejor es antes de que se ejecuten consultas en esa tabla.

Desde la versión 1.91, IBAnalyst muestra la diferencia de estadísticas de índices y le permite recalcularlas en cualquier momento. Primero debe observar la información de registros de la tabla: ¿es el conteo promedio habitual de registros o no? Si es así, puede recalcular la selectividad del índice con seguridad. Si no, quizás sea mejor no tocar las estadísticas del índice, porque podría hacer que el optimizador produzca planes de consulta aún peores.