Actualización de stored procedures y triggers

4 comentarios

¿En qué momento se actualiza un stored procedure o un trigger?

Tú cambias el contenido de un stored procedure o de un trigger, luego ejecutas el COMMIT correspondiente, y sin embargo parece que la actualización no ha funcionado.

¿Por qué?

Porque la actualización se realiza solamente cuando el caché está vacío.

En Classic eso ocurre al cerrar la conexión actual.

En SuperServer, como el caché es compartido, eso ocurre cuando se cerraron todas las conexiones.

Artículos relacionados:

El índice del blog Firebird21

El foro del blog Firebird21

 

Anuncios

Optimizando SuperServer: poniendo toda la Base de Datos en la memoria caché

5 comentarios

Hay un truco que podemos utilizar para optimizar a SuperServer, funciona también en Classic pero solamente con bases de datos mucho más pequeñas.

Como recordarás, si las filas que devolverá un SELECT ya se encuentran en la memoria caché entonces la velocidad de respuesta será muy alta porque la memoria RAM es muchísimo más rápida que el disco duro. Entonces, la idea es tener a toda la Base de Datos (o al menos a las tablas más utilizadas cuando tener a toda la Base de Datos no es posible) en la memoria caché.

Para poner a toda la Base de Datos en la memoria caché podríamos ejecutar el siguiente stored procedure:

Listado 1.

CREATE PROCEDURE LLENAR_CACHE
AS
   DECLARE VARIABLE lcNombreTabla   VARCHAR(1024);
   DECLARE VARIABLE lcComando       VARCHAR(1024);
   DECLARE VARIABLE lnCantidadFilas INTEGER;
BEGIN
   
   FOR SELECT
      RDB$RELATION_NAME
   FROM
      RDB$RELATIONS
   INTO
      :lcNombreTabla
   DO BEGIN
      lcComando = 'SELECT COUNT(*) FROM ' || lcNombreTabla;
      EXECUTE STATEMENT lcComando INTO :lnCantidadFilas;
   END

END;

Luego de ejecutar el comando EXECUTE PROCEDURE LLENAR_CACHE y el comando COMMIT, toda la Base de Datos se encontrará en la memoria caché (si hay suficiente espacio en la memoria caché, por supuesto). ¿Por qué? Porque cada vez que se lee una página de una tabla esa página es puesta en la memoria caché. La función COUNT(*) lee cada página de una tabla y por consiguiente coloca a cada página de esa tabla en la memoria caché. El stored procedure LLENAR_CACHE realiza esa operación para todas las tablas de la Base de Datos. Como resultado final, todas las páginas de todas las tablas se encontrarán en la memoria caché, si es que hay allí suficiente espacio libre.

¿Y si no hay suficiente espacio libre?

Entonces podríamos poner en la memoria caché a las tablas más utilizadas en los informes.

Listado 2.

CREATE PROCEDURE LLENAR_CACHE
AS
BEGIN
   SELECT COUNT(*) FROM BANCOS;
   SELECT COUNT(*) FROM PRODUCTOS;
   SELECT COUNT(*) FROM CLIENTES;
   SELECT COUNT(*) FROM PROVEEDORES;
   SELECT COUNT(*) FROM STOCK;
   SELECT COUNT(*) FROM MONEDAS;
END; 

Eso hará que todas las operaciones de lectura se realicen súper rápido. Pero durante el trabajo normal diario muchas operaciones de INSERT, UPDATE, y DELETE se irán ejecutando y en la memoria caché ya no se encontrarán las últimas filas insertadas, actualizadas, y borradas.

¿Y entonces?

Entonces lo que debemos hacer es volver a ejecutar el stored procedure LLENAR_CACHE para que nuevamente en la memoria caché se encuentre toda la Base de Datos (como en el caso del Listado 1.) o las tablas más utilizadas (como en el caso del Listado 2.).

Ese proceso podríamos hacerlo manualmente (por ejemplo, cada hora) pero es mucho más inteligente pedirle al Sistema Operativo que realice esa tarea.

