Problema engorde y rendimiento
 

Buenas a todos,

Tengo una base de datos hecha en FB1.5 que ya funciona correctamente desde hace años. No se exactamente porque en el caso de este cliente la base de datos engorda una barbaridad diariamente. Para que os hagais una idea, una base que se a realizado un backup-restore y se queda en 25mb en cuestion de horas vuelve a ocupar 200mb. El volumen de informacion que se introduce diariamente es minimo. Y la base de datos tiene como maximo 8 usuarios.

Si bien es cierto que hay un programa que esta conectado a esta base de datos para importar datos desde otro sistema. Y esta importando los datos de forma constante cada 5 minutos, importando nuevos registros y actualizando los existentes. Con lo que esta importando varias tablas de 12.000 reg.

El problema en si no es el tamaño sino que va relacionado con el rendimiento. Pues cuando ha engordado como os comentaba antes a 200mb, 450mb, pues procedimientos almacenados que tardaban menos de 1s tardan 1 min. largo. con el problema que ocasiona pues realiza conflictos de bloqueos. Principalmente se nota el problema en un procedimento almacenado que actualiza datos de tablas de forma masiva.

Si vuelvo a realizar un backup-restore el problema se soluciona de inmediato, incluido en dicho procedimiento.

Las aplicaciones utilizan IBX + ClientDataSets para acceder a FB1.5. Y utilizan unicamente commits para las transacciones. Todo esto esta montado en un Windows 2008 Server. FB1.5 (tamaño pagina 1024, intervalo limpieza basura 20000, ODS 10.1)

De igual forma que al realizar un backup-restore todo vuelve a funcionar rapido, he probado a realizar un 'gfix -sweep -user SYSDBA - masterkey' y me deja la base de datos con el mismo tamaño (gorda) pero todo vuelve a funcionar rapido.


Saludos,

Debe haber algo anómalo en el tratamiento de las transacciones porque no es nada normal que tengas problemas de bloqueos, tal y como mencionas:
¿Qué parámetros tienes en las transacciones?

Lo que sí te aconsejo es que cambies el tamaño de página de 1024 a 8192, estás penalizando su rendimiento.

Hola Casimiro,

los parametros que tengo en el TIBTransaction que utilizo son los de 'Read Commited' (read_commited, rec_version, nowait)

Lo que comentaba sobre los conflictos en los bloqueos es porque al tardar tanto tiempo la ejecucion de un procedimiento almacenado que realiza muchas actualizaciones (masivas) pues puede provocoar eso. Pero cuando todo esta bien funciona muy rapido.

Para cambiar el tamaño de pagina que utilizo el gfix? puede dañar la base?

Muchas gracias!

saludos,

Hola.

Parece bastante lógico pensar que tus problemas vienen por transacciones demasiado largas (en tiempo y nº de registros).

Las transacciones tienen que ser lo más cortas posibles, y sobre todo nunca debes dejar transacciones abiertas con modificaciones realizadas (esos registros quedan bloqueados, con lo que bloquean las importaciones, los procedimientos almacenados, etc. ...). Yo incluso te recomendaría no dejar nunca transacciones abiertas de ningún tipo (ni siquiera que sean de lectura).

Así pues, en tu proceso de importación de datos, ve confirmando las transacciones cada poco rato (por ejplo. cada 500 registros es un buen valor), en lugar de esperar hasta el final de la importación.

Y en tu aplicación, no dejes nunca transacciones abiertas. Si el usuario tiene que modificar datos, ponlos en un ClientDataset, de manera que el usuario puede modificar los datos tan tranquilamente como quiera (ya que no se mantiene ninguna transacción abierta), y cuando sea necesario los aplicas a la base de datos llamando al método ApplyUpdates (eso inicia una transacción, ejecuta las sentencias INSERT, DELETE y UPDATE necesarias para aplicar a la Base de Datos los cambios realizados a los Datos en el ClientDataset, y cierra automáticamente la transacción. Todo el proceso apenas dura unos pocos milisegundos con la transacción abierta).

En definitiva, cuanto más cortas sean tus transacciones, menos posibilidades hay de encontrar bloqueos. Y si las transacciones solo duran unos pocos milisegundos (abres la transacción, lees los datos, se pasan al clientdataset y se cierra, o se realizan los cambios y se cierra) es bastante difícil encontrar bloqueos (yo puedo contar con los dedos de una mano los bloqueos que me he encontrado en diez años usando Firebird).

