Olympia + Firebird + Mon$statements

Hi, we deploy a omplyia server using firebird 3.0 database and we notice some crashes (A lot of them)

the database is using all the memory in server, and we notice a very high increase in cpu usage too

The first thing we notice is the table mon$statments is keep getting bigger and bigger (maybe the transactions is not been commited?) - i was able to notice this when the server wasn`t very loaded, only 3 users and after a few minutes we get 200+ records in this table

Our .daConnections:

		<Definition>
			<Description/>
			<ConnectionString>FB4.NET?Server=IP;Database=IP:XXXXX;UserID=XXX;Password=XXXXXX</ConnectionString>
			<ConnectionType>Interbase</ConnectionType>
			<Default>True</Default>
			<Name>Olympia.FB.NET</Name>
		</Definition>

we also detected a large number of deadlock:
An exception was raised on the server: deadlock update conflicts with concurrent update concurrent transaction number is 4785765
Inner Exception: IscException: deadlock update conflicts with concurrent update concurrent transaction number is 4785765

we can’t get the transaction, beacause if we try to select anyting from the mon$ tables in firebird the server will crash, cause the very high load on the machine

and the last error just before the server crash:

An exception was raised on the server: too many open handles to database
Inner Exception: IscException: too many open handles to database

again, we cant even select anything from mon$attachments without crashing the server

here is the json

{
  "ServiceName": "PServerSession",
  "Port": 3090,
  "EventTimeout": 180,
  "SessionTimeout": 1800,
  "PoolSize": 1,
  "InMemoryMode": false 
}

we have something like 100 users ±, and 150-200 sessions

before olympia we used a regular sessionmanager in memory (Delphi 10.3)

Hi,

Looks like you have caught this issue - Memory leak in Firebird 3 on implicit transaction [DNET743] · Issue #685 · FirebirdSQL/NETProvider · GitHub

as a workarounds, you can try to

  • install the latest versions of Firebird and NETProvider. also use PoolSize = 4 here:

or

  • back to Firebird 2.5

Looks fixed in firebird 3.0.3

the last .net driver is 9.0.2 dosent seems to work using olympia, do you know wich version i can update?

Hi,

Can you retest this issue with the latest Firebird versions, i.e. Firebird 3.0.10, Firebird 4.0.2 and Firebird 2.5.9, pls?

I will test using firebird 3.0.10

Can’t test using 2.5 or 4.0+

I will let you know the results

(couldn’t update the ado.net Olympia just crash when I try the last one)

Tested using firebird 3.0.10
.net driver version 7.0

same results, at the moment MON$STATEMENTS has 193.404 records

mix of

        DELETE
        FROM
        "EVENTUSERDATA"
        WHERE
        ("EVENTUSER" = ?) AND
        ("EVENTID" = ?)

UPDATE “SESSION”
SET
“LASTACCESSED” = ?,
“DATA” = ?
WHERE
(“ID” = ?)

        INSERT
        INTO "EVENTUSERDATA"
        ("EVENTUSER", "EVENTID")
        VALUES
        (?, ?)

still getting this before crash

An exception was raised on the server: too many open handles to database

and random

An exception was raised on the server: deadlock
update conflicts with concurrent update
concurrent transaction number is 2185594
Inner Exception: IscException: deadlock
update conflicts with concurrent update

hello, help! whelp!

Hi,

This is issue with NETProvider.
They have closed that bug as not a bug/wontfix so it still be reproduced in FB 3.0

Ok, but what can i do ? there is a way to upgrade de .net drivers for olympia?

and, they said is cause because this Issues · FirebirdSQL/firebird · GitHub
and that one is fixed

Hi,

as for me, in [FB3] Memory Leaks. FB does not release memory after load tests. [CORE5416] they have fixed leaks after load tests.

if you still reproduce issue in FB 3.0.10 so they did incomplete fix and issue is still persist.

I can suggest to try to test this issue with Firebird 2.5.


by other hand, you can try to use 3rd party providers like ODBC Driver for Firebird 32/64-bit - Devart - they may work more stable

Ok, im now using sqlite, is stable, not so fast but stable.

there is any chance to upgrade .net drivers for olympia in the future? i notice is using version 7.0.0

the last version is 9.2

i will test using PG too, but fb is easier for me

Logged as bugs://D19290.