Performance issue with FB50-beta-1

196 views
Skip to first unread message

Attila Molnár

unread,
Mar 29, 2023, 4:43:24 AM3/29/23
to firebird-devel
Hi *!

Out test program showed major performance degredation in FB50-beta-1.
The logs are attached.

"Operations time" : this is the time of the SQL commands for prepare, run and checks. These are nearly the same, no problem there.

The big differences are on this places :
- rollback to savepoint: "Cleanup time"
- rollback at the end of each test case: "AbstractConnectionWrapper.rollback"
- reset connection context: "DBTestLibraFirebirdConfigurator.sql" (see in attached zip) and "DBTestLibraFirebirdConfigurator. commit" (commit on the context reset)



Synthetic test case based on our observations and analysis. The issue might be with context handling, or savepoint/transaction handling.

EXECUTE BLOCK
AS
DECLARE VARIABLE i INTEGER = 0;
DECLARE VARIABLE x INTEGER;
BEGIN
  WHILE (i < 1000 * 100)
  DO BEGIN
    IN AUTONOMOUS TRANSACTION
    DO BEGIN
      x = (SELECT rdb$set_context('USER_SESSION', 'USER_NAME', 'XXX') FROM rdb$database);
      x = (SELECT rdb$set_context('USER_SESSION', 'PASSWORD_USER', 'xxx') FROM rdb$database);
      x = (SELECT rdb$set_context('USER_SESSION', 'DUMMY', 'dummy') FROM rdb$database);
    END
    IN AUTONOMOUS TRANSACTION
    DO BEGIN
      FOR
          SELECT rdb$set_context('USER_SESSION', v.mon$variable_name, NULL)
          FROM mon$context_variables v
          WHERE v.mon$attachment_id = CURRENT_CONNECTION AND
              v.mon$variable_name NOT IN ('USER_NAME', 'PASSWORD_USER')
          UNION ALL
          SELECT rdb$set_context('USER_SESSION', 'USER_NAME', 'XXX')
          FROM rdb$database
          UNION ALL
          SELECT rdb$set_context('USER_SESSION', 'PASSWORD_USER', 'xxx')
          FROM rdb$database
      INTO x
      DO BEGIN
      END
    END
    i = i + 1;
  END
END

FB25 (fbserver.exe); Execute time = 32s 32ms ( rollback or commit at the end ~ 94 ms)
FB30; Execute time = 1m 53s 344ms (rollback or commit at the end ~ 93 ms)
FB40; Execute time = 2m 6s 141ms (rollback or commit at the end ~ 188 ms)
FB50; Execute time = 2m 42s 62ms (rollback or commit at the end ~ 4406 ms)


The jump between 25 and 30 is huge, but might be because of the scalability refactor.

Also there is a big jump between 40 and 50. Note that here not just the execute time jumped, but the rollback/commit time also.
test_statistics.zip

Vlad Khorsun

unread,
Mar 30, 2023, 4:48:19 AM3/30/23
to firebir...@googlegroups.com
Interesting, thanks. Could you specify what CPU and OS was used when testing ?

Regards,
Vlad

Attila Molnár

unread,
Mar 30, 2023, 5:49:45 AM3/30/23
to firebird-devel
Hi Vlad!

OS : Windows 10 Enterprise, CPU: Virtual Machine (Intel Xeon E3-12xx v2 (Ivy Bridge, IBRS)   2.50 GHz reported by Windows)

Vlad Khorsun

unread,
Mar 30, 2023, 3:49:49 PM3/30/23
to firebir...@googlegroups.com
30.03.2023 12:49, Attila Molnár wrote:
> Hi Vlad!
>
> OS : Windows 10 Enterprise, CPU: Virtual Machine (Intel Xeon E3-12xx v2 (Ivy Bridge, IBRS)   2.50 GHz reported by Windows)

Thanks, good to know it is not related with Efficient\Performance cores.

Next questions:
- what connection run this query - one that creates database ?
- what kind of storage was used - HDD, SSD, NAS, etc ?
- if FW settings and page size was equal for every test database ?

I run your test, but changed it a bit
- my script creates test database and reconnects to it immediately:
CREATE DATABASE ...;
COMMIT;
CONNECT ;

