IBAnalyst: Comprendiendo tu base de datos
Dmitri Kuzmenko, [email protected], última actualización 31-marzo-2014
He estado trabajando con InterBase desde 1994. En aquel entonces, la mayoría de las bases de datos eran pequeñas y no requerían ningún ajuste. Por supuesto, había ocasiones en las que tenía que cambiar ibconfig en un servidor y reconfigurar el hardware o el sistema operativo, pero eso era casi todo lo que podía hacer para ajustar el rendimiento.
Hace cuatro años, nuestra empresa comenzó a brindar soporte técnico y capacitación a usuarios de InterBase. Trabajar con muchas bases de datos de producción también me enseñó muchas cosas diferentes. Sin embargo, la mayor parte de lo que aprendí concernía a las aplicaciones: uso de parámetros de transacción, optimización de consultas y conjuntos de resultados.
Por supuesto, había sabido durante bastante tiempo sobre gstat, la herramienta que proporciona información de estadísticas de la base de datos. Si alguna vez has mirado la salida de gstat o has leído opguide.pdf al respecto, sabrías que la salida estadística parece solo un montón de números y nada más. Vale, puedes descubrir información de fragmentación para una tabla o índice en particular, pero ¿qué otra información útil se puede obtener?
Afortunadamente, antes de trabajar con InterBase, me interesaban diferentes estructuras de datos, cómo se almacenan y qué algoritmos usan. Esto me ayudó a interpretar la salida de gstat. En ese momento decidí escribir una herramienta que pudiera analizar la salida de gstat para ayudar en el ajuste de la base de datos o al menos identificar la causa de los problemas de rendimiento.
Historia larga, pero el resultado fue que se creó IBAnalyst. A pesar de mi experiencia, todavía me permite encontrar cosas muy interesantes o problemas de rendimiento en diferentes bases de datos.
Los sistemas reales tienen un rendimiento en tiempo de ejecución que fluctúa como una ola. La amplitud de tales ‘olas’ puede ser baja o alta, por lo que puedes ver cómo el rendimiento difiere de un día a otro (o de hora en hora). El rendimiento real depende de muchos factores, incluido el diseño de la aplicación, la configuración del servidor, la concurrencia de transacciones, la basura de versiones en la base de datos y demás. Para descubrir qué está sucediendo en una base de datos (tanto aspectos positivos como negativos del rendimiento), deberías al menos echar un vistazo a las estadísticas de la base de datos de vez en cuando.
Los sistemas reales tienen un rendimiento en tiempo de ejecución que fluctúa como una ola. La amplitud de tales ‘olas’ puede ser baja o alta, por lo que puedes ver cómo el rendimiento difiere de un día a otro (o de hora en hora). El rendimiento real depende de muchos factores, incluido el diseño de la aplicación, la configuración del servidor, la concurrencia de transacciones, la basura de versiones en la base de datos y demás. Para descubrir qué está sucediendo en una base de datos (tanto aspectos positivos como negativos del rendimiento), deberías al menos echar un vistazo a las estadísticas de la base de datos de vez en cuando.
Echemos un vistazo a las capacidades de IBAnalyst. IBAnalyst puede tomar estadísticas de gstat o de la API de Servicios y compilarlas en un informe que te brinda información completa sobre la base de datos, sus tablas e índices. Tiene advertencias in situ que están disponibles durante la navegación de las estadísticas; también incluye comentarios de sugerencias e informes de recomendaciones.
Información de la base de datos
Figura 1 Resumen de estadísticas de la base de datos
El resumen que se muestra en la Figura 1 proporciona información general sobre tu base de datos. Las advertencias o comentarios mostrados se basan en conocimientos cuidadosamente recopilados de un gran número de bases de datos de producción del mundo real.
Nota: Todas las figuras en este artículo contienen estadísticas de gstat que se tomaron de una base de datos de producción del mundo real (con el permiso de sus propietarios).
Como dije antes, las estadísticas crudas de la base de datos parecen crípticas y son difíciles de interpretar. IBAnalyst resalta cualquier problema potencial claramente en amarillo o rojo y el detalle del problema se puede leer simplemente colocando el cursor sobre la entrada relevante y leyendo la sugerencia que se muestra.
¿Qué podemos descubrir de la figura anterior? Esta es una base de datos de dialecto 3 con un tamaño de página de 4096 bytes. Hace seis a ocho años, los desarrolladores usaban un tamaño de página predeterminado de 1024 bytes, pero en tiempos más recientes, un tamaño de página tan pequeño podría llevar a muchos problemas de rendimiento. Dado que esta base de datos tiene un tamaño de página de 4k, no se muestra ninguna advertencia, ya que este tamaño de página está bien.
A continuación, podemos ver que el parámetro Forced Write está configurado en OFF y marcado en rojo. InterBase 4.x y 5.x por defecto tenían este parámetro en ON. Forced Writes en sí es un método de caché de escritura: cuando está en ON, escribe los datos cambiados inmediatamente al disco, pero en OFF significa que las escrituras se almacenarán durante un tiempo desconocido por el sistema operativo en su caché de archivos. InterBase 6 crea bases de datos con Forced Writes en OFF.
¿Por qué está marcado en rojo en el informe de IBAnalyst? La respuesta es simple: usar escrituras asíncronas puede causar corrupción de la base de datos en casos de fallos de energía, del sistema operativo o del servidor.
Consejo: Es interesante que las interfaces modernas de HDD (ATA, SATA, SCSI) no muestran ninguna diferencia importante en el rendimiento con Forced Write configurado en On u Off(1).
Lo siguiente en el informe es el misterioso “intervalo de barrido”. Si es positivo, establece el tamaño del espacio entre la transacción más antigua (2) y la transacción de instantánea más antigua, en el cual el motor se alerta de la necesidad de iniciar una recolección de basura automática. En algunos sistemas, alcanzar este umbral causará un efecto de “pérdida repentina de rendimiento”, y como resultado a veces se recomienda que el intervalo de barrido se configure en 0 (deshabilitando el barrido automático por completo). Aquí, el intervalo de barrido está marcado en amarillo, porque el valor del espacio de barrido es negativo, lo cual puede ser en estadísticas de InterBase 6.0, Firebird y Yaffil pero no en InterBase 7.x. Cuando el valor del espacio de barrido es mayor que el intervalo de barrido (si el intervalo de barrido no es 0), la entrada del informe para el intervalo de barrido se marcará en rojo con una sugerencia apropiada.
Examinaremos las siguientes 8 filas como un grupo, ya que todas muestran aspectos del estado de transacciones de la base de datos:
- La transacción más antigua es la transacción no confirmada más antigua. Cualquier número de transacción inferior es para transacciones confirmadas, y no hay versiones de registros disponibles para tales transacciones. Los números de transacción superiores a la transacción más antigua son para transacciones que pueden estar en cualquier estado. Esto también se llama la “transacción interesante más antigua”, porque se congela cuando una transacción termina con rollback, y el servidor no puede deshacer sus cambios en ese momento.
- La instantánea más antigua: la transacción activa más antigua (es decir, aún no confirmada) que existía al inicio de la transacción que actualmente es la transacción “interesante” más antigua. Indica el número de transacción de instantánea más bajo que está interesado en versiones de registros.
- La activa más antigua: la transacción activa actualmente más antigua (3).
- La próxima transacción: el número de transacción que se asignará a una nueva transacción.
- Transacciones activas: IBAnalyst dará una advertencia si el número de transacción activa más antigua es un 30% menor que el recuento diario de transacciones. Las estadísticas no indican si hay otras transacciones activas entre la activa más antigua y la próxima transacción, pero puede haber tales transacciones. Generalmente, si la activa más antigua se queda atascada, hay dos causas posibles: a) que alguna transacción esté activa durante mucho tiempo o b) que el diseño de la aplicación permita que las transacciones se ejecuten durante mucho tiempo. Ambas causas impiden la recolección de basura y consumen recursos del servidor.
- Transacciones por día: esto se calcula a partir de la próxima transacción, dividido por el número de días transcurridos desde la creación de la base de datos hasta el punto donde se recuperan las estadísticas. Esto solo puede ser correcto para bases de datos de producción, o para bases de datos que se restauran periódicamente desde una copia de seguridad, lo que hace que la numeración de transacciones se reinicie.
Como ya has aprendido, si hay advertencias, se muestran como líneas de colores, con sugerencias claras y descriptivas sobre cómo corregir o prevenir el problema.
Cabe señalar que las estadísticas de la base de datos no siempre son útiles. Las estadísticas que se recopilan durante operaciones de trabajo y mantenimiento pueden carecer de significado.
No recopiles estadísticas si:
- Acabas de restaurar tu base de datos
- Realizaste una copia de seguridad (gbak -b db.gdb) sin el interruptor -g
- Recientemente realizaste un barrido manual (gfix -sweep)
Las estadísticas que obtienes en tales ocasiones serán prácticamente inútiles. También es correcto que durante el trabajo normal puede haber momentos en que la base de datos esté en un estado perfecto, por ejemplo, cuando las aplicaciones hacen menos carga de base de datos de lo habitual (los usuarios están almorzando o es un momento tranquilo en el día laboral).
¿Cómo puedes saber cuándo hay algo mal con la base de datos?
Tus aplicaciones pueden estar tan bien diseñadas que siempre trabajarán con transacciones y datos correctamente, sin crear espacios de barrido, sin acumular muchas transacciones activas, sin mantener instantáneas de larga duración y demás. Generalmente no sucede así (lo siento, colegas).
La razón más común es que los desarrolladores prueban sus aplicaciones ejecutando solo dos o tres usuarios simultáneos. Cuando la aplicación se usa luego en un entorno de producción con quince o más usuarios simultáneos, la base de datos puede comportarse de manera impredecible. Por supuesto, el modo multiusuario puede funcionar bien porque la mayoría de los conflictos multiusuario se pueden probar con dos o tres aplicaciones ejecutándose simultáneamente. Sin embargo, con un mayor número de usuarios, pueden surgir problemas de recolección de basura. Tales problemas potenciales se pueden detectar si recopilas estadísticas de la base de datos en los momentos correctos.
Información de tablas
Echemos un vistazo a otra salida de muestra de IBAnalyst.
.jpg)
Figura 2 Estadísticas de tablas
La vista de estadísticas de tablas de IBAnalyst también es muy útil. Puede mostrar qué tablas tienen muchas versiones de registros, dónde se realizaron un gran número de actualizaciones/eliminaciones, tablas fragmentadas, con fragmentación causada por actualizaciones/eliminaciones o por blobs, y demás. Puedes ver qué tablas se están actualizando con frecuencia y cuál es el tamaño de la tabla en megabytes. La mayoría de estas advertencias son personalizables.
En este ejemplo de base de datos hay varios problemas. En primer lugar, el color amarillo en la columna VerLen advierte que el espacio ocupado por las versiones de registros es mayor que el ocupado por los propios registros. Esto puede resultar de actualizar muchos campos en un registro o por eliminaciones masivas. Consulta las filas en las que la columna MaxVers está marcada en azul. Esto muestra que solo se almacena una versión por registro y, en consecuencia, que el problema se debe a eliminaciones masivas. El valor en la columna Versions muestra cuántos registros se eliminaron.
Las transacciones activas de larga duración que impiden la recolección de basura son la razón principal de la degradación del rendimiento. Para algunas tablas puede haber muchas versiones que aún están “en uso”. El servidor no puede decidir si realmente están en uso, porque las transacciones activas potencialmente necesitan cualquiera o todas estas versiones. En consecuencia, el servidor no considera estas versiones como basura, y lleva cada vez más tiempo construir un registro correcto a partir de muchas versiones cuando una transacción lo lee. En la Figura 2 puedes ver dos tablas que tienen un recuento de versiones tres veces mayor que el recuento de registros. Usando esta información también puedes verificar si el hecho de que tus aplicaciones actualicen estas tablas con tanta frecuencia es por diseño o debido a un error.
La vista de índices
Los índices son utilizados por el motor de la base de datos para hacer cumplir las restricciones de clave primaria, clave externa y unicidad. También aceleran la recuperación de datos. Los índices únicos son los mejores para recuperar datos, pero el nivel de beneficio de los índices no únicos depende de la diversidad de los datos indexados.
Por ejemplo, mira ADDR_ADDRESS_IDX6. En primer lugar, el nombre del índice en sí sugiere que fue creado manualmente. Si las estadísticas se tomaron mediante la API de Servicios con información de metadatos, puedes ver qué columnas están indexadas (en IBAnalyst 1.83 y superiores). Para el índice en examen puedes ver que tiene 34999 claves, TotalDup es 34995 y MaxDup es 25056. Ambas columnas de duplicados están marcadas en rojo. Esto se debe a que solo hay 4 valores de clave únicos entre todas las claves en este índice, como se puede ver en la columna Uniques. Además, la cadena de duplicados más grande (clave que apunta a registros con el mismo valor de columna) es 25056, es decir, casi todas las claves almacenan uno de cuatro valores únicos. Como resultado, este índice podría:
- Reducir la velocidad del proceso de restauración. Está bien, treinta y cinco mil claves no es un gran problema para las bases de datos y el hardware modernos, pero el impacto debe tenerse en cuenta de todos modos.
- Ralentizar la recolección de basura. Los índices con un bajo número de valores únicos pueden impedir la recolección de basura hasta diez veces en comparación con un índice completamente único. Este problema se ha resuelto en InterBase 7.1/7.5 y Firebird 2.0.
- Producir lecturas de páginas innecesarias cuando el optimizador lee el índice. Depende del valor que se busque en una consulta particular: buscar por un índice que tiene un valor mayor para MaxDup será más lento. Buscar por valor en una columna que tiene menos valores duplicados será más rápido, pero solo tú sabes que la columna está indexada.
Por eso IBAnalyst llama tu atención sobre tales índices, marcándolos en rojo y amarillo, e incluyéndolos en el informe de Recomendaciones. Desafortunadamente, la mayoría de los índices “malos” se crean automáticamente para hacer cumplir las restricciones de clave externa. En algunos casos, este problema se puede resolver evitando, mediante disparadores, eliminaciones o actualizaciones de la clave primaria en tablas de referencia. Pero si no es posible implementar tales cambios, IBAnalyst te mostrará los índices “malos” en Claves Externas cada vez que veas las estadísticas.
Informes
No hay necesidad de revisar todo el informe cada vez, detectando el color de las celdas y leyendo sugerencias para nuevas advertencias. Se puede obtener información más directa y detallada utilizando la función de Recomendaciones de IBAnalyst. Simplemente carga las estadísticas y ve al menú Informes/Ver Recomendaciones. Este informe proporciona un análisis paso a paso, incluyendo advertencias descriptivas más detalladas sobre escrituras forzadas, intervalo de barrido, actividad de la base de datos, estado de transacciones, tamaño de página de la base de datos, barrido, páginas de inventario de transacciones, tablas fragmentadas, tablas con muchas versiones de registros, eliminaciones/actualizaciones masivas, índices profundos, índices no amigables para el optimizador, índices inútiles e incluso tablas vacías. Toda esta información y las sugerencias que la acompañan se crean dinámicamente basándose en las estadísticas cargadas.
Como ejemplo de la salida del informe, echemos un vistazo a un informe generado para las estadísticas de la base de datos que viste anteriormente en este artículo:
“El tamaño general de las páginas de inventario de transacciones (TIP) es grande: 94 kilobytes o 23 páginas. La transacción Read_committed usa el TIP global, pero las transacciones snapshot hacen copias propias del TIP en memoria. Un tamaño grande de TIP puede ralentizar el rendimiento. Intenta ejecutar el barrido manualmente (gfix -sweep) para disminuir el tamaño del TIP.”
Aquí hay otra cita de la parte de tablas/índices del informe:
“Cantidad de tablas versionadas: 8. Una gran cantidad de versiones de registros generalmente ralentiza el rendimiento. Si hay muchas versiones de registros en una tabla, entonces la recolección de basura no funciona, o los registros no están siendo leídos por ninguna declaración select. Puedes intentar seleccionar count(*) en esas tablas para forzar la recolección de basura, pero esto puede llevar mucho tiempo (si hay muchas versiones e índices no únicos) y puede no tener éxito si hay al menos una transacción interesada en estas versiones.
Aquí está la lista de tablas con una proporción versión/registro mayor que 3:
| Tabla | Registros | Versiones | Tamaño Reg/Vers |
| CLIENTS_PR | 3388 | 10944 | 92% |
| DICT_PRICE | 30 | 1992 | 45% |
| DOCS | 9 | 2225 | 64% |
| N_PART | 13835 | 72594 | 83% |
| REGISTR_NC | 241 | 4085 | 56% |
| SKL_NC | 1640 | 7736 | 170% |
| STAT_QUICK | 17649 | 85062 | 110% |
| UO_LOCK | 283 | 8490 | 144% |
Resumen
IBAnalyst es una herramienta invaluable que ayuda al usuario a realizar un análisis detallado de las estadísticas de bases de datos Firebird o InterBase e identificar posibles problemas con una base de datos en términos de rendimiento, mantenimiento y cómo una aplicación interactúa con la base de datos. Toma estadísticas crípticas de la base de datos y las muestra de manera gráfica y fácil de entender, y automáticamente hará sugerencias sensatas sobre cómo mejorar el rendimiento de la base de datos y facilitar su mantenimiento.
1 InterBase 7.5 y Firebird 1.5 tienen características especiales que pueden vaciar periódicamente páginas no guardadas si las Escrituras Forzadas están Desactivadas.
2 La transacción más antigua es la misma transacción interesante más antigua, mencionada en todas partes. La salida de gstat no muestra esta transacción como “interesante”.
3 Ann Harrison dice que la más antigua activa es la transacción más antigua que estaba activa cuando comenzó la transacción activa más antigua actual. Para las aplicaciones, esto no es una gran diferencia aquí.