Mejorando el rendimiento de las bases de datos (3)

1 comentario

En artículos previos ya habíamos visto algunos métodos que podemos usar para mejorar el rendimiento de nuestras bases de datos ya que lo usual es que deseemos obtener más velocidad para todas las operaciones (inserciones, actualizaciones, borrados, consultas).

En cuando al Sistema Operativo a usar podemos leerlo en este enlace:

https://firebird21.wordpress.com/2013/08/15/cual-sistema-operativo-usar-en-el-servidor/

Administración:

En este apartado veremos lo que puede hacer el Administrador de la Base de Datos para que ésta funcione mejor. Una de las tantas cosas buenas que tiene Firebird es que no necesita de un Administrador dedicado exclusivamente a su tarea, como sí es el caso de Oracle o de SQL Server. Las bases de datos de Firebird requieren de un mantenimiento mínimo, pero de todas maneras requieren de mantenimiento, aunque sea de vez en cuando. Las tareas básicas del Administrador son:

  1. Detectar y eliminar las transacciones que demoran mucho en finalizar
  2. Hacer un barrido manual
  3. Hacer un backup y verificar que funcione
  4. Verificar los identificadores de las transacciones
  5. Verificar las estadísticas de los índices y reconstruirlos si es necesario

1. Detectando y eliminando transacciones que demoran mucho en finalizar

Lo que más puede degradar el rendimiento de las bases de datos es que se actualicen o se borren filas y que la transacción que actualizó o borró esas filas no termine con un COMMIT o con un ROLLBACK.

Una transacción que no terminó ni con un COMMIT ni con un ROLLBACK para el Firebird continúa activa, aunque hayan pasado días, semanas, o meses. Y continúa activa porque el Firebird no puede saber, no puede adivinar, si la transacción no finalizó porque el Desarrollador no quiere que finalice aún o porque el Desarrollador se olvidó de escribir un COMMIT o un ROLLBACK. Como el Firebird no es adivino entonces por las dudas mantiene a la transacción abierta, no la cierra por su propia cuenta ya que cerrar la transacción es competencia exclusiva del Desarrollador, no del Firebird.

Entonces, una de las tareas del Administrador de la Base de Datos es verificar si hay transacciones que están activas desde hace mucho tiempo. En una aplicación bien programada ninguna transacción debería tardar más de unos cientos de milisegundos en finalizar. Si una transacción tarda más de un segundo eso ya debería llamar la atención a cualquier Administrador competente. Y ni que hablar si hace horas o días que está activa. Para saber como detectar y eliminar las transacciones que están activas desde hace mucho tiempo puedes leer estos artículos:

https://firebird21.wordpress.com/2013/03/02/detectando-aplicaciones-y-usuarios-que-mantienen-las-transacciones-abiertas-durante-mucho-tiempo/

https://firebird21.wordpress.com/2013/05/07/detectando-una-consulta-que-esta-tardando-mucho/

Aunque el título del último artículo dice “detectando una consulta” en realidad se refiere a transacciones.

Después de detectar y eliminar la transacción problemática hay que buscar y encontrar el motivo. ¿Por qué esa transacción demoró tanto? ¿Cuál es el programa que inició esa transacción? ¿por qué no finalizó ni con un COMMIT ni con un ROLLBACK?

Aquí, el Administrador debe comunicarse con el Desarrollador para informarle del problema que encontró y pedirle que lo solucione a la brevedad posible.

2. Haciendo un barrido manual

El barrido (sweep, en inglés) debe hacerse sí o sí cuando la diferencia entre la OST y la OAT es grande. Aquí, la palabra “grande” depende del contexto. Por defecto el Firebird inicia el sweep cuando esa diferencia es mayor que 20.000 pero ese número puede ser cambiado si se lo desea. Lo importante a recordar es que el sweep es un proceso que consume muchísimos recursos y en consecuencia todas las operaciones con la Base de Datos pueden volverse lentísimas. Es por ese motivo que la mayoría de los Administradores ponen el intervalo del sweep en 0 (cero) para realizarlo de forma manual, porque si se inicia de forma automática entorpecerá las actividades normales de los usuarios desde que empieza hasta que finaliza (según la conocida Ley de Murphy empezará en el peor momento posible, cuando más velocidad necesitan los usuarios), lo cual puede demorar horas en una Base de Datos muy grande y con mucha basura acumulada. Es por eso que muchos Administradores hacen el sweep en horarios en que nadie está usando la Base de Datos (y si eso no es posible, entonces en un día y hora en que muy pocas personas la están usando).

