[firebird-support] connecting to firebird server

[Tony Christiansen t...@adegroup.com.au [firebird-support]]

Hi All

I have FB3 installed on a server. My DelphiXE6 application can access 
the database via TIBDatabase etc no problem but I cannot connect with 
the server to back up the database.

So I installed Flamerobin on the workstation to check and it cannot 
connect to the firebird server (fails after about 10 seconds with a 
isc_service_attach failed.

Everything works fine running on the server itself.

I am confused that my application is accessing the database ok but 
flamerobin cannot connect. Would it be a network permissions issue (and 
if so permissions to what?)

Thanks for any info.

Regards
Tony

Re: [firebird-support] Table Size

[Dimitry Sibiryakov s...@ibphoenix.com [firebird-support]]

09.05.2017 21:59, dres...@tbinc.com [firebird-support] wrote:
> Does anyone have any tools that they can recommend that could help me easily 
> manage this?

   gstat


-- 
   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] Table Size

[dres...@tbinc.com [firebird-support]]

I have a number of growing databases on my server and would like to 
occasionally see how much space each table is taking in the database--there may 
be some log files that could be cleaned up now and then. Many of my tables 
include blob fields, so this becomes different than just doing record count 
times the amount of data in a record.
 

 Does anyone have any tools that they can recommend that could help me easily 
manage this?
 

 much appreciated,
 

 Ed Dressel

AW: AW: [firebird-support] RE: Firebird SuperClassic hangs

[Freddy Ertl f.e...@speexx.com [firebird-support]]

Hi Ann,

I'm not using any transaction level explicitly. I'm using JPA and what I see in 
the trace is always this:
READ_COMMITTED | REC_VERSION | WAIT | READ_WRITE

I will wait now until it happens again (maybe in five minutes or a week) and 
then I will perform a full lock print as suggested.

What I would be interested in is what kind of SQL pattern could produce such a 
deadlock that basically kills Firebird? Would I see anything in the trace?

Best regards
Freddy

Von: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com]
Gesendet: Dienstag, 9. Mai 2017 16:43
An: firebird-support@yahoogroups.com
Betreff: Re: AW: [firebird-support] RE: Firebird SuperClassic hangs




> On May 9, 2017, at 9:10 AM, Freddy Ertl f.e...@speexx.com [firebird-support] 
>  wrote
>
> that's the question, that I have: How to find out what causes the deadlock 
> scans. I have thousands of transactions per minutes, doing all possible kind 
> of things. What to look for? Where?

Your lock header reported an actual deadlock which is actually quite rare. If 
at all possible, instrument your code to find actual deadlock errors ignoring 
the more common "update conflict" errors.

And, although I'm sure you've said it before, wha transaction isolation level 
are you using?

Good luck,

Ann
>
> Best regards
> Freddy Ertl
>
> Von: firebird-support@yahoogroups.com 
> [mailto:firebird-support@yahoogroups.com]
> Gesendet: Dienstag, 9. Mai 2017 15:05
> An: firebird-support@yahoogroups.com
> Betreff: RE: [firebird-support] RE: Firebird SuperClassic hangs
>
>
>
>> Deadlock scans: 289, Deadlocks: 1, Scan interval: 10
>
> Is probably the culprit, 289 deadlock scans, one actual deadlock found
>
> "Deadlock scans. The number of times that the lock manager walked a chain of
> locks and owners looking for deadlocks. The lock manager initiates a deadlock 
> scan
> when a process has been waiting 10 seconds for a lock."
>
> "Deadlocks. The number of actual deadlocks found, A deadlock occurs when
> Process A, wants a lock on Resource 1 which is held in an incompatible mode
> by Process B and Process B wants a lock on some Resource 2 which is held in an
> incompatible mode by Process A.
>
> "Each owner stands around glowering at the other and neither will do anything
> to improve the situation, so the lock manager returns a fatal error to one or 
> the
> other. Deadlocks can also occur with a single resource if both owners start 
> with
> read locks and request conversions to write locks. However, deadlocks always
> involve two owners (or two separate transactions)
>
> Errors that are returned as "lock conflict" from "no wait" lock requests will 
> not
> be recorded in the lock table as deadlocks because only one owner is waiting.
>
> Errors returned as "deadlock" with a secondary message specifying "update 
> conflicts
> with concurrent update" are not actual deadlocks either. What has happened in 
> those
> cases is that one owner has modified (or erased) a record and moved on. 
> Another
> concurrent owner has attempted to modify (or erase) the same record, noticed 
> that the
> most recent version is one he can't see waited to find out how the other 
> transaction
> ends up, and found to his disappointment, that the other transaction 
> succeeded.
> In that case, our patient transaction can't modify the record because it 
> can't find
> out what its late contemporary actually did."
>
> "Scan interval. The lock manager waits some period of time after a request 
> starts
> waiting before it starts a deadlock scan. The default interval is 10 seconds, 
> which may be
> long considering the change in CPU performance since 1983. Deadlock scans 
> should not be done
> whenever there's a wait because waiting is normal and scans are not free."
>
> A deadlock scan would cause your server to hiccup while the lock manager 
> tries to
> find if there is a real deadlock within the lock manager. You might want to 
> investigate
> why deadlock scans are being instigated..
>
> With thanks to Ann Harrison :-)
>
> Regards
> Paul Beach
>
>
>
> [Non-text portions of this message have been removed]
>
>
>
> 
> Posted by: Freddy Ertl 
> 
>
> ++
>
> 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
>
>
>



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