Respecto al tamaño de la base de datos, yo no me preocuparía lo más mínimo por ello. Firebird es una base de datos multigeneracional, cuando modificas un registro se hace una copia del mismo, eso quiere decir que si hay muchas modificaciones simultaneas en largas transacciones abiertas (como parece ser actualmente tu caso), entonces tienes muchas copias de tus registros. Pero una vez confirmada la transacción, ese espacio se reutiliza. Así que tu Base de Datos crecerá hasta un tamaño determinado para poder mantener todas las versiones en memoria que haces en tu uso normal del programa, y a partir de ese punto solo crecerá moderadamente, a medida que se vayan dando de alta registros.

NOTA: Aunque verás que si haces las transacciones más cortas, tu base de datos también crece mucho menos (ya que no tiene que mantener tantas versiones simultaneas de los registros).

Saludos.

Por mucho que tarde un procedimiento no puede causar errores por bloqueos.

Para cambiar el tamaño de página puedes hacer un backup y restore:

gbak -b -t -v -user sysdba -password masterkey tubd.fdb tubd.fbk

gbak -r -v -p 8192 -user sysdba - password masterkey tubd.fbk tubd.fdb

Edito: acabo de leer lo escrito por guillotmarc, hazle caso :)
Y en cuanto al tamaño de la base de datos, esta mañana he estado atendiendo una duda de un cliente que su BD ha pasado los 24 GB y no se queja por el rendimiento.

NOTA: Por cierto, para cambiar el tamaño de la base de datos yo hago un Backup y un Restore (parámetro -P). No creo que puedas hacerlo con el GFix.

Casimiro, ¿ puedes borrar este último mensaje ?, te me has adelantado.

:)

Gracias.

Así queda repetido, para machacar mejor las ideas y no olvidarlas :D

Para el tema de insertar y cerrar transacciones mira este link
Básicamente debes hacer esto

Código Delphi [-]
 with Consulta do
  begin
    SQL.Clear;
    SQL.Add( 'INSERT INTO CLIENTES' );
    SQL.Add( '( NOMBRE, NIF, IMPORTEPTE )' );
    SQL.Add( 'VALUES' );
    SQL.Add( '( ''ANTONIO GARCIA LOPEZ'', ''46876283D'', 140.23 )' );

    Transaction.StartTransaction;

    try
      ExecQuery;
      Transaction.Commit;
    except
      on E: Exception do
      begin
        Application.MessageBox( PChar( E.Message ), 'Error de SQL', MB_ICONSTOP );
        Transaccion.Rollback;
      end;
    end;
  end;

y mejor si lo haces así

Código Delphi [-]
var
  i: Integer;
  dwTiempo: DWord;
begin
  with Consulta do
  begin
    //////////////  METODO RÁPIDO  ////////////////

    dwTiempo := TimeGetTime;

    Transaction.StartTransaction;

    SQL.Clear;
    SQL.Add( 'UPDATE CLIENTES' );
    SQL.Add( 'SET NOMBRE = :NOMBRE' );
    SQL.Add( 'WHERE ID = :ID' );
    Prepare;

    for i := 1 to 1000 do
    begin
      Params.ByName( 'NOMBRE' ).AsString := 'NOMBRE CLIENTE Nº '+ IntToStr( i );
      Params.ByName( 'ID' ).AsInteger := i;
      ExecQuery;
    end;

    try
      Transaction.Commit;
    except
      on E: Exception do
      begin
        Application.MessageBox( PChar( E.Message ), 'Error de SQL', MB_ICONSTOP );
        Transaccion.Rollback;
      end;
    end;

    ShowMessage( 'Tiempo: ' + IntToStr( TimeGetTime - dwTiempo ) + ' milisegundos' );
  end;
end;

Duilioisola te recomiendo que cambies las excepciones de captura de error por esta :

Código Delphi [-]
  except       
    on E: Exception do 
    begin
      Transaccion.Rollback;  // Primero cancelamos la transacción
      Application.MessageBox( PChar( E.Message ), 'Error de SQL', MB_ICONSTOP );
    end;
  end;

