Print Page - SELECT * FROM takes forever in Firebird, after windows reboot

Programming => Databases => Topic started by: fjabouley on January 17, 2020, 09:49:39 am

Title: SELECT * FROM takes forever in Firebird, after windows reboot
Post by: fjabouley on January 17, 2020, 09:49:39 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. Does my program corrupts database, although everything works well ?
Does someone has experienced that behavior ? or has any idea how to solve that issue or at least track where the problem is?

Title: Re: SELECT * FROM takes forever in Firebird, after windows reboot
Post by: rvk on January 17, 2020, 09:52:57 am

How many records are in that table?
Have you tried adding ORDER BY indexname to the query?

Title: Re: SELECT * FROM takes forever in Firebird, after windows reboot
Post by: fjabouley on January 17, 2020, 09:57:20 am

Hi Rvk, happy new year ! (long time no see :) )
the number of records is quite small.
For example when it takes forever, it happens on a table where there are about 3000 records.

Title: Re: SELECT * FROM takes forever in Firebird, after windows reboot
Post by: rvk on January 17, 2020, 10:07:48 am

I do notice some delay when first querying a newly started Firebird-server.
But it's in the range of milliseconds to a second (not 10 minutes).

I think Firebird does some cleaning when it firsts starts (and gets its first request) so maybe there is something wrong with the database.

You could run a gstat on the file to see if there are no hanging transactions.
Other option is to restore the backup and see if the problem goes away.

(You can also see if this problem exists with another database or clean database)

Title: Re: SELECT * FROM takes forever in Firebird, after windows reboot
Post by: fjabouley on January 17, 2020, 11:16:27 am

Actually restoring a backup solves the problem, but it happens if I need to reboot computer few days after.

Title: Re: SELECT * FROM takes forever in Firebird, after windows reboot
Post by: rvk on January 17, 2020, 11:19:03 am

Quote from: fjabouley on January 17, 2020, 11:16:27 am

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.

Title: Re: SELECT * FROM takes forever in Firebird, after windows reboot
Post by: devEric69 on January 17, 2020, 11:28:44 am

Hi,

Som basic tracks:
- really: disable the options concerning the use of statistics (the statistics gathering). This server tuning (use of statistics) theoretically improve, help the query optimizer; it's true, BUT this feature becomes only effective, only on huge bases (Gb), and only after having collected a lot of statistics (i.e. only when the distributions became stable.). Otherwise, before having collected a large number of query statistics, this query statistics slows everything down, for a long time before becoming effectively useful (this is globally valid for all databases).
- check if there are "full table scan", "the devil" of databases >:D of course (among the IBX components, there is a Monitor component; it allows you to monitor from the client application, the execution plan of all the queries. Otherwise, I think it should be possible to do this on the database side) ==> if that's the case, add missing indexes or recreate them.

A well known reminder, concerns strings: with variable length strings, we use VARCHAR, instead of CHAR (length_of_the_longest_possible_string).

nb: I see you're doing "SELECT * ":
- If you use small tables that don't grow much, it's okay.
- If you use large tables, you should find a way to launch filtering queries (adding a way to create "WHERE" or "HAVING" dynamic filters from the client sides's UI, returning only a subset of the table: the network and the clients will be "happy".

One last point to check: If you then apply filters over your "SELECT *" to filter the records (by changing a Filter property of a T{IBX}TableSet, for example), the client will do a understandably `fetch all` for the first "SELECT *" (but remember: il will slow down the application, as the\each table grows; and all the more so, if you're doing it on multiple tables at the same time), and it will also do a "worried `fetch all`" before each client-side application change of its filter, "worried `fetch all`" in order to be sure, that the client application will have all the lastest updated records on the client workstation, before launching its loops on the client side to filter all the existing records ==> T{IBX}Table must be replaced, as often as possible, by T{IBX}Query.

Title: Re: SELECT * FROM takes forever in Firebird, after windows reboot
Post by: fjabouley on January 17, 2020, 12:50:25 pm

Quote from: rvk on January 17, 2020, 11:19:03 am

Quote from: fjabouley on January 17, 2020, 11:16:27 am
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.

Title: Re: SELECT * FROM takes forever in Firebird, after windows reboot
Post by: rvk on January 17, 2020, 12:52:33 pm

Quote from: fjabouley on January 17, 2020, 12:50:25 pm

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.

So maybe a gstat (of a slow and fast database) can give you that information.

Title: Re: SELECT * FROM takes forever in Firebird, after windows reboot
Post by: fjabouley on January 17, 2020, 01:19:01 pm

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) ?

I got this for the first query I make after killing firebird service (in flamerobin)

Code: [Select]


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.

and after that :

Code: [Select]


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

Title: Re: SELECT * FROM takes forever in Firebird, after windows reboot
Post by: fjabouley on January 17, 2020, 01:26:58 pm

gstat of working / non working :

https://pastebin.com/86hUy7WT (https://pastebin.com/86hUy7WT)

Title: Re: SELECT * FROM takes forever in Firebird, after windows reboot
Post by: rvk on January 17, 2020, 01:39:57 pm

Quote from: fjabouley on January 17, 2020, 01:19:01 pm

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) ?