Re: AW: [firebird-support] Firebird SuperClassic hangs

[Alexey Kovyazin a...@ib-aid.com [firebird-support]]

Hi,

The only thing I can say that situation is not obvious, I suggest to ask 
for professional support:

https://www.firebirdsql.org/en/support/

Regards,
Alexey





Hi Alexey,

I have already changed the configuration, but it didn’t help. What I 
find strange is the fact, that the memory consumption drops like it 
would do when you close all connections. See my first description of 
the problem. And only to be clear when I say hang, basically it locks 
up without any sign of processing. When does Firebird perform deadlock 
scans? We have a lot of things going on and this number stay at 0 
until the problem shows up. Is something causing a deadlock that FB 
cannot properly detect? How to analyze such a problem?


Best regards
Freddy Ertl

Von: firebird-support@yahoogroups.com 
[mailto:firebird-support@yahoogroups.com]

Gesendet: Dienstag, 9. Mai 2017 13:48
An: firebird-support@yahoogroups.com
Betreff: Re: [firebird-support] Firebird SuperClassic hangs


Hi Freddy,

Try to use optimized Firebird configuration
https://ib-aid.com/en/optimized-firebird-configuration/

As Sean already told, hang could be caused by very long processing of 
some requests - extremely long queues in lock table, or related with 
excessive number of record versions (Max Versions in database 
statistics), or with other reasons. I would recommend to perform 
detailed investigation of the problem.



Regards,
Alexey Kovyazin
IBSurgeon www.ib-aid.com


Hi,

we’re running Firebird 2.5.7 SuperClassic on Windows Server 2012R2 as 
part of a web application. Database is around 130GB with millions of 
transaction per day. From time to time Firebird hangs. It does not 
respond to any client. It’s also not possible to connect locally to 
the server. The interesting thing is the memory consumption that drops 
from 5-7GB to around 50MB and the CPU load goes down to 0%. When 
Firebird is in this state, I also see a lot of waiting threads in the 
Task Manager under “Analyze wait chain”. The only way to get out, is 
to stop the service. This takes longer than normal and sometimes shows 
some kind of timeout. But the process is stopped and can be started 
again. I also ran fb_lock_print when the problem occurred. It shows 
something under “Deadlock scans” which is not normal, I think.


LOCK_HEADER BLOCK
Version: 145, Active owner: 0, Length: 25165824, Used: 24798216
Flags: 0x0001
Enqs: 17157097538, Converts: 22693011, Rejects: 12135777, Blocks: 
93039760

Deadlock scans: 289, Deadlocks: 1, Scan interval: 10
Acquires: 18346589405, Acquire blocks: 1203176111, Spin count: 0
Mutex wait: 6.6%
Hash slots: 1009, Hash lengths (min/avg/max): 47/ 67/ 93
Remove node: 0, Insert queue: 0, Insert prior: 0
Owners (151): forward: 30704, backward: 21097392
Free owners (4): forward: 750216, backward: 24346456
Free locks (10120): forward: 28728, backward: 693760
Free requests (7877): forward: 8570432, backward: 1678000
Lock Ordering: Enabled

Has anybody experienced something like this? Thanks in advance.

Best regards
Freddy Ertl



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

Re: AW: [firebird-support] RE: Firebird SuperClassic hangs

[Ann Harrison aharri...@ibphoenix.com [firebird-support]]

> On May 9, 2017, at 9:10 AM, Freddy Ertl f.e...@speexx.com [firebird-support] 
>  wrote
> 
> that's the question, that I have: How to find out what causes the deadlock 
> scans. I have thousands of transactions per minutes, doing all possible kind 
> of things. What to look for? Where?

