Serious performance problems after migrating from FB3 to FB5

[Ivan Cruz]

After migrating from FB3 to FB5, we began experiencing severe performance issues. I understand that performance tuning is a highly complex task and not easily resolved via a mailing list. Nevertheless, today, while utilizing an HQBird trace tool, I noticed a very strange behavior. Please see below:

  

RANK 14 of 188; LINE: 992; TIME: 35060ms; INFO: 35060 ms, 17 fetch(es), 5 mark(s)

2026-08-06T17:07:47.7230 (3676:00000000719E39C0) EXECUTE_STATEMENT_FINISH MHS (ATT_2952196, EDIMAIL:NONE, ISO88591, TCPv4:192.168.0.102/63389) C:\Apache24\bin\httpd.exe:9076 (TRA_2075634796, CONCURRENCY | NOWAIT | READ_WRITE) Statement 323762070: -------------------------------------------------------------------------------

insert into "Log" ("Seq", "Hora", "Severidade", "Modulo", "Referencia", "Origem", "Descricao", "Sessao") values (GEN_ID("SeqLog", 1), 'Now', ?, ?, ?, ?, ?, ?)

param0 = integer, "1" param1 = varchar(80), "ediMail@SM031 (9076, 7788)" param2 = varchar(400), "CENTRAL" param3 = varchar(200), "192.168.101.1" param4 = varchar(4096), "Sessão iniciada para operação "ApiLista naolidas"" param5 = bigint, "6499233" 0 records fetched 35060 ms, 17 fetch(es), 5 mark(s) Table Natural Index Update Insert Delete Backout Purge Expunge **************************************************************************************************************** Log 1

Its a very simple insert, on a very simple table. The only table constraint is a primary key. There are 2 non unique indexes on Hora and Sessao. No triggers are fired. Normaly that insert will take just 0.02 seconds or less, but in that case it took a wooping 35 seconds.

I´m baffled. Any thougs? Where can I go from here to understand that problem?

[Alexey Kovyazin]

Hello,

This query looks like symptom, not a problem - something else has overloaded the system (backup of VM is often reason) and as a result, insert is almost hanged. 

If you have HQbird, compare performance reports from 3 with 5, section Plan-Summary and Plan-Frequency, to see what query became worse. 

This advice makes sense only If you have changed from 3 to 5 on the same equipment/hardware/VM. If you also have changed server VM/hardware, it is probably hardware/VM problem. 

Regards, 

Alexey Kovyazin 

 'Ivan Cruz' via firebird-support <firebird...@googlegroups.com>:

--
Support the ongoing development of Firebird! Consider donating to the Firebird Foundation and help ensure its future. Every contribution makes a difference. Learn more and donate here:
https://www.firebirdsql.org/donate
---
You received this message because you are subscribed to the Google Groups "firebird-support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-suppo...@googlegroups.com.
To view this discussion, visit https://groups.google.com/d/msgid/firebird-support/dd1d362e-75d1-46e9-8275-c4a34d8a199cn%40googlegroups.com.

[Pieter Bas Hofstede]

I am / have been facing similar symptoms.

They were related to mass deletion (0,1% of database, but still over a million rows accumulated over tens of tables) causing a massive garbage collection.

This garbage collection made the Firebird process stall (5.0 Super) for 30 up to 300 seconds. In the meantime only writes were observed in Windows Resource Monitor, at the speed of Q1T1@db-pagesize.

My 'resolution' was to set GCPolicy = cooperative so only clients with bad luck will cleanup some garbage themself and the rest was done by daily gbak and / or sweep. Still during this gbak / sweep the db was hardly responsive during a few intervals during that task But we could schedule both during a minimal user activity period in the day.

So maybe you're facing the same? When you're using Servermode Super, maybe changing ServerMode to SuperClassic can give you an insight if the shared cache is the problem or something else.

I am still in the analyzing process and am in contact with Firebird team.

Op vrijdag 7 augustus 2026 om 09:22:51 UTC+2 schreef alexey....@gmail.com:

[Ivan Cruz]

#CONFIDENCIAL

Hello Alexey,

Thanks for your answer. Let me explain it better. The migration didn't change the VM in any way.
The same processors, the same memory, and the same disk (NAS volume). There were changes
in software. Besides FB, we updated all the client libraries to the most up-to-date version. The main
application also changed. Many non-critical features were removed to make the system usable.
Despite this, the old stack performed way better.

The case I presented is, of course, an outlier. We register thousands of logs a minute and 99.9%
take less than 0.02 seconds. Those extreme cases happen more or less randomly and can affect
any query. I could not pinpoint one specific suspect.

I believe disk retention does not play a role. I can have problems even on queries that do not access
the disk in any way, like the one on that FlameRobin log extract:

Starting transaction...
Preparing statement: SELECT LOCALTIMESTAMP FROM RDB$DATABASE
Statement prepared (elapsed time: 0.000s).
Field #01: .LOCALTIMESTAMP Alias:LOCALTIMESTAMP Type:TIMESTAMP
PLAN (RDB$DATABASE NATURAL)
Parameters: 0


