Firebird .Net multithreading performance

[Lasse Hansen]

Hi

I have a firebird 2.5.9 installation which causes some issues when under load.

I am using the .Net 6.0 provider with a connection string that looks like this.

 host=***;User=***;Password=***;Database=Vinterman;Port=3050;Dialect=3;Charset=UTF8;Pooling=true;MinPoolSize=0;MaxPoolSize=200;

I have a simple test program which start a number of threads and runs a simple query using LINQ: 

await context.IdpIdRelations.Where(x => x.IdpUniqueId == email).ToListAsync();

The IdpIdRelations table is indexed on the field IdpUniqueId.

Running this with one thread results in a execution time of 0.6 seconds.
Running 200 threads makes all threads take  2.7 seconds.
Even more threads makes the execution time of each thread bigger.

The reason I have made this program is because we are seeing serious performance problems under load.

We have a superclassic installation but have also tried with a classic installation and the same issue occurs.

Running the threads directly through isql seems like it is faster (it completes the first threads before the rest have started).

Is there anything wrong with the .Net provider? Am I using it wrong? Or any other guess as to why this issue occurs?

[Mark Rotteveel]

Given you you ask "Is there anything wrong with the .Net provider?", it
might be better to ask your question on the firebird-net-provider Google
Group (https://groups.google.com/g/firebird-net-provider).

As an aside, trying to run 200 threads in parallel in general will slow
down operations compared to uncontested running, or running a number of
threads close to the number of CPU cores.

Mark

--
Mark Rotteveel

[Lasse Hansen]

I will try and ask the question there as well.

A followup question to your comment.

Is there something fundementally wrong with using firebird for a production setup with 6000-12000 users which will probably result in 1000-2000 requests/connections pr minute? I would expect a database to be able to handle this "relatively" light load.

[Dimitry Sibiryakov]

'Lasse Hansen' via firebird-support wrote 12.10.2023 13:03:
> Is there something fundementally wrong with using firebird for a production
> setup with 6000-12000 users which will probably result in 1000-2000
> requests/connections pr minute? I would expect a database to be able to handle
> this "relatively" light load.

Such setup requires a good DBA, sysadmin, database architect and application
programmer.

--
WBR, SD.

[Elmar Haneke]

Is there something fundementally wrong with using firebird for a production setup with 6000-12000 users which will probably result in 1000-2000 requests/connections pr minute? I would expect a database to be able to handle this "relatively" light load.

At first, you should consider changing to a later FirebirdSQL than 2.5.9. Starting with v3 there are significant improvements in spreading work on several CPU cores. With v2.5 SuperServer is locked to a single core (for a single database), v3 can spread each connection to a different core.

For a Scenario with up to 12k Users (online at the same time?) you should consider to design an application server which handles the database activity with fewer permanently open database connections (connection-pool).

Elmar

[Lasse Hansen]

Hi

We are in the process of switching the entire database and applications to a more modern foundation we just have the to live with the client for another 6 months - however there are some critical business functions which will have to function in those 6 months.

the 12k users are active at the same time in peak situations (10-30 nights of the year). We might look into batching options for the most common (persistent) requests.

Better horizontal scaling on out application server seem to have relieved some of the concerns, we are however still experiencing a somewhat significant slowdown from the database/.Net provider when performing load tests. For now we will continue to make small improvements to the Firebird settings and scaling and hopefully we can make it through until we have something better.

Thanks for the replies.

[Alexey Kovyazin]

Hello Lasse,

A bit offtopic, but it is a very interesting  question  - what is the background of the situation?

I guess you did not reach 10K users in 1 year, since it is 2.5, probably production started around 2010-2015?

How was your journey with Firebird? When did you notice problems - with 100, 1000, 5000 users, with 100, 500, 1000 Gb?

Did you consider migrating to Firebird 4 and/or optimize it with professional help from one of https://firebirdsql.org/en/support/ companies instead of rewriting SQLs to another platform?

If you can show some stats from the production database, like fb_lock_print -c -d databasenamepath.fdb and gstat -h databasepathname.fdb, it will be also useful to understand the scale of the problem .

Regards,

Alexey Kovyazin

President Of Firebird Foundation

чт, 12 окт. 2023 г. в 16:29, 'Lasse Hansen' via firebird-support <firebird...@googlegroups.com>:

--
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 on the web, visit https://groups.google.com/d/msgid/firebird-support/03941843-27d2-4d23-979d-8dfc64e2d972n%40googlegroups.com.

[Lasse Hansen]

Hi

The project initially started in 1997 and has had continuous build up of functionality and users ever since. The product has to do with winter services (salt spreading, snow clearing) so it is very seasonal and weather dependent how many users we have online. We were not the initial developers of this so I cannot speak too much of the period from 1997-2020 where we took over. Especially in the last couple of years we have seen an influx of users and problems have started to arise on nights with a lot of snow/ice in the country. The main application is written in Delphi and we have slowly been rewriting the logic in .Net. Our plan is that this winter season will be the last with firebird and the Delphi client but we still want it to not be unusable under heavy load.

Here are the stats you asked for (after running my primitive load test)

LOCK_HEADER BLOCK
        Version: 145, Active owner:      0, Length: 4194304, Used: 3693920
        Flags: 0x0001
        Enqs: 562048, Converts:   9889, Rejects:   2901, Blocks:      7
        Deadlock scans:      0, Deadlocks:      0, Scan interval:  10
        Acquires: 862964, Acquire blocks:   1868, Spin count:   0
        Mutex wait: 0.2%
        Hash slots: 1009, Hash lengths (min/avg/max):    0/   0/   3
        Remove node:      0, Insert queue:      0, Insert prior:      0
        Owners (1):     forward:  20824, backward:  20824
        Free owners (503):      forward: 2013832, backward: 1396680
        Free locks (1833):      forward:  22536, backward: 3386192
        Free requests (54295):  forward: 2251560, backward: 3223056

        Lock Ordering: Enabled

Database header page information:
        Flags                   0
        Checksum                12345
        Generation              18683438
        Page size               16384
        ODS version             11.2
        Oldest transaction      19671619
        Oldest active           20786975
        Oldest snapshot         20786975
        Next transaction        20792998
        Bumped transaction      1
        Sequence number         0
        Next attachment ID      3858407
        Implementation ID       26
        Shadow count            0
        Page buffers            150
        Next header page        0
        Database dialect        1
        Creation date           Jun 20, 2023 20:03:43
        Attributes              multi-user maintenance

    Variable header data:
        Sweep interval:         100000
        *END*

[Mark Rotteveel]

On 13-10-2023 09:28, 'Lasse Hansen' via firebird-support wrote:
> Here are the stats you asked for (after running my primitive load test)
>
> LOCK_HEADER BLOCK

[..]

>         Hash slots: 1009, Hash lengths (min/avg/max):    0/   0/   3

[..]

The lock stats are more interesting when it is slow, not after you've
run the test. However, it seems you're using the default number of hash
slots. You may want to set a higher number (setting LockHashSlots in
firebird.conf).

>
> Database header page information:
[..]
>         Attributes              multi-user maintenance

Why is your database in multi-user maintenance mode? That is not the
normal operation mode.

Mark
--
Mark Rotteveel

[Alexey Kovyazin]

Hello Lasse,

Thanks for the explanation.

We see such stories very frequently - original team abandoned project, default configuration for high load project, wrong maintenance (sweep interval, OIT frozen, multi-user maintenance, page buffers in header, etc), old version. It is not a surprise that performance is not as good as expected.

I believe, at Firebird Project level we should start a campaign to explain the minimum necessary configuration adjustments and minimum maintenance approach, and also promote Firebird migration to the recent versions.

Regards,

Alexey Kovyazin

President of Firebird Foundation

пт, 13 окт. 2023 г. в 09:28, 'Lasse Hansen' via firebird-support <firebird...@googlegroups.com>:

To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/747dbce1-effb-4ab9-a807-0dcb580d1781n%40googlegroups.com.

[Dimitry Sibiryakov]

'Mark Rotteveel' via firebird-support wrote 13.10.2023 9:39:
>>          Hash slots: 1009, Hash lengths (min/avg/max):    0/   0/   3
> [..]
>
> The lock stats are more interesting when it is slow, not after you've run the
> test. However, it seems you're using the default number of hash slots. You may
> want to set a higher number (setting LockHashSlots in firebird.conf).

Why? It has perfect lengths. In superserver mode lock table is not used as
active as in classic.

--
WBR, SD.

[Dimitry Sibiryakov]

'Lasse Hansen' via firebird-support wrote 13.10.2023 9:28:
>         Sweep interval:         100000

I wonder if you had a reason for this setting or just mindlessly use
inherited one?

--
WBR, SD.

[Mark Rotteveel]

The OP is using SuperClassic, and the lock print was done *after* a load
test, not during a load test.

Mark
--
Mark Rotteveel

[Lasse Hansen]

Thanks for all the replies.

I will try and respond to everyone:

1. I will try to adjust the LockHashSlots and see if that has an effect.
2. If " multi-user maintenance " is not the normal operation mode, what is and how would I change that (is that in the config file)?

3. Sweep interval:         100000  - I am pretty sure this was mindlessly set. My understanding is that lowering this might make new transactions faster? What are the risks associated with this?

Here is the lock print during load.
LOCK_HEADER BLOCK
        Version: 145, Active owner:      0, Length: 4194304, Used: 3710056
        Flags: 0x0001
        Enqs: 2039660, Converts:  10421, Rejects:   3037, Blocks:     11

        Deadlock scans:      0, Deadlocks:      0, Scan interval:  10

        Acquires: 2306192, Acquire blocks:   1627, Spin count:   0
        Mutex wait: 0.1%
        Hash slots: 1009, Hash lengths (min/avg/max):    0/   1/   5

        Remove node:      0, Insert queue:      0, Insert prior:      0

        Owners (501):   forward:  25152, backward: 1454048
        Free owners (4):        forward: 3705328, backward: 3199856
        Free locks (342):       forward:  22536, backward:  46520
        Free requests (672):    forward: 1707312, backward: 3126112
        Lock Ordering: Enabled

[Tomasz Dubiel]

The most recommended way is to set it to 0 and do manual sweep at night (or when the smallest load).

Best regards,

Tomasz.

[Dimitry Sibiryakov]

'Lasse Hansen' via firebird-support wrote 13.10.2023 12:23:
> 1. I will try to adjust the LockHashSlots and see if that has an effect.

Trial-and-error is a bad strategy. You must localize problem first.

> 2. If " multi-user maintenance " is not the normal operation mode, what is and
> how would I change that (is that in the config file)?

https://firebirdsql.org/file/documentation/html/en/firebirddocs/gfix/firebird-gfix.html#gfix-dbmode

> 3. Sweep interval:         100000  - I am pretty sure this was mindlessly set.
> My understanding is that lowering this might make new transactions faster? What
> are the risks associated with this?

Changes settings without understanding what they do has an obvious risk: bad
performance.
You must watch transaction dynamic and sweep activity to make an informed
decision about sweep interval. It is not what you can think judging by name.

--
WBR, SD.

[Dimitry Sibiryakov]

Tomasz Dubiel wrote 13.10.2023 12:28:
> The most recommended way is to set it to 0 and do manual sweep at night (or when
> the smallest load).

Yes, this is rule for dumb. It is good for low load databases but can blow up
high load one.

--
WBR, SD.

[Lasse Hansen]

" Trial-and-error is a bad strategy. You must localize problem first."

I understand but change one thing at a time and running my relatively simple test could give some initial pointers to where the problem might be.

" Changes settings without understanding what they do has an obvious risk: bad
performance.
You must watch transaction dynamic and sweep activity to make an informed

decision about sweep interval. It is not what you can think judging by name."

I hear your concern, other than the simple load test we also have a more broad load test which simulates an actual load with calls through the individual server applications. It is not my intention to not double/triple check everything and work my way backwards if I see some optimizations.

[Dimitry Sibiryakov]

'Lasse Hansen' via firebird-support wrote 13.10.2023 12:37:
> I understand but change one thing at a time and running my relatively simple
> test could give some initial pointers to where the problem might be.

Your "lock print during load" also shows a perfect distribution and no need
to adjust hash table size. Wait with it while "average hash length" raise to 5
at least.

--
WBR, SD.

[Tomasz Dubiel]

What is the downside of it? Let's say one user starts transaction and holds it for a long time. It's not a usual occuring, but it happens. The difference between OST and OIT is larger than some sweep interval value. Sweep will then run automatically, will be unable to do the job, difference is still larger than sweep interval. Is it not harmful for a database under high load? When sweep interval is set and the first sweep fails, when do Firebird repeat this action?

Best regards,

Tomasz.

[Mark Rotteveel]

On 13-10-2023 12:23, 'Lasse Hansen' via firebird-support wrote:
> 2. If " multi-user maintenance " is not the normal operation mode, what
> is and how would I change that (is that in the config file)?

You need to use:
gfix -user SYSDBA -password <password> -online <database>

Mark
--
Mark Rotteveel

[Dimitry Sibiryakov]

Tomasz Dubiel wrote 13.10.2023 12:44:
> Let's say one user starts transaction and holds it for a long time. It's not a
> usual occuring, but it happens. The difference between OST and OIT is larger
> than some sweep interval value. Sweep will then run automatically, will be
> unable to do the job, difference is still larger than sweep interval.

Nope. If a living application just holds a transaction for long time the
interval won't grow and autosweep don't start. A transaction must die horribly
for the interval to start growing which should never happen during normal
database load.

> Is it not harmful for a database under high load?

It is but turning off autosweep don't solve the problem, it make it worse:
even occasional dead transaction may cause snowball of garbage and high tensions
on its collection (which is the real reason for the performance degradation
before FB 5). That's why decreasing sweep interval can actually help in this
case: counters will be normalized before a little problem grows to a complete
disaster.

> When sweep interval is set and the first sweep fails, when do Firebird repeat this action?

Sweep never fails. If it is unable to move the counters - it is a bad symptom
that something is really wrong. But yes, it will be started again at the next
transaction start.

--
WBR, SD.

[Tomasz Dubiel]

" Nope. If a living application just holds a transaction for long time the
interval won't grow and autosweep don't start. A transaction must die horribly
for the interval to start growing which should never happen during normal

database load. "

that's quite a normal situation for us. From time to time some user will start transaction and won't close it. The longer it's opened, NT-OAT grows. We get notification about this difference, we kill the attachment - then we get big difference between OST and OIT. Then auto sweep would immediately run.

In those situation, if we know that server is powerful/there is no big load, we run manual sweep, but we want not to run it otherwise.

Best regards,

Tomasz.

[Dimitry Sibiryakov]

Tomasz Dubiel wrote 13.10.2023 13:39:
> that's quite a normal situation for us. From time to time some user will start
> transaction and won't close it. The longer it's opened, NT-OAT grows. We get
> notification about this difference, we kill the attachment - then we get big
> difference between OST and OIT.

People are different. What is normal for one can be considered crazy for the
others and vice versa.
I can only say that you would better not to kill the connection but ask the
application to shutdown transactions properly.

--
WBR, SD.

[Tomasz Dubiel]

In our system it is implemented. There is a time limit for a transaction, but we don't ban using any other software, using any own created solutions, integrations, etc. We have to handle it as well to maintain system performance.

Best regards,

Tomasz.

[Dimitry Sibiryakov]

Tomasz Dubiel wrote 13.10.2023 13:54:
> In our system it is implemented. There is a time limit for a transaction, but we
> don't ban using any other software, using any own created solutions,
> integrations, etc. We have to handle it as well to maintain system performance.

If I were you I would provide a separate database mirror for such
unpredictable third-party connections. That can keep main database performance safe.

--
WBR, SD.