Your lock header reported an actual deadlock which is actually quite rare.  If 
at all possible, instrument your code to find actual deadlock errors ignoring 
the more common "update conflict" errors. 

And, although I'm sure you've said it before, wha transaction isolation level 
are you using?

Good luck,

Ann
> 
> Best regards
> Freddy Ertl
> 
> Von: firebird-support@yahoogroups.com 
> [mailto:firebird-support@yahoogroups.com]
> Gesendet: Dienstag, 9. Mai 2017 15:05
> An: firebird-support@yahoogroups.com
> Betreff: RE: [firebird-support] RE: Firebird SuperClassic hangs
> 
> 
> 
>> Deadlock scans: 289, Deadlocks: 1, Scan interval: 10
> 
> Is probably the culprit, 289 deadlock scans, one actual deadlock found
> 
> "Deadlock scans. The number of times that the lock manager walked a chain of
> locks and owners looking for deadlocks. The lock manager initiates a deadlock 
> scan
> when a process has been waiting 10 seconds for a lock."
> 
> "Deadlocks. The number of actual deadlocks found, A deadlock occurs when
> Process A, wants a lock on Resource 1 which is held in an incompatible mode
> by Process B and Process B wants a lock on some Resource 2 which is held in an
> incompatible mode by Process A.
> 
> "Each owner stands around glowering at the other and neither will do anything
> to improve the situation, so the lock manager returns a fatal error to one or 
> the
> other. Deadlocks can also occur with a single resource if both owners start 
> with
> read locks and request conversions to write locks. However, deadlocks always
> involve two owners (or two separate transactions)
> 
> Errors that are returned as "lock conflict" from "no wait" lock requests will 
> not
> be recorded in the lock table as deadlocks because only one owner is waiting.
> 
> Errors returned as "deadlock" with a secondary message specifying "update 
> conflicts
> with concurrent update" are not actual deadlocks either. What has happened in 
> those
> cases is that one owner has modified (or erased) a record and moved on. 
> Another
> concurrent owner has attempted to modify (or erase) the same record, noticed 
> that the
> most recent version is one he can't see waited to find out how the other 
> transaction
> ends up, and found to his disappointment, that the other transaction 
> succeeded.
> In that case, our patient transaction can't modify the record because it 
> can't find
> out what its late contemporary actually did."
> 
> "Scan interval. The lock manager waits some period of time after a request 
> starts
> waiting before it starts a deadlock scan. The default interval is 10 seconds, 
> which may be
> long considering the change in CPU performance since 1983. Deadlock scans 
> should not be done
> whenever there's a wait because waiting is normal and scans are not free."
> 
> A deadlock scan would cause your server to hiccup while the lock manager 
> tries to
> find if there is a real deadlock within the lock manager. You might want to 
> investigate
> why deadlock scans are being instigated..
> 
> With thanks to Ann Harrison :-)
> 
> Regards
> Paul Beach
> 
> 
> 
> [Non-text portions of this message have been removed]
> 
> 
> 
> 
> Posted by: Freddy Ertl 
> 
> 
> ++
> 
> 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
> 
> 
> 

AW: [firebird-support] RE: Firebird SuperClassic hangs

['Paul Beach' pabe...@waitrose.com [firebird-support]]

<>

Thats what a lock print is for - when it "hangs the next time" get a lock print
fb_lock_print -a -d database > lock.txt
Once you have it - you can analyse it.

https://www.ibphoenix.com/resources/documents/how_to/doc_402
http://www.ibphoenix.com/files/conf2011/Lock-Print-Yemanov-2011.pdf

Regards
Paul

AW: [firebird-support] RE: Firebird SuperClassic hangs

[Freddy Ertl f.e...@speexx.com [firebird-support]]

Hi Paul,

that's the question, that I have: How to find out what causes the deadlock 
scans. I have thousands of transactions per minutes, doing all possible kind of 
things. What to look for? Where?

Best regards
Freddy Ertl

Von: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com]
Gesendet: Dienstag, 9. Mai 2017 15:05
An: firebird-support@yahoogroups.com
Betreff: RE: [firebird-support] RE: Firebird SuperClassic hangs



> Deadlock scans: 289, Deadlocks: 1, Scan interval: 10

Is probably the culprit, 289 deadlock scans, one actual deadlock found

"Deadlock scans. The number of times that the lock manager walked a chain of
locks and owners looking for deadlocks. The lock manager initiates a deadlock 
scan
when a process has been waiting 10 seconds for a lock."