First of all, SELECT * FROM CLIENTS WHERE NOM LIKE '%JAB%' can't use indexes. This is because, even is you would have an index on NOM, it can't use it because you use %JAB%. If you would use JAB% it could use an index on NOM.
(B.T.W. you should use SELECT * FROM CLIENTS WHERE UPPER(NOM) LIKE '%JAB%', because you might want case-insensitivity with this search)

Second, 2980204 fetches seems a lot. Is this on a table with just 3000 records?

About your gstat.

Code: [Select]

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

For the non-working version you see an "Oldest snapshot" which is very old. You also have a gap between your Oldest transaction and Next transaction. So executing the query might mean it needs to dig through a lot of data because the snapshots (and transaction-data) is fragmented.

Quote

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.

Quote

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.

Was the non-working database, a database which was in use at the time? And if the database is in use, how long was it in use (programs that don't close itself)? That could explain the long-running transactions and old snapshots.

It's at least clear that the sweep isn't working (probably because of that hanging transaction).
https://firebirdsql.org/manual/gstat-example-header.html
https://firebirdsql.org/manual/gfix-housekeeping.html

Try this on the non-working database. (select Open transactions)

Code: SQL [Select][+]

SELECT
  ma.mon$attachment_id,
  ma.mon$server_pid,
  ma.mon$state,
  ma.mon$attachment_name,
  ma.mon$remote_protocol,
  ma.mon$remote_address,
  ma.mon$remote_process,
  ma.mon$timestamp,
  mt.mon$transaction_id,
  mt.mon$timestamp,
  CAST(ms.mon$sql_text AS CHAR(5000)) AS Sql_statement
FROM mon$attachments ma
LEFT JOIN mon$transactions mt ON mt.mon$attachment_id=ma.mon$attachment_id
LEFT  JOIN mon$statements ms ON ms.mon$transaction_id=mt.mon$transaction_id
ORDER BY 1

Title: Re: SELECT * FROM takes forever in Firebird, after windows reboot
Post by: fjabouley on January 17, 2020, 01:49:31 pm

Thanks for your answer.

The client table has about 3000 records.

Actually I use zeos components, (some created during run-time and then freed) with auto-commit, but it generally has active components that are not closed and stay opened. Closing them (TZquery.active := false) could be enough to avoid this issue ? Is it a committing issue ?

Title: Re: SELECT * FROM takes forever in Firebird, after windows reboot
Post by: rvk on January 17, 2020, 01:52:50 pm

Quote from: fjabouley on January 17, 2020, 01:49:31 pm

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.

Does the oldest transaction and oldest snapshot increase after the first query after a reboot?
In that case it was the sweep/clean-up of Firebird which slowed down your first query.

It's usually advised to close all clients at some point (at night) to let the DB do it's clean-up work.

Title: Re: SELECT * FROM takes forever in Firebird, after windows reboot
Post by: fjabouley on January 17, 2020, 01:53:45 pm

here the result

Title: Re: SELECT * FROM takes forever in Firebird, after windows reboot
Post by: fjabouley on January 17, 2020, 01:56:11 pm

I need to go back to work, I'll come back in few hours, thanks so much rvk and all !!

Title: Re: SELECT * FROM takes forever in Firebird, after windows reboot
Post by: rvk on January 17, 2020, 01:59:16 pm

Quote from: fjabouley on January 17, 2020, 01:53:45 pm

here the result

Yes, but that's after the clean-up is done (and after the transactions are close/ended), isn't it?
If you ran this when everyone already has exited the DB, the clean-up process already continued (with a delay of several minutes to do the actual clean-up).

So run this on the non-working DB in production-state (when everyone is still in it).

Title: Re: SELECT * FROM takes forever in Firebird, after windows reboot
Post by: fjabouley on January 17, 2020, 02:01:38 pm

the result appears on the non working database, I just tried SELECT FROM after that and it took about 5 minutes again.
I'll try with other computers connected

Title: Re: SELECT * FROM takes forever in Firebird, after windows reboot
Post by: rvk on January 17, 2020, 02:10:32 pm

Quote from: fjabouley on January 17, 2020, 02:01:38 pm

the result appears on the non working database, I just tried SELECT FROM after that and it took about 5 minutes again.
I'll try with other computers connected

I wonder why the sweep hasn't taken place yet.
Because the oldest snapshot is 76 and you have restarted the server so there shouldn't be any more older interesting transactions.

Quote

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.

You could do a manual sweep and see if that fixes it.
gfix -sweep databasename

If it does (and the oldest snapshot is increased) you'll need to figure out why the automatic sweep didn't take place.

Title: Re: SELECT * FROM takes forever in Firebird, after windows reboot
Post by: lucamar on January 17, 2020, 02:13:19 pm

I've been looking a little in our (very few) Firebird installations and could find nothing even remotely similar to what you're seeing, just the normal startup delays (a few seconds).

Frankly, I'd suggest you ask in the proper Firebird mailing list (https://firebirdsql.org/en/mailing-lists/); people there should be more attuned than us to these kind of problems.

Title: Re: SELECT * FROM takes forever in Firebird, after windows reboot
Post by: devEric69 on January 17, 2020, 02:35:44 pm

Quote

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.

+1: it could just be a tuning server problem. firebird-support@yahoogroups.com is database administrators oriented.

Otherwise, it's just an idea to continue on the "path" of @rvk ( it's a fact: auto-commit or not, Firebird is transactional ): theoretically, you could write a query that shows who is connected, and since when: https://firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref-appx05-monattach.html (https://firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref-appx05-monattach.html). So, it would be possible too, to write a kind of "watchdog", and to launch it regularly with a TTimer, so that it rollback the too long elapsed opened transactions, or even kills the too long connexions (which will rollback all the transactions using that connection), and informs the user (for that, the first thing which might be do, would be to figure out how to retrieve the transactions list inside an opened program).

