Prepare of procedure with 1k dependencies

[synaschwabe]

Hi,

we recently upgrade our database from Firebird 2.5 to Firebird 4.0.3 (running on Windows Server 2k22 or Windows 11). Now some people are complaining that the startup of our application takes way longer then before and they are right.

First of all about the database (downloads.webaverp.com/tmp/2023.01.FB4.zip) 3k tables 11k procedures 9k triggers It was created by backup from 2.5 and restore in 4.0.3. Recompiled all triggers and procedures.

Connecting to the database and executing a procedure (directly after connection) will take up to 10 seconds. Second execution is done within milliseconds no matter if I commit or rollback the first execution of the procedure. We are using IBExpert as IDE and I checked their SQL monitor: [07.09.2023 15:46:01] - [Starting transaction] Transaction 51335 started [07.09.2023 15:46:01] - [Prepare] select from P_BMAND_CHECK [07.09.2023 15:46:08] - [Execute] select from P_BMAND_CHECK [07.09.2023 15:46:08] - [Fetch] BMAND_ID = 1

There is a delay of 7 second between prepare and execute.

Using the same database in Firebird 2.5.9 (same structure) it looks like this: [07.09.2023 15:50:30] - [Starting transaction] Transaction 51344 started [07.09.2023 15:50:30] - [Prepare] select from P_BMAND_CHECK [07.09.2023 15:50:30] - [Execute] select from P_BMAND_CHECK [07.09.2023 15:50:30] - [Fetch] BMAND_ID = 1

There is no delay between prepare and execute.

Comparing to the same database under FB2.5.9 on first execution after connect: [07.09.2023 15:56:32] - [Starting transaction] Transaction 76236 started [07.09.2023 15:56:32] - [Prepare] select from P_BMAND_CHECK [07.09.2023 15:56:32] - [Execute] select from P_BMAND_CHECK [07.09.2023 15:56:32] - [Fetch] BMAND_ID = 1

There is no delay between prepare and execute.

I am awre of that Firebird is loading dependency tree on startup or first execution of procedures but the performance got worse on that.

BTW: The procedure P_BMAND_CHECK has dependencies to 940 procedures and 250 triggers.

Thanks for you help.

Kind regard André Schwabe

[AlexPeshkoff]

On 9/7/23 17:05, synaschwabe wrote:

Hi,

we recently upgrade our database from Firebird 2.5 to Firebird 4.0.3 (running on Windows Server 2k22 or Windows 11). Now some people are complaining that the startup of our application takes way longer then before and they are right.

First of all about the database (downloads.webaverp.com/tmp/2023.01.FB4.zip) 3k tables 11k procedures 9k triggers It was created by backup from 2.5 and restore in 4.0.3. Recompiled all triggers and procedures.

Connecting to the database and executing a procedure (directly after connection) will take up to 10 seconds. Second execution is done within milliseconds no matter if I commit or rollback the first execution of the procedure. We are using IBExpert as IDE and I checked their SQL monitor: [07.09.2023 15:46:01] - [Starting transaction] Transaction 51335 started [07.09.2023 15:46:01] - [Prepare] select from P_BMAND_CHECK [07.09.2023 15:46:08] - [Execute] select from P_BMAND_CHECK [07.09.2023 15:46:08] - [Fetch] BMAND_ID = 1

There is a delay of 7 second between prepare and execute.

Using the same database in Firebird 2.5.9 (same structure) it looks like this: [07.09.2023 15:50:30] - [Starting transaction] Transaction 51344 started [07.09.2023 15:50:30] - [Prepare] select from P_BMAND_CHECK [07.09.2023 15:50:30] - [Execute] select from P_BMAND_CHECK [07.09.2023 15:50:30] - [Fetch] BMAND_ID = 1

There is no delay between prepare and execute.

Comparing to the same database under FB2.5.9 on first execution after connect: [07.09.2023 15:56:32] - [Starting transaction] Transaction 76236 started [07.09.2023 15:56:32] - [Prepare] select from P_BMAND_CHECK [07.09.2023 15:56:32] - [Execute] select from P_BMAND_CHECK [07.09.2023 15:56:32] - [Fetch] BMAND_ID = 1

There is no delay between prepare and execute.

What server mode are you using in 2.5 and 4.0 (i.e. Super/SuperClassic/Classic)?

[synaschwabe]

Superserver in 4.0.3 and in 2.5.

[AlexPeshkoff]