Puesto que estás dejando abierta la transacción (y los registros bloqueados) hasta que el usuario acepta el mensaje de error. Y no hay ninguna necesidad de ello (imagínate que el usuario mantiene el mensaje en pantalla, para poder llamar al servicio técnico. Igual te deja media hora con los registros bloqueados).

Siempre se debería evitar que las transacciones (especialmente las que ya han modificado algo) permanezcan abiertas a la espera de una acción del usuario.

Saludos.

Hola,

En mis programas suelo utilizar los componentes IBX + datasetprovider + clientdataset. Y abro las consultas en clientdataset y realizo las actualizaciones mediante el ApplyUpdate(). Esto es lo que suelo utilizar para las consultas y las consultas actualizables.

En el programa de importacion que comentaba lo que si es cierto que realizo un commit por cada nuevo registro que añado o modifico, esto lo realizo por que en caso de error en un registro no quiero que afecta a otros posibles registros. Y me deje de importar un bloque de xxx registros. Tambien decir que en este programa de importacion estoy utilizando Ibquery + IbupdateSQL y en otros casos utilizo ibx + dsp + clientdataset. Unos procesos de importacion usan un modelo de datos y otros el otro.

Por cierto, para las consultas de modificacion, insercion y/o procedimientos alm. como los utilizais con los clientdataset para sincronizar todo en una transaccion?

Muchas gracias a todos.

Saludos,

Muy buen apunte guillomarc.
Habrá que dejarle un mensajito en le blog de Delphi al Limite que es de dónde saqué el código.

Hola.

No hay que ponerlas, el ClientDataset no va a utilizar las consultas de modificación o inserción que haya en el IBX. Sino que el mismo se construye automáticamente esas consultas (en función de los ProviderFlags en los campos persistentes del componente IBX).

Respecto a como sincronizar en una misma transacción varias operaciones. Si tienes abierta una transacción, el ClientDataset realizará los cambios dentro de esa transacción en lugar de abrir una de nueva, por lo que puedes encadenar varios cambios y finalizar la transacción al final para aglutinarlo todo. No te olvides de finalizar por ti mismo la transacción, puesto que si la dejas abierta es cuando empiezas a tener problemas de bloqueos y crecimiento de la base de datos.

Te recomiendo que hagas un seguimiento de las transacciones existentes en tu programa. Parece que algún punto debes dejar alguna abierta.

Por cierto, yo en FIBPlus pongo un TimeOut de 1 a mis transacciones para asegurarme de que nunca queden abiertas. No sé si en IBX tienes algo similar.

Hola,

respecto a esto:

Creo que no me he explicado correctamente, quiero decir que yo utilizo los clientdataset para consultas y consultas modificables y realizo un applyupdates para grabar todos los cambios.

Pero cuando en el mismo proceso de guardar esos cambios quiero incluir en una misma transaccion una actualizacion o insercion mediante instrucciones SQL (insert, update) o un procedimiento almacenado.

Por ejemplo solamente con componentes IBX lo haria asi:

utilizando los clientdataset:

Hola.

Yo te recomendaría que definas una sola transacción y que la controles manualmente (la inicies y la finalices tú, en lugar de dejar que se haga implícitamente).

NOTA: No utilizo IBX, pero debería ser algo así.

Hola,

He probado a ir aislando los problemas y finalmente lo que me engorda la base de datos es la ejecuccion de un procedimiento almacenado que realiza calculos sobre tablas de forma 'masiva'. Algo similiar a esto pero 5 veces con diferentes tablas.


Incluso si lo ejecuto desde el IBManager me engorda la base de datos. Aunque lo que realmente me importa no es el tamaño de base de datos, sino que por algun motivo este engorde afecta al 'rendimiento brutalmente'.

Tambien he probado a realizar el cambio de paginado que me comentaba Casimiro, pero al realizar el cambio con la nueva base de datos con el tamañp de paginado a 8192, me empezo a realizar unos extraños (cambiar masivamente el estado de diferentes documentos) y tuve que recuperar la copia de seguridad que habia realizado justo antes. La verdad que no le pude encontrar otra explicacion a la incidencia. No se si tenia que haber cerrado el servicio de FB al realizar el cambio de base de datos.

Muchas gracias por las aportaciones.

Hola Toni.

¿ Cuantos registros hay involucrados en ese procedimiento almacenado ?.

Si la Base de Datos crece exageradamente, es que se están modificando de golpe una cantidad desorbitada de registros, lo cual ocurre lógicamente en una única transacción con lo que crece la base de datos por el versionado de esos registros (y también explica la caída de rendimiento).