Para ello crearemos un archivo de script y un archivo batch.

Listado 3.

CONNECT MiBaseDatos.FDB USER SYSDBA PASSWORD masterkey;

-- Primera vez
EXECUTE PROCEDURE LLENAR_CACHE;
COMMIT;
SHELL PING 192.0.2.2 -n 1 -w 36000 > NUL;

-- Segunda vez
EXECUTE PROCEDURE LLENAR_CACHE;
COMMIT;
SHELL PING 192.0.2.2 -n 1 -w 5000 > NUL;

-- Finalizar
EXIT;

Escribimos el contenido del Listado 3. en el bloc de notas y lo grabamos con el nombre LLENAR_CACHE.SQL

¿Qué hace este archivo de script?

Primero, se conecta a nuestra Base de Datos. Segundo, ejecuta el stored procedure llamado LLENAR_CACHE. Tercero, para finalizar la transacción hace un COMMIT. Cuarto, ejecuta el comando PING del Sistema Operativo donde la dirección IP mostrada es una IP que nunca existe y se usa normalmente para pruebas, la opción -n indica la cantidad de repeticiones, la opción -w indica la cantidad de milisegundos (36.000 milisegundos = 1 hora), y al redirigir a NUL no se ve salida en la pantalla.

En el Listado 3. el stored procedure LLENAR_CACHE se ejecutó 2 veces, con una hora de diferencia entre esas ejecuciones. Si la Base de Datos se usará durante 10 horas seguidas cada día, entonces deberíamos escribir 10 veces el EXECUTE PROCEDURE, el COMMIT, y el SHELL. Para que el Listado 3. no sea muy largo solamente escribí 2 veces esos comandos, en tu caso podrías necesitar escribirlos 8 veces, 10 veces, 14 veces, etc.

Listado 4.

C:
CD "\ARCHIVOS DE PROGRAMA\FIREBIRD\FIREBIRD_2_5\BIN"
ISQL -INPUT C:\USERS\WALTER\DESKTOP\LLENAR_CACHE.SQL

¿Qué hace este archivo batch?

Primero, se ubica en la unidad C:. Segundo, se ubica en la carpeta donde se encuentra el programa ISQL.EXE. Tercero, ejecuta el programa ISQL.EXE con la opción -INPUT y el nombre completo del archivo de script.

En síntesis, ejecuta al programa ISQL.EXE pidiéndole que ejecute los comandos que se encuentran en el archivo de script.

¿Cómo ejecutamos ese archivo batch?

Tenemos dos formas:

  • Manualmente
  • Automáticamente

Manualmente sería haciendo clic en él cada día antes de que el primer usuario empiece a trabajar, por ejemplo a las 7:30 de cada día. El problema es que podríamos olvidarnos de hacer ese clic o llegamos tarde al trabajo o estamos de vacaciones, etc.

Automáticamente sería mediante el Programador de Tareas del Windows. Podríamos programar una tarea que se ejecute cada vez que se enciende el Servidor o cada día a las 7:30. También podríamos hacerlo en nuestra aplicación: si detectamos que nadie está usando la Base de Datos entonces ejecutamos el archivo batch.

Conclusión:

Si la arquitectura que usamos es SuperServer entonces podemos conseguir que los resultados de las consultas sean rapidísimos poniendo a toda la Base de Datos en la memoria caché. Si no tenemos suficiente memoria para poner a toda la Base de Datos en la memoria caché, entonces pondremos allí a las tablas más utilizadas.

Dependiendo de nuestro caso crearíamos un stored procedure como el mostrado en el Listado 1. o como el mostrado en el Listado 2.

Luego creamos un archivo de script como el mostrado en el Listado 3., el cual se encargará de llenar la memoria caché. Como los usuarios estarán insertando, actualizando, y borrando filas de las tablas el contenido de la memoria caché se irá quedando desactualizado. Por eso cada hora (o el tiempo que te parezca conveniente) se vuelve a llenar la memoria caché con el contenido actual de cada tabla.