Title: Re: SELECT * FROM takes forever in Firebird, after windows reboot
Post by: Thaddy on January 17, 2020, 02:50:27 pm

It might be as simple as not defining a primary index instead of having it built on the fly every time.

Title: Re: SELECT * FROM takes forever in Firebird, after windows reboot
Post by: rvk on January 17, 2020, 02:53:16 pm

Quote from: Thaddy on January 17, 2020, 02:50:27 pm

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.
(B.T.W. there is a primary index on this table)

This is defenitly a transaction/sweep problem. When the database first starts it's cache is empty. When the first query with LIKE is done, it takes time to internally access all those transaction/snapshots. By the second query this is faster because it can be done from memory.

And it's already established that after a restore everything works as expected because the old transactions are removed.

Title: Re: SELECT * FROM takes forever in Firebird, after windows reboot
Post by: Thaddy on January 17, 2020, 02:58:45 pm

Quote from: rvk on January 17, 2020, 02:53:16 pm

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.

Title: Re: SELECT * FROM takes forever in Firebird, after windows reboot
Post by: rvk on January 17, 2020, 03:00:26 pm

Quote from: Thaddy on January 17, 2020, 02:58:45 pm

Quote from: rvk on January 17, 2020, 02:53:16 pm
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 :P

In any database you would encounter similar problems if transactions are not closed properly.

Title: Re: SELECT * FROM takes forever in Firebird, after windows reboot
Post by: Thaddy on January 17, 2020, 03:51:35 pm

Quote from: rvk on January 17, 2020, 03:00:26 pm

Do you mean that Postgress can clean transactions even if they are still in use by a client? Magical :P

No. But Firebird and family seem to have more problems. (I never saw it scaling beyond 10 connections.)

Quote

In any database you would encounter similar problems if transactions are not closed properly.

Yes, but less often as visible.

Title: Re: SELECT * FROM takes forever in Firebird, after windows reboot
Post by: ttomas on January 17, 2020, 03:55:40 pm