¿ Estás seguro que no hay triggers enmedio que te están forzando la actualización inesperada de más registros en otras tablas ?.

Será mejor que revises el código para ver si en ese procedimiento almacenado no estás haciendo muchísimas más actualizaciones de las necesarias.

Hola Guillotmarc,

Como comentaba este procedimiento recalcula (suma y agrupa el contenido de unas tablas y actualiza con el resultado otras) las tablas que agrupan tienen al rededor de 11000 registros y la que actualiza tambien.

He comprobado que no hay triggers implicados.

Se que esto no es lo mas optimo, pero inicialmente funciona bastane bien. A medida que va 'acumulando' versiones de los registros y va aumentando el tamaño de la base de datos, cuando el tamaño se multiplicado ya 5 el rendimiento cae en picado.

¿Pero estos registros no tendrian que influirle tanto, no?

¿la unica manera de mejorar el rendimiento sera hacer backup-restore?

Muchas gracias.

Habría que ver cómo es ese procedimiento. Tendrías que ver si puedes aplicar esta idea a tu procedimiento:

Código SQL [-]
/*Por cada registro tratado hago un update*/
declare variable importe double precision;
begin
   for select importe from tabla
       where ...
       into :importe
   do
   begin
       update totales 
       set importe=importe + :importe
       where ...;
   end
end

Código SQL [-]
/*Acumulo en una variable y hago un solo update final*/
declare variable importe double precision;
declare variable total double precision;
begin
   for select importe from tabla
       where ...
       into :importe
   do
   begin
       Total = Total + importe;
   end
   update totales 
   set importe=total
   where ...;
end

Hola duilioisola,

En el procedimiento realizo un for select que agrupa por los campos clave de la tabla que actualizo, con lo que ya realiza una sola actualizacion por registro. Lo que pasa es que hay bastantes registros implicados y cada ejecucion del procedimiento pues supone como unos 50.000 registros actualizados. Y este procedimiento se ejecuta 20-30 veces diariamente.

Muchas gracias por tu interes.

Todo apunta a que sigues teniendo como mínimo una transacción que se queda siempre abierta, por lo que no se puede ejecutar la recolección de basura de Firebird, y no se recupera el espacio usado por el versionado de productos, aparte de finalmente caerse el rendimiento de la base datos.

Tienes todos los síntomas relatados en este artículo de Firebird :

http://www.ibphoenix.com/main.nfs?a=...x&page=ibp_oit

Tienes que revisar todas las transacciones de tu programa, y verificar que se finalicen inmediatamente una vez terminada su tarea. Nunca dejes transacciones abiertas a la espera de que el usuario haga algo, y no confíes en las transacciones implícitas de los componentes, puesto que si hay una transacción abierta, ejecutarán su consulta dentro de esa transacción y la dejarán como estaba, es decir abierta. Programa tu mismo el inicio y commit de las transacciones en cada actualización de la base de datos.

Saludos.

Estoy de acuerdo con lo comentado por guillotmarc, hay alguna transacción que se te está escapando, no es lógico que disminuya el rendimiento de la base de datos porque ocupe más. Está claro, por lo que cuentas de que al hacer backup/restore (o una 'limpieza') todo vuelve a ir bien.

Hola Toni.
Todo lo que estáis discutiendo es muy interesante... yo soy fan de la optimización y depuración de código, pero tu caso me recuerda a algo que me pasó con un proyecto que realicé hace algún tiempo.
Se trataba de un sistema multihilo con muuuchas llamadas a procedimientos almacenados, y tanto la base de datos como la RAM que consumía el proceso fbserver se disparaban.
Revise... investigué... optimicé... pero no se corregía.

Durante unos meses el cliente debía reiniciar una vez por semana el servicio FB para que liberara la RAM, y también compactar la bbdd.

Hasta que salió FB 2.5 y su soporte transaccional para procedimientos almacenados y triggers. Todo solucionado.

Espero que esto te ayude, pues con versiones anteriores la cosa puede que no tenga remedio.

Nota: ojo con los campos blob en vitas!

Hola.

Por lo que dices, a mi me da la impresión de que seguramente tendrías el mismo problema que Toni : se te quedaban transacciones abiertas. Por eso al utilizar FB 2.5, al gestionar manualmente las transacciones dentro de los procedimientos almacenados, entonces ya se finalizaron correctamente y se acabaron los problemas.