Finalmente, creamos un archivo batch que se encargará de ejecutar al programa ISQL.EXE teniendo como entrada al archivo de script. Ese archivo batch debería ser ejecutado cada día antes de que el primer usuario se conecte a la Base de Datos. Esto podríamos hacerlo manualmente o mucho mejor, automáticamente.

Artículos relacionados:

El índice del blog Firebird21

El foro del blog Firebird21

 

Eligiendo el tamaño adecuado de las páginas de la Base de Datos

Deja un comentario

En este artículo ya hemos visto lo que son las páginas de la Base de Datos:

Entendiendo las páginas de la Base de Datos

y sabemos que esas páginas pueden tener 3 tamaños posibles:

  • 4096 bytes
  • 8192 bytes
  • 16384 bytes

¿hay alguna diferencia en el rendimiento si la Base de Datos tiene alguno de esos tamaños de página?

Sí, si tiene el tamaño adecuado entonces todas las operaciones serán más rápidas (a veces, bastante más rápidas) que si tiene un tamaño inadecuado. Las operaciones que realiza el Firebird (INSERT, UPDATE, DELETE, SELECT, FETCH) siempre afectan a una o más páginas de la Base de Datos, por lo tanto utilizar el tamaño de página adecuado es importante

entonces la pregunta ahora es ¿cuál de esos tres tamaños es el más adecuado para mi Base de Datos?

 Pues bien, la respuesta más simple es “prueba y error”. O sea, pruebas con un tamaño, luego pruebas con otro, y luego pruebas con el tercero. Comparas los desempeños y eliges el que te pareció mejor.

Desde luego que “prueba y error” es una posibilidad. Como hay solamente 3 tamaños distintos entonces es factible de realizar. Sin embargo, podemos mejorar un poco nuestro análisis para determinar el tamaño más conveniente.

  1. Tamaño de la Base de Datos. Si alguna tabla tiene o tendrá más de 100.000.000 de filas entonces elige un tamaño de página de 16384 bytes porque en tablas tan grandes los índices también serán gigantescos y por lo tanto tendrán mucha profundidad (el Firebird usa índices B-Tree, y en tales índices una profundidad mayor que 3 empieza a ser problemática).
  2. El tamaño del caché que usa la Base de Datos. Las bases de datos de Firebird tienen una memoria caché, es decir usan una porción de la memoria RAM para realizar sus procesos. Un error frecuente de los principiantes es pensar “cuanto más grande el caché, mejor”. Bien, eso no es tan así. Si fuera tan sencillo entonces el Firebird por su propia cuenta se asignaría el caché más grande posible. En un Sistema Operativo de 32 bits la mayor cantidad de memoria que puede ser direccionada es de 4 Gb (o sea, 2 elevado a la 32), pero el Windows limita esa cantidad, para que un solo proceso no esté usando toda la memoria. Por defecto, un proceso puede usar como máximo 2 Gb aunque en el archivo CONFIG.INI puede cambiarse hasta 3 Gb. Si usamos SuperServer y en el archivo FIREBIRD.CONF ponemos en la entrada DefaultDbCachePages el número 100000 y el tamaño de nuestras páginas es de 16384 bytes entonces el caché de cada Base de Datos consumirá 1.6 Gb. Lo cual implica que podremos tener abierta una sola Base de Datos, porque 1.6 Gb por 2 es 3.2 Gb, que sobrepasa el máximo de 3 Gb que el Sistema Operativo nos permite direccionar. Pero lo peor es que un caché tan gigantesco tampoco nos asegura que nuestras operaciones serán rapidísimas ¿por qué? porque el propio Sistema Operativo usa su propio caché en operaciones repetitivas de lectura en disco y por lo tanto no se usará el caché del Firebird, ocupará mucha memoria pero no se lo usará ¿Y entonces? bueno, en general un tamaño de página de 16384 bytes y un tamaño de caché moderado (o sea, alrededor de 20000) es lo más adecuado.
  3. Cantidad de filas por página. A mayor tamaño de la página, mayor cantidad de filas se pueden guardar en ella y por lo tanto la Base de Datos necesita de menos páginas. Lo normal es que si una Base de Datos tiene pocas páginas sea menos propensa a corromperse que si tiene muchas páginas. En consecuencia, un tamaño de página de 16384 bytes es preferible porque será más difícil que la Base de Datos se corrompa.
  4. Tamaño del clúster. Cuando se formatea un disco duro se debe elegir el tamaño del clúster, el cual en NTFS es de 512 bytes por defecto pero puede ser cambiado.

