Firebird/Jaybird scaling

[Freddy Ertl]

Hi,

We're using Firebird in a web application with a high number of transactions (3000-4000 per second). Even though 95% of the statements are read only, we're facing issues where the CPU load goes up and statement execution is very slow. On top of JayBird we're using JPA (EclipseLink). Now I want to try if read-only transactions would have a positive impact, but controlling this in our code base would be a nightmare. So I though about doing this on the JDBC level. Since our SELECT statements do not have modifying side effects, I want to use read-only transactions whenever a SELECT statement is executed. Do you think this could be done somewhere in JayBird without touching too much?

Regards

Freddy

[Mark Rotteveel]

Using read only transaction has to be a conscious choice on your side.
Jaybird cannot control that for you: not all selects are read-only
operations (e.g. think about `select * from SOME_PROCEDURE`, where
SOME_PROCEDURE also performs modifications), so Jaybird cannot decide
that for you, and also it is impossible to decide from the first
statement what the entire a transaction will need (though granted, in
auto-commit mode, it might be possible).

Which server mode are you currently using SuperServer, SuperClassic or
Classic? Switching from SuperServer to Classic might have benefits
(however, don't forget to tune other things like page buffers
configuration). Also check if you're not running into problems with
long-running transactions inhibiting garbage collection, that might slow
down queries.

Mark

P.S. It is Jaybird with a lowercase 'b', not JayBird :)

--
Mark Rotteveel

[Freddy Ertl]

Thank you Mark for the quick response, and sorry for the wrong spelling of Jaybird. Too much camel caps stuff in my head.

We're using Firebird 3.0.10 in SuperServer mode on a quite powerful machine (AMD Epyc, 96 physical cores, 256GB, NVMe disks, Windows Server 2022).

The database transactions that we have are many shorter, less complex transactions, (mostly SELECT like I mentioned above) and heavier transactions from management tools. I don't understand why performance starts to degrade without really saturating the server. If this is due to garbage collection, would it help to use read-only transactions? Is SuperClassic/Classic better suited for such a load profile?

Freddy

[Mark Rotteveel]

On 13/03/2024 10:32, Freddy Ertl wrote:
> Thank you Mark for the quick response, and sorry for the wrong spelling
> of Jaybird. Too much camel caps stuff in my head.
>
> We're using Firebird 3.0.10 in SuperServer mode on a quite powerful
> machine (AMD Epyc, 96 physical cores, 256GB, NVMe disks, Windows Server
> 2022).
>
> The database transactions that we have are many shorter, less complex
> transactions, (mostly SELECT like I mentioned above) and heavier
> transactions from management tools. I don't understand why performance
> starts to degrade without really saturating the server. If this is due
> to garbage collection, would it help to use read-only transactions? Is
> SuperClassic/Classic better suited for such a load profile?

The benefit from using Classic is that you're using separate processes
for connections, and though this increases overhead from lock
coordination between those processes, it might improve the processor
utilization.

However, those are all questions that are probably better suited for
asking on firebird-support. I'm not that experienced with tuning
Firebird for high load, so you're more likely to find help there.

Do know that since Firebird 4 (with its default settings of always using
READ CONSISTENCY for READ COMMITTED transaction), the benefit of
read-only transaction is reduced compared to older versions.

Mark
--
Mark Rotteveel

[Freddy Ertl]

Maybe we have to look into Firebird 4/5 and see if it improves the situation.

But I have one more thought on scalability. For PostgreSQL there is pgpool-II which makes load distribution transparent by directing traffic to the appropriate server (primary or replicas) depending on the nature of the statement. That's why I had the idea to do something similar in the JDBC layer for the transaction type. Do you know how others achieved this with JPA/JDBC/Firebird without effectively duplicating everything in code?

Freddy

[Mark Rotteveel]

On 13/03/2024 10:57, Freddy Ertl wrote:
> Maybe we have to look into Firebird 4/5 and see if it improves the
> situation.
>
> But I have one more thought on scalability. For PostgreSQL there
> is pgpool-II which makes load distribution transparent by directing
> traffic to the appropriate server (primary or replicas) depending on the
> nature of the statement. That's why I had the idea to do something
> similar in the JDBC layer for the transaction type. Do you know how
> others achieved this with JPA/JDBC/Firebird without effectively
> duplicating everything in code?

As far as I'm aware, there is nothing like this for Firebird.

Mark
--
Mark Rotteveel