Recent

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

fjabouley

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

rvk

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

fjabouley

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


rvk

  • Hero Member
  • *****
  • Posts: 4329
Re: SELECT * FROM takes forever in Firebird, after windows reboot
« Reply #18 on: January 17, 2020, 02:10:32 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.

lucamar

  • Hero Member
  • *****
  • Posts: 3079
Re: SELECT * FROM takes forever in Firebird, after windows reboot
« Reply #19 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; people there should be more attuned than us to these kind of problems.
Turbo Pascal 3 CP/M - Amstrad PCW 8256 (512 KB !!!) :P
Lazarus 2.0.8/FPC 3.0.4 - 32/64 bits on:
(K|L|X)Ubuntu 12..18, Windows XP, 7, 10 and various DOSes.

devEric69

  • Sr. Member
  • ****
  • Posts: 359
Re: SELECT * FROM takes forever in Firebird, after windows reboot
« Reply #20 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. 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).
« Last Edit: January 17, 2020, 02:49:32 pm 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.

Thaddy

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

rvk

  • Hero Member
  • *****
  • Posts: 4329
Re: SELECT * FROM takes forever in Firebird, after windows reboot
« Reply #22 on: January 17, 2020, 02:53:16 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.

Thaddy

  • Hero Member
  • *****
  • Posts: 10516
Re: SELECT * FROM takes forever in Firebird, after windows reboot
« Reply #23 on: January 17, 2020, 02:58:45 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.

rvk

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

Thaddy

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

ttomas

  • Full Member
  • ***
  • Posts: 189
Re: SELECT * FROM takes forever in Firebird, after windows reboot
« Reply #26 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.
« Last Edit: January 17, 2020, 04:19:37 pm by ttomas »

rvk

  • Hero Member
  • *****
  • Posts: 4329
Re: SELECT * FROM takes forever in Firebird, after windows reboot
« Reply #27 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)
« Last Edit: January 17, 2020, 04:00:23 pm by rvk »

devEric69

  • Sr. Member
  • ****
  • Posts: 359
Re: SELECT * FROM takes forever in Firebird, after windows reboot
« Reply #28 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)? 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.
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: 114
Re: SELECT * FROM takes forever in Firebird, after windows reboot
« Reply #29 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;

 

TinyPortal © 2005-2018