this is necessary to make fair comparison. Firebird creates database with FW=ON, this is well
known. Before Firebird 3 there was hack used to make database creation (and restore) fast -
header page contains FW=ON flag but actual flags passed into CreateFile did not included
FILE_FLAG_WRITE_THROUGH. Thus connect that created database works with FW=OFF, while next
connections works with FW=ON, as expected. In Firebird 3 this hack was removed and now
engine creates and init new database in FW=OFF mode and switched to the FW=ON (if FW=OFF was
not set explicitly in DPB) before return from createDatabase().

Also, I set affinity for firebird process to exclude Efficient cores (my CPU is Intel i7-12700
with 8P and 4E cores), else Firebird could run very slow. SS in Firebird 2.5 binds to the
1st core by default and it is Performance core, so there is no need to change its affinity.

Finally, I changed number of iterations from 100K to 10K to not fall into sleep while
running tests.

Results of my testing (Firebird version, exec time, commit time):

2.5.9 3.094 0.004
3.0.11 5.956 0.003
4.0.3 8.534 0.004
5.0.0 6.314 0.008

Next, I removed part of query with access of monitoring tables, and here is results:

2.5.9 2.849 0.001
3.0.11 2.829 0.000
4.0.3 3.004 0.000
5.0.0 3.011 0.000

I.e. access of monitoring tables takes the most part of perf difference between versions.
Note, number of monitoring tables and, correspondingly, snapshot size is increased in every
Firebird version. Also, FB4+ have 4 times wider string metadata fields.

Regards,
Vlad

P-Soft - Fabio Codebue

unread,
Mar 31, 2023, 3:42:51 AM3/31/23
to firebir...@googlegroups.com
reading Release notes of Firebird 5.0.1 beta I found

Create Android packages with all necessary files in all architectures (x86, x64, arm32, arm64) /
Tracker reference: #7293/
but nothing was found about Android.
 
And about IOS, I suppose it' time to pay someone to mantain it


Fabio Codebue


P-SOFT di Codebue Fabio

Via Nuova n. 9 - 24060 Tavernola B.sca (BG)

P.I. 03624950162

C.F. CDBFBA72A11C618T

Mobile: +39.348.3515786

Fax: +39.030.5100306

Web: http://www.p-soft.biz

 

pec: amminis...@pec.p-soft.biz
cod.intermediario SDI: KRRH6B9
 


Mark Rotteveel

unread,
Mar 31, 2023, 3:46:02 AM3/31/23
to firebir...@googlegroups.com
Fabio,

Despite my instructions in reply to your post on firebird-support, you
posted this in an existing thread as well, the thread "Performance issue
with FB50-beta-1".

In the future, please start a *new* thread by writing a new message
instead of replying to an existing message (in the web UI it is the "+
New conversation" button in the top-left, using email, create a new
email and send it to the group address).

Mark -- moderator firebird-devel


On 31-03-2023 09:40, 'P-Soft - Fabio Codebue' via firebird-devel wrote:
> reading Release notes of Firebird 5.0.1 beta I found
>
> /Create Android packages with all necessary files in all architectures
> (x86, x64, arm32, arm64) //
> /Tracker reference: #7293//
> //
> so I go to https://firebirdsql.org/en/firebird-5-0-0-beta1/
> <https://firebirdsql.org/en/firebird-5-0-0-beta1/>
> <https://firebirdsql.org/en/firebird-5-0-0-beta1/
> <https://firebirdsql.org/en/firebird-5-0-0-beta1/>> to find it....
> but nothing was found about Android.
> And about IOS, I suppose it' time to pay someone to mantain it

--
Mark Rotteveel

Adriano dos Santos Fernandes

unread,
Mar 31, 2023, 5:49:13 AM3/31/23
to firebir...@googlegroups.com
On 31/03/2023 04:40, 'P-Soft - Fabio Codebue' via firebird-devel wrote:
> reading Release notes of Firebird 5.0.1 beta I found
>
> /Create Android packages with all necessary files in all architectures
> (x86, x64, arm32, arm64) //
> /Tracker reference: #7293//
> //
The beta.1 release was not done with the automation workflow that build
these archs.

That's being adjusted and will be used in the next beta.

Once that is finished, we may release the Android beta.1 manually with
some delay.


Adriano

P-Soft - Fabio Codebue