Si el tamaño de la página es mayor que el del clúster entonces cuando se quiere leer una página se debe leer más de un clúster desde el disco duro y eso es lento. Por ejemplo:

Tamaño de la página = 4096 bytes

Tamaño del clúster = 512 bytes

implica que leer una sola página de la Base de Datos requiere leer 8 clústers en el disco duro (ya que 512 * 8 = 4096). Lo mismo cuando se quiere escribir en una página, se requerirá escribir en 8 clústers. Y si los clústers no están contiguos eso hará aún más lenta a la operación (nosotros no podemos saber si estarán contínuos o no, porque eso es de incumbencia del Sistema Operativo).

 Si el tamaño de la página es menor que el tamaño del clúster a veces puede ser beneficioso cuando se lee, sin embargo cuando se escribe se tardará más. Por ejemplo:

Tamaño de la página = 4096 bytes

Tamaño del clúster = 8192 bytes

Como el Sistema Operativo no puede leer menos que un clúster, un clúster es lo mínimo que puede leer desde el disco duro, cada vez que lea un clúster estará trayendo 2 páginas. Eso puede ser bueno si necesitaremos luego los datos que están en la segunda página pero si no es así entonces se leyeron 4096 bytes inútiles ¿por qué? porque los primeros 4096 bytes sí los usamos, esos fueron los que pedimos, pero los siguientes 4096 nunca los usamos y por lo tanto fueron leídos inutilmente. A su vez, cuando necesitemos escribir lo haremos por duplicado porque cuando escribamos en la primera página escribiremos en el clúster y cuando escribamos la segunda página también escribiremos en el clúster.

¿Lo mejor?

Que el tamaño de la página y el tamaño del clúster sean iguales.

El tamaño adecuado puede cambiar con el tiempo

Un punto muy, pero muy importante a tener en cuenta es el siguiente: el mejor tamaño de página hoy puede no ser el mejor dentro de un mes o dentro de un año.

¿Por qué?

Porque las bases de datos son dinámicas, no son estáticas, constantemente se les están insertando, actualizando, y borrando filas. Un tamaño de página excelente cuando la Base de Datos tenía una tamaño de 50 Mb puede ser horrible cuando creció hasta tener un tamaño de 2 Gb.

Así que debemos recordar que a veces cambiar el tamaño de las páginas puede ser una muy buena alternativa para que todas las operaciones se realicen más rápidamente.

Conclusión:

Si nuestra Base de Datos tiene un tamaño de página adecuado entonces todas las operaciones que se realicen en ella (INSERT, UPDATE, DELETE, SELECT, FETCH) serán rápidas. Pero si no es así, entonces esas operaciones serán más lentas de lo que deberían.

Como hay solamente 3 tamaños de página posibles entonces es muy fácil realizar tests de “prueba y error”. Sin embargo, también podemos tener en cuenta algunos parámetros para hallar el tamaño de página más adecuado y arriba se detallan esos parámetros.

Algo importante a tener en cuenta es que el tamaño del clúster del disco duro debe ser igual al tamaño de la página de la Base de Datos, para conseguir el máximo rendimiento posible.

Artículos relacionados:

Entendiendo las páginas de la Base de Datos

El índice del blog Firebird21

El foro del blog Firebird21