Executing statement...
Statement executed (elapsed time: 0.000s).
0 fetches, 94 marks, 0 reads, 65 writes.
0 inserts, 0 updates, 0 deletes, 0 index, 1 seq.
Delta memory: 0 bytes.
Total execution time: 1.499s
Script execution finished.
Committing transaction...
Transaction committed (elapsed time: 0.340s).

	Ivan Camilo da Cruz Analista 21 3385-3099 | 3050 21 974-333-609 Porto Atlântico Business Square Bl 3 Sl 1507 – Rio de Janeiro, RJ www.supplymidia.com.br

To view this discussion, visit https://groups.google.com/d/msgid/firebird-support/CAH0_ZaT6oYMG%3DXUF79r9imPiuUa7G2iV1e2pQh2yqQRzgCyHKA%40mail.gmail.com.

CONFIDENCIALIDADE E DISPOSITIVOS LEGAIS - As informações contidas na mensagem e seus anexos podem conter informações CONFIDENCIAIS, protegidas pelo sigilo legal e por direitos autorais. A divulgação, distribuição, reprodução ou qualquer forma de utilização do teor deste documento depende de autorização do remetente. O descumprimento das orientações expostas sujeitará o responsável às penalidades civis e criminais cabíveis. Caso não seja o destinatário pretendido, por favor, notifique-me imediatamente e retorne a mensagem original.

[Alexey Kovyazin]

Hello Ivan,

When you are saying old version was better, there should be numbers - queries execution times, fetches, reads, writes. Identify which queries changed, then check their plans and statistics. HQbird report which you have quoted provides these data. 

Regards, 

Alexey Kovyazin 

To view this discussion, visit https://groups.google.com/d/msgid/firebird-support/e591ab88-d999-f490-3198-a8d41651aedd%40supplymidia.com.br.

[Ivan Cruz]

#CONFIDENCIAL

Hello Alexey,

We don't have metrics for individual queries on the old system, simply because we didn't needed them.
What we have are the timings for the whole HTTP request. If you take the timings from an arbitrary period
and sort them by duration, you can easily determine how many requests are slow. By my criteria, a slow
request is anything that takes more than 1 second.

On FB3, most of the time, slow requests would be less than 1%.

On FB5, most of times they are more than 10%. It is that bad.

I don't believe a bad plan is the culprit. The same query that takes 2 seconds now will take 0.02 seconds
the next moment. A select from RDB$DATABASE for example, not even have an alternative plan other
than "NATURAL".

Ivan Cruz.

To view this discussion, visit https://groups.google.com/d/msgid/firebird-support/CAH0_ZaR%3Dnce8KvAi9uZsCFHBwB0JMAVBLC6FK5SuzseoN0iHQQ%40mail.gmail.com.

[Ivan Cruz]

#CONFIDENCIAL

Hello Pieter,

We have had our fair share of problems with GC, but most of them were solved more than
a decade ago. We simply turned off the automatic GC and started using a scheduled script
for that job. Under FB3, we could experience a long sweep execution coupled with some
slowness when someone left a non-committed transaction active for some hours, but even
that last problem was solved by FB5.

	Ivan Camilo da Cruz Analista 21 3385-3099 | 3050 21 974-333-609 Porto Atlântico Business Square Bl 3 Sl 1507 – Rio de Janeiro, RJ www.supplymidia.com.br

To view this discussion, visit https://groups.google.com/d/msgid/firebird-support/19d3b30b-6b14-417e-9d0f-9f1c42697da2n%40googlegroups.com.

[Ivan Cruz]

#CONFIDENCIAL

Hello All,

I found it.

To my surprise the problem was not on FB itself nor on my application. I discovered, by pure
chance, that two services on the HQBird package are causing that slowness: FBDataGuard Agent
and FBHQbird Monitor 4. By stoping both, performce shines. Start just one of them and everything
goes downhill.

Time to talk to the russians.

Thanks to everybody.

Ivan Cruz.

CONFIDENCIALIDADE E DISPOSITIVOS LEGAIS - As informações contidas na mensagem e seus anexos podem conter informações CONFIDENCIAIS, protegidas pelo sigilo legal e por direitos autorais. A divulgação, distribuição, reprodução ou qualquer forma de utilização do teor deste documento depende de autorização do remetente. O descumprimento das orientações expostas sujeitará o responsável às penalidades civis e criminais cabíveis. Caso não seja o destinatário pretendido, por favor, notifique-me imediatamente e retorne a mensagem original. --

Support the ongoing development of Firebird! Consider donating to the Firebird Foundation and help ensure its future. Every contribution makes a difference. Learn more and donate here:
https://www.firebirdsql.org/donate
---
You received this message because you are subscribed to the Google Groups "firebird-support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-suppo...@googlegroups.com.

To view this discussion, visit https://groups.google.com/d/msgid/firebird-support/4fb54757-8eb5-b6b9-cf9b-2cc191aef211%40supplymidia.com.br.