Recent

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

rvk

  • Hero Member
  • *****
  • Posts: 6110
Re: SELECT * FROM takes forever in Firebird, after windows reboot
« Reply #45 on: January 20, 2020, 05:35:04 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.

avra

  • Hero Member
  • *****
  • Posts: 2514
    • Additional info
Re: SELECT * FROM takes forever in Firebird, after windows reboot
« Reply #46 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?
ct2laz - Conversion between Lazarus and CodeTyphon
bithelpers - Bit manipulation for standard types
pasettimino - Siemens S7 PLC lib

rvk

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

fjabouley

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

devEric69

  • Hero Member
  • *****
  • Posts: 648
Re: SELECT * FROM takes forever in Firebird, after windows reboot
« Reply #49 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]
« Last Edit: January 21, 2020, 10:29:02 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.

ttomas

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


rvk

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

fjabouley

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


devEric69

  • Hero Member
  • *****
  • Posts: 648
Re: SELECT * FROM takes forever in Firebird, after windows reboot
« Reply #53 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 translator is really excellent.
use: Linux 64 bits (Ubuntu 20.04 LTS).
Lazarus version: 2.0.4 (svn revision: 62502M) compiled with fpc 3.0.4 - fpDebug \ Dwarf3.

EgonHugeist

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

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

Have fun, Michael from ZeosDevTeam
« Last Edit: January 22, 2020, 07:13:08 am by EgonHugeist »

fjabouley

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

ttomas

  • Full Member
  • ***
  • Posts: 245
Re: SELECT * FROM takes forever in Firebird, after windows reboot
« Reply #56 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
« Last Edit: January 22, 2020, 10:39:35 am by ttomas »

avra

  • Hero Member
  • *****
  • Posts: 2514
    • Additional info
Re: SELECT * FROM takes forever in Firebird, after windows reboot
« Reply #57 on: January 22, 2020, 10:19:38 am »
One more (and last) time:
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
ct2laz - Conversion between Lazarus and CodeTyphon
bithelpers - Bit manipulation for standard types
pasettimino - Siemens S7 PLC lib

EgonHugeist

  • Jr. Member
  • **
  • Posts: 78
Re: SELECT * FROM takes forever in Firebird, after windows reboot
« Reply #58 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?
« Last Edit: January 22, 2020, 06:42:59 pm by EgonHugeist »

EgonHugeist

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

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

 

TinyPortal © 2005-2018