Puedes leer más sobre el sweep en este artículo:

https://firebird21.wordpress.com/2013/09/11/entendiendo-sweep-y-garbage-collection/

Resumiendo:

a) Si no se hace el sweep y la diferencia entre la OST y la OAT es grande entonces todas las operaciones se volverán muy lentas.

b) Mientras se está haciendo el sweep todas las operaciones se vuelven muy lentas

Recomendación:

Hacer el sweep cuando nadie está usando la Base de Datos o cuando muy pocas personas la están usando.

3. Haciendo un backup y verificando que funcione

Tener backups actualizados es algo tan elemental que supongo que no es necesario abundar sobre ese tema. Ningún profesional de la Informática tiene la menor duda al respecto.

Pero la tarea del Administrador no se limita a hacer backups, también debe verificar que ese backup pueda ser restaurado exitosamente. ¿Por qué? porque no hay algo peor que creer que se tiene un backup actualizado y a la hora de restaurarlo se descubre que no es posible lograrlo, que está dañado y es inútil e inservible.

Una regla muy conveniente para seguir es la siguiente:

    • Se hace un backup
    • Se lo restaura para asegurarse de que funciona
    • Se copia el backup en otro dispositivo y se lo lleva lejos de la computadora donde se encuentra el Servidor

De lo anterior se deduce que siempre tendremos al menos dos backups idénticos. Uno de ellos se mantendrá cerca del Servidor para poder restaurar rápidamente la Base de Datos en caso de ésta tener algún problema. El otro backup se mantendrá lejos del Servidor (en otro edificio o inclusive en otra localidad) para poder restaurar la Base de Datos en caso de catástrofe (terremoto, incendio, inundación, explosión de una bomba, robo de la computadora, etc). También es muy conveniente tener una copia en Internet. En esta época hay muchos sitios que permiten almacenar archivos grandes (MediaFire, Mega, RapidShare, DropBox, etc.) y tener el backup guardado en ellos es muy conveniente porque permiten que ese backup sea recuperado aún en el caso de ocurrir una catástrofe.

Si el backup se hace con el programa GBAK.EXE se tienen además otras ventajas:

    • El backup no tiene basura porque la basura no se  guarda en el backup
    • Cuando se restaura el backup se reconstruyen los índices así que al finalizar la restauración los índices estarán perfectos

4. Verificando los identificadores de las transacciones

Todo buen Administrador debe verificar periódicamente los identificadores de las transacciones porque como sabemos así se puede detectar si la Base de Datos tiene mucha basura. Puedes leer más en este artículo:

https://firebird21.wordpress.com/2013/09/08/entendiendo-los-identificadores-de-las-transacciones/

5. Verificando las estadísticas de los índices y reconstruyéndolos

Los índices pueden ir degradándose, eso ocurre frecuentemente cuando en una tabla se actualizaron o se borraron muchas filas. Por ese motivo es conveniente reconstruirlos de vez en cuando, puedes leer más en estos artículos:

https://firebird21.wordpress.com/2013/03/02/mantenimiento-de-indices/

https://firebird21.wordpress.com/2013/03/03/recreando-los-indices-de-las-tablas/

https://firebird21.wordpress.com/2013/03/09/selectividad-de-los-indices/

https://firebird21.wordpress.com/2013/08/24/usando-indices-en-firebird/

Conclusión:

Aunque afortunadamente Firebird no requiere que una persona trabaje exclusivamente como Administrador de las Bases de Datos, sí hay tareas que deben realizarse de vez en cuando para asegurarse de que las bases de datos se encuentren en un buen estado operativo, de lo contrario se irán degradando y el rendimiento decaerá. Si todo se realiza de la forma correcta, la tarea del Administrador no demorará más que unos cuantos minutos cada día e inclusive si la empresa u organización no es muy grande el trabajo podría ser de unos cuantos minutos a la semana o al mes.