It's not FirebirdSQL problem. You have bugy design Application.
Please read about transaction type and uses for Firebird.
https://ib-aid.com/en/transactions-in-firebird-acid-isolation-levels-deadlocks-and-update-conflicts-resolution/

Monitor gstat, this numbers must be in range of active users/transactions
Oldest transaction 6656
Oldest active 6657
Oldest snapshot 76
Next transaction 7188
Main problem 7188-76 toooooo much
Also 7188-6656 tooooo much
Why you use snapshot transaction? This type of transaction is snapshot (freeze state of data) in moment of start the transaction. If you relay need snapshot, don't keep this transaction open for long period of time.
Every job in DB must be:
START TRANSACTION
DO YOUR JOB
COMMIT/ROLLBACK TRANSACTION

Do your job as soon as possible.

Title: Re: SELECT * FROM takes forever in Firebird, after windows reboot
Post by: rvk on January 17, 2020, 03:56:33 pm

At least I've never seen any of these problems (with lots of customers with 10-15 clients).

And maybe most of these problems occur because there is no real system-administrator on-site.
(correct system-administration is a specialty too)

Title: Re: SELECT * FROM takes forever in Firebird, after windows reboot
Post by: devEric69 on January 17, 2020, 04:11:54 pm

Firebird maintainers are well aware of Firebird's weaknesses. For example, the future Firebird will be able to be installed, allowing to create a database, and to access it in development, without knowing that there is an admin account, privileges, and privilege groups called roles. It's only, once "stuck" during several months of development - as web developers did in the 2000s - that they will ask themselves the question "what about roles and security?" ^^ .

Otherwise, do you know another database that could do that ==> Firebird, Wine and unixODBC (http://home.prea.net/old/priscilla0/index/index-353.html?q=book/export/html/77 (http://home.prea.net/old/priscilla0/index/index-353.html?q=book/export/html/77))? It allows in theory to do embedded under Wine and to access the server nested in the *nix part.

Last thing: for those who want to start Firebird development: use IBX! The components are not generalist like the Zeos ones: they stick to the features and to the API of this server.

Title: Re: SELECT * FROM takes forever in Firebird, after windows reboot
Post by: fjabouley on January 17, 2020, 07:40:50 pm

Thanks for all your answers.

Actually I got a TzConnection (modules.jconnect) component in my form, and some Tzquery components too linked to it.

The Tzconnection has autocommit checked and transactionisolationlevel to tiReadCommitted

For the existing components
I often do

Code: [Select]

Tzquery.Edit;
TZquery.fieldbyname......
Tzquery.applyupdates;

and I very often create other components at runtime

Code: [Select]


    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;

Title: Re: SELECT * FROM takes forever in Firebird, after windows reboot
Post by: Thaddy on January 17, 2020, 08:17:40 pm

AutoCommit is usually not a good idea in your scenario: too much traffic.

Title: Re: SELECT * FROM takes forever in Firebird, after windows reboot
Post by: fjabouley 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 ?

Title: Re: SELECT * FROM takes forever in Firebird, after windows reboot
Post by: rvk 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?

Quote from: Thaddy on January 17, 2020, 08:17:40 pm

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).

Title: Re: SELECT * FROM takes forever in Firebird, after windows reboot
Post by: fjabouley 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 !

Title: Re: SELECT * FROM takes forever in Firebird, after windows reboot
Post by: devEric69 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][+]

transaction.start;
try
    pseudo code A;
    pseudo code B;
    transaction.commit;
except
    transaction.rollback;
    on E:Exception do oLog.write('Firebird error:'+etc);
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 (https://www.mwasoftware.co.uk/documentation)...

Anyway, good refactoring :) .

Title: Re: SELECT * FROM takes forever in Firebird, after windows reboot
Post by: fjabouley 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 !

Title: Re: SELECT * FROM takes forever in Firebird, after windows reboot
Post by: rvk 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).

Title: Re: SELECT * FROM takes forever in Firebird, after windows reboot
Post by: fjabouley 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

Title: Re: SELECT * FROM takes forever in Firebird, after windows reboot
Post by: fjabouley 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

Title: Re: SELECT * FROM takes forever in Firebird, after windows reboot
Post by: fjabouley 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.

Title: Re: SELECT * FROM takes forever in Firebird, after windows reboot
Post by: rvk 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).

Title: Re: SELECT * FROM takes forever in Firebird, after windows reboot
Post by: zamronypj on January 20, 2020, 06:31:16 am

