Recent

Author Topic: SELECT * FROM takes forever in Firebird, after windows reboot  (Read 5606 times)

Thaddy

  • Hero Member
  • *****
  • Posts: 10526
Re: SELECT * FROM takes forever in Firebird, after windows reboot
« Reply #30 on: January 17, 2020, 08:17:40 pm »
AutoCommit is usually not a good idea in your scenario: too much traffic.

fjabouley

  • Full Member
  • ***
  • Posts: 116
Re: SELECT * FROM takes forever in Firebird, after windows reboot
« Reply #31 on: January 18, 2020, 07:50:28 am »
Is it OK to have components like dbgrids associated with TZquery and transactions or do have I to fill manually components by starting a transaction, doing stuff and then committing ?
Is it also necessary to start transaction if autocommit is true ?
« Last Edit: January 18, 2020, 09:35:53 am by fjabouley »

rvk

  • Hero Member
  • *****
  • Posts: 4329
Re: SELECT * FROM takes forever in Firebird, after windows reboot
« Reply #32 on: January 18, 2020, 09:59:27 am »
If your application sits idle (without anything open), does it have any queries open to the database?

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.

https://sourceforge.net/p/zeoslib/wiki/TZConnection.AutoCommit/
Quote
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.

I suspect there is a connection made in the program which runs during its lifetime. When someone doesn't close the program (ever), that transaction is the reason there are very old snapshots.

Easiest solution would be to make sure everyone restarts the program at least once a day. And furthermore, you'll need to understand transaction and examine the use of them in the program (including the various options of them).

fjabouley

  • Full Member
  • ***
  • Posts: 116
Re: SELECT * FROM takes forever in Firebird, after windows reboot
« Reply #33 on: January 18, 2020, 10:29:59 am »
Hi rvk,
I got opened Dbgrids and TZqueries, so I think it keeps transaction opened.
I use the same connection to make other queries at runtime, is it necessary to create other connections ?
Since now :
Code: [Select]
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 ?
I do think I misunderstand how transactions work with zeos, and i'm grateful you all try to help me !
Thanks a lot !

« Last Edit: January 18, 2020, 10:37:24 am by fjabouley »

devEric69

  • Sr. Member
  • ****
  • Posts: 363
Re: SELECT * FROM takes forever in Firebird, after windows reboot
« Reply #34 on: January 18, 2020, 10:44:23 am »
Quote
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 ?

I don't know Zeos with Firebird (I've used it, but only with MariaDb). But, in fact, unlike @rvk, I've not studied how transactions with Zeos work :-[ .

With IBX, I separate auto-commit transactions from explicitly managed transactions. So, I have two "front-end" datamodules:
- one with 2 components: a connection and a transaction, dedicated auto-commit.
- one with 2 components: a connection and a transaction, explicitly managed (for accounting, stock clearance, ...).
- afterwards, there are other possible adaptations: if the client application is in a highly competitive working context, and therefore in a highly competitive context to COMMIT's a work, the TIBXUpdate "cached updates" can use an transaction that makes a batch of small commits inside this transactional (the component can handle this).


My "feeling":
- there seems to be a really administrative server's problem. When you reboot, your server behaves as if the indexes were disabled: it's not normal! Why, what are the administrative reasons, that can produce this (ask the firebird list)?
- the design of your database seems to me correct: the primary indexes, the foreign key indexes (automatically created by Firebird), the one on the NAME field that you have created in the hope of speeding up your `SELECT * WHERE NAME...` (I imagine), exist, and are used when the database is running well.
- there is probably a client side design problem, in your application.

If I were you, I would do a refactoring like this, to improve your project as is (with Zeos).
I would create an administration tool with Lazarus, usable by one person, that will monitors anomalies via queries using the $MON_etc and $ADMIN_etc Firebird's fields.
a) in the short term, the admin tool should be able to automatically kill too long transactions (20 minutes maximum), and log who created them.
b) in the medium term, this application, ideally, should be able to detect statistical modes of "frequency" of anomalies: is it always the same workstations that create these too long transactions. If so, then in which part of your application - the persons that are this statistical modes of lon transactions anomalies - this presons are essentially working? What is their favorite role(s)? With this information, you will then be able to do a rebuild of your application: for example, create a second connection in your application used by the code causing these "frequencies" of anomal long transactions, by using the the code below (nb: "deja dit", but nothing prevents you from having a dedicated auto-commit transaction connection, and another dedicated explicit transaction connection):

Code: Pascal  [Select][+][-]
  1. transaction.start;
  2. try
  3.     pseudo code A;
  4.     pseudo code B;
  5.     transaction.commit;
  6. except
  7.     transaction.rollback;
  8.     on E:Exception do oLog.write('Firebird error:'+etc);
  9. end;

c) in the long term, the administrative tool should kill all the connections in the early evening, to let the sweep happen overnight.

nb1: snapshot are rather to be used for reporting (read-only).
nb2: read_commited is good for management (read and write).

ps (which unfortunately doesn't add anything to your problem at the moment): for your future Firebird developments, I advise you to study this documentation: https://www.mwasoftware.co.uk/documentation...

Anyway, good refactoring :) .
« Last Edit: January 18, 2020, 11:03:24 am by devEric69 »
use: Linux 64 bits (Ubuntu 20.04 LTS).
Lazarus version: 2.0.4 (svn revision: 62502M) compiled with fpc 3.0.4 - fpDebug \ Dwarf3.

fjabouley

  • Full Member
  • ***
  • Posts: 116