Si todas las transacciones son cortas, todas las transacciones finalizan con un COMMIT o con un ROLLBACK, se hacen backups y restauraciones diarios, entonces nunca deberías tener problemas y todo debería funcionar de maravillas.

Pero no te olvides que tú o alguien más debe dedicarle un poco de tiempo a la tarea de Administrar la Base de Datos, solamente el ojo humano puede detectar fallas, eso es algo que no se puede automatizar.

Artículos relacionados:

¿Cual Sistema Operativo usar en el Servidor?

Detectando aplicaciones y usuarios que mantienen las transacciones abiertas durante mucho tiempo

Detectando una consulta que está tardando mucho

Entendiendo sweep y garbage collection

Entendiendo los identificadores de las transacciones

Mantenimiento de índices

Recreando los índices de las tablas

Recreando todos los índices de todas las tablas

Recreando índices y calculando estadísticas

Selectividad de los índices

Usando índices en Firebird

El índice del blog Firebird21

Anuncios

SQL_RENDIMIENTO. Verificando la velocidad de las operaciones

4 comentarios

Muchas veces nos interesa saber que tan rápidas son las operaciones INSERT, UPDATE, DELETE, SELECT en una computadora Cliente o mismo en el Servidor del Firebird.

Esa información puede ser muy útil para recomendar la compra de más memoria RAM, un mejor procesador, un router de mejor calidad, etc.

La mayoría de las empresas disponen de varias computadoras y las velocidades con las cuales se realizan las operaciones en ellas no son las mismas, así que puede resultarnos muy provechoso poder determinar cuales son las computadoras lentas. Esa lentitud puede estar en el Servidor, en el Cliente, o en ambos.

Por ese motivo hice un programa que me ayudará con esa tarea y ahora lo comparto con los lectores de este blog. El enlace para descargarlo es:

http://www.mediafire.com/download/eudns020fs6xf64/SQL_RENDIMIENTO.ZIP

Está desarrollado en Visual FoxPro y tiene todos los programas fuente incluidos para que quienes conozcan dicho lenguaje puedan realizarle todas las modificaciones que crean pertinentes. También podría servirles como base para crear otros programas similares.

SQL_RENDIMIENTO1

Captura 1. Si haces clic en la imagen la verás más grande

Como puedes ver en la Captura 1, es bastante auto-explicativo pero si tienes alguna duda puedes preguntarme.

Para usarlo:

  1. Copia la Base de Datos de nombre VELOCIDAD.FDB en un disco duro de la computadora donde está instalado el Servidor del Firebird
  2. Crea una carpeta en la computadora Cliente
  3. Descomprime el archivo SQL_RENDIMIENTO.ZIP en esa carpeta
  4. Ejecuta el programa SQL_RENDIMIENTO.EXE

También podrías ejecutarlo desde un pen-drive pero eso no es recomendable pues allí tendrás que controlar una variable más: la velocidad del pen-drive, pues si es USB 3.0 será mucho más rápido que USB 2.0 y este será mucho más rápido que USB 1.0, y en una computadora Cliente podrías tener USB 2.0 y en otra USB 3.0 y en otra USB 1.0 y por lo tanto la comparación no sería justa.

Mientras el programa está verificando las velocidades va mostrando unos mensajes en el campo de edición “Comentarios” y al finalizar la verificación muestra un resúmen que puede servirte de guía para encontrar las fallas. Ese resúmen puede ser guardado en un archivo de texto (cuya ubicación y nombre debes colocar a continuación de: “Grabar en el archivo”).

Las demoras en el Cliente típicamente son mayores que las demoras en el Servidor porque el primero necesita que los datos vayan al Servidor y regresen y eso toma su tiempo. Es por eso que se muestran ambas demoras.