Quote from: fjabouley on January 17, 2020, 09:49:39 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.

Title: Re: SELECT * FROM takes forever in Firebird, after windows reboot
Post by: fjabouley 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...)

Title: Re: SELECT * FROM takes forever in Firebird, after windows reboot
Post by: rvk 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?)

Title: Re: SELECT * FROM takes forever in Firebird, after windows reboot
Post by: fjabouley 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...

Title: Re: SELECT * FROM takes forever in Firebird, after windows reboot
Post by: rvk on January 20, 2020, 05:35:04 pm

Quote from: fjabouley on January 20, 2020, 05:27:30 pm

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.

Title: Re: SELECT * FROM takes forever in Firebird, after windows reboot
Post by: avra on January 20, 2020, 06:45:45 pm

Have you tried to turn off db sweep, antivirus, and windows search/indexing? Do you use some FB beta?

Title: Re: SELECT * FROM takes forever in Firebird, after windows reboot
Post by: rvk on January 20, 2020, 06:50:31 pm

It is indeed possible that the slowness is caused by the sweep itself.
If you reboot the server, all transactions have completed and the sweep could be done during first interaction with the database. In that case, disabling automatic sweep could help. Manualky sheduling a sweep is then needed.

Exiting everyone at night before doing the gbak shoukd work too (because the sweep then will be done at night).

Title: Re: SELECT * FROM takes forever in Firebird, after windows reboot
Post by: fjabouley on January 21, 2020, 09:52:09 am

Thanks everybody for all your answers.
Do you think it would be a good idea to create datasets at runtime filling it with a component that would include a [TZconnection and a TZquery] (one component per table) so that it would be possible to open/close a connection at each request and avoid commit-retaining?

Title: Re: SELECT * FROM takes forever in Firebird, after windows reboot
Post by: devEric69 on January 21, 2020, 10:23:57 am

[aside /on]

>
> [snip] at each request and avoid commit-retaining
>

Hi,

I 'm not sure that it's possible to talk about true "Commit-retaining" with Zeos. It should better to continue to talk about "commit soft" or "commit soft" as in the preceding posts (in short, using a different neologism), with Zeos.

Let me explain: what I know, as information, is t hat "Commit-retaining" and "Rollback-retaining" would be a feature specific to Firebird, and can only be properly set up easily with IBX, AFAIK. I'm not at all sure that ZEOS supports this feature.

"Commit-retaining" and "Rollback-retaining is a Firebird transaction mode, which is only particularly recommended for a single or very few user(s): so, typically recommended for *Firbird embedded*. The "Commit-retaining" and "Rollback-retaining" option tells Firebird to leave in memory - in the heap - all the allocated structures and objects that are used to manage the transaction log for all subsequent Commits and Rollbacks, per user connection. In other words, after a user transaction is opened (startTransaction to SELECT, UPDATE, ...), the allocated structures\objects - dedicated par user - are definitively loaded into memory, until the user logs out.

==> Is this really what Zeos is doing? I'm not sure.

[aside /off]

Title: Re: SELECT * FROM takes forever in Firebird, after windows reboot
Post by: ttomas on January 21, 2020, 11:01:07 am

Personally I don't like ZeosDbo with Firebird. Zeos only support one active transaction per connection, like PostgreSQL and MySQL. Firebird support have future to have many active transaction per connection. You can use one type of transaction for select and another type of transaction for DML.
Zeos have bad behavior to always have active/started transaction when you connect. When I test mORMot/Zeos/Firebird and monitor/trace firebird I notice this.
First http request connect and start transaction1 execute sql commit1 and start transaction2.
Second http request use transaction2 execute sql commit and start transaction3.
I see transaction shift from request to request. If you have long period of time between requests you will have open inactive transaction in db. This is no go for me to use Zeos with firebird. I like to have control of transactions and any job in db to look like book
START TRANSACTION
DO SQL/DML
COMMIT/ROLLBACK

Title: Re: SELECT * FROM takes forever in Firebird, after windows reboot
Post by: rvk on January 21, 2020, 11:12:09 am

After reading that, Zeos would also never be an option for me i.c.w. Firebird.
On Delphi I use IBX but that's not maintained specifically for Firebird but more for Interbase.

Since a few years, there is IBX for Lazarus. Actively developed and maintained and much more geared towards Firebird.