"Deadlocks. The number of actual deadlocks found, A deadlock occurs when
Process A, wants a lock on Resource 1 which is held in an incompatible mode
by Process B and Process B wants a lock on some Resource 2 which is held in an
incompatible mode by Process A.

"Each owner stands around glowering at the other and neither will do anything
to improve the situation, so the lock manager returns a fatal error to one or 
the
other. Deadlocks can also occur with a single resource if both owners start with
read locks and request conversions to write locks. However, deadlocks always
involve two owners (or two separate transactions)

Errors that are returned as "lock conflict" from "no wait" lock requests will 
not
be recorded in the lock table as deadlocks because only one owner is waiting.

Errors returned as "deadlock" with a secondary message specifying "update 
conflicts
with concurrent update" are not actual deadlocks either. What has happened in 
those
cases is that one owner has modified (or erased) a record and moved on. Another
concurrent owner has attempted to modify (or erase) the same record, noticed 
that the
most recent version is one he can't see waited to find out how the other 
transaction
ends up, and found to his disappointment, that the other transaction succeeded.
In that case, our patient transaction can't modify the record because it can't 
find
out what its late contemporary actually did."

"Scan interval. The lock manager waits some period of time after a request 
starts
waiting before it starts a deadlock scan. The default interval is 10 seconds, 
which may be
long considering the change in CPU performance since 1983. Deadlock scans 
should not be done
whenever there's a wait because waiting is normal and scans are not free."

A deadlock scan would cause your server to hiccup while the lock manager tries 
to
find if there is a real deadlock within the lock manager. You might want to 
investigate
why deadlock scans are being instigated..

With thanks to Ann Harrison :-)

Regards
Paul Beach



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

RE: [firebird-support] RE: Firebird SuperClassic hangs

['Paul Beach' pabe...@waitrose.com [firebird-support]]

> Deadlock scans:289, Deadlocks:  1, Scan interval:  10

Is probably the culprit, 289 deadlock scans, one actual deadlock found

"Deadlock scans. The number of times that the lock manager walked a chain of 
locks and owners looking for deadlocks. The lock manager initiates a deadlock 
scan 
when a process has been waiting 10 seconds for a lock."

"Deadlocks.  The number of actual deadlocks found, A deadlock occurs when 
Process A, wants a lock on Resource 1 which is held in an incompatible mode 
by Process B and Process B wants a lock on some Resource 2 which is held in an 
incompatible mode by Process A.

"Each owner stands around glowering at the other and neither will do anything 
to improve the situation, so the lock manager returns a fatal error to one or 
the 
other. Deadlocks can also occur with a single resource if both owners start 
with 
read locks and request conversions to write locks. However, deadlocks always 
involve two owners (or two separate transactions)

Errors that are returned as "lock conflict" from "no wait" lock requests will 
not 
be recorded in the lock table as deadlocks because only one owner is waiting.

Errors returned as "deadlock" with a secondary message specifying "update 
conflicts 
with concurrent update" are not actual deadlocks either. What has happened in 
those 
cases is that one owner has modified (or erased) a record and moved on. Another 
concurrent owner has attempted to modify (or erase) the same record, noticed 
that the 
most recent version is one he can't see waited to find out how the other 
transaction 
ends up, and found to his disappointment, that the other transaction succeeded. 
In that case, our patient transaction can't modify the record because it can't 
find 
out what its late contemporary actually did."

"Scan interval.  The lock manager waits some period of time after a request 
starts 
waiting before it starts a deadlock scan.  The default interval is 10 seconds, 
which may be 
long considering the change in CPU performance since 1983. Deadlock scans 
should not be done 
whenever there's a wait because waiting is normal and scans are not free."

A deadlock scan would cause your server to hiccup while the lock manager tries 
to
find if there is a real deadlock within the lock manager. You might want to 
investigate 
why deadlock scans are being instigated..

With thanks to Ann Harrison :-)

Regards
Paul Beach

[firebird-support] Potential Causes for Query to Slow Down

['Paul Beach' pabe...@waitrose.com [firebird-support]]

<>

Garbage collection usually.

Regards
Paul Beach

AW: [firebird-support] Firebird SuperClassic hangs

[Freddy Ertl f.e...@speexx.com [firebird-support]]

Hi Alexey,

I have already changed the configuration, but it didn’t help. What I find 
strange is the fact, that the memory consumption drops like it would do when 
you close all connections. See my first description of the problem. And only to 
be clear when I say hang, basically it locks up without any sign of processing. 
When does Firebird perform deadlock scans? We have a lot of things going on and 
this number stay at 0 until the problem shows up. Is something causing a 
deadlock that FB cannot properly detect? How to analyze such a problem?