El programa califica con una nota (EXCELENTE, MUY BUENO, BUENO, ACEPTABLE, REGULAR, MALO, MUY MALO, PÉSIMO) a ambas computadoras, dependiendo del tiempo total con la cual se realizaron todas las operaciones. También da unos consejos (que en versiones posteriores podrían ampliarse) para mejorar esas velocidades. Debes tener en mente que esa calificación está basada en el procesamiento de 1.000.000 de filas y que si varías dicha cantidad de filas la calificación podría variar ya que cuantas más filas proceses el rendimiento mejorará (si multiplicas por 10 la cantidad de filas el tiempo que se demora en procesarlas el Firebird no se multiplica por 10, sino quizás por 2).

Otro punto muy importante a considerar es el siguiente: una Base de Datos totalmente vacía es mucho más rápida que una Base de Datos en la cual ya se realizaron operaciones aunque se hayan eliminado luego todas esas operaciones. ¿Qué significa esto? que siempre antes de ejecutar el programa SQL_RENDIMIENTO.EXE debes hacer un ciclo backup/restore en la Base de Datos VELOCIDAD.FDB y usar la Base de Datos restaurada para la verificación (o aún más rápido, copia la Base de Datos original, la que descargaste en el archivo .ZIP, en el Servidor) . Si no haces así, entonces cada nueva ejecución de SQL_RENDIMIENTO.EXE demorará más y eso no será justo

Entonces, antes de verificar una computadora:

  1. Copia el archivo VELOCIDAD.FDB que está en tu pen-drive en el disco duro del Servidor
  2. Crea una carpeta en el disco duro de la computadora Cliente y coloca allí a SQL_RENDIMIENTO
  3. Verifica esa computadora

Haz esto siempre, no te olvides, o los resultados mostrados no serán correctos.

Artículo relacionado:

El índice del blog Firebird21

Optimizando las consultas

2 comentarios

Este artículo está basado en el documento “Planos de Optimizaçao do Firebird” de Gladiston Santana.

Seguramente ya has leído o escuchado que el Firebird es rapidísimo para devolver el resultado de las consultas. ¿A qué se debe esa gran velocidad?

Quienes están acostumbrados a usar el modelo desktop (tablas .DBF, Paradox, Access) saben que el rendimiento está directamente ligado al rendimiento del Servidor de archivos, rendimiento de la red, tamaño de las tablas y el uso de índices correctos. Todo eso también es cierto en Firebird pero éste dispone de algo más, muy poderoso: el PLAN de optimización (“PLAN optimizer” en inglés).

¿Qué es el PLAN de optimización?

Es la lista del índice (o ningún índice) de cada tabla involucrada en una consulta que el Firebird utilizará para devolver el resultado de esa consulta (o sea, de ese SELECT)

La intención al usar un PLAN es que los resultados sean devueltos lo más rápidamente posible. Todas las consultas, absolutamente todas, tienen un PLAN de optimización, el cual puede ser puesto:

  • Automáticamente, por el Firebird
  • Manualmente, por el programador

¿Cómo el Firebird determina el PLAN?

Cuando el programador no le dice cual PLAN usar, el Firebird crea su propio PLAN usando para ello un módulo llamado “Query optimizer”, en castellano: optimizador de la consulta.

La tarea de este optimizador es analizar la consulta, evaluando: índices, combinaciones de índices, agrupamientos como sort, union, (inner, left, outer) join, y muchas cosas más y después de haber analizado todo eso evaluar el costo de esa consulta.

Este “costo” es una nota, una calificación, que indica si la optimización de la consulta es ventajosa o desventajosa. Si el optimizador encuentra que la nota es desventajosa entonces puede realizar una optimización distinta en la consulta o decidir no usar optimización. A este último caso se le llama NATURAL PLAN (o sea, un PLAN que no usa índices).

¿En qué casos se usaría el NATURAL PLAN?

Podría parecer extraña la idea de no utilizar optimización pero en algunos casos la mejor alternativa es no usar optimización ya que si se debe re-evaluar una operación o seleccionar un índice, eso a veces toma más tiempo que recorrer la tabla entera. Y en este caso no vale la pena usar el optimizador.