Hasta donde yo sé, no hay nada malo con FB 1.5, no es que pueda soportar menos tamaño de la base de datos ni carga de transacciones que Firebird 2.5.

Saludos.

Marc.
Mi mayor problema era el consumo incontrolado de ram... como era una sistema de funcionamiento ininterrumpido, al cabo de 2 semanas se agotaba la ram, y pof! (y para colmo, un tal Murphy se encargaba de que petase el sábado por la tarde).

Lo probé casi todo. Incluso llegué a que cada vez que un hilo necesitaba ejecutar un pa, creaba una conexión y los objetos necesarios, abría y cerraba las transacciones y luego destruía todo. Y tampoco funcionaba.
Sé que en alguna parte había algo mal, pero me volví loco y no lo encontré.

Debo aclarar que era un sistema ado+odbc, y pensé que el problema estaba en odbc. probé otros drivers y también oledb, pero la cosa seguía igual...

Cuando estaba a punto de suicidarme, apareció fb 2.5 y ¡aleluya! todo perfecto. Creo que me moriré sin saber dónde estaba el fallo, pero como tengo otras cosas en marcha... Tal vez en otra vida retome el tema.

Por esto, y por muchos otros motivos recomiendo migrar a Firebird 2.5.

Y ahora una queja al cielo: Estoy un poco harto de leer que ado no es adecuado para conectarse a bbdd grandes. Yo he usado de todo, y creo que ado es muy válido. Si os parece, podemos abrir un hilo para discutir esto.

Hola.

Pues sí, tienes razón, con lo que probaste está claro que no eran las transacciones. Además si el problema solo estaba en el consumo de RAM, entonces no tengo ni idea, no recuerdo haber leído nada relacionado.

Saludos.

Hola a todos,

Posiblemente tenga razon Estifmauin, porque esta aplicacion con la misma base de dato esta instalada en varios sitios sin problema alguno. Y en este caso que me da problemas la unica personalizacion importante es el dichoso procedimiento que actualiza muchos registros. Es en el unico caso que se nota la falta de rendimiento, aunque tambien he de decir que principalmente la falta de rendimiento donde mas se nota es al llamar al mismo procedimiento curiosamente. El resto de la aplicacion que realiza todo tipo de consultas a la base de datos funciona con normalidad.

He programado diariamente con programador de tareas de Windows, que realice un gfix cada dia por la noche y el rendimiento mejora, pero no ha llegado al medio dia y ya se nota que se vuelve a poner lenta.

Ya me gustaria migrarla la base de datos a FB2.5 pero creo que puede ser una locura la de problemas que me puedo encontrar y que dificilmente voy poder preveer en modo test.

Muchas gracias a todos.

Bueno Toni, todo depende del margen de maniobra que tengas. Firebird evoluciona con compatibilidad hacia atrás, añadiendo mejoras pero sin sacrificar características.
Yo he migrado varios sistemas de versiones 2.X a la 2.5 sin problemas. Sólo en un par de ocasiones tuve que migrar "a mano", y eran sistemas con mucho manejo de campos blob, y cuando eso ocurrió, lo que hice fue migrar la estructura, y luego bombear los datos con el IBPump. Eso si, hay que tener cuidado con ciertos parámetros como el charset y el dialecto.

He hecho una pequeña investigación, y no he encontrado referencias a problemas en este sentido.
Te animo a que lo intentes, al menos en modo test, a ver si surge algo raro y en ese caso, a ver si podemos ayudarte.

Valor! :cool:

Finalmente parece que es lo que comenta el compañero Estifmauin, con el tratamiento de las transacciones dentro de los procedimientos almacenados.
Y mas en concreto con los procedimientos que realizan muchas actuliazaciones e inserciones como es mi caso. Estos generan mucha basura y engordan la base de datos.

Por el momento lo he podido solventar dignamente forzando la recoleccion de basura 2 veces al dia y por cierto con todos los usuario desconectado sino no lo hace correctamente. Mediante la utilidad de Firebird 'gfix -sweep'

En cuanto tenga algo de tiempo quiero migrarlo todo a FB 2.5, pero empezare con otra aplicación menos critica en la que me pueda permitir algunos fallos.