Best regards
Freddy Ertl

Von: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com]
Gesendet: Dienstag, 9. Mai 2017 13:48
An: firebird-support@yahoogroups.com
Betreff: Re: [firebird-support] Firebird SuperClassic hangs


Hi Freddy,

Try to use optimized Firebird configuration
https://ib-aid.com/en/optimized-firebird-configuration/

As Sean already told, hang could be caused by very long processing of some 
requests - extremely long queues in lock table, or related with excessive 
number of record versions (Max Versions in database statistics), or with other 
reasons.  I would recommend to perform detailed investigation of the problem.


Regards,
Alexey Kovyazin
IBSurgeon www.ib-aid.com


Hi,

we’re running Firebird 2.5.7 SuperClassic on Windows Server 2012R2 as part of a 
web application. Database is around 130GB with millions of transaction per day. 
From time to time Firebird hangs. It does not respond to any client. It’s also 
not possible to connect locally to the server. The interesting thing is the 
memory consumption that drops from 5-7GB to around 50MB and the CPU load goes 
down to 0%. When Firebird is in this state, I also see a lot of waiting threads 
in the Task Manager under “Analyze wait chain”. The only way to get out, is to 
stop the service. This takes longer than normal and sometimes shows some kind 
of timeout. But the process is stopped and can be started again. I also ran  
fb_lock_print when the problem occurred. It shows something under “Deadlock 
scans” which is not normal, I think.

LOCK_HEADER BLOCK
Version: 145, Active owner:  0, Length: 25165824, Used: 
24798216
Flags: 0x0001
Enqs: 17157097538, Converts: 22693011, Rejects: 12135777, 
Blocks: 93039760
Deadlock scans:289, Deadlocks:  1, Scan interval:  10
Acquires: 18346589405, Acquire blocks: 1203176111, Spin count:  
 0
Mutex wait: 6.6%
Hash slots: 1009, Hash lengths (min/avg/max):   47/  67/  93
Remove node:  0, Insert queue:  0, Insert prior:  0
Owners (151):   forward:  30704, backward: 21097392
Free owners (4): forward: 750216, backward: 24346456
Free locks (10120): forward:  28728, backward: 693760
Free requests (7877):forward: 8570432, backward: 1678000
Lock Ordering: Enabled

Has anybody experienced something like this? Thanks in advance.

Best regards
Freddy Ertl




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

Re: [firebird-support] Firebird SuperClassic hangs

[Alexey Kovyazin a...@ib-aid.com [firebird-support]]

Hi Freddy,

Try to use optimized Firebird configuration
https://ib-aid.com/en/optimized-firebird-configuration/

As Sean already told, hang could be caused by very long processing of 
some requests - extremely long queues in lock table, or related with 
excessive number of record versions (Max Versions in database 
statistics), or with other reasons.  I would recommend to perform 
detailed investigation of the problem.



Regards,
Alexey Kovyazin
IBSurgeon www.ib-aid.com



Hi,

we’re running Firebird 2.5.7 SuperClassic on Windows Server 2012R2 as 
part of a web application. Database is around 130GB with millions of 
transaction per day. From time to time Firebird hangs. It does not 
respond to any client. It’s also not possible to connect locally to 
the server. The interesting thing is the memory consumption that drops 
from 5-7GB to around 50MB and the CPU load goes down to 0%. When 
Firebird is in this state, I also see a lot of waiting threads in the 
Task Manager under “Analyze wait chain”. The only way to get out, is 
to stop the service. This takes longer than normal and sometimes shows 
some kind of timeout. But the process is stopped and can be started 
again. I also ran  fb_lock_print when the problem occurred. It shows 
something under “Deadlock scans” which is not normal, I think.


LOCK_HEADER BLOCK

Version: 145, Active owner:  0, Length: 25165824, Used: 24798216

Flags: 0x0001

Enqs: 17157097538, Converts: 22693011, Rejects: 12135777, Blocks: 93039760

Deadlock scans:289, Deadlocks:  1, Scan interval:  10

Acquires: 18346589405, Acquire blocks: 1203176111, Spin count:   0

Mutex wait: 6.6%

Hash slots: 1009, Hash lengths (min/avg/max):   47/ 67/  93

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

Owners (151):   forward:  30704, backward: 21097392

Free owners (4): forward: 750216, backward: 24346456

Free locks (10120): forward:  28728, backward: 693760

Free requests (7877):forward: 8570432, backward: 1678000

Lock Ordering: Enabled

Has anybody experienced something like this? Thanks in advance.

Best regards

Freddy Ertl