On 9/8/23 08:17, synaschwabe wrote:

Superserver in 4.0.3 and in 2.5.

That's known issue. We work on it but that work is in progress. We did not receive too serious complaints before.

BTW, can you check to make sure that this is really effect I think about

• if you restart 2.5 server and after it attach/prepare/execute first time, do you have a delay?

[hvlad]

Could you provide also original DB in ODS 11.x (fb25) ?

[synaschwabe]

If it is ok for you to have a slightly different database in ODS 11, then you can download it from downloads.webaverp.com/release/averp2023.01.zip. Otherwise I can provide that version on Friday.

[hvlad]

Thanks, downloaded. To make correct comparison, I've made backup of this db by fb2.5 and restore by 5.0.

First execution of query by 2.5:

SQL> select * from P_BMAND_CHECK;

    BMAND_ID
============
           1

Current memory = 521609672
Delta memory = 484137760
Max memory = 521695688
Elapsed time = 4.449 sec
Buffers = 2048
Reads = 21731
Writes = 450
Fetches = 1225819

First execution of query by 5.0:

SQL> select * from P_BMAND_CHECK;

    BMAND_ID
============
           1

Current memory = 507939072
Delta memory = 466396816
Max memory = 508113776
Elapsed time = 4.834 sec
Buffers = 2048
Reads = 24312
Writes = 866
Fetches = 2732759

You see, there is a small difference of elapsed time.

First execution of the query above (actually preparing) is slow due to the loading, parsing and security checking of the big number of metadata objects.

Note, SS architecture up to v2.5 had have a shared metadata cache, while in v3+ metadata cache is per-connection. Shared metadata cache for SS is currently under development.

[synaschwabe]

Thanks for that information. But now you have a good example to improve loading metadata objects on thread start ;) Are there any plans to backport this from any v5 developments into v4? Or is development just planned and not in progress?

I'm not sure if I should open a new ticket for this and if it is related to private metadata cache: (Please don't blame me for the next words) In some cases the database engine crashes if metadata ist changed. It happens on changing a trigger or stp having lots of active connections (100+). The error is Fatal lock manager error: invalid lock id (nnnnn) errno: 0 Is it because server trys to synchronize metadata changes to private metadata cache?

[sim1984]

A shared metadata cache is a work in progress, but will not be included in Firebird 5.0. Firebird 5.0 is close to RC stage, but the shared metadata cache is far from complete. In any case, such major changes are not backported to minor updates.

[synaschwabe]

thanks for information on question 1. So this private metadata cache will (maybe only in my case) increase server requirements regarding memory on superserver in v4. We use the database in environments of 100+ simultanous connections and have to calculate with 600 to 800 MB per connection?

Can somebody give a statement on question 2. Or should I create anew issue on that?

thanks.

[hvlad]

In some cases the database engine crashes if metadata ist changed. It happens on changing a trigger or stp having lots of active connections (100+). The error is Fatal lock manager error: invalid lock id (nnnnn) errno: 0

Reproducible test case helps to fix this.

Is it because server trys to synchronize metadata changes to private metadata cache?

It is impossible to guess without test case, sorry.

[hvlad]

So this private metadata cache will (maybe only in my case) increase server requirements regarding memory on superserver in v4.

Yes

We use the database in environments of 100+ simultanous connections and have to calculate with 600 to 800 MB per connection?

It looks so, but better try yourself - maybe with less number of connections, just to evaluate.

[a848300]

مرحبا

قمنا مؤخرا بترقية قاعدة البيانات الخاصة بنا من Firebird 2.5 إلى Firebird 4.0.3 (تعمل على Windows Server 2k22 أو Windows 11). الآن يشكو بعض الناس من أن بدء تشغيل تطبيقنا يستغرق وقتا أطول من ذي قبل وهم على حق.

بادئ ذي بدء ، حول قاعدة البيانات (downloads.webaverp.com/tmp/2023.01.FB4.zip) 3k الجداول 11k الإجراءات 9k المشغلات تم إنشاؤه عن طريق النسخ الاحتياطي من 2.5 والاستعادة في 4.0.3. إعادة ترجمة جميع المشغلات والإجراءات.

