Performance issue with FB50-beta-1

[Attila Molnár]

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.

[Vlad Khorsun]

Interesting, thanks. Could you specify what CPU and OS was used when testing ?

Regards,
Vlad

[Attila Molnár]

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]

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]

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/> to find it....

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]

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]

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//
> //

> 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.
>  

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]

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>

A firebir...@googlegroups.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.

To view this discussion on the web visit https://groups.google.com/d/msgid/firebird-devel/90fdb53f-7c75-d3b1-18ab-4598dfe062b2%40gmail.com.

[Attila Molnár]

Hi Vlad!

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

[Adriano dos Santos Fernandes]

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]

>> 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.

To view this discussion on the web visit https://groups.google.com/d/msgid/firebird-devel/c4c6f512-54f7-627e-f4b1-c2362806a959%40gmail.com.

 

[P-Soft - Fabio Codebue]

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>

A firebir...@googlegroups.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.

To view this discussion on the web visit https://groups.google.com/d/msgid/firebird-devel/c4c6f512-54f7-627e-f4b1-c2362806a959%40gmail.com.

[Adriano dos Santos Fernandes]

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]

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]

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>

A firebir...@googlegroups.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.

To view this discussion on the web visit https://groups.google.com/d/msgid/firebird-devel/92035c04-ca66-7658-fd4f-6690b4504eec%40gmail.com.

[Karol Bieniaszewski]

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

 

Od: Adriano dos Santos Fernandes

--

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.

To view this discussion on the web visit https://groups.google.com/d/msgid/firebird-devel/92035c04-ca66-7658-fd4f-6690b4504eec%40gmail.com.

 

[Attila Molnár]

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.