Recent

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

fjabouley

  • Full Member
  • ***
  • Posts: 112
SELECT * FROM takes forever in Firebird, after windows reboot
« 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?

rvk

  • Hero Member
  • *****
  • Posts: 4324
Re: SELECT * FROM takes forever in Firebird, after windows reboot
« Reply #1 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?

fjabouley

  • Full Member
  • ***
  • Posts: 112
Re: SELECT * FROM takes forever in Firebird, after windows reboot
« Reply #2 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.

rvk

  • Hero Member
  • *****
  • Posts: 4324
Re: SELECT * FROM takes forever in Firebird, after windows reboot
« Reply #3 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)

fjabouley

  • Full Member
  • ***
  • Posts: 112
Re: SELECT * FROM takes forever in Firebird, after windows reboot
« Reply #4 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.


rvk

  • Hero Member
  • *****
  • Posts: 4324
Re: SELECT * FROM takes forever in Firebird, after windows reboot
« Reply #5 on: January 17, 2020, 11:19:03 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.


devEric69

  • Sr. Member
  • ****
  • Posts: 350
Re: SELECT * FROM takes forever in Firebird, after windows reboot
« Reply #6 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.

« Last Edit: January 17, 2020, 11:44:11 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: 112
Re: SELECT * FROM takes forever in Firebird, after windows reboot
« Reply #7 on: January 17, 2020, 12:50:25 pm »
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.

rvk

  • Hero Member
  • *****
  • Posts: 4324
Re: SELECT * FROM takes forever in Firebird, after windows reboot
« Reply #8 on: January 17, 2020, 12:52:33 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.

fjabouley

  • Full Member
  • ***
  • Posts: 112
Re: SELECT * FROM takes forever in Firebird, after windows reboot
« Reply #9 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

fjabouley

  • Full Member
  • ***
  • Posts: 112
Re: SELECT * FROM takes forever in Firebird, after windows reboot
« Reply #10 on: January 17, 2020, 01:26:58 pm »
gstat of working / non working :


https://pastebin.com/86hUy7WT

rvk

  • Hero Member
  • *****
  • Posts: 4324
Re: SELECT * FROM takes forever in Firebird, after windows reboot
« Reply #11 on: January 17, 2020, 01:39:57 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][+][-]
  1. SELECT
  2.   ma.mon$attachment_id,
  3.   ma.mon$server_pid,
  4.   ma.mon$state,
  5.   ma.mon$attachment_name,
  6.   ma.mon$remote_protocol,
  7.   ma.mon$remote_address,
  8.   ma.mon$remote_process,
  9.   ma.mon$timestamp,
  10.   mt.mon$transaction_id,
  11.   mt.mon$timestamp,
  12.   CAST(ms.mon$sql_text AS CHAR(5000)) AS Sql_statement
  13. FROM mon$attachments ma
  14. LEFT JOIN mon$transactions mt ON mt.mon$attachment_id=ma.mon$attachment_id
  15. LEFT  JOIN mon$statements ms ON ms.mon$transaction_id=mt.mon$transaction_id
  16. ORDER BY 1
« Last Edit: January 17, 2020, 01:45:38 pm by rvk »

fjabouley

  • Full Member
  • ***
  • Posts: 112
Re: SELECT * FROM takes forever in Firebird, after windows reboot
« Reply #12 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 ?


rvk

  • Hero Member
  • *****
  • Posts: 4324
Re: SELECT * FROM takes forever in Firebird, after windows reboot
« Reply #13 on: January 17, 2020, 01:52:50 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.

fjabouley

  • Full Member
  • ***
  • Posts: 112
Re: SELECT * FROM takes forever in Firebird, after windows reboot
« Reply #14 on: January 17, 2020, 01:53:45 pm »
here the result

 

TinyPortal © 2005-2018