سيستغرق الاتصال بقاعدة البيانات وتنفيذ إجراء (مباشرة بعد الاتصال) ما يصل إلى 10 ثوان. يتم التنفيذ الثاني في غضون أجزاء من الثانية بغض النظر عما إذا كنت قد ارتكبت أو تراجعت عن التنفيذ الأول للإجراء. نحن نستخدم IBExpert ك IDE وراجعت شاشة SQL الخاصة بهم: [07.09.2023 15:46:01] - [بدء المعاملة] بدأت المعاملة 51335 [07.09.2023 15:46:01] - [إعداد] حدد من P_BMAND_CHECK [07.09.2023 15:46:08] - [تنفيذ] حدد من P_BMAND_CHECK [07.09.2023 15:46:**08**] - [جلب] BMAND_ID = 1

هناك تأخير لمدة 7 ثوان بين الإعداد والتنفيذ.

باستخدام نفس قاعدة البيانات في Firebird 2.5.9 (نفس الهيكل) يبدو كما يلي: [07.09.2023 15:50:30] - [بدء المعاملة] بدأت المعاملة 51344 [07.09.2023 15:50:30] - [إعداد] حدد من P_BMAND_CHECK [07.09.2023 15:50:30] - [تنفيذ] حدد من P_BMAND_CHECK [07.09.2023 15:50:**30**] - [جلب] BMAND_ID = 1

لا يوجد تأخير بين الإعداد والتنفيذ.

مقارنة بنفس قاعدة البيانات تحت FB2.5.9 عند التنفيذ الأول بعد الاتصال: [07.09.2023 15:56:32] - [بدء المعاملة] بدأت المعاملة 76236 [07.09.2023 15:56:32] - [إعداد] حدد من P_BMAND_CHECK [07.09.2023 15:56:32] - [تنفيذ] حدد من P_BMAND_CHECK [07.09.2023 15:56:32] - [جلب] BMAND_ID = 1

لا يوجد تأخير بين الإعداد والتنفيذ.

أنا مرعوب من أن Firebird يقوم بتحميل شجرة التبعية عند بدء التشغيل أو التنفيذ الأول للإجراءات ولكن الأداء ازداد سوءا في ذلك.

راجع للشغل: يحتوي الإجراء P_BMAND_CHECK على تبعيات ل 940 إجراء و 250 محفزا.

شكرا لمساعدتك.

أطيب التحيات أندريه شوابي

[AlexPeshkoff]

Sorry - this is English speaking place.

[a848300]

Hi,

we recently upgrade our database from Firebird 2.5 to Firebird 4.0.3 (running on Windows Server 2k22 or Windows 11). Now some people are complaining that the startup of our application takes way longer then before and they are right.

First of all about the database (downloads.webaverp.com/tmp/2023.01.FB4.zip) 3k tables 11k procedures 9k triggers It was created by backup from 2.5 and restore in 4.0.3. Recompiled all triggers and procedures.

Connecting to the database and executing a procedure (directly after connection) will take up to 10 seconds. Second execution is done within milliseconds no matter if I commit or rollback the first execution of the procedure. We are using IBExpert as IDE and I checked their SQL monitor: [07.09.2023 15:46:01] - [Starting transaction] Transaction 51335 started [07.09.2023 15:46:01] - [Prepare] select from P_BMAND_CHECK [07.09.2023 15:46:08] - [Execute] select from P_BMAND_CHECK [07.09.2023 15:46:08] - [Fetch] BMAND_ID = 1

There is a delay of 7 second between prepare and execute.

Using the same database in Firebird 2.5.9 (same structure) it looks like this: [07.09.2023 15:50:30] - [Starting transaction] Transaction 51344 started [07.09.2023 15:50:30] - [Prepare] select from P_BMAND_CHECK [07.09.2023 15:50:30] - [Execute] select from P_BMAND_CHECK [07.09.2023 15:50:30] - [Fetch] BMAND_ID = 1

There is no delay between prepare and execute.

Comparing to the same database under FB2.5.9 on first execution after connect: [07.09.2023 15:56:32] - [Starting transaction] Transaction 76236 started [07.09.2023 15:56:32] - [Prepare] select from P_BMAND_CHECK [07.09.2023 15:56:32] - [Execute] select from P_BMAND_CHECK [07.09.2023 15:56:32] - [Fetch] BMAND_ID = 1

There is no delay between prepare and execute.

I am awre of that Firebird is loading dependency tree on startup or first execution of procedures but the performance got worse on that.

BTW: The procedure P_BMAND_CHECK has dependencies to 940 procedures and 250 triggers.

Thanks for you help.

Kind regard André Schwabe

[AlexPeshkoff]

This is known issue, will be fixed in next firebird release (FB6).