Re: [firebird-support] Slow execution of Stored Procedure after 20 min. inactivity

2017-12-05 Thread Dimitry Sibiryakov s...@ibphoenix.com [firebird-support] 
05.12.2017 10:07, Marcel Brink mbr...@xploration.onmicrosoft.com 
[firebird-support] wrote:
> Does anyone have an idea why this happens only after around 20 minutes and 
> not every time ?

   You should, probably, turn performance counters output in trace as well. May 
be in 
these 20 minutes some old transactions are ended and your procedure has to 
collect garbage.


-- 
   WBR, SD.






++

Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu.  Try FAQ and other links from the left-side menu there.

Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ 

++


Yahoo Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/

[firebird-support] windows server 2016 as server for firebird 2.5.7

2017-12-05 Thread Nils Boedeker nilsboede...@t-online.de [firebird-support] 
Hi


I want to use windows server 2016 as server for


firebird 2.5.7 (Classic Server)


are there any known issus with using firebird with this system?


with best regareds


Nils


PS: The power plan ist on "High Performance"

[firebird-support] Slow execution of Stored Procedure after 20 min. inactivity

2017-12-05 Thread Marcel Brink mbr...@xploration.onmicrosoft.com [firebird-support] 
Hello,

After spending more than a week trying to resolve the following issue we hope 
someone has an answer on this.

We have a database with about 230 tables and 200 stored procedures which is 
being used by our customers.
Each customer have a dedicated installation with it's own database varying in 
size from 50MB up to 2GB.
At the moment they use Firebird 2.5.1.26351 Super-Classic Win32 on different 
platforms (Win 7 Pro, Win 10 Pro, Windows server 2012 R2).

The issue occurs when we execute a complex (recursive) stored procedure after 
the database engine has been idle for about 20 minutes.
Executing the stored procedure after those 20 minutes can take around 18-20 
seconds to complete, while it normally takes around 0,1 seconds.
Even after restarting the Firebird service executing the stored procedure 
immediately never takes longer then 3-4 seconds (unless the 20 minutes are 
passed).

Things we tried:

  *   Restarting the Firebird service.
Even if we restart the Firebird service between those 20 minutes, once the 20 
minute mark is reached it simply takes around 20 seconds again.
  *   Installed firebird and the database on an SSD (Samsung Evo 500GB) under 
Windows 10 Pro (Fall Creators update) with default configuration.
- No difference.
  *   Turned off inactivity settings in Windows for the HDD, so Windows doesn't 
turn off the HDD.
- No difference.
  *   Upgraded Firebird to version 2.5.7.27050.
- No difference.
  *   Upgraded Firebird to version 3.0.2 using the migration guide 
(backup/restore, etc.).
- No difference.

When we insert a record into a random table within the same connection and 
transaction before executing the stored procedure,
after those 20 minutes the insert is fast and executing the stored procedure 
takes around 20 seconds again.

This is the trace of what happens when we execute the stored procedure after 20 
minutes (watch the prepare of statement 70).

2017-12-05T09:57:19.4580 (8628:0395DB90) TRACE_INIT
SESSION_17 User Trace 1

2017-12-05T09:57:19.4580 (8628:0395DB90) ATTACH_DATABASE
COACH3 (ATT_10442, SYSDBA:NONE, NONE, TCPv4:127.0.0.1)
C:\Users\Marcel\documents\visual studio 
2017\Projects\FirebirdTest\FirebirdTest\bin\Debug\FirebirdTest.exe:20176

2017-12-05T09:57:19.4600 (8628:0395DB90) START_TRANSACTION
COACH3 (ATT_10442, SYSDBA:NONE, NONE, TCPv4:127.0.0.1)
C:\Users\Marcel\documents\visual studio 
2017\Projects\FirebirdTest\FirebirdTest\bin\Debug\FirebirdTest.exe:20176
   (TRA_27500, READ_COMMITTED | REC_VERSION | 
NOWAIT | READ_WRITE)

2017-12-05T09:57:19.4670 (8628:0395DB90) PREPARE_STATEMENT
COACH3 (ATT_10442, SYSDBA:NONE, NONE, TCPv4:127.0.0.1)
C:\Users\Marcel\documents\visual studio 
2017\Projects\FirebirdTest\FirebirdTest\bin\Debug\FirebirdTest.exe:20176
   (TRA_27500, READ_COMMITTED | REC_VERSION | 
NOWAIT | READ_WRITE)