unread,
Mar 31, 2023, 6:19:40 AM3/31/23
to firebir...@googlegroups.com
Do you ever discuss about IOS version?

Is there someone could mantain it?

I think it's really important to follow this way too

Fabio Codebue


P-SOFT di Codebue Fabio

Via Nuova n. 9 - 24060 Tavernola B.sca (BG)

P.I. 03624950162

C.F. CDBFBA72A11C618T

Mobile: +39.348.3515786

Fax: +39.030.5100306

Web: http://www.p-soft.biz

 

pec: amminis...@pec.p-soft.biz
cod.intermediario SDI: KRRH6B9
 



------ Messaggio originale ------
Da "Adriano dos Santos Fernandes" <adri...@gmail.com>
Data 31/03/2023 11:49:07
Oggetto Re: [firebird-devel] FB50-beta-1 - mobile versions

--
You received this message because you are subscribed to the Google Groups "firebird-devel" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-deve...@googlegroups.com.

Attila Molnár

unread,
Mar 31, 2023, 6:20:55 AM3/31/23
to firebird-devel
Hi Vlad!

Sent you an email with details to recreate the test environment.

Adriano dos Santos Fernandes

unread,
Mar 31, 2023, 6:51:38 AM3/31/23
to firebir...@googlegroups.com
On 31/03/2023 07:19, 'P-Soft - Fabio Codebue' via firebird-devel wrote:
> Do you ever discuss about IOS version?
>
> Is there someone could mantain it?
>

I never see any user asking for it.

I don't think Firebird on Android is something really great from user
application's system architecture.

I see it as something mostly for internal apps of a company.

I doubt most companies do use iPhone for such things...


Adriano

Karol Bieniaszewski

unread,
Mar 31, 2023, 8:23:55 AM3/31/23
to firebir...@googlegroups.com

>> I don't think Firebird on Android is something really great from user application's system architecture.

 

Why? We use it a lot on Android.

 

>> I doubt most companies do use iPhone for such things...

 

It is really depend on the country, we mostly uses Samsung and Android and near never IPhone

 

Regards,

Karol Bieniaszewski

--

You received this message because you are subscribed to the Google Groups "firebird-devel" group.

To unsubscribe from this group and stop receiving emails from it, send an email to firebird-deve...@googlegroups.com.

P-Soft - Fabio Codebue

unread,
Mar 31, 2023, 5:13:49 PM3/31/23
to firebir...@googlegroups.com
Really no user ask for it?
European company market could be very large if we have a really mobile database.
Now We have only SQLite and Interbase (which really is very expensive)

Infact there are lots of applications for retail, sales that could use firebird on mobile....
and I know sales man in Europe...prefer iPAD


Fabio Codebue


P-SOFT di Codebue Fabio

Via Nuova n. 9 - 24060 Tavernola B.sca (BG)

P.I. 03624950162

C.F. CDBFBA72A11C618T

Mobile: +39.348.3515786

Fax: +39.030.5100306

Web: http://www.p-soft.biz

 

pec: amminis...@pec.p-soft.biz
cod.intermediario SDI: KRRH6B9
 



------ Messaggio originale ------
Da "Adriano dos Santos Fernandes" <adri...@gmail.com>
Data 31/03/2023 12:51:33
Oggetto Re: [firebird-devel] FB50-beta-1 - mobile versions
--
You received this message because you are subscribed to the Google Groups "firebird-devel" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-deve...@googlegroups.com.

Adriano dos Santos Fernandes

unread,
Apr 1, 2023, 7:23:27 AM4/1/23
to firebir...@googlegroups.com
On 31/03/2023 09:23, Karol Bieniaszewski wrote:
>>> I don't think Firebird on Android is something really great from user
> application's system architecture.
>
>  
>
> Why? We use it a lot on Android.
>

Could you share how you use it, how applications are made, for what, who
use them, size of installed applications, how they are installed?


Adriano

Paul Reeves

unread,
Apr 1, 2023, 1:51:57 PM4/1/23
to firebir...@googlegroups.com
I think it depends on the their geographic location. While Android dominates
the market at a global level there are many countries where the iPhone is
dominant. These countries are typically OECD members so the value of the
iPhone market will also be significant.

So I suspect where the iPhone has large market share that companies will
create internal apps for it.


