Re: [firebird-support] Re: Rename database command
On Thu, Jun 6, 2013 at 6:45 PM, sir_wally_lewis wrote: > Hi, > > > There are no long running transactions. > OK, then just gstat -h won't show any significant difference between the OAT and the Next Transaction. > > Your assuming my code is the source of the issue, is fascinating to me. It > is a large "Jump to conclusion". Maybe it should be on the "Jump to > conclusion mat", that was a joke for anyone who saw "Office space" the > movie. > > Many people have used Firebird and InterBase for nearly three decades without finding a case where a high transaction number caused the database to slow down unless the transaction number approached 2**32. Lots of people have had databases become slow because of a build-up of old record versions. When you've got time, run gstat with the -a -r switches - send the output to a file. The -r switch causes gstat to report the max, min, and average numbers of back versions of records for each table. That's the definitive answer to whether your slowdown is caused by excessive back versions - if so, there are a number of diagnostic steps that can help avoid that problem. One other question. If you are running a version and architecture that supports several garbage collection options, (cooperative, separate thread, mixed) you might try changing the options. Firebird supports three options for garbage collection because different work loads put different stresses on garbage collection and you may find that a different strategy works better for you. Good luck, Ann [Non-text portions of this message have been removed]
Re: [firebird-support] Re: Rename database command
Em 6/6/2013 19:45, sir_wally_lewis escreveu: > Hi, > > Please do not misunderstand me. I already have gone over this ground. I am > not a newbie when it comes to firebird. In fact I have worked major miracles > in making with firebird to do what it does now. > > There are no long running transactions. > > Your assuming my code is the source of the issue, is fascinating to me. It is > a large "Jump to conclusion". Maybe it should be on the "Jump to conclusion > mat", that was a joke for anyone who saw "Office space" the movie. > > I will attempt to get my DBA to actually gather some physical evidence for > this situation. > > Kind Regards, > > Robert. > > I am not assuming you are newbie... but *you* said you don't intend to go deep into firebird... So, if my advices was useless forgive me for taking your time... And I can't see a reason why a 32bit integer value of 100 is processed faster then a 32bit integer 2.123.456.789.. anyway As you said you have confidence thos fact.. I think you reaaly need a way to rename a back-up... maybe you could sponsor such feature... wish you luck... see you !
RE: [firebird-support] Re: Rename database command
> query the database headers per: > > gstat -h If you post the results we should be able find the source of the "slowdowns". Further, if you haven't already, please provide the details of the Firebird version/release number you are running. Sean
Re: [firebird-support] Re: Rename database command
Em 5/6/2013 20:01, sir_wally_lewis escreveu: > Hi Sean, > > While I don't pretend to understand Firebird at the atomic level. I am just > trying to cope with database slowdowns. We find the only bullet proof > methodology to solve database slowdowns is a backup restore. So we are > searching for a method to be able to resolve database slowdowns, while > keeping the database online. > > I am not concerned with whether theoretically firebird should or should not > require a backup/restore. It seems that in practice under large database > conditions to be a requirement. > > Our networks guy is going to spend some time seeing if he can give evidence > of this requirement. Of course we will try any method to attempt to resolve > this. > > In the past we have not found sweeping the database to help, but we will > continue to do everything we can to resolve this for our customers sake. > > Kind Regards, > > Robert > Robert, Maybe you (or the person responsible for the application code) needs to understand the Firebird principles (MGA) this way you could understand why the system become slow and fix the reason. I don't think this is "understand Firebird at atomic level", but to understand why some coding mistakes could lead to a system slow down and how to fix it. Look at the cause not at the symptom... If you manage your transactions properly I doubt you will need a back-up/restore cycle every week to keep the system responsive. You said that sweep did not "fix" the problem, but in what circustances was the sweep run ? If your application keeps a transaction open for a long time (what I think is the case) running sweep could not help that much, but if you run sweep when no one is connected I think the result will be the same (as I said previously the restore process will do more than a sweep, but I think it's not necessary) as running a back-up/restore cycle. The transaction counter is a 32bit integer and when will get close to this limit you will need to perform a restore to reset it (as the restore creates a new database, the transaction counter will start from 0 again). To remove garbage and move forward the OIT the sweep is all you need, as long when you run sweep there is no transaction open for hours. see you !
Re: [firebird-support] Re: Rename database command
Hi Robert, what you can do for your own information: query the database headers per: gstat -h that shows you the relevant transaction informations, and also if the internal housekeeping (sweeping) is activated - and when, at what interval (delta between oldest active transaction and oldest snapshot, see below: gstat-sample-header) From what i've read your database/application turns slower every day until you perform a backup/restore - that is what resets the whole transaction system. That also could be done manually by gfix -sweep or managed by the firebird server per gfix -housekeeping , wherein the default value is 2. Which one you choose depends on the application and performace requirements. Regarding transactions and performance: See http://www.firebirdsql.org/manual/gfix-housekeeping.html and http://www.firebirdsql.org/manual/gstat-example-header.html Marcus sir_wally_lewis wrote: > > Hi Sean, > > While I don't pretend to understand Firebird at the atomic level. I am > just trying to cope with database slowdowns. We find the only bullet > proof methodology to solve database slowdowns is a backup restore. So > we are searching for a method to be able to resolve database > slowdowns, while keeping the database online. > > I am not concerned with whether theoretically firebird should or > should not require a backup/restore. It seems that in practice under > large database conditions to be a requirement. > > Our networks guy is going to spend some time seeing if he can give > evidence of this requirement. Of course we will try any method to > attempt to resolve this. > > In the past we have not found sweeping the database to help, but we > will continue to do everything we can to resolve this for our > customers sake. > > Kind Regards, > > Robert > > [Non-text portions of this message have been removed]
Re: [firebird-support] Re: Rename database command
Em 5/6/2013 17:34, Marcus Bajohr escreveu: > From my little knowlegde i would guess Robert is talking about the > delta between the oldest active transaction and next transaction. > But as long as i do not know anything about the application (event > listeners, long time transactions and that) and the environment (Number > of active connections and that) Robert writes about (may be he has in an > earlier post) , it is just peeking around. > > just my EUR 0,02, Marcus > yep... that *would* cause slowndown... but back-up/restore is not needed to fix it. In fact.. the fix is in the application (transaction control), and if the application could not be fixed a simple SWEEP (gfix -sweep) would fix that if run when no one is connected (I assume that it is possible since he could back-up/restore) see you !
Re: [firebird-support] Re: Rename database command
From my little knowlegde i would guess Robert is talking about the delta between the oldest active transaction and next transaction. But as long as i do not know anything about the application (event listeners, long time transactions and that) and the environment (Number of active connections and that) Robert writes about (may be he has in an earlier post) , it is just peeking around. just my EUR 0,02, Marcus Alexandre Benson Smith wrote: > Em 5/6/2013 13:33, Leyne, Sean escreveu: >> Alexandre, >> >> While I completely agree with your points. I read the OP as meaning >> that they are running out of transactions ID (given the current 2GB >> limit) -- they have an 800GB database, so a large number of >> transactions would/could be expected, so transactions ID could get >> exhausted. In that case, a backup/restore would be required, but a >> weekly cycle is excessive (it means that the db is handling more than >> 3550 transactions per second!!! Very unlikely). Sean > > Hi Sean ! > > I read that, but the point I was talking about is related to this phrase: > "It became clear to us that to keep the system going we have to run a > backup/restore every weekend because of slow downs when transaction > counts got too high" > > But I don't think that the transaction count getting too high would lead > to a server slowdown. > > > see you ! > > > > > ++ > > 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 > > > [Non-text portions of this message have been removed]
Re: [firebird-support] Re: Rename database command
Em 5/6/2013 13:33, Leyne, Sean escreveu: > Alexandre, > > While I completely agree with your points. I read the OP as meaning > that they are running out of transactions ID (given the current 2GB > limit) -- they have an 800GB database, so a large number of > transactions would/could be expected, so transactions ID could get > exhausted. In that case, a backup/restore would be required, but a > weekly cycle is excessive (it means that the db is handling more than > 3550 transactions per second!!! Very unlikely). Sean Hi Sean ! I read that, but the point I was talking about is related to this phrase: "It became clear to us that to keep the system going we have to run a backup/restore every weekend because of slow downs when transaction counts got too high" But I don't think that the transaction count getting too high would lead to a server slowdown. see you !
RE: [firebird-support] Re: Rename database command
Alexandre, > Em 4/6/2013 22:23, sir_wally_lewis escreveu: > > It became clear to us that to keep the system going we have to run a > backup/restore every weekend because of slow downs when transaction > counts got too high which means the system is barely keeping it's head > above water. > > > > I can't see a reason why transaction count (transaction ID) getting high would > make the engine slower... > The back-up/restore could in fact make you database faster, but it has > nothing to do with the transaction counter being reset. While I completely agree with your points. I read the OP as meaning that they are running out of transactions ID (given the current 2GB limit) -- they have an 800GB database, so a large number of transactions would/could be expected, so transactions ID could get exhausted. In that case, a backup/restore would be required, but a weekly cycle is excessive (it means that the db is handling more than 3550 transactions per second!!! Very unlikely). Sean
Re: [firebird-support] Re: Rename database command
Em 4/6/2013 22:23, sir_wally_lewis escreveu: > It became clear to us that to keep the system going we have to run a > backup/restore every weekend because of slow downs when transaction counts > got too high which means the system is barely keeping it's head above water. > I can't see a reason why transaction count (transaction ID) getting high would make the engine slower... The back-up/restore process would reorganize the data (no record versions, balanced indices, indices statistics up to date, no fragmented tables/records, etc.) and since it's a new database, the transaction ID would be reset, but the fact that the transaction ID is a low number has nothing to do with server slowness... I think you are looking at the wrong numbers... If you run gstat when your database became slow, you could find the real culprit (long running transaction, lot of record versions, fragmented tables, etc.) The back-up/restore could in fact make you database faster, but it has nothing to do with the transaction counter being reset. see you !
Re: [firebird-support] Re: Rename database command
On Sun, Jun 2, 2013 at 7:06 PM, sir_wally_lewis wrote: > > > I have in the past also put forward a database level suggestion by > enhancements to "gbak" > > The suggestion is: > > 1. gbak run with two new options to "-xbackup op.fbk" "-xrestore op2.fdb" > 2. this option backups up to op.fbk. > 3. Also server keeps a separate file for new changes to database during > process. > 4. server then restores op2.fdb and at the end attaches all new changes to > op2.fdb. > 5. shuts down live database and replaces it. > > I think it can be done. > That's one of the challenges that caused nbackup to be created. As you know, gbak restore creates new, empty database, then creates most of the metadata for the database, then loads the data from the backup file, then finishes metadata. The metadata is split to avoid problems with foreign key and other constraints during the data load phase. That means that all the simple ways to identify tables and records (i.e. table and record ids) change. I'm not convinced that you can replay a series of inserts, updates, and deletes and guarantee that the results will be the same as the original. Yes, I know that's how MySQL does replication - it keeps a statement log and applies it at the remote site. I spent considerable time trying to make that work with MVCC - and was never convinced that it was right there either. Yes, it can be made to work if the database and applications are designed to work from a statement log, but if that feature were to be part of gbak, it would have to work every time with any database. My advice to those who want a backup that's up to the minute is to use nbackup. Use gbak for archival storage and to avoid major headaches like running out of transaction ids. However, if you have a critical site and need reliability, replicate. Good luck, Ann IBPhoenix, your source for Firebird support [Non-text portions of this message have been removed]
Re: [firebird-support] Re: Rename database command
Not fool proof at all really. DB needs to be off line from the second the backup starts until the restored file is renamed. You need a better way Alan On 01/06/2013 4:22 PM, "sir_wally_lewis" wrote: > current practice is. > > - backup database > - restore to new name. > - rename old database ( can't be done by firebird ) > - rename new name to production name. ( can't be done by firebird ) > > any step fails process is failed. > > This allows for 100 percent fool proof backup restore. > > > > > > > > ++ > > 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 > > > > [Non-text portions of this message have been removed]