Re: SELECT * FROM takes forever in Firebird, after windows reboot
« Reply #35 on: January 18, 2020, 11:13:54 am »
Thanks very much devEric69.
I'm also going to try to convert the Active Tzqueries which autocommits to manual transaction to get more control and see if the issue persists.
I'll tell you if it works !


Many many thanks to everyone !

rvk

  • Hero Member
  • *****
  • Posts: 4329
Re: SELECT * FROM takes forever in Firebird, after windows reboot
« Reply #36 on: January 18, 2020, 12:48:31 pm »
Before you look at the transactions in your program, you need to know if the sweep really works.
I thought the sweep was done when you run gbak.

You need to check what works on a non-working DB.
Because you know there are no active transaction after reboot, you need to test what happens after doing a gbak on that DB  (so after reboot, no users- no connections). After that you can check with gstat -h if the sweep is done.
If it's not done, try the manual sweep with gfix -sweep.
Is it done now?

Then you need to monitor (with the query I mentioned earlier) the live DB and see what transactions are live. You'll see quite quickly which transactions are long-running. I expect it's just one per client (from the connection).
« Last Edit: January 18, 2020, 12:51:38 pm by rvk »

fjabouley

  • Full Member
  • ***
  • Posts: 116
Re: SELECT * FROM takes forever in Firebird, after windows reboot
« Reply #37 on: January 19, 2020, 12:51:21 pm »
I tried to kill firebird service on a few days running database (with 4 clients connected), as it could happen if I reboot the server.


Code: [Select]

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




I then started flamerobin, and connected to database, and left it few seconds without querying anything.
I ran gstat another time :


Code: [Select]


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

like if sweep had been done
and then I made a SELECT request ....

Code: [Select]

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.

but it takes too much time again
« Last Edit: January 19, 2020, 12:52:57 pm by fjabouley »

fjabouley

  • Full Member
  • ***
  • Posts: 116
Re: SELECT * FROM takes forever in Firebird, after windows reboot
« Reply #38 on: January 19, 2020, 01:18:09 pm »
I saw that on zeos documentation :
Code: [Select]
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
« Last Edit: January 19, 2020, 01:36:45 pm by fjabouley »

fjabouley

  • Full Member
  • ***
  • Posts: 116
Re: SELECT * FROM takes forever in Firebird, after windows reboot
« Reply #39 on: January 19, 2020, 07:17:28 pm »
Argh, cannot manage to correct this...
I tried to add another TZconnection (connexion2)
and as example it works like this :


Code: [Select]


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;

but gstat won't change latest transaction.

Maybe I need to disconnect / reconnect each time I make a request ? Is there another means to stop transaction without disconnecting (same question for TZQuery)?

I'm really sorry, I feel like a real noob asking stupid questions, but I realize I don't know how to use correctly zeos and transactions and maybe I should go and have a look at IBX components to see if manipulating transactions is easier.

rvk

  • Hero Member
  • *****
  • Posts: 4329
Re: SELECT * FROM takes forever in Firebird, after windows reboot
« Reply #40 on: January 19, 2020, 07:22:02 pm »
I'm not that familiar with zeos but like I mentioned before, maybe you can disable the transaction of the connection and manually create a TTransaction for each TQuery (and commit it).

zamronypj

  • Full Member
  • ***
  • Posts: 102
    • Fano Framework, Free Pascal web application framework
Re: SELECT * FROM takes forever in Firebird, after windows reboot
« Reply #41 on: January 20, 2020, 06:31:16 am »
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.

Execute SELECT without any filtering criteria will load whole table to memory. Depending on how many records in table, this may takes time. Many RDBMS have cache query which I believe this what cause initial query was slow but subsequent query was fast.

Many RDBMS provides EXPLAIN command to solve database performance issue. You should take database execution plan into consideration before taking step to solve database performance issue.
Fano Framework, Free Pascal web application framework https://fanoframework.github.io
Personal Projects https://v3.juhara.com
Github https://github.com/zamronypj

fjabouley

  • Full Member
  • ***
  • Posts: 116
Re: SELECT * FROM takes forever in Firebird, after windows reboot
« Reply #42 on: January 20, 2020, 09:37:46 am »
Thanks zamronypj,
In fact I never use 'SELECT * FROM TABLE without a WHERE clause, or I sometimes do it on tables where there are very few records, it was given as an example (a bad one indeed...)


I have a problem with transactions as rvk, devEric69, and others said.
I thought everything would be fine keeping connection opened and using auto-commit but in fact it is not OK, because it keeps transactions opened (soft commit with zeos apparently). The main issue is that zeos retains transactions until you close the connection, and consequently garbage collector can't do its job.
I don't know how to proceed with my current Zeos components to workaround this, I think, as rvk said, that I will have to manually manage transactions/connections for each query... (as hard_commit doesn't seem to work...)
« Last Edit: January 20, 2020, 10:07:34 am by fjabouley »

rvk

  • Hero Member
  • *****
  • Posts: 4329
Re: SELECT * FROM takes forever in Firebird, after windows reboot
« Reply #43 on: January 20, 2020, 11:22:52 am »
Closing all programs at night should also work  :D
(or is this an application which is 24/7 actively used?)

fjabouley

  • Full Member
  • ***
  • Posts: 116
Re: SELECT * FROM takes forever in Firebird, after windows reboot
« Reply #44 on: January 20, 2020, 05:27:30 pm »
it's a 24/7 app but not actively used at night, it includes a http server so I can access it from my home.
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...

 

TinyPortal © 2005-2018