Re: [firebird-support] Periodic database slowdown - troubleshooting steps?
Hi Bob, > I have an FB 2.1.5 Classic server running on a Windows 2003 server, > with a single hard drive for the operating system, and a 3 disk raid 5 > array for the database. We have one database on this machine, which > is a dialect 1 database that was started on IB6.0 many years ago, > currently at 90GB. We have sweep disabled, and each night run gbak, > gfix –sweep, as well as reindex all tables via a script. > > > > The database has very little OLTP, and is mostly used for reporting > and serving web pages to internal business users. We do alot of ETL > starting very early each morning, and create a mix of scheduled > reports as well as allowing users to specify parameters to create > pre-designed reports in an ad-hoc manner. > > > > Once or twice per month, the system slows down tremendously. One ETL > process typically runs at a pace of about 1000 records per 10 seconds. > During these slow periods, the same ETL will run 1000 transactions per > 60-80 seconds. When processing a file with 1mil+ records, this slow > down costs us hours. > > > > I have not been able to determine the reason for these slow periods. > They do not coincide with higher cpu or disk usage – most of the time > I'm seeing very little usage of anything – disk/cpu/network/memory. I > do see more connections to the database during these periods – > typically we have 10 to 15 connections, and the number may double > during the problem times. This is due to the fact that the reports > that users are requesting are taking longer to run, and our > pooled-connection application server or web server creates more > connections to satisfy new user requests. > > > > I do see more queries running by the time I am notified of the > problem. Again though, this is a coincidence of the slowdown and not > the cause – queries are taking longer to run, and therefore we have > more chance of overlapping user requests than usual. > > > > Most of the time we need to reboot once or even twice to fix the > problem. That is not a viable long-term solution though, and I'm > looking for more ideas to determine what may be happening. Any ideas > would be most helpful. > > > > I have included gstat –h output of the database as it is suffering > from the issue below: > > > > Database header page information: > > Flags 0 > > Checksum12345 > > Generation 43136192 > > Page size 8192 > > ODS version 11.1 > > Oldest transaction 40789582 > > Oldest active 41467442 > > Oldest snapshot 41467442 > > Next transaction42431040 > > Bumped transaction 1 > > Sequence number 0 > > Next attachment ID 705070 > > Implementation ID 16 > > Shadow count0 > > Page buffers2048 > > Next header page0 > > Database dialect1 > > Creation date May 2, 2009 22:22:39 > > Attributes force write, no reserve > > > > Variable header data: > > Sweep interval: 0 > > *END* 1): The most obvious thing according to the header page is a very large gap between the oldest active transaction and the next transaction. This means, you have a long-running/stuck transaction. If you are lucky, you can go into the MON$TRANSACTIONS table and check out if you find the MON$TRANSACTION_ID for 41467442. "Lucky", because I saw occasions where the OAT according to the header page isn't available in the monitoring tables. Perhaps some client (ETL tool?) doesn't behave well from a client transaction management POV. 2): Although you say you aren't in an OLTP pattern here, I guess due to ETL, it isn't a read-only database, right? If so, running the database in "no reserve" mode isn't a good idea, because, basically you are telling Firebird to not reserve space for back record version on the same data page as the primary record version. This results in more reads from disk, especially in a reporting scenario where you have long-running read-write transactions/queries, where concurrent read/write requests generate a longer back record chain until it can be removed via co-operative GC (the only GC mode in CS). While gfix can be used to remove the "no reserve" thing, this doesn't change the layout of already allocated data pages. If you have a maintainence window, I would go with a backup/restore cycle to re-build the database with "reserve" (the default, btw, thus you don't have to provide anything special for that) from scratch. Might be a challenge for a 90GB database and a small maintenance window. A few tricks to shorten the offline window: * Run both, backup and restore through the services API. When using gbak, this can be done via the -service switch. This results in not going through the TCP st
Re: [firebird-support] Periodic database slowdown - troubleshooting steps?
Hello Bob, > currently at 90GB. We have sweep disabled, and each night run gbak, > gfix --sweep, as well as reindex all tables via a script. > Seems like you do correct things, but do you check that sweep is really successful? Look at the transactions' markers log in IBTM (IBSurgeon Transaction Monitor), gathered from Profitmed database (120Gb, 400 clients, 2mln transactions per 12 hours): http://www.ib-aid.com/images/transactions_maintenance.gif You can see gfix -sweep operations 2 times per day (~6am and ~21am), which were successfully performed: there are 2 moments when all transactions markers (Oldest, OAT, OST and NEXT) are equal (Next is +1). This is one of critical things to watch for if you really want to sweep your database. Regards, Alexey Kovyazin IBSurgeon www.ib-aid.com [Non-text portions of this message have been removed]
RE: [firebird-support] Periodic database slowdown - troubleshooting steps?
Alexey - >> Alexey Kovyazin [mailto:a...@ib-aid.com] >> Seems like you do correct things, but do you check that sweep is really successful? >> >> Look at the transactions' markers log in IBTM (IBSurgeon Transaction Monitor), gathered from Profitmed >> database (120Gb, 400 clients, 2mln transactions per 12 hours): >> http://www.ib-aid.com/images/transactions_maintenance.gif >>You can see gfix -sweep operations 2 times per day (~6am and ~21am), which were successfully performed: >> there are 2 moments when all transactions markers (Oldest, OAT, OST and NEXT) are equal (Next is +1). >> >>This is one of critical things to watch for if you really want to sweep your database. Is there any way to tell if the sweep was successful other than all of the markers matching? Is there any way to tell why a sweep would have failed? I'm running the sweep from a batch file, but never thought to check the errorlevel after completion... Thanks, Bob M.. [Non-text portions of this message have been removed]
RE: [firebird-support] Periodic database slowdown - troubleshooting steps?
Thomas - -Original Message- From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Thomas Steinmaurer >> 1): The most obvious thing according to the header page is a very large >> gap between the oldest active transaction and the next transaction. This >> means, you have a long-running/stuck transaction. If you are lucky, you >> can go into the MON$TRANSACTIONS table and check out if you find the >> MON$TRANSACTION_ID for 41467442. "Lucky", because I saw occasions where >> the OAT according to the header page isn't available in the monitoring >> tables. Perhaps some client (ETL tool?) doesn't behave well from a >> client transaction management POV. No such luck - 42450558 is the earliest of the 29 records listed. >> 2): Although you say you aren't in an OLTP pattern here, I guess due to >> ETL, it isn't a read-only database, right? If so, running the database >> in "no reserve" mode isn't a good idea, because, basically you are >> telling Firebird to not reserve space for back record version on the >> same data page as the primary record version. This results in more reads >> from disk, especially in a reporting scenario where you have >> long-running read-write transactions/queries, where concurrent >> read/write requests generate a longer back record chain until it can be >> removed via co-operative GC (the only GC mode in CS). I have definitely never used the "no reserve" option. I wonder if it was a default on an earlier version of the server that just carried over. I'll use gfix to use reserve to at least deal with those tables that are emptied and overwritten regularly. >> While gfix can be used to remove the "no reserve" thing, this doesn't change the layout of >> already allocated data pages. If you have a maintainence window, I would >> go with a backup/restore cycle to re-build the database with "reserve" >> (the default, btw, thus you don't have to provide anything special for >> that) from scratch. Might be a challenge for a 90GB database and a small >> maintenance window. That has been a problem for a very long time. Right now, a full backup/restore cycle is taking more than 24 hours, and at best we only have a 12 hour window at best on a Sunday. Hence the May 2009 creation date of the current DB. >> A few tricks to shorten the offline window: >>* Run both, backup and restore through the services API. When using >> gbak, this can be done via the -service switch. This results in not >> going through the TCP stack, which can improve performance a lot. That's a good trick, but since we are backing up to a seperate server the gbak -b can't use the service switch. Since we are restoring locally on the second server I could use that switch, but instead we are using the embedded gbak. Using embedded is definitely faster than regular gbak -c, but I'm curious as to whether -service is faster. I would assume that they are probably about the same. >> * Backup the database with the -g option, because this suppress garbage >> collection in the source database This is standard practice when planning on replacing the database. >> * If enough RAM is available, restore the database with a MUCH higher >> page buffers value as 2048, because this can speed up index creation >> during a restore a lot. E.g. 10, with a page size of 8K, this means >> ~800MB RAM for the page cache for this single restore connection only. >> Use it with caution and don't forget to set it to the original value >> after the restore!!! Good suggestion, I'm going to try that tonight. >> * If you have a spare SSD, even if it is only a cheap consumer SSD, make >> use of it for both, backup and restore. Unfortunately it's a corporate datacenter with fixed configurations, so no goodies like SSD's. >> 3:) As you are talking about reporting, make use of read-only >> transactions. Even better would be a combination of read-only >> transaction in read committed isolation mode, but read committed might >> be problematic in a reporting scenario, when you need a stable snapshot >> of the underlaying data for the period of report generation. Very good points! >> 4:) Keep an eye on the fb_lock_print output to possibly increase the >> default hash slot value. >> 5:) Try to run gfix -sweep at a time, when there is zero or close to >> zero load. Yes, we run it at night just before the backup kicks off. Unfortunately, there is overlap because the sweep usually takes about 2.5 hours. Thanks, Bob M.. ++ 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 <*> To visit your group on the web, go to: http://gro
Re: [firebird-support] Periodic database slowdown - troubleshooting steps?
Hello Bob, >>> 1): The most obvious thing according to the header page is a very > large >>> gap between the oldest active transaction and the next transaction. > This >>> means, you have a long-running/stuck transaction. If you are lucky, > you >>> can go into the MON$TRANSACTIONS table and check out if you find > the >>> MON$TRANSACTION_ID for 41467442. "Lucky", because I saw occasions > where >>> the OAT according to the header page isn't available in the > monitoring >>> tables. Perhaps some client (ETL tool?) doesn't behave well from a >>> client transaction management POV. > > No such luck - 42450558 is the earliest of the 29 records listed. Haven't got an explanation why OAT isn't visible in the mon tables, but it could be due to a crashed server or possibly a transaction in limbo, in case you are using distributed transactions. You can use gfix to check if there are transactions in limbo. Other than that, a backup/restore will cure this situation. >>> 2): Although you say you aren't in an OLTP pattern here, I guess > due to >>> ETL, it isn't a read-only database, right? If so, running the > database >>> in "no reserve" mode isn't a good idea, because, basically you are >>> telling Firebird to not reserve space for back record version on > the >>> same data page as the primary record version. This results in more > reads >>> from disk, especially in a reporting scenario where you have >>> long-running read-write transactions/queries, where concurrent >>> read/write requests generate a longer back record chain until it > can be >>> removed via co-operative GC (the only GC mode in CS). > > I have definitely never used the "no reserve" option. I wonder if it > was a default on an earlier version of the server that just carried > over. I'll use gfix to use reserve to at least deal with those tables > that are emptied and overwritten regularly. No reserve, isn't and never wasn't the default. It can be changed via gfix, during a gbak restore or even through client applications and the services API. Are you using IBObjects by any chance? >>> While gfix can be used to remove the "no reserve" thing, this > doesn't change the layout of >>> already allocated data pages. If you have a maintainence window, I > would >>> go with a backup/restore cycle to re-build the database with > "reserve" >>> (the default, btw, thus you don't have to provide anything special > for >>> that) from scratch. Might be a challenge for a 90GB database and a > small >>> maintenance window. > > That has been a problem for a very long time. Right now, a full > backup/restore cycle is taking more than 24 hours, and at best we only > have a 12 hour window at best on a Sunday. Hence the May 2009 > creation date of the current DB. Ok. Another scenario/option could be: * Disconnect all users from the database * Install a trigger-based logging mechanism (I won't do a product plug here *g*) * Start the gbak backup * Allow users to connect again This way, all DML after the backup has been started will be captured in the source database and can be re-applied on the restored database at a later point. This could cut downtime a lot. Although, never did that in production, but it should work. >>> A few tricks to shorten the offline window: > >>> * Run both, backup and restore through the services API. When using >>> gbak, this can be done via the -service switch. This results in not > >>> going through the TCP stack, which can improve performance a lot. > > That's a good trick, but since we are backing up to a seperate server > the gbak -b can't use the service switch. Since we are restoring > locally on the second server I could use that switch, but instead we > are using the embedded gbak. Using embedded is definitely faster than > regular gbak -c, but I'm curious as to whether -service is faster. I > would assume that they are probably about the same. Don't have any concrete numbers, but to improve the backup process, you should backup to the local server, if this is possible, and then move the backup file to the remote server. >>> * Backup the database with the -g option, because this suppress > garbage >>> collection in the source database > > This is standard practice when planning on replacing the database. > > >>> * If enough RAM is available, restore the database with a MUCH > higher >>> page buffers value as 2048, because this can speed up index > creation >>> during a restore a lot. E.g. 10, with a page size of 8K, this > means >>> ~800MB RAM for the page cache for this single restore connection > only. >>> Use it with caution and don't forget to set it to the original > value >>> after the restore!!! > > Good suggestion, I'm going to try that tonight. > > >>> * If you have a spare SSD, even if it is only a cheap consumer SSD, > make >>> use of it for both, backup and restore. > > Unfortunately it's a corporate datacenter with fixed configurations, > so no goodies like SSD's. > > >>> 3:) As you are
Re: [firebird-support] Periodic database slowdown - troubleshooting steps?
Hello Bob, > Is there any way to tell if the sweep was successful other than all of > the markers matching? Is there any way to tell why a sweep would have > failed? > No. You should manually check transactions' markers difference, or use tool like FBDataGuard which can check it in desired time and send warning. > > I'm running the sweep from a batch file, but never thought to check > the errorlevel after completion... > > errorlevel is not related with sweep results. Regards, Alexey Kovyazin IBSurgeon (www.ib-aid.com) [Non-text portions of this message have been removed]
RE: [firebird-support] Periodic database slowdown - troubleshooting steps?
Alexey >> >> From: Alexey Kovyazin [mailto:a...@ib-aid.com] >> >> >> Is there any way to tell if the sweep was successful other than all of the markers matching? Is there any way to tell why a sweep would have failed? >> >> No. You should manually check transactions' markers difference, or use >>tool like FBDataGuard which can check it in desired time and send warning. And what happens if the transaction markers don't align after a sweep? Bob M.. [Non-text portions of this message have been removed] ++ 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 <*> 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: http://docs.yahoo.com/info/terms/
Re: [firebird-support] Periodic database slowdown - troubleshooting steps?
Hello Bob, >> >> Is there any way to tell if the sweep was successful other than all of the markers > > matching? Is there any way to tell why a sweep would have failed? > > >> > >> No. You should manually check transactions' markers difference, or use > > >>tool like FBDataGuard which can check it in desired time and send warning. > > And what happens if the transaction markers don't align after a sweep? > > It's an indication that you have tons of garbage in your database, and you'll have slowness soon. To have more details about garbage, you can gather gstat -r statistics after failed sweep and analyse it - in text form or graphically with IBAnalyst. Regards, Alexey Kovyazin IBSurgeon (www.ib-aid.com) [Non-text portions of this message have been removed]
RE: [firebird-support] Periodic database slowdown - troubleshooting steps?
Here's a related question for you - as I looked at our script for doing nightly backups, I see a note that says: "do not use garbage collection (gbak -g) since we run a manual sweep every night" Do you know if that's true - we don't need to do garbage collection via gbak if we are running gfix -sweep? Thanks, Bob M.. ++ 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 <*> 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: http://docs.yahoo.com/info/terms/
Re: [firebird-support] Periodic database slowdown - troubleshooting steps?
> Here's a related question for you - as I looked at our script for > doing nightly backups, I see a note that says: > > "do not use garbage collection (gbak -g) since we run a manual sweep > every night" > > Do you know if that's true - we don't need to do garbage collection > via gbak if we are running gfix -sweep? Correct. If the backup acts as an immediate replacement for the source database after a restore, you also don't need to run a sweep on the source database. -- With regards, Thomas Steinmaurer http://www.upscene.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 <*> 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: http://docs.yahoo.com/info/terms/
RE: [firebird-support] Periodic database slowdown - troubleshooting steps?
>>From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Thomas Steinmaurer >>> Here's a related question for you - as I looked at our script for v> doing nightly backups, I see a note that says: >>> >>> "do not use garbage collection (gbak -g) since we run a manual sweep >>> every night" >>> >>> Do you know if that's true - we don't need to do garbage collection >>> via gbak if we are running gfix -sweep? >> >>Correct. >> >>If the backup acts as an immediate replacement for the source database >>after a restore, you also don't need to run a sweep on the source database. But what if you are *not* replacing the database? ++ 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 <*> 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: http://docs.yahoo.com/info/terms/
Re: [firebird-support] Periodic database slowdown - troubleshooting steps?
>>> From: firebird-support@yahoogroups.com > [mailto:firebird-support@yahoogroups.com] On Behalf Of Thomas > Steinmaurer > Here's a related question for you - as I looked at our script for > v> doing nightly backups, I see a note that says: "do not use garbage collection (gbak -g) since we run a manual > sweep every night" Do you know if that's true - we don't need to do garbage > collection via gbak if we are running gfix -sweep? >>> >>> Correct. >>> >>> If the backup acts as an immediate replacement for the source > database >>> after a restore, you also don't need to run a sweep on the source > database. > > > But what if you are *not* replacing the database? If you intend to run a sweep on the source database after a backup, you don't need to garbage collect in the source database during a backup, thus using gbak -g is fine. -- With regards, Thomas Steinmaurer http://www.upscene.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 <*> 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: http://docs.yahoo.com/info/terms/
Re: [firebird-support] Periodic database slowdown - troubleshooting steps?
On Mon, Sep 17, 2012 at 10:17 PM, Bob Murdoch wrote: > > That has been a problem for a very long time. Right now, a full > backup/restore cycle is taking more than 24 hours, and at best we only > have a 12 hour window at best on a Sunday. Hence the May 2009 > creation date of the current DB. > > Sorry to jump into this late, but I had a thought (rare, but it happens). It appears that through some glitch, an old transaction is listed as active on the header page but is not actually present. Firebird is running in Classic mode, with one process per connection. The oldest active from the header page is not visible through the monitoring tables. Shutting down the database does not cure the problem - that's odd, which is why I'm suggesting that there's a glitch. What you might try is installing SuperServer configuration of the same version of Firebird using the instructions for installing multiple versions of Firebird. Shut down the Classic and start the SuperServer. If there's actually a client out there using the database, SuperServer won't be able to get the access it needs to the database file and that will suggest where to look for the missing active transaction. If it does start, it should reset the oldest active. Good luck, Ann [Non-text portions of this message have been removed]
Re: [firebird-support] Periodic database slowdown - troubleshooting steps?
On Tue, Sep 18, 2012 at 2:57 PM, Thomas Steinmaurer wrote: > > If you intend to run a sweep on the source database after a backup, you > don't need to garbage collect in the source database during a backup, > thus using gbak -g is fine. On the other hand, the time spent garbage collecting during the backup reduces the time the sweep takes, so the net diffference is probably not measurable. Cheers, Ann > [Non-text portions of this message have been removed]
RE: [firebird-support] Periodic database slowdown - troubleshooting steps?
-Original Message- From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Bob Murdoch Sent: Monday, September 17, 2012 10:27 AM > I have an FB 2.1.5 Classic server running on a Windows 2003 server, > with a single hard drive for the operating system, and a 3 disk raid 5 > array for the database. We have one database on this machine, which > is a dialect 1 database that was started on IB6.0 many years ago, > currently at 90GB. We have sweep disabled, and each night run gbak, > gfix –sweep, as well as reindex all tables via a script. > > Once or twice per month, the system slows down tremendously. One ETL > process typically runs at a pace of about 1000 records per 10 seconds. > During these slow periods, the same ETL will run 1000 transactions per > 60-80 seconds. When processing a file with 1mil+ records, this slow > down costs us hours. We have taken the database offline and did a full backup/restore/replace, removing "no reserve", and setting the page size to 16384. The hardware is raid 5 running on 3 scsi hard drives using NTFS 4k cluster size on Windows 2003 32bit with 4GB ram. Using IB Analyzer on the original database we found that there were a number of indexes with a depth of 4, and a number of tables showing fragmentation because of record size or blobs (not sure what that is about). In this setup, should using a page size of 16384 with the same 2048 page buffers do a better job than the previous 8192 page size? The ondisk size of the db is now 93.4GB vs the old one at 91.2GB, which I suppose is the 80% page fill and the larger pages. Thanks for looking through this. Bob M.. ++ 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 <*> 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: http://docs.yahoo.com/info/terms/
Re: [firebird-support] Periodic database slowdown - troubleshooting steps?
>> If you intend to run a sweep on the source database after a backup, you >> don't need to garbage collect in the source database during a backup, >> thus using gbak -g is fine. > > > On the other hand, the time spent garbage collecting during the backup > reduces > the time the sweep takes, so the net diffference is probably not measurable. True, but with omitting garbage collection during the backup, one can get a finished backup faster. Regards, Thomas
Re: [firebird-support] Periodic database slowdown - troubleshooting steps?
>> I have an FB 2.1.5 Classic server running on a Windows 2003 server, >> with a single hard drive for the operating system, and a 3 disk raid > 5 >> array for the database. We have one database on this machine, which >> is a dialect 1 database that was started on IB6.0 many years ago, >> currently at 90GB. We have sweep disabled, and each night run gbak, >> gfix –sweep, as well as reindex all tables via a script. >> >> Once or twice per month, the system slows down tremendously. One > ETL >> process typically runs at a pace of about 1000 records per 10 > seconds. >> During these slow periods, the same ETL will run 1000 transactions > per >> 60-80 seconds. When processing a file with 1mil+ records, this slow >> down costs us hours. > > We have taken the database offline and did a full > backup/restore/replace, removing "no reserve", and setting the page > size to 16384. The hardware is raid 5 running on 3 scsi hard drives > using NTFS 4k cluster size on Windows 2003 32bit with 4GB ram. Using > IB Analyzer on the original database we found that there were a number > of indexes with a depth of 4, and a number of tables showing > fragmentation because of record size or blobs (not sure what that is > about). Fragmentation also as a reason for the no reserve stuff, I guess. The worst thing I've ever seen regarding index depth was 6, but this was because the guy used a page size of 1K "per accident". ;-) You also should have a look on "useless" indices, basically large indices with only 1 unique value as the worst scenario. > In this setup, should using a page size of 16384 with the same 2048 > page buffers do a better job than the previous 8192 page size? You pretty much double max. RAM usage for the page cache and AFAIR you are using Classic, thus, this means PER connection. Have an eye on that that these settings don't exceed the available RAM getting into a swap scenario, especially with other concurrent RAM-intensive processes. > The > ondisk size of the db is now 93.4GB vs the old one at 91.2GB, which I > suppose is the 80% page fill and the larger pages. Correct. -- With regards, Thomas Steinmaurer http://www.upscene.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 <*> 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: http://docs.yahoo.com/info/terms/