Este es el caso de tablas pequeñas o de SELECTs que no precisan de índices para realizar una búsqueda. También puede ocurrir que una tabla tenga una columna indexada pero que el optimizador elija no utilizar ese índice.

Ejemplo 1:

Tenemos una tabla llamada PERSONAS, la cual tiene una columna llamada PER_APELLD (apellidos de las personas) y un índice llamado IDX_PERSONAS sobre esa columna:

CREATE INDEX IDX_PERSONAS ON PERSONAS(PER_APELLD);

Ahora escribimos esta consulta, porque deseamos obtener los datos de todas las personas cuyo apellido sea ‘TORRES’ o cuyo apellido sea ‘CABRAL’:

SELECT
   *
FROM
   PERSONAS
WHERE
   PER_APELLD LIKE '%TORRES%CABRAL%'

El Firebird usará el índice IDX_PERSONAS, ¿verdad?. No, falso. No usará el índice IDX_PERSONAS como podemos ver al revisar el PLAN utilizado.

PLAN1

(haciendo clic en la imagen la verás más grande)

¿Por qué no usó el índice? Porque hay un “%” en el inicio del LIKE y eso implica que debe recorrer la tabla completa para mostrar el resultado de la consulta. Y no tiene sentido usar un índice en ese caso, ya que tomará más tiempo y no se obtendrá algún beneficio.

Inclusive, llamar al optimizador ya sería un desperdicio porque ningún índice ayudaría a acelerar esta consulta, entonces ni siquiera llamará al optimizador.

Para este caso de consultas que no pueden ser optimizadas es que existe el PLAN NATURAL.

Ejemplo 2:

Usando la misma tabla del ejemplo 1 escribimos esta consulta:

SELECT
   *
FROM
   PERSONAS
WHERE
   PER_APELLD LIKE 'TORRES%CABRAL%'

Fíjate que se borró el “%” que estaba al principio del LIKE. Volvemos a revisar cual es el PLAN utilizado y nos muestra:

PLAN2

(haciendo clic en la imagen la verás más grande)

 Y ahora sí está usando el índice porque es adecuado usarlo.

El SELECT extendido

Los SELECTs que escribimos en los dos ejemplos anteriores no son los que realmente utilizará el Firebird cuando los ejecutemos. ¿Por qué no? porque el Firebird automáticamente le agrega la cláusula PLAN a todos los SELECTs que no tengan dicha cláusula escrita.

Así, para el primer ejemplo, el SELECT que usará el Firebird es:

SELECT
   *
FROM
   PERSONAS
WHERE
   PER_APELLD LIKE '%TORRES%CABRAL%'
PLAN
   (PERSONAS NATURAL)

Fíjate que le agrega las palabras PLAN y luego el PLAN utilizado (en este caso PERSONAS NATURAL). PERSONAS es el nombre de la tabla y NATURAL le indica que no debe usar un índice.

Para el segundo ejemplo el SELECT que usará el Firebird es:

SELECT
   *
FROM
   PERSONAS
WHERE
   PER_APELLD LIKE 'TORRES%CABRAL%'
PLAN
   (PERSONAS INDEX (IDX_PERSONAS))

Fíjate que ahora sí usa el índice IDX_PERSONAS

Preparando un PLAN

El proceso de someter una consulta al optimizador para que éste la analice y determine el mejor PLAN se llama “Prepare query” (preparar la consulta). Los programadores experimentados siempre escriben un PLAN en el SELECT porque eso ahorra tiempo y mejora el rendimiento ya que el Firebird usa el PLAN especificado y no debe llamar al optimizador para determinar cual PLAN utilizará.

En EMS SQL Manager, haríamos clic en la opción “Prepare query” para preparar la consulta  y en “Explain query” para ver cual será el PLAN que utilizará el Firebird, como se ve a continuación:

PLAN3

(haciendo clic en la imagen la verás más grande)

 Entonces, como ya se le indica cual PLAN debe usar el Firebird no pierde tiempo analizando la consulta y determinando ese PLAN.

Advertencia

