Re: [firebird-support] RE: Table update performance dropped significantly within few days.
Hi !
Em 16/1/2014 04:13, brucedickin...@wp.pl escreveu:
Hi guys.
Yesterday I was trying to figure out what is the cause of my problem.
And I've noticed that two months ago my colleague added one trigger on
this table. The trigger is:
SET TERM ^ ;
ALTER TRIGGER CLIENT_LM INACTIVE
AFTER INSERT OR UPDATE POSITION 1
AS
BEGIN
UPDATE OR INSERT INTO LAST_MODIFICATION (TABLE_NAME, RECORD_TIME)
VALUES ('CLIENT', NEW.RECORD_TIME)
MATCHING (TABLE_NAME);
END^
SET TERM ; ^
So, after each insert or update this trigger was saving last
modification time to a different table. On each table in the system
such trigger exists. After dissabling the trigger, performance get
back to normal.
Results for gstat are strange:
Database header page information:
Flags 0
Checksum 12345
Generation 228629442
Page size 16384
ODS version 11.2
Oldest transaction 219409373
Oldest active 219409374
Oldest snapshot 219409374
Next transaction227560937
Bumped transaction 1
Sequence number 0
Next attachment ID 1068492
Implementation ID 24
Shadow count0
Page buffers0
Next header page0
Database dialect3
Creation date Aug 9, 2013 23:34:51
Attributes force write
Variable header data:
Sweep interval: 2
*END*
I see the big difference between oldest transaction and the current
transaction. Is it possible that this trigger is caused this? BTW my
colleague has reported to me that he had a number of deadlocks becuase
of this mentioned trigger.
Oldest transaction 219409373
Oldest active 219409374
Oldest snapshot 219409374
Next transaction227560937
Your oldest transaction is around 8 million transctions old, you have
around 1.5 milion transactions a day (22 million transactions in 150
days), so you have an open transaction for around 6 days.
The trigger is not causing the transaction lock, it's caused by an open
transaction that never gets closed.
The trigger could cause a lot of record versions (check with gstat
you_databse.fdb -r -t LAST_MODIFICATION -user sysdba -password
masterkey) because it updates the LAST_MODIFICATION table and since
there is a transaction that needs to see the old version, new versions
are created and never get garbage collected, because it's still
interesting for some transaction.
You should check wich application holds the oldest transaction open, and
fix it.
see you !
Re: [firebird-support] Confused about delta files
Looking at my production system I see that I have an FDB file and an FDB.delta. The date of the FDB is May 2nd, 2013. The date of the delta is today. These are just 2 separate databases. Firebird does not require a special extension, .fdb ist just the usual extension to use. From the names I would guess that the .delta database has been derived (copied) from the .fdb database and then changed (hence the delta). I believe that this is the reason I cannot directly connect to the FDB file on my new server? It's difficult to say that when you don't specifiy *how* you cannot connect to the fdb file (is there an error message? if so, which one?) In any event, is there some way to fold these two files back together into a single FDB? No. At least not a simple, one-click merge databases way. Regards Stefan
Re: [firebird-support] Confused about delta files [SOLVED]
Whoa, this is interesting... My first attempt to move the DB was to simply copy over the FDB. That didn't work, so that's when I went down the nbackup rabbit hole. As it turns out, nbackup is the problem, not the solution... When I tried to re-attach the FDB file on the new server I got a file not found error. But looking in some of the logging files I found... Reason: I/O error for file CreateFile (open) D:\FISHBOWL\DATABASE\DATA\ASSOLAR.FDB.delta Error while trying to open file null A. Notice *which* file it can't find? Well then, perhaps if I copy BOTH files over... and it works! So basically it seems that the FDB file contains some sort of information that points to the delta. When you attempt to connect to a FDB in this state, it also looks for that delta file, and if it's not there, you get file not found. Makes sense in retrospect! I then used isql to ALTER DATABASE END BACKUP. This took about 15 seconds to run (impressive!), and successfully merged the delta back into the FDB. Everything is back to normal. Yay! This is definitely something that should be mentioned in the documentation! Is there a method for me to do the documentation change? I'm pretty good at that sort of thing. p.s. Now that I'm running, does anyone have any suggestions on any post-move maintenance I might want to do?
Re: [firebird-support] Confused about delta files [SOLVED]
Hello Maury, When I tried to re-attach the FDB file on the new server I got a file not found error. But looking in some of the logging files I found... Reason: I/O error for file CreateFile (open) D:\FISHBOWL\DATABASE\DATA\ASSOLAR.FDB.delta Error while trying to open file null A. Notice *which* file it can't find? Well then, perhaps if I copy BOTH files over... and it works! So basically it seems that the FDB file contains some sort of information that points to the delta. When you attempt to connect to a FDB in this state, it also looks for that delta file, and if it's not there, you get file not found. Makes sense in retrospect! Of course it needs the delta. As long as the backup state lasts, the main database file is frozen and all changes are written to the delta. That is, the .fdb and the .fdb.delta *together* form the database. I then used isql to ALTER DATABASE END BACKUP. This took about 15 seconds to run (impressive!), and successfully merged the delta back into the FDB. Everything is back to normal. Yay! Great! This is definitely something that should be mentioned in the documentation! What exactly are you missing in the documentation? Notice that delta files are normally very short-lived. If you use nbackup -b, the delta has disappeared when the command completes (and if something went wrong, there'll be an error message). And if you use nbackup -l, you are supposed to know what you are doing ;-) I daresay that if you had read the nbackup manual from beginning to end, you could have solved this problem yourself. (This is not criticism, BTW; this group is called firebird-SUPPORT for a reason.) Is there a method for me to do the documentation change? I'm pretty good at that sort of thing. If you want to propose changes to a manual, the preferred way is to submit them to the firebird-docs list (to subscribe, mail to firebird-docs-requ...@lists.sourceforge.net) People who write or update documentation regularly can get CVS access. Kind regards, Paul Vinkenoog
Re: [firebird-support] Confused about delta files [SOLVED]
On 2014-01-16, at 10:11 AM, Paul Vinkenoog wrote: What exactly are you missing in the documentation? Notice that delta files are normally very short-lived. That's the issue right there. As it is clearly possible that these are not *always* short lived, this should be mentioned along with suggestions on what to do. More specifically, there should be a mention in the sections on moving the databases that state that if a delta file is present, it must be moved as well. I daresay that if you had read the nbackup manual from beginning to end, I did. I saw no mention of copying FDB files - which I wouldn't expect. Did I miss something? you could have solved this problem yourself. Well, I did... If you want to propose changes to a manual, the preferred way is to submit them to the firebird-docs list (to subscribe, mail to firebird-docs-requ...@lists.sourceforge.net) Done.
Re: [firebird-support] RE: Garbage collection / sweep not happening on super classic
On Wed, Jan 15, 2014 at 4:42 PM, rmcgi...@oceris.com wrote: Ok, running the gfix -sweep with everyone logged out did work and only took about 5 minutes. I believe the issue came from me only trying to run the sweep one time and a transaction being stuck. I never tried to run sweep AFTER restarting the services. I also think there was confusion because I was under the incorrect assumption that gbak was doing a sweep while backing up. Gbak (without the -g switch) will remove old record versions, but, like a sweep, it only works up to the oldest transaction that was active when the oldest transaction currently open started. So if you have a transaction that's stuck, neither gbak nor sweep will remove versions newer than some version older than the stuck transaction. The difference between sweep and a gbak backup without the -g is that sweep resets the value of the Oldest Interesting Transaction (OIT), and gbak does not. After a sweep, all record versions older than the sweep threshold are guaranteed to be committed. That used to be important because InterBase had to carry around a bit vector the length of the difference between the OIT and the current transaction - which could be a lot of bits on a machine with a mid-1980's sized memory. Each bit in the vector represents the state of a transaction - committed or not. The vector still exists, but it takes a whole lot of bits to use any significant fraction of the memory of a modern computer. Good luck, Ann
[firebird-support] RE: Table update performance dropped significantly within few days.
You should check wich application holds the oldest transaction open, and fix it. Thank you, is it possible that this transaction is just hanging and an application which started it,has been already terminated? If I am not mistaken, I can somehow check out some system table for active transactions and process id which is started them?
[firebird-support] RE: Table update performance dropped significantly within few days.
Do you know if or when the trigger was ACTIVATED? It's very hard to see how deactivating a hungry trigger like that one could *degrade* performance. I am sorry Helen for confusing you. I've posted DDL for already deactivated trigger which I've deactivated yesterday. The trigger was firstly activated 21.11.2013 and was active until 15.01.2014, for the whole time server was running without any problems (until the very last few days).We have 5-10 GUI applications connected to the database on daily basis and we have one console application which is importing around 200k new records everyday, and updating another 100k. Best regards.
[firebird-support] Multiple Embedded Connections
I notice that it is now possible to connect to a database via the embedded server and simultaneously connect via other embedded processes to the same database file. This was not possible in previous version - the second process would be locked out. Can someone lead me to an explanation of how these multiple connections are managed? Which embedded server will coordinate updates and modifications to the database? It appears that updates from one process are visible to the other(s) but I'm not sure how the second would know if a transaction is being managedin the first - is there a lock file being written to somewhere that I can't find? (WIN32) V2.5 Regards Alan McDonald
Re: [firebird-support] Multiple Embedded Connections
Hi Alan, I notice that it is now possible to connect to a database via the embedded server and simultaneously connect via other embedded processes to the same database file. This was not possible in previous version - the second process would be locked out. Can someone lead me to an explanation of how these multiple connections are managed? Which embedded server will coordinate updates and modifications to the database? It appears that updates from one process are visible to the other(s) but I'm not sure how the second would know if a transaction is being managedin the first - is there a lock file being written to somewhere that I can't find? (WIN32) V2.5 From the 2.5 Quick Start Guide: Windows Embedded now contains a SuperClassic instead of a SuperServer engine. File locks are shared, so a database can be accessed by one or more Embedded servers and a regular Classic or SuperClassic server at the same time. Consult the Firebird 2.5 Release Notes for full details. Cheers, Paul Vinkenoog
Re[2]: [firebird-support] Multiple Embedded Connections
Hello, Paul, Alan! PV Windows Embedded now contains a SuperClassic instead of a SuperServer engine. PV File locks are shared, so a database can be accessed by one or more Embedded PV servers and a regular Classic or SuperClassic server at the same time. PV Consult the Firebird 2.5 Release Notes for full details. Yes, but this provocates some people to use embedded for multi-tier middleware, where definitely server (not embedded) must be used. AM It appears that updates from one process are visible to the other(s) but I'm AM not sure how the second would know if a transaction is being managedin the AM first - is there a lock file being written to somewhere that I can't find? I need to note that Embedded Firebird is a dll that works in an address space of it's caller (exe). Thus EXE itself becomes server. And if that server have bugs, chances to get broken database are much higher, especially if you want to use multiple exe+embedded on one DB. -- Dmitry Kuzmenko, www.ib-aid.com
RE: [firebird-support] Multiple Embedded Connections
Hi Alan, I notice that it is now possible to connect to a database via the embedded server and simultaneously connect via other embedded processes to the same database file. This was not possible in previous version - the second process would be locked out. Can someone lead me to an explanation of how these multiple connections are managed? Which embedded server will coordinate updates and modifications to the database? It appears that updates from one process are visible to the other(s) but I'm not sure how the second would know if a transaction is being managedin the first - is there a lock file being written to somewhere that I can't find? (WIN32) V2.5 From the 2.5 Quick Start Guide: Windows Embedded now contains a SuperClassic instead of a SuperServer engine. File locks are shared, so a database can be accessed by one or more Embedded servers and a regular Classic or SuperClassic server at the same time. Consult the Firebird 2.5 Release Notes for full details. Yes - but where is this global lock table? It doesn't tell me if it's a file somewhere or in the memory of the first server loaded? Alan Cheers, Paul Vinkenoog ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo Groups Links
RE: Re[2]: [firebird-support] Multiple Embedded Connections
Hello, Paul, Alan! PV Windows Embedded now contains a SuperClassic instead of a SuperServer engine. PV File locks are shared, so a database can be accessed by one or more PV Embedded servers and a regular Classic or SuperClassic server at the same time. PV Consult the Firebird 2.5 Release Notes for full details. Yes, but this provocates some people to use embedded for multi-tier middleware, where definitely server (not embedded) must be used. AM It appears that updates from one process are visible to the other(s) AM but I'm not sure how the second would know if a transaction is being AM managedin the first - is there a lock file being written to somewhere that I can't find? I need to note that Embedded Firebird is a dll that works in an address space of it's caller (exe). Thus EXE itself becomes server. And if that server have bugs, chances to get broken database are much higher, especially if you want to use multiple exe+embedded on one DB. I don't want to use it like this - I just want to know how it's working. As far as corruption is concerned, I've used the embedded version for years and this possibility seems moot to me - I've never experienced corruption - escpeialy not in the single user/embedded databasesetup. Alan -- Dmitry Kuzmenko, www.ib-aid.com ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo Groups Links
Re: [firebird-support] Unknown ISC Error 0
On 16/01/2014 6:07 PM, Helen Borrie wrote: At 06:28 p.m. 16/01/2014, Cam wrote: Hi Using Firebird 2.5.2 32 bit SuperServer. Happens on Win XP, WIN7 64 BIT oS'S Occasionally on some sites I am getting a dialog Box with ISCErrorCode 335544648 Unknown ISC Error 0 According to the error code list the 335544648 code means 335544648 conn_lost Connection lost to pipe server. Not sure what this means. I have terminated the application using Firebird. Stopped and Started the Firebird Service. Backed up and restored the database with no errors. Restart the application and the error occurs immediately. The only way I have been able to clear the error is to reboot the PC. It appears your clients are using Named Pipes protocol (WNET) to connect to the server. The number of simultaneous WNET connections will be limited by the Windows licence. It's not just database connections that are counted: so are network and shared printers, file shares, NAS directories, etc. If the network is being stretched to the limit, the NMS will disconnect any quiet connections to let the next connection request through. Watch the Firebird log when the app gets a 335544648: there might be a corresponding WNET error there. But WNET is a noisy protocol, prone to Connection Lost and Connection Reset errors with no cause other than poor error recovery. Is there another way to clear the conditions causing this error? Better to *avoid* the error. Use TCP/IP as the connection protocol for your database clients. It isn't affected by the Windows licence limitations. It's less noisy, as well. All that said, it would make sense to check the network hardware - cards, connectors, cables, routers, including wifi if it's in use. Helen Borrie, Support Consultant, IBPhoenix (Pacific) Author of The Firebird Book and The Firebird Book Second Edition http://www.firebird-books.net Hi Helen Just checked my code. Using IBObjects. Embarrassed to say that the protocol on the TIB_Connection was cpLocal, so this may be the culprit. Thanks for the reply Cheers Paul
Re: [firebird-support] Multiple Embedded Connections
Alan McDonald wrote: Windows Embedded now contains a SuperClassic instead of a SuperServer engine. File locks are shared, so a database can be accessed by one or more Embedded servers and a regular Classic or SuperClassic server at the same time. Consult the Firebird 2.5 Release Notes for full details. Yes - but where is this global lock table? It doesn't tell me if it's a file somewhere or in the memory of the first server loaded? Iirc, it's a lock file in ProgramData\Firebird. Paul Vinkenoog
Re: [firebird-support] Confused about delta files [SOLVED]
Maury Markowitz wrote: What exactly are you missing in the documentation? Notice that delta files are normally very short-lived. That's the issue right there. As it is clearly possible that these are not *always* short lived, this should be mentioned along with suggestions on what to do. That's right. Although your experience seems to be exceptional, it *did* happen and it may happen to others. So a warning in the nbackup manual would be in order. More specifically, there should be a mention in the sections on moving the databases that state that if a delta file is present, it must be moved as well. In general, moving Firebird database files is a definite no-no. You copy or move Firebird databases by gbak'ing them and restoring them at the new location. Including suggestions in the docs like if you move a Firebird database, check if there's a delta present and if so, move that too might give users the impression that moving database files around is a good idea. What's more, if there's a delta present, then the main .fdb file is usually locked - so it can be moved or copied without risk - but the delta is live, so it shouldn't be touched unless there's absolutely no other option. In the situation you described, the logical thing to do would have been ALTER DATABASE END BACKUP (or nbackup -N) on the original machine (once you found out the right credentials, which I believe you did). Cheers, Paul Vinkenoog