Statement 55:
---
execute procedure SP_CHECK_TOEGANGSPROFIEL(?,?)
  4 ms

2017-12-05T09:57:19.4690 (8628:0395DB90) EXECUTE_PROCEDURE_START
COACH3 (ATT_10442, SYSDBA:NONE, NONE, TCPv4:127.0.0.1)
C:\Users\Marcel\documents\visual studio 
2017\Projects\FirebirdTest\FirebirdTest\bin\Debug\FirebirdTest.exe:20176
   (TRA_27500, READ_COMMITTED | REC_VERSION | 
NOWAIT | READ_WRITE)

Procedure SP_CHECK_TOEGANGSPROFIEL:
param0 = varchar(20), "AAJQ2295384"
param1 = varchar(20), "AAJQ1796287"


2017-12-05T09:57:37.5380 (8628:0395DB90) PREPARE_STATEMENT
COACH3 (ATT_10442, SYSDBA:NONE, NONE, TCPv4:127.0.0.1)
C:\Users\Marcel\documents\visual studio 
2017\Projects\FirebirdTest\FirebirdTest\bin\Debug\FirebirdTest.exe:20176
   (TRA_27500, READ_COMMITTED | REC_VERSION | 
NOWAIT | READ_WRITE)

Statement 70:
---
EXECUTE PROCEDURE SP_CHECK_PROFIELEXPRESSIETREE('AAJQ1796287', 'AAJQ2295385', 
NULL, 1)
  18068 ms

2017-12-05T09:57:37.5390 (8628:0395DB90) EXECUTE_PROCEDURE_START
COACH3 (ATT_10442, SYSDBA:NONE, NONE, TCPv4:127.0.0.1)
C:\Users\Marcel\documents\visual studio 
2017\Projects\FirebirdTest\FirebirdTest\bin\Debug\FirebirdTest.exe:20176
   (TRA_27500, READ_COMMITTED | REC_VERSION | 
NOWAIT | READ_WRITE)

Procedure SP_CHECK_PROFIELEXPRESSIETREE:
param0 = varchar(20), "AAJQ1796287"
param1 = varchar(20), "AAJQ2295385"
param2 = varchar(20), ""
param3 = integer, "1"