Si al escribir un SELECT especificamos el PLAN a utilizar y ese PLAN es el más adecuado entonces el SELECT se ejecutará más rápido, sin embargo hay que tener en cuenta algo muy importante: a veces, el PLAN que hoy es excelente puede dejar de serlo conforme las condiciones de la Base de Datos van cambiando, por ejemplo si se le van agregando nuevos índices, fórmulas, e inclusive el mismo aumento en la cantidad de datos. Esto implica que periódicamente debemos verificar el rendimiento de nuestros SELECTs para detectar aquellos planes que ya no son los más adecuados.

Un PLAN inadecuado no es solamente malo para esa consulta en particular, es malo para toda la Base de Datos porque consultas concurrentes podrían sumarse al problema y tornar al Servidor en una verdadera tortuga de tres patas.

Tiempos de ejecución

Veamos ahora cuanto tarda ejecutar cada uno de los SELECTs de los ejemplos anteriores:

PLAN4

(haciendo clic en la imagen la verás más grande)

 La tabla PERSONAS tiene 3.516.272 registros y ejecutar el primer SELECT (el que tiene PLAN NATURAL y por lo tanto no usa índices) tardó 14,093 segundos.

PLAN5

(haciendo clic en la imagen la verás más grande)

 Al ejecutar el segundo SELECT (que usa al índice IDX_PERSONAS) el tiempo se redujo considerablemente, ahora es de solamente 1,282 segundos. ¿Y qué ocurrirá si quitamos el apellido CABRAL de la consulta y dejamos solamente al apellido TORRES? ¿El tiempo mejorará o empeorará? veamos:

PLAN6

(haciendo clic en la imagen la verás más grande)

 Esto puede parecer muy extraño, ya que ahora tenemos muchos más datos (ya que hay más apellidos ‘TORRES’ que ‘TORRES CABRAL’) sin embargo el tiempo de la consulta disminuyó.

¿Por qué?

Porque el Firebird recupera los datos por páginas y en una página generalmente caben muchos registros. Esos registros ya están en la memoria y por lo tanto mostrarlos es muy rápido.

Veamos ahora lo que sucede al volver a ejecutar el último SELECT

PLAN7

(haciendo clic en la imagen la verás más grande)

 El tiempo se redujo aún más, ahora ni siquiera tardó 1 segundo en finalizar.

¿Por qué?

Porque esa consulta ya estaba en la memoria, en el caché del Firebird, y por lo tanto no tuvo que ser leída desde el disco duro, con lo cual se aceleró aún más obtener los resultados pedidos.

Lo bueno de esto es que si el mismo usuario ejecuta el mismo SELECT o si algún otro usuario lo hace, como los datos ya están en la memoria mostrarlos es súper rápido.

Estos tiempos de ejecución fueron obtenidos con una tabla que tiene 3.516.272 registros y una computadora que ya tiene varios años: Pentium IV con 4 Gbytes de RAM y de un solo núcleo. En cualquier computadora nueva los tiempos serán mucho menores.

En Firebird si multiplicamos por 10 la cantidad de registros de una tabla no aumenta por 10 el tiempo de consulta. Si la tabla PERSONAS tuviera 35.162.720 registros el tiempo de ejecución de la consulta aún sería de alrededor de 1 segundo.

En cambio, en las tablas desktop (.DBF, Paradox, Access) si multiplicamos por 10 la cantidad de registros el tiempo de consulta se multiplica por 10, por 12, por 15.

Conclusión:

Entender lo que es el PLAN y aprender a usar el PLAN correcto hará que tus consultas sean rapidísimas. Sin embargo debes recordar que a veces el PLAN va decayendo en su rendimiento cuando le vas agregando índices o fórmulas a las tablas o mismo por el aumento de los datos así que es buena práctica revisar los planes periódicamente para asegurar que siguen siendo los más adecuados.

La gran diferencia en el rendimiento que se observa cuando se usa Firebird en comparación con tablas .DBF, Paradox y Access es que Firebird, al igual que Oracle, Sybase, MSSQL, usa un PLAN. Pero mientras que por usar esos SGBD debes pagar mucho dinero, por usar Firebird no pagas, es gratis.

Artículos relacionados:

Usando un PLAN

Algo más sobre PLAN

El índice del blog Firebird21