Title: Re: SELECT * FROM takes forever in Firebird, after windows reboot
Post by: fjabouley on January 21, 2020, 11:40:01 am

Ok, then I will try IBX, I never thought about how zeos was managing transactions at the beginning of coding my app... I will try now to convert from zeos to IBX, as there doesn't seem to be a real solution for this problem.
And again thank you all for your help. This forum is awesome.

Title: Re: SELECT * FROM takes forever in Firebird, after windows reboot
Post by: devEric69 on January 21, 2020, 12:28:47 pm

I post small tests to understand how IBX components work, more precisely the cient side effects of properties (when an important property is set to true, what happens exactly, etc).
For those who are interested, it's in French, but the deepl.com (http://deepl.com) translator is really excellent.

Title: Re: SELECT * FROM takes forever in Firebird, after windows reboot
Post by: EgonHugeist on January 22, 2020, 06:59:23 am

Hi all. Sorry for my english..
Did ever somebody ask fjabouley which Zeos version he uses?
Since 7.2.6 and 7.3 the FireBird transaction handling did change completely.

In the past the AutoCommit mode was emulated by Zeos and as the old docs discribe there was an infinate retained transaction used for the transaction. So the old docs need an update.

Then we introduced the use of "isc_tpb_autocommit" to stop the emulation of the AutoCommit mode (did fail for some reaseons). See https://sourceforge.net/p/zeoslib/tickets/179/ (https://sourceforge.net/p/zeoslib/tickets/179/)
This then meant we permanently create an new transaction on changing the AutoCommit mode.
Technical details for the new behavior:
As most of you know FB closes all record streams for commits and rollbacks except the retain mode is used. So i remove the old transaction from the dbc transaction manger and call a retain commit. Each open LOB or recordstream which is not fetched until EOR holds a reference to it's transaction. If last row was fetched or the stream get's closed we unlock the reference to the transaction. If the Txn refcount goes to zero we call the "hard" commit/rollback which removes the garbage and snapshots on the server. The "hard_commit" property still is supported.

Summary: Zeos does no longer use "long" transactions. Transactions are underway as long a cursor is open per txn. It's more user friendly the introducing a CommitRetain or RollBackRetain imho Which is a FB thing only.

Since 7.3 automatic nested transactions are supported see https://sourceforge.net/p/zeoslib/tickets/238/ (https://sourceforge.net/p/zeoslib/tickets/238/) Connection lost, BCD/FMTBcd fields too. The 7.3 dbc kernel got performance upgrades for the CRUD operations etc.
R/W ops using ZDBC only are somewhat faster now than any known TDataSet (just for those who need high performance)

For 7.2.6 a bug fix is required if you do not use SVN: see https://sourceforge.net/p/zeoslib/code-0/6227/ (https://sourceforge.net/p/zeoslib/code-0/6227/)

I don't think fjabouley needs to change the components .. he needs an upgrade.

Have fun, Michael from ZeosDevTeam

Title: Re: SELECT * FROM takes forever in Firebird, after windows reboot
Post by: fjabouley on January 22, 2020, 08:38:09 am

Hi !
Actually I used 7.2.4 version, I didn't upgrade yet (didn't know it had been updated), but that's good news, I will try to see if it solves my issue !
Thanks for your message !

Title: Re: SELECT * FROM takes forever in Firebird, after windows reboot
Post by: ttomas on January 22, 2020, 09:56:24 am

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

Title: Re: SELECT * FROM takes forever in Firebird, after windows reboot
Post by: avra on January 22, 2020, 10:19:38 am

One more (and last) time:

Quote from: avra on January 20, 2020, 06:45:45 pm

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:
https://firebirdsql.org/manual/gfix-housekeeping.html

Title: Re: SELECT * FROM takes forever in Firebird, after windows reboot
Post by: EgonHugeist on January 22, 2020, 06:13:03 pm

@ttomas
we just create a txn if a request is done. To be honest since we support Unicode compilers including NextGen and we found ton's of problems with Charset "NONE"+Attachment Characterset different to "NONE"(SQL's are translated corectly while Params and colum conversions did fail) we implicit ask the DB for the characterset after the connection is established. We close the connection then, reopen with attachment CS "NONE" and use the given CodePage-Info as Alias for all (VAR)CHAR/CLOB columns to identify what CS_NONE means to be. Means 1 txn was made and released -> while waiting for the next request we're in Microsoft words "Zombi"-state(no active txn).
Since AB(synopse) changed his job i got no more replys for my upgrades of SynDBZeos.pas. I've my own local version. Maybe i'll add the maintained version to our SVN(Decision are not made yet). The current SynDBZeos.pas may lead to the behavior you do not "like". On the other hand if this request is done, the txn is tagget as orphan and will destroy it's selve as described above.
Please use the Zeos forum for such questions. It's not related to the original question.