Paul
--

Paul Reeves
http://www.ibphoenix.com
Supporting users of Firebird

P-Soft - Fabio Codebue

unread,
Apr 1, 2023, 1:56:54 PM4/1/23
to firebir...@googlegroups.com
Adriano

I have a lot of Android application on store and deployed only via apk for my customers.
I have to use SQLite because there's no a standard streaming affordable.
For some of them I try to use Firebird using https://www.npmjs.com/package/node-firebird but it's really difficult without samples, docs and support
Usually I use DB into mobile applications for mange data in industrial company where Internet connection it's not available or not permitted ,so usually we prefer manage import / export from server to app.

For desktop I have no problem with NodeJS driver (I manage DB with receive something like 6 record of datas every second) and DB are 4 GB

If we will have a stable and well documented Android and IOS versions we can use it into our applications




Fabio Codebue


P-SOFT di Codebue Fabio

Via Nuova n. 9 - 24060 Tavernola B.sca (BG)

P.I. 03624950162

C.F. CDBFBA72A11C618T

Mobile: +39.348.3515786

Fax: +39.030.5100306

Web: http://www.p-soft.biz

 

pec: amminis...@pec.p-soft.biz
cod.intermediario SDI: KRRH6B9
 



------ Messaggio originale ------
Da "Adriano dos Santos Fernandes" <adri...@gmail.com>
Data 01/04/2023 13:23:21
Oggetto Re: ODP: [firebird-devel] FB50-beta-1 - mobile versions

--
You received this message because you are subscribed to the Google Groups "firebird-devel" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-deve...@googlegroups.com.

Karol Bieniaszewski

unread,
Apr 2, 2023, 4:57:57 PM4/2/23
to firebir...@googlegroups.com

Hi Adriano

 

We use FB Android release fixed by Alex Peshkoff

https://github.com/FirebirdSQL/firebird/issues/6146

 

We use Delphi + Firedac to build the apk file which is installed on the devices.

Application is for internal use of the company, around 400 users.

 

Regards,

Karol Bieniaszewski

 

--

You received this message because you are subscribed to the Google Groups "firebird-devel" group.

To unsubscribe from this group and stop receiving emails from it, send an email to firebird-deve...@googlegroups.com.

Attila Molnár

unread,
Apr 4, 2023, 8:29:58 AM4/4/23
to firebird-devel
Vlad's investigation result:

"The problem I found is in the following query:


SELECT rdb$set_context('USER_SESSION', v.mon$variable_name, NULL)
  FROM mon$context_variables v
 WHERE v.mon$attachment_id = CURRENT_CONNECTION
   AND v.mon$variable_name NOT IN ('USER_NAME', 'PASSWORD_USER')
UNION ALL
SELECT rdb$set_context('USER_SESSION', 'USER_NAME', 'xxx') FROM rdb$database

UNION ALL
SELECT rdb$set_context('USER_SESSION', 'PASSWORD_USER', 'xxx') FROM rdb$database

This query executed many times. On fb5 it produced some writes, which I found very weird - this is SELECT, why writes ? Then I found that writes happens because of some temporary blobs, produced while executing query, have size more than 1 database page (16KB). Note, engine keeps in memory part of blob up to the page size, then it put data on database pages. Of course this involves allocating pages and, when transaction ends, deallocating pages. The blobs source are monitoring tables and such "big" blobs are from MON$COMPILED_STATEMENTS, column MON$EXPLAINED_PLAN. This is new table, introduced in fb5, therefore fb3 and fb4 is not affected.

You may check this by running tests with fb5 using database in ODS13 (i.e. created with fb4) - this ODS have no MON$COMPILED_STATEMENTS table. Or run tests without this query, if possible.

I'm trying to find a way to lower perf. penalty but not sure it is possible.

May I ask - why you need this query and why is it executed so often ? If you need to
re-initialize user context variables, we can implement direct way to do it, for example
with new RDB$RESET_CONTEXT(namespace) system function, what do you think ?

Regards,
Vlad"



Out workaround:
In case of FB4+ we reset the context variables with ALTER SESSION RESET, and the first part of the UNION ALL can be left out. After this FB50-beta-1 has similar performance as 40 and 30.
Reply all
Reply to author
Forward
0 new messages