2017-12-05T09:57:37.5670 (8628:0395DB90) PREPARE_STATEMENT
COACH3 (ATT_10442, SYSDBA:NONE, NONE, TCPv4:127.0.0.1)
C:\Users\Marcel\documents\visual studio 
2017\Projects\FirebirdTest\FirebirdTest\bin\Debug\FirebirdTest.exe:20176
   (TRA_27500,

Re: [firebird-support] Re: lock denied...

2017-12-05 Thread ehmmm.fireb...@seznam.cz [firebird-support] 
Thank you for your response.

You are right, the /tmp/firebird/fb_lock_table.dump is really there.
I've attached it.

It has the same timestamp like the "Fatal lock manager error" event, ie. 00:
11.

I'm quite interested what you will find inside.

Jakub


-- Původní e-mail --
Od: peshk...@mail.ru [firebird-support] 
Komu: firebird-support@yahoogroups.com
Datum: 5. 12. 2017 14:52:24
Předmět: [firebird-support] Re: lock denied... 
" 
  



Yes, minimum requirement to let your program talk to SS is to avoid embedded
connections, and one of the ways to do it is to add hostname/ip-address 
before database name.

Is there /tmp/firebird/fb_lock_table.dump on your box? If yes please upload 
it here - it can give an idea what's wrong with SC in your case.




"

[Non-text portions of this message have been removed]

[firebird-support] Re: lock denied...

2017-12-05 Thread peshk...@mail.ru [firebird-support] 
Yes, minimum requirement to let your program talk to SS is to avoid embedded 
connections, and one of the ways to do it is to add hostname/ip-address before 
database name.

Is there /tmp/firebird/fb_lock_table.dump on your box? If yes please upload it 
here - it can give an idea what's wrong with SC in your case.

Re: [firebird-support] Re: lock denied...

2017-12-05 Thread ehmmm.fireb...@seznam.cz [firebird-support] 
Thank you for your response.
Compared to you I'm just a beginner so I have other questions.

> i see no relation between such services and requirement to use 
SuperClassic

I din't investigate it much but I think it is becuase the C programs it is 
historically written something like this:

int dbConnect()
{
    EXEC SQL
    SET DATABASE DB = '/var/lib/firebird/2.5/data/xxx.fdb';
    EXEC SQL
    CONNECT DB USER 'xxx' PASSWORD 'xxx';
    ...

(Don't get confused by the "2.5" subdirectory, I did "gbak -r" to 3.0 
version but I put it in the old dir.)
I think that in this case the C programs access somehow more directly and 
they can't cooperate with SuperServer.
But I promise I'll lokk at it.

> Try to lower load on lock manager.
Please how? Make less connections?

> Switch to Super
Discussed above.

> or try to increase linger on security3.fdb(it is 60 sec by default).
Please how?
I've found some: "ALTER DATABASE SET LINGER TO"
I guess I'll have to do it via isql and not by Flamerobin. (?)
How can I check how much is it right now?
(I've found some tip "gstat -h" which I use for sweeping monitor. But I can'
t see there any "linger".)
I've found some presentation that this "linger functionality" has effect 
whene there are no connections.
But I think that I have there new attachments almost every second. (?)

> If there was coredump produced by crash, you may provide us with 
backtraces of all stacks. 
How can I get this dump?
I've found some document from 2008 mentioning BugcheckAbort=1 in the 
firebird.conf.
That's it?




Jakub


-- Původní e-mail --
Od: hv...@users.sourceforge.net [firebird-support] 
Komu: firebird-support@yahoogroups.com
Datum: 5. 12. 2017 10:15:00
Předmět: [firebird-support] Re: lock denied... 
" 
  



---In firebird-support@yahoogroups.com,  wrote :




> last weekend we switched from FB 2.5.x to  FB V3.0.3.32796
> 
> We have couple databases, the biggest has around 12 GB.
> About 100-200 clients are connecting to this DB via PHP approximately 
twice per minute to see some data.
> Some services on server are writing data into this DB.


> We are using FB in SuperClassic mode, because some services are written in
C and compiled via gpre/gcc.





  Hmm... i see no relation between such services and requirement to use 
SuperClassic.

Сould you try Super mode ? SuperClassic in FB3 is legacy and not recommended
mode.





> In firebird log I can see messages like this:
> 
> z3  Sun Dec  3 01:43:15 2017
>     Database: /opt/firebird/security3.fdb
>     page 0, page type 1 lock denied (216)


...
> Could you please explain me, what do this messages mean?



  It looks like bug due to race condition when lock manager initialized 
shared memory while another 


instance (attachment) is deinitialized it (it is simplified and far from 
exact description). It could be 


something else - i need to reproduce the issue to make correct conclusion.



> How can I avoid them?




  Try to lower load on lock manager. Switch to Super, or try to increase 
linger on security3.fdb

(it is 60 sec by default).





> What shall I look at?




  If there was coredump produced by crash, you may provide us with 
backtraces of all stacks. 




Regards,

Vlad








"

[firebird-support] Re: lock denied...

2017-12-05 Thread hv...@users.sourceforge.net [firebird-support] 
---In firebird-support@yahoogroups.com,  wrote :
 
 > last weekend we switched from FB 2.5.x to  FB V3.0.3.32796
> 
> We have couple databases, the biggest has around 12 GB.
> About 100-200 clients are connecting to this DB via PHP approximately twice 
> per minute to see some data.
> Some services on server are writing data into this DB.

 > We are using FB in SuperClassic mode, because some services are written in C 
 > and compiled via gpre/gcc.

 

   Hmm... i see no relation between such services and requirement to use 
SuperClassic.
 Сould you try Super mode ? SuperClassic in FB3 is legacy and not recommended 
mode.
 

 
> In firebird log I can see messages like this:
> 
> z3  Sun Dec  3 01:43:15 2017
> Database: /opt/firebird/security3.fdb
> page 0, page type 1 lock denied (216)

 ...
> Could you please explain me, what do this messages mean?
 

  It looks like bug due to race condition when lock manager initialized shared 
memory while another 

 instance (attachment) is deinitialized it (it is simplified and far from exact 
description). It could be 

 something else - i need to reproduce the issue to make correct conclusion.


 > How can I avoid them?
 

   Try to lower load on lock manager. Switch to Super, or try to increase 
linger on security3.fdb
 (it is 60 sec by default).

 

 > What shall I look at?
 

   If there was coredump produced by crash, you may provide us with backtraces 
of all stacks. 
 

 Regards,
 Vlad

Re: [firebird-support] lock denied...

2017-12-05 Thread ehmmm.fireb...@seznam.cz [firebird-support] 
It happened again.

z3  Tue Dec  5 08:37:45 2017
    Database: /opt/firebird/security3.fdb
    page 0, page type 1 lock denied (216)


z3  Tue Dec  5 08:37:54 2017
    Database:
    page 0, page type 1 lock denied (216)


zl3  Tue Dec  5 08:37:54 2017
    Authentication error
    page 0, page type 1 lock denied

I've noticed that since this moment I could conenct to tcp:3050 (eg. via 
telnet) but I couldn't open any working DB connection.
Applications that keep their connection still worked and collected data.
But applications that need to open new connection (eg. cron jobs, or php 
scripts in apache) are out of order.

So I restarted firebird.

It seems like that tonight I will have return to FB 2.5.

Jakub


-- Původní e-mail --
Od: ehmmm.fireb...@seznam.cz [firebird-support] 
Komu: firebird-support@yahoogroups.com
Datum: 5. 12. 2017 6:59:21
Předmět: [firebird-support] lock denied... 
" 
  



Hi,

last weekend we switched from FB 2.5.x to  FB V3.0.3.32796

We have couple databases, the biggest has around 12 GB.
About 100-200 clients are connecting to this DB via PHP approximately twice 
per minute to see some data.
Some services on server are writing data into this DB.
We are using FB in SuperClassic mode, because some services are written in C
and compiled via gpre/gcc.

In firebird log I can see messages like this:

z3  Sun Dec  3 01:43:15 2017
    Database: /opt/firebird/security3.fdb
    page 0, page type 1 lock denied (216)


z3  Sun Dec  3 01:43:24 2017
    Database:
    page 0, page type 1 lock denied (216)


z3  Sun Dec  3 01:43:24 2017
    Authentication error
    page 0, page type 1 lock denied
...

z3  Mon Dec  4 01:36:53 2017
    Fatal lock manager error: invalid lock id (82928), errno: 0


z3  Mon Dec  4 01:36:53 2017
    /opt/firebird/bin/fbguard: /opt/firebird/bin/firebird terminated 
abnormally (-1)



z3  Mon Dec  4 01:36:53 2017
    /opt/firebird/bin/fbguard: guardian starting /opt/firebird/bin/
firebird

...

z3  Tue Dec  5 00:11:35 2017
    Fatal lock manager error: invalid lock id (82928), errno: 0


z3  Tue Dec  5 00:11:35 2017
    /opt/firebird/bin/fbguard: /opt/firebird/bin/firebird terminated 
abnormally (-1)



z3  Tue Dec  5 00:11:35 2017
    /opt/firebird/bin/fbguard: guardian starting /opt/firebird/bin/
firebird

...

z3  Tue Dec  5 01:02:55 2017
    Database: /opt/firebird/security3.fdb
    page 0, page type 1 lock denied (216)


z3  Tue Dec  5 01:03:04 2017
    Database:
    page 0, page type 1 lock denied (216)


z3  Tue Dec  5 01:03:04 2017
    Authentication error
    page 0, page type 1 lock denied

...

z3  Tue Dec  5 05:44:27 2017
    Database: /opt/firebird/security3.fdb
    page 0, page type 1 lock denied (216)


z3  Tue Dec  5 05:44:36 2017
    Database:
    page 0, page type 1 lock denied (216)


z3  Tue Dec  5 05:44:36 2017
    Authentication error
    page 0, page type 1 lock denied

Could you please explain me, what do this messages mean?
How can I avoid them?
What shall I look at?

Best regards,
Jakub





"