@fjabouley
please consider all hints you got inbetween. Nothing prevents you from changing the components if you like the work which goes in hand by doing that. I just wanted to clearify the txn behavior of the documantions, which is outdated inbetween. Also consider thinking about design issues you made in your programm. It's definitely possible you've loads of work changing the components but the results are the same.... It's not worth it the approach?

Title: Re: SELECT * FROM takes forever in Firebird, after windows reboot
Post by: EgonHugeist on January 24, 2020, 07:03:40 am

Double post.
@tttomas, On 7.3 the connection is established, if all server informations (Charset, ServerVersion etc.) or collected, we close the txn if no AutoCommit is used. Zeos is waiting for requests to create the txn handles.
See https://sourceforge.net/p/zeoslib/code-0/6270/ (https://sourceforge.net/p/zeoslib/code-0/6270/)

@fjabouley
i've checked the code twice. Your problem is the permanent AutoCommit mode. So my decribed behavior will not happen. There is no reason for Zeos to spawn a new txn if nothing changes on the transaction-parameter-buffer and the Retained behavior continues. If you do not close the active autocommit-txn. Means you just need to call a StartTransaction+Commit/Rollback from time to time. Then the refcounting logic i made will do the job as described.
It's your turn to free the resources on the Server not the job of Zeos.
Just a quick$dirty proposal to resolve your problem in a hand full of lines:
Use a TTimer object, add YourConnection.StartTransaction followed by YourConnection.Commit in the OnTimer event. Set the interval to a minute and you will see your issue is gone.
As i wrote consider ALL hints you got.

Have fun, Michael from ZeosDevTeam.

Title: Re: SELECT * FROM takes forever in Firebird, after windows reboot
Post by: fjabouley on January 24, 2020, 11:53:09 am

@EgonHugeist
Thanks for your answers !
Actually the trick seems to work. (the transactions increases in gstat).
Is it enough to make the sweep perform or have I to invoke it sometimes(I set auto sweep every 10000 transactions)?

In fact I got a function that checks the database every few seconds, inserting connection.starttransaction and commit would work too ?

Code: Pascal [Select][+]

 
 
function getlastlogid(typeid: string; extra: string = ''): logid;
var
  jth_misc: TZquery;
begin
  Result.id := -1;
  jth_misc := TZquery.Create(nil);
  with jth_misc do
  begin
    Connection := modules.jconnect;
    Connection.starttransaction;
    SQL.Clear;
    if length(extra) = 0 then
    begin
      SQL.add('SELECT FIRST 1 * FROM LOGS WHERE TYPE=:TYPE ORDER BY ID DESC');
      ParamByName('TYPE').AsString := typeid;
    end
    else
    begin
      SQL.add('SELECT FIRST 1 * FROM LOGS WHERE (TYPE=:TYPE) OR (TYPE=:TYPE2) ORDER BY ID DESC');
      ParamByName('TYPE').AsString := typeid;
      ParamByName('TYPE2').AsString := extra;
    end;
    Open;
    if not EOF then
      with Result do
      begin
        id := FieldByName('ID').AsInteger;
        type_id := FieldByName('TYPE').AsString;
        date_log := FieldByName('DATE_LOG').AsDateTime;
        heure_log := FieldByName('HEURE_LOG').AsDateTime;
        valeur := FieldByName('VALEUR').AsString;
        texte := FieldByName('TEXTE').AsString;
      end;
    Close;
    Connection.commit;
    Free;
  end;
end;      
 

Best regards

Title: Re: SELECT * FROM takes forever in Firebird, after windows reboot
Post by: devEric69 on January 24, 2020, 02:42:31 pm

Quote from: ttomas on January 22, 2020, 09:56:24 am

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

@tttomas: thank you for the link above. Very, very informative your trick for retrieving the essential of transactional informations, summarized in your xls sheet. And it's a Lazarus \ Free Pascal topic, so not at all off-topic.

@fjabouley: I'm finally glad your Firebird\Zeos project is going ahead: I know it's been hard work :) .
Nevertheless, I post here the message sent at your request, because the timer idea could be to benefit other people, and it is already integrated in the IBX components. It may be of interest to those who will make this choice of components:

[message /on]>>>>>

Quote

Are IBX components easy to use,
Best regards

I can't answer yes or no.
What I can say is that if I use Firebird, it's because there are the IBX components.
Originally, I had an OMT development training on 3FN SQL databases. I also had an IBX training: via the old Delphi developer's guides (I have the paper ones from 1 to 3), and also by a freelance trainer. And the discovery of the TIB{X}Dataset component "resonated" fully with "OMT over SQL databases". Simply put, this component perfectly fits the "active record" pattern in the different client-server configurations.

Quote

or at least easy enough to switch from zeos?

I don't know how you coded the application: if you created a datamodule as a business object, i.e. one datamodule per table, I can already answer that this is possible.
But the properties of TIB{X}Dataset are more numerous than those of a similar Zeos component (for example, you have to define in it 5 queries managing the record (SELECT, UPDATE, INSERT, DELETE and REFRESH), it has an integrated timer to say what to do when nothing happens transactionally anymore, it can tell whether it takes into account simple auto-commit or commit-retaining, caching (writing to a client-side transaction log, which can then be "ApplyUpdates", with resolving possibilities). So I can't answer yes, it's easy, since the *.dfm will be quite different.

On the other hand, it's worth installing the IBX package, first of all because there's an administration application included, which compiles and can connect to any database. It has a tab where it is possible to monitor connections with an auto-refresh option. It uses, and queries on the database via the "$my..." tables, ..., and calling SQL server services via a component encapsulating the server APIs.
In addition there are sample applications, which is instructive. Concerning the IBX suite, there are components that I find "smart", like a component to pass differential scripts in series to make an automatic update of the database (an upgrade), a SQL parser to easily modify a query, etc..

As far as your "old" transaction problems are concerned, I can only advise you to fix them using the "watchdog" idea:
- Basically, it consists of opening a timer on the opening of a record (which opens a dataset, I guess);
- the watchdog timer goes off on an OnIdle event and counts down the time;
- after x minutes (it can be in the *.ini file, so configurable by role), let's say 5, if nothing happened, the idea is to call a serialized code (the same for all your records) which displays a message like "Save your modifications and refresh the data modified by your colleagues...": it checks the (?) oTransaction components.InTransaction; it calls COMMIT; if it fails, it calls ROLLBACK; it must be handled silently (capturing a possible logged exception); it writes in a record the primary keys of the current bookmarks; it closes the detail datasets, then their master dataset; it closes the connection; it re-opens the connection; it opens a transaction, the detail dataset and then its master within that transaction; it bookmarks the current records again.

I really have the impression that there might be a "disable index" option somewhere in your backup: I know it exists, but ask your problem on the Yahoo-firebird mailing list: it's the Firebird forum.
<<<<<[message /off]

Where did the idea for the timer, the so-called "watchdog" come from? I'll repeat myself, but in the HTML page posted here ( https://forum.lazarus.freepascal.org/index.php/topic,48159.msg346926.html#msg346926 ),
there is in it, an explanation on the "Study of the "watchdog making regular rounds" (in french:
"Étude du chien de garde faisant régulièrement sa ronde"): it's just a TTimer integrated in the TIB{X}Transaction's property) using its associated property, TIB{X}Transaction.DefaultAction".
This integrated timer allows an automatic Commit or Rollback to each OnTimer event.
But, the timer is already cleverly integrated in the IB{X} transaction component ;) .

Title: Re: SELECT * FROM takes forever in Firebird, after windows reboot
Post by: fjabouley on January 24, 2020, 03:17:39 pm

I just wanted to thank you all again, I'm so glad you all helped me to better understand what was happening and to be less ignorant with firebird and zeos' behaviours, and I wish I could be as helpful as you were for me (but I can barely help because I'm a noob :) ) This forum is really cool !

Title: Re: SELECT * FROM takes forever in Firebird, after windows reboot
Post by: fjabouley on January 30, 2020, 01:53:52 pm

Just to give some news, the timer trick definitely seems to work, I tried to kill firebird server, on a few days running database (all clients with the timer) and the request on a freshly started firebird server is OK. I know it's only a workaround and that I'll have to, in long term, do something to make better transaction management, but once again thank you all for your replies ! Best regards