Actually restoring a backup solves the problem, but it happens if I need to reboot computer few days after.And what happens after rebooting directly after restoring the database?
Actually restoring a backup solves the problem, but it happens if I need to reboot computer few days after.And what happens after rebooting directly after restoring the database?
If it's fast then you'll need to check the stats. Maybe there are hanging transactions and very old snapshots.
In that case you'll need to check your program why that is (not closing transactions).
If it's slow again after restoring and rebooting then restoring the database didn't do anything specific so the problem is something else.
After a restore, if I reboot, it is fast.And if it becomes slow again after a few days of use you'll need to check what's different with the database.
Starting transaction...
Preparing query: SELECT * FROM CLIENTS WHERE NOM LIKE '%JAB%'
Prepare time: 0.014s
Field #01: CLIENTS.ID Alias:ID Type:INTEGER
Field #02: CLIENTS.CIVIL Alias:CIVIL Type:STRING(20)
Field #03: CLIENTS.NOM Alias:NOM Type:STRING(25)
Field #04: CLIENTS.PRENOM Alias:PRENOM Type:STRING(25)
Field #05: CLIENTS.TEL Alias:TEL Type:STRING(15)
Field #06: CLIENTS.TEL2 Alias:TEL2 Type:STRING(15)
Field #07: CLIENTS.TEL3 Alias:TEL3 Type:STRING(15)
Field #08: CLIENTS.TEL4 Alias:TEL4 Type:STRING(15)
Field #09: CLIENTS.ADRESSE Alias:ADRESSE Type:STRING(50)
Field #10: CLIENTS.ADRESSE2 Alias:ADRESSE2 Type:STRING(50)
Field #11: CLIENTS.CP Alias:CP Type:STRING(8)
Field #12: CLIENTS.VILLE Alias:VILLE Type:STRING(40)
Field #13: CLIENTS.EMAIL Alias:EMAIL Type:STRING(40)
Field #14: CLIENTS.MONTANT Alias:MONTANT Type:NUMERIC(18,2)
Field #15: CLIENTS.GENRANDOM Alias:GENRANDOM Type:STRING(32)
Field #16: CLIENTS.COURRIER Alias:COURRIER Type:STRING(1)
Field #17: CLIENTS.SMS Alias:SMS Type:STRING(1)
Field #18: CLIENTS.MAILING Alias:MAILING Type:STRING(1)
Field #19: CLIENTS.REMISES Alias:REMISES Type:STRING(1)
Field #20: CLIENTS.REMACTES Alias:REMACTES Type:INTEGER
Field #21: CLIENTS.REMPROD Alias:REMPROD Type:INTEGER
Field #22: CLIENTS.REMALIM Alias:REMALIM Type:INTEGER
Field #23: CLIENTS.SMS_AGENDA Alias:SMS_AGENDA Type:STRING(1)
Field #24: CLIENTS.SMS_MEDICAMENTS Alias:SMS_MEDICAMENTS Type:STRING(1)
Field #25: CLIENTS.REMARQUE Alias:REMARQUE Type:BLOB SUB_TYPE 1
Field #26: CLIENTS.DATE_CREATION Alias:DATE_CREATION Type:DATE
Field #27: CLIENTS.ECO_PRINT Alias:ECO_PRINT Type:STRING(1)
Field #28: CLIENTS.NOM_COMPTA Alias:NOM_COMPTA Type:STRING(10)
Field #29: CLIENTS.NPR Alias:NPR Type:STRING(1)
Field #30: CLIENTS.WARN Alias:WARN Type:STRING(1)
Field #31: CLIENTS.POPUP Alias:POPUP Type:STRING(1)
Field #32: CLIENTS.SCANID Alias:SCANID Type:STRING(30)
PLAN (CLIENTS NATURAL)
Executing...
Done.
2980204 fetches, 2888299 marks, 22388 reads, 2663263 writes.
0 inserts, 0 updates, 0 deletes, 397 index, 18955 seq.
Delta memory: 487136 bytes.
Total execution time: 0:04:59 (hh:mm:ss)
Script execution finished.
Starting transaction...
Preparing query: SELECT * FROM CLIENTS WHERE NOM LIKE '%JAB%'
Prepare time: 0.014s
Field #01: CLIENTS.ID Alias:ID Type:INTEGER
Field #02: CLIENTS.CIVIL Alias:CIVIL Type:STRING(20)
Field #03: CLIENTS.NOM Alias:NOM Type:STRING(25)
Field #04: CLIENTS.PRENOM Alias:PRENOM Type:STRING(25)
Field #05: CLIENTS.TEL Alias:TEL Type:STRING(15)
Field #06: CLIENTS.TEL2 Alias:TEL2 Type:STRING(15)
Field #07: CLIENTS.TEL3 Alias:TEL3 Type:STRING(15)
Field #08: CLIENTS.TEL4 Alias:TEL4 Type:STRING(15)
Field #09: CLIENTS.ADRESSE Alias:ADRESSE Type:STRING(50)
Field #10: CLIENTS.ADRESSE2 Alias:ADRESSE2 Type:STRING(50)
Field #11: CLIENTS.CP Alias:CP Type:STRING(8)
Field #12: CLIENTS.VILLE Alias:VILLE Type:STRING(40)
Field #13: CLIENTS.EMAIL Alias:EMAIL Type:STRING(40)
Field #14: CLIENTS.MONTANT Alias:MONTANT Type:NUMERIC(18,2)
Field #15: CLIENTS.GENRANDOM Alias:GENRANDOM Type:STRING(32)
Field #16: CLIENTS.COURRIER Alias:COURRIER Type:STRING(1)
Field #17: CLIENTS.SMS Alias:SMS Type:STRING(1)
Field #18: CLIENTS.MAILING Alias:MAILING Type:STRING(1)
Field #19: CLIENTS.REMISES Alias:REMISES Type:STRING(1)
Field #20: CLIENTS.REMACTES Alias:REMACTES Type:INTEGER
Field #21: CLIENTS.REMPROD Alias:REMPROD Type:INTEGER
Field #22: CLIENTS.REMALIM Alias:REMALIM Type:INTEGER
Field #23: CLIENTS.SMS_AGENDA Alias:SMS_AGENDA Type:STRING(1)
Field #24: CLIENTS.SMS_MEDICAMENTS Alias:SMS_MEDICAMENTS Type:STRING(1)
Field #25: CLIENTS.REMARQUE Alias:REMARQUE Type:BLOB SUB_TYPE 1
Field #26: CLIENTS.DATE_CREATION Alias:DATE_CREATION Type:DATE
Field #27: CLIENTS.ECO_PRINT Alias:ECO_PRINT Type:STRING(1)
Field #28: CLIENTS.NOM_COMPTA Alias:NOM_COMPTA Type:STRING(10)
Field #29: CLIENTS.NPR Alias:NPR Type:STRING(1)
Field #30: CLIENTS.WARN Alias:WARN Type:STRING(1)
Field #31: CLIENTS.POPUP Alias:POPUP Type:STRING(1)
Field #32: CLIENTS.SCANID Alias:SCANID Type:STRING(30)
PLAN (CLIENTS NATURAL)
Executing...
Done.
3656 fetches, 0 marks, 0 reads, 0 writes.
0 inserts, 0 updates, 0 deletes, 0 index, 3027 seq.
Delta memory: 120944 bytes.
Total execution time: 0.040s
Script execution finished
may I post the gstat of a non working database (just after killing firebird service) and after running a query (during 5 minutes this time) ?
Non working DB
Oldest transaction 6656
Oldest active 6657
Oldest snapshot 76
Next transaction 7188
Working DB
Oldest transaction 7197
Oldest active 7198
Oldest snapshot 7198
Next transaction 7199
Oldest snapshot - the ID of the oldest transaction which is currently not eligible to be garbage-collected. Any transaction with this or a higher ID cannot, yet, have old record versions removed by a sweep, for example. Normally, this is the same as the OAT above. The difference between this value and the OIT, if greater than the database sweep interval - assuming that automatic sweeping is not disabled - determines if an automatic sweep takes place.
Oldest transaction - the transaction ID of what is known as Oldest Interesting Transaction or OIT. This is simply the ID of the longest running transaction that has so far not been completed by way of a hard commit. It may have been rolled back, or be in limbo, but if it has been committed, it is no longer interesting. This value, along with the Oldest Snapshot Transaction, is used when determining if an automatic sweep of the database is required.
Is it a committing issue ?It's a transaction issue, I think. You could easily test that by closing all programs and seeing if the transaction-SQL still gives active transactions.
here the resultYes, but that's after the clean-up is done (and after the transactions are close/ended), isn't it?
the result appears on the non working database, I just tried SELECT FROM after that and it took about 5 minutes again.I wonder why the sweep hasn't taken place yet.
I'll try with other computers connected
If you find that starting a new transaction takes a long time, it may be a good idea to run a manual sweep of the database in case the need for a sweep is causing the hold-up.
Frankly, I'd suggest you ask in the proper Firebird mailing list; people there should be more attuned than us to these kind of problems.
It might be as simple as not defining a primary index.No, it's not. First, the shown query can't use indexes with LIKE '%ABC%'. Second, the second query is fast.
And it's already established that after a restore everything works as expected because the old transactions are removed.Well, then: do what I did many moons ago and drop Firebird and family and use a mainstream database engine like Postgress, MariaDb or even MySql.
Do you mean that Postgress can clean transactions even if they are still in use by a client? Magical :PAnd it's already established that after a restore everything works as expected because the old transactions are removed.Well, then: do what I did many moons ago and drop Firebird and family and use a mainstream database engine like Postgress, MariaDb or even MySql.
Do you mean that Postgress can clean transactions even if they are still in use by a client? Magical :PNo. But Firebird and family seem to have more problems. (I never saw it scaling beyond 10 connections.)
In any database you would encounter similar problems if transactions are not closed properly.Yes, but less often as visible.
Tzquery.Edit;
TZquery.fieldbyname......
Tzquery.applyupdates;
jquery := TZQuery.Create(nil);
with jquery do
begin
connection := modules.jconnect;
SQL.Clear;
SQL.Add('UPDATE CLIENTS SET MONTANT=:MT');
ParamByName('MT').AsFloat := MGF - MGR;
ExecSQL;
Free;
end;
AutoCommit is usually not a good idea in your scenario: too much traffic.Do note that, when autocommit is false, zeos wil create a transaction when you connect to the database. This can result in long-running transactions which is (very) bad too. So make sure the transaction of the connection is disabled and you use manually created transactions per query in that case.
If you always want to be in control when to commit, then AutoCommit mode probably isn't for you. In That case set AutoCommit to false. Zeos will start a transaction for you immediately after you connect to the database. Calling Commit or Rollback will end that transaction and immediately start a new transaction. Zeos will never do a commit or rollback on its own.
new_query := TZQuery.create(nil);
with new_query do begin
Connection := the_main_connection;
stuff... and then new_query.free
Perhaps it's not a good idea to use the same connection for active zqueries and new ones created at runtime ?Is it OK to have components like dbgrids associated with TZquery and transactions or do have I to fill manually components starting a transaction, doing stuff and then committing ?
Database "C:\JSW\JDB.FDB"
Gstat execution time Sun Jan 19 12:42:43 2020
Database header page information:
Flags 0
Generation 9433
System Change Number 0
Page size 4096
ODS version 12.0
Oldest transaction 9197
Oldest active 9198
Oldest snapshot 7206
Next transaction 9439
Sequence number 0
Next attachment ID 543
Implementation HW=AMD/Intel/x64 little-endian OS=Windows CC=MSVC
Shadow count 0
Page buffers 0
Next header page 0
Database dialect 3
Creation date Jan 11, 2020 8:34:10
Attributes force write
Variable header data:
Sweep interval: 1000
*END*
Gstat completion time Sun Jan 19 12:42:43 2020
Database "C:\JSW\JDB.FDB"
Gstat execution time Sun Jan 19 12:42:55 2020
Database header page information:
Flags 0
Generation 9435
System Change Number 0
Page size 4096
ODS version 12.0
Oldest transaction 9439
Oldest active 9440
Oldest snapshot 9440
Next transaction 9441
Sequence number 0
Next attachment ID 548
Implementation HW=AMD/Intel/x64 little-endian OS=Windows CC=MSVC
Shadow count 0
Page buffers 0
Next header page 0
Database dialect 3
Creation date Jan 11, 2020 8:34:10
Attributes force write
Variable header data:
Sweep interval: 1000
*END*
Gstat completion time Sun Jan 19 12:42:55 2020
Starting transaction...
Preparing query: SELECT * FROM CLIENTS WHERE NOM LIKE '%JAB%'
Prepare time: 0.030s
Field #01: CLIENTS.ID Alias:ID Type:INTEGER
Field #02: CLIENTS.CIVIL Alias:CIVIL Type:STRING(20)
Field #03: CLIENTS.NOM Alias:NOM Type:STRING(25)
Field #04: CLIENTS.PRENOM Alias:PRENOM Type:STRING(25)
Field #05: CLIENTS.TEL Alias:TEL Type:STRING(15)
Field #06: CLIENTS.TEL2 Alias:TEL2 Type:STRING(15)
Field #07: CLIENTS.TEL3 Alias:TEL3 Type:STRING(15)
Field #08: CLIENTS.TEL4 Alias:TEL4 Type:STRING(15)
Field #09: CLIENTS.ADRESSE Alias:ADRESSE Type:STRING(50)
Field #10: CLIENTS.ADRESSE2 Alias:ADRESSE2 Type:STRING(50)
Field #11: CLIENTS.CP Alias:CP Type:STRING(8)
Field #12: CLIENTS.VILLE Alias:VILLE Type:STRING(40)
Field #13: CLIENTS.EMAIL Alias:EMAIL Type:STRING(40)
Field #14: CLIENTS.MONTANT Alias:MONTANT Type:NUMERIC(18,2)
Field #15: CLIENTS.GENRANDOM Alias:GENRANDOM Type:STRING(32)
Field #16: CLIENTS.COURRIER Alias:COURRIER Type:STRING(1)
Field #17: CLIENTS.SMS Alias:SMS Type:STRING(1)
Field #18: CLIENTS.MAILING Alias:MAILING Type:STRING(1)
Field #19: CLIENTS.REMISES Alias:REMISES Type:STRING(1)
Field #20: CLIENTS.REMACTES Alias:REMACTES Type:INTEGER
Field #21: CLIENTS.REMPROD Alias:REMPROD Type:INTEGER
Field #22: CLIENTS.REMALIM Alias:REMALIM Type:INTEGER
Field #23: CLIENTS.SMS_AGENDA Alias:SMS_AGENDA Type:STRING(1)
Field #24: CLIENTS.SMS_MEDICAMENTS Alias:SMS_MEDICAMENTS Type:STRING(1)
Field #25: CLIENTS.REMARQUE Alias:REMARQUE Type:BLOB SUB_TYPE 1
Field #26: CLIENTS.DATE_CREATION Alias:DATE_CREATION Type:DATE
Field #27: CLIENTS.ECO_PRINT Alias:ECO_PRINT Type:STRING(1)
Field #28: CLIENTS.NOM_COMPTA Alias:NOM_COMPTA Type:STRING(10)
Field #29: CLIENTS.NPR Alias:NPR Type:STRING(1)
Field #30: CLIENTS.WARN Alias:WARN Type:STRING(1)
Field #31: CLIENTS.POPUP Alias:POPUP Type:STRING(1)
Field #32: CLIENTS.SCANID Alias:SCANID Type:STRING(30)
PLAN (CLIENTS NATURAL)
Executing...
Done.
544669 fetches, 505145 marks, 4984 reads, 399222 writes.
0 inserts, 0 updates, 0 deletes, 397 index, 18958 seq.
Delta memory: 430976 bytes.
Total execution time: 39.157s
Script execution finished.
Retaining After confirming the changes made in a transaction by COMMIT or resetting them by ROLLLBACK the transaction normally is going to be ended and an existing resultset of a query or stored procedure will be discarded.
These COMMITs and ROLLBACKs are called "hard" commit or "hard" rollback. By using the ZEOS library this will become a little bit different. ZEOS keeps the resultset alive. This is achieved by closing transaction with "soft" commits or "soft" rollbacks.
All this is done by the TZConnection object. This method is called retaining. The COMMIT and ROLLBACK commands are executed with the addition RETAINING. Retaining causes the closing of the current transaction and immediately opening a new transaction with all the data and resources (especially the resultset) of the "old" transaction.
Retaining becomes a problem if it is uses for huge tables. It constrains the internal cleanup mechanism of firebird (garbage collection). This leads (because of the versioning and the multigenerational architecture of Firebird) to a lot of old records that have to be kept but will not be needed anymore. This influences the server's performanced in a negative way.
A so called sweep would discard these old versions and improve the performance. This sweep will only be executed by sending a "hard" COMMIT or ROLLBACK. The ZEOS Library only executes these "hard" commands when ending the database connection (closing connection).
It is not possible to send them while a database connection is active. So the database connection should be deactivated and immediately activated occasionally to achieve this performance improvement.
Perhaps specifying Zconnection.Properties.Add('hard_commit=TRUE') could work ? I'll try
procedure Tmodules.addtolog(typelog:string;date:tdatetime;valeur:string='';texte:string='';vu:boolean=false);
var jquery:TZquery;
var vuchar:shortstring;
begin
Jquery:=TZquery.Create(nil);
Jquery.Connection:=connexion2;
case vu of
true:vuchar:='1';
false:vuchar:='0';
end;
with Jquery do begin
Connection.StartTransaction;
SQL.clear;
SQL.add('INSERT INTO LOGS (TYPE,VALEUR,DATE_LOG,HEURE_LOG,TEXTE,READ) VALUES (:TYP,:VAL,:DATE,:HEURE,:TEXTE,:READ)');
ParamByName('TYP').AsString:=typelog;
ParamByName('VAL').AsString:=valeur;
ParamByName('TEXTE').AsWideString:=texte;
ParamByName('DATE').AsDate:=date;
ParamByName('HEURE').AsTime:=date;
ParamByName('READ').AsString:=vuchar;
ExecSQL;
Connection.Commit;
Free;
end;
end;
Hello all !
I'm facing an weird issue for many months, and as I can't find an active forum in firebird website, I try to post my question here.
I run a program on 6 computers, that are accessing the same database on a server.
Everything works OK. Making queries are fast. I backup database every evening but don't restore it.
I made garbage collector to perform a sweep every 20000 transactions.
The database is quite small in size, on a ssd.
When I have to reboot the server and try then to run a simple query after reboot (SELECT * FROM TABLE), (not on every tables), the query takes like forever to run (sometimes about 10 minutes !), with process firebird server that makes disk running at 100% in task manager. And then it shows the output, and everything becomes fine again, the new and same queries 'SELECT * FROM TABLE' instantly showing result.
I tried to perform manual sweep, but it doesn't help. Not sure what happens... does it validate, reconstructs indices, or whatever else.
I can schedule a reboot during the night, but will it be enough to solve the problem ? and it doesn't change the fact I don't manage transactions as I should...It will close all transactions so the oldest interesting transaction should increase. And if the gbak is done after that the sweep should work correctly.
Have you tried to turn off db sweep, antivirus, and windows search/indexing? Do you use some FB beta?Hint: Search for all occurences of sweep word on this page:
Hi @EgonHugeist
Is there any change/planning to have active connection and not started transaction in ZeosDbo? This is old post in Synopse/mORMot forum about bad transaction handling in mORMot app. I don't like to have started transaction on server if nobody is connected to my mORMot server.
https://synopse.info/forum/viewtopic.php?pid=14105#p14105
Are IBX components easy to use,
Best regards
or at least easy enough to switch from zeos?