RE: [firebird-support] High CPU use after restore
When we restore databases following repair work, I have sometimes had indexes not reactivated where there is a broken reference. I am not sure how Firebird determines what is a terminal broken reference and what isn’t because usually when there is missing reference the restore just fails with an error. Since we discovered this problem we now have a tool we wrote that checks all indexes and referential constraints are properly enabled before starting using the database. Cheers, Neil Pickles - n...@csy.co.uk From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] Sent: 31 October 2015 17:58 To: firebird-support@yahoogroups.com Subject: Re: [firebird-support] High CPU use after restore Hi First of all thanks for help on the way. After a lot of work (and waiting for restore,backups indexing...) I found out that it actually was as simple as an index that was inactive. The index must have been disabled during my first backup/restore as I am sure I did not do this myself. The index was a normal foreign key. However the table included a blob column. Anyone that has an idea why this was disable during backup/restore? Anyway one the results of this is that I know both have better understanding of firebird and also have better tools for dealing with situations where I need to take down the database. Best regards Jardar On Thu, Oct 29, 2015 at 11:03 AM, Helen Borrie hele...@iinet.net.au [firebird-support] wrote: Hello Jardar, Thursday, October 29, 2015, 9:55:20 PM, you wrote: The comment about "at least rebuild indexes" does that mean that I can expect this to work or do I risk that I still need to backup/restore? <mailto:hele...@iinet.net.au> A restore will rebuild all of the indexes. However, the indexes affected by the v.2.5.1 bug are those that are compound, i.e., multi-column, so they are the only ones you need to rebuild. A backup/restore will not be required. If you have multi-column primary, foreign or unique key constraints, note that ALTER INDEX INACTIVE will not work on a constraint index; but ALTER INDEX ACTIVE will rebuild those anyway. Do I need to say, do this job whilst you have exclusive access as an administrator of the database or as the owner of the affected tables. Helen -- Jardar Maatje Nortek Data Services AS C.J. Hambros Plass 2C 0164 Oslo tlf: +47 95184034
Re: [firebird-support] High CPU use after restore
Hi First of all thanks for help on the way. After a lot of work (and waiting for restore,backups indexing...) I found out that it actually was as simple as an index that was inactive. The index must have been disabled during my first backup/restore as I am sure I did not do this myself. The index was a normal foreign key. However the table included a blob column. Anyone that has an idea why this was disable during backup/restore? Anyway one the results of this is that I know both have better understanding of firebird and also have better tools for dealing with situations where I need to take down the database. Best regards Jardar On Thu, Oct 29, 2015 at 11:03 AM, Helen Borrie hele...@iinet.net.au [firebird-support] wrote: > > > Hello Jardar, > > Thursday, October 29, 2015, 9:55:20 PM, you wrote: > > > > > The comment about "at least rebuild indexes" does that mean that I can > expect this to work or do I risk that I still need to backup/restore? > > A restore will rebuild all of the indexes. > However, the indexes affected by the v.2.5.1 bug are those that are > compound, i.e., multi-column, so they are the only ones you need to > rebuild. A backup/restore will not be required. > > If you have multi-column primary, foreign or unique key constraints, note > that ALTER INDEX INACTIVE will not work on a constraint index; > but ALTER INDEX ACTIVE will rebuild those anyway. > > Do I need to say, do this job whilst you have exclusive access as an > administrator of the database or as the owner of the affected tables. > > Helen > > > -- Jardar Maatje Nortek Data Services AS C.J. Hambros Plass 2C 0164 Oslo tlf: +47 95184034
Re: [firebird-support] High CPU use after restore
Re: [firebird-support] High CPU use after restore
Hi again The comment about "at least rebuild indexes" does that mean that I can expect this to work or do I risk that I still need to backup/restore? Best regards jardar On Thu, Oct 29, 2015 at 9:52 AM, Mark Rotteveel m...@lawinegevaar.nl [firebird-support] wrote: > > > On Thu, 29 Oct 2015 07:43:29 +0100, "Jardar Maatje > jardar.maa...@nds.nortek.no [firebird-support]" > wrote: > > Hi again, > > > > Sounds like a reasonable cause for the problem. So now is my question, > what > > is the best way to upgrade. > > My current setup is as follows: > > I have two databases, one for raw data (RAW), and the second one for > > decoded data (MAIN). I need to keep the RAW db with as high availability > as > > possible (or we will loose incoming data). For the MAIN db I have more > > slack regarding downtime (weekends/nights). They both have ODS version > > 11.2. > > > > The best would be if I could upgrade to the latest version (2.5.4) of > the > > server db without the need to do backup/restore as this takes a lot of > > time. > > You will need to backup and restore or at least rebuild the indexes, see > the release notes for 2.5.3: > > http://www.firebirdsql.org/file/documentation/release_notes/html/rlsnotes254.html#notes-253 > > Mark > > -- Jardar Maatje Nortek Data Services AS C.J. Hambros Plass 2C 0164 Oslo tlf: +47 95184034
Re: [firebird-support] High CPU use after restore
On Thu, 29 Oct 2015 07:43:29 +0100, "Jardar Maatje jardar.maa...@nds.nortek.no [firebird-support]" wrote: > Hi again, > > Sounds like a reasonable cause for the problem. So now is my question, what > is the best way to upgrade. > My current setup is as follows: > I have two databases, one for raw data (RAW), and the second one for > decoded data (MAIN). I need to keep the RAW db with as high availability as > possible (or we will loose incoming data). For the MAIN db I have more > slack regarding downtime (weekends/nights). They both have ODS version > 11.2. > > The best would be if I could upgrade to the latest version (2.5.4) of the > server db without the need to do backup/restore as this takes a lot of > time. You will need to backup and restore or at least rebuild the indexes, see the release notes for 2.5.3: http://www.firebirdsql.org/file/documentation/release_notes/html/rlsnotes254.html#notes-253 Mark
Re: [firebird-support] High CPU use after restore
On Wed, 28 Oct 2015 23:59:59 +0100, "setysvar setys...@gmail.com [firebird-support]" wrote: > http://tracker.firebirdsql.org/browse/CORE-4601 is a report of a 60-time > improvement (from half a minute to half a second) with Firebird > 2.5.2/2.5.3 and Jaybird 2.2.5/2.1.6 when changing from blob to varchar, > I guess this could be the same problem as you observe. CORE-4601 is not a bug, it is an improvement request. The described behavior is inherent to the way blobs are handled in the wire protocol of Firebird, and I don't think it is the cause of this problem (it would also - as far as I know - not explain the high CPU use). Mark
Re: [firebird-support] High CPU use after restore
Hi again, Sounds like a reasonable cause for the problem. So now is my question, what is the best way to upgrade. My current setup is as follows: I have two databases, one for raw data (RAW), and the second one for decoded data (MAIN). I need to keep the RAW db with as high availability as possible (or we will loose incoming data). For the MAIN db I have more slack regarding downtime (weekends/nights). They both have ODS version 11.2. The best would be if I could upgrade to the latest version (2.5.4) of the server db without the need to do backup/restore as this takes a lot of time. Best regards Jardar 29. okt. 2015 00.00 skrev "setysvar setys...@gmail.com [firebird-support]" < firebird-support@yahoogroups.com>: > > > > Den 28.10.2015 20:56, skrev Jardar Maatje jardar.maa...@nds.nortek.no > [firebird-support]: > > Hi again > > A bit more details. I found the query the slows this down and it is a > query that includes blob data. If I adjust the query to cast the blob to > varchar first the query executes 30 timers faster. I will try to change my > queries to fix this. > > However I still wonder why this suddenly became a problem after restoring > the database. > > Best regards > > Jardar > > http://tracker.firebirdsql.org/browse/CORE-4601 is a report of a 60-time > improvement (from half a minute to half a second) with Firebird 2.5.2/2.5.3 > and Jaybird 2.2.5/2.1.6 when changing from blob to varchar, I guess this > could be the same problem as you observe. > > Could it be that your original database had never before been restored > with Firebird 2.5.1, but only with an older Firebird/InterBase version (or > never restored, but created with an older Firebird/InterBase version)? If > so, then you may have been protected against this particular bug (I'm just > guessing, I don't know if older versions have it or not), as well as the > bug (only existing in 2.5.1, 2.5.0 and 2.5.2 are OK) that I warned about > earlier today regarding indexes containing more than one field (which - I > think - can give you wrong results from some queries). > > Set > > >
Re: [firebird-support] High CPU use after restore
Den 28.10.2015 20:56, skrev Jardar Maatje jardar.maa...@nds.nortek.no [firebird-support]: Hi again A bit more details. I found the query the slows this down and it is a query that includes blob data. If I adjust the query to cast the blob to varchar first the query executes 30 timers faster. I will try to change my queries to fix this. However I still wonder why this suddenly became a problem after restoring the database. Best regards Jardar http://tracker.firebirdsql.org/browse/CORE-4601 is a report of a 60-time improvement (from half a minute to half a second) with Firebird 2.5.2/2.5.3 and Jaybird 2.2.5/2.1.6 when changing from blob to varchar, I guess this could be the same problem as you observe. Could it be that your original database had never before been restored with Firebird 2.5.1, but only with an older Firebird/InterBase version (or never restored, but created with an older Firebird/InterBase version)? If so, then you may have been protected against this particular bug (I'm just guessing, I don't know if older versions have it or not), as well as the bug (only existing in 2.5.1, 2.5.0 and 2.5.2 are OK) that I warned about earlier today regarding indexes containing more than one field (which - I think - can give you wrong results from some queries). Set
Re: [firebird-support] High CPU use after restore
Hi again A bit more details. I found the query the slows this down and it is a query that includes blob data. If I adjust the query to cast the blob to varchar first the query executes 30 timers faster. I will try to change my queries to fix this. However I still wonder why this suddenly became a problem after restoring the database. Best regards Jardar On Wed, Oct 28, 2015 at 7:39 PM, Jardar Maatje wrote: > Hi again > > Thanks for the feedback. I will try and investigate further based on your > feedback. So far I have updated the index statistics and the seems to make > minor improvements. I thought the indexes was recreated (and thus the > statistics) when it was restored. > > Regarding the .GDB i doubt this as the filename of the db is exactly the > same as before. > > When restoring I just used the default restore options, no special options > selected. > > Will keep you posted on the status (and probably ask more questions when I > fail to sole this). > > At the same time I have another question. The database is used for > collecting data and there almost no deletes/updates to the DB. And the > webpage is only retreiving data from it and not manipulating the content. > Is there a way to prevent the transaction ID's to increment for the > readonly requests? > > best regards > > Jardar > > > > On Wed, Oct 28, 2015 at 6:43 PM, setysvar setys...@gmail.com > [firebird-support] wrote: > >> >> >> Hei Jardar, I have never even heard of Nortek before! >> >> >We have DB of about 40GB where transaction counter exceeded max and we >> had to backup and restore to get the db back up an running. >> >However after doing this we have had trouble where the DB consumes 25% >> CPU (100% on one core). This typically happens when accessing >> >the DB from IIS web pages with quite a bit of transactions. However >> normally this work very fine without this 100% CPU core load. >> > >> >I find it hard to detect the real cause of the problem and also >> surprised that this should happen after a restore. >> >One thing to mention that after the restore I had to recreate store >> procedures and trigger. From documentation on web I got the >> >impression that this was caused by a encoding issue of text. >> >> Did you modify the procedures/triggers at all? Recent changes would be >> suspicious, and I agree that a restore being the cause doesn't make much >> sense. And how did you restore your database? There ought to be some >> free space on each page when restoring, I hope you didn't use the >> -USE_ALL_SPACE option (unless the database is read-only). Has anything >> else changed with the restore, e.g. the sweep interval (I don't know >> where the sweep interval is stored, just that gfix can set it)? And are >> there any queries being held open for a prolonged period that didn't >> exist before? >> >> >Another thing to mention is that we are running the DB on Win 2008 >> Server and the fb version is 2.5.1. We also have the .GDB extension >> >on the database, but since this has not been a trouble before, I doubt >> that this is the problem. >> >> GDB can matter since it is (or was?) monitored by Windows >> (https://msdn.microsoft.com/en-us/library/aa378870(VS.85).aspx), making >> Windows make a backup of the file when connecting. However, it is a long >> time since I last heard of someone having an issue with this, and have >> no clue whether or not it is still of importance with Win 2008. >> Moreover, I presume you just restored the database, not reinstall Windows. >> >> 2.5.1 might or might not be OK, since it contains a bug regarding >> multi-field indexes. Here's what I found in the release notes for 2.5.2 >> >> "Warning re Databases Created or Restored under Firebird 2.5.1 >> All users upgrading from Firebird 2.5.1 to a higher sub-release are >> strongly advised to migrate databases using gbak backup/restore. If this >> is impracticable, at least rebuild all compound indices in the databases >> being migrated. >> Databases being upgraded from older Firebird versions (ODS 11.1 and >> lower) or v.2.5.0 are not affected by this regression." >> >> We (Kreftregisteret) also use Fb 2.5.1 (or used, I wonder if we upgraded >> to 2.5.3 or 2.5.4 a while ago), but we almost exclusively use single >> field indexes and has never had serious problems caused by the bug (I >> think I experienced duplicates with SELECT , , >> COUNT(*) FROM GROUP BY 1, 2 due to this bug when we had a >> combined index on FieldName1 and FieldName2 and one of the fields were >> , but am far from certain this bug was the culprit). >> >> >What I have done so far is to: >> >* validate the db >> >* run manual sweep on it >> >* restarted fbserver >> >* restarted server >> >* restarted IIS >> > >> >Right now I am starting each of the websites/services that are >> accessing the DB. >> > >> >Any suggestions on how t >> >feel like I am just guessing about the cause and applying different >> potential fixes at "random". >> >> One "problem" with Firebird, is that on
Re: [firebird-support] High CPU use after restore
Hi again Thanks for the feedback. I will try and investigate further based on your feedback. So far I have updated the index statistics and the seems to make minor improvements. I thought the indexes was recreated (and thus the statistics) when it was restored. Regarding the .GDB i doubt this as the filename of the db is exactly the same as before. When restoring I just used the default restore options, no special options selected. Will keep you posted on the status (and probably ask more questions when I fail to sole this). At the same time I have another question. The database is used for collecting data and there almost no deletes/updates to the DB. And the webpage is only retreiving data from it and not manipulating the content. Is there a way to prevent the transaction ID's to increment for the readonly requests? best regards Jardar On Wed, Oct 28, 2015 at 6:43 PM, setysvar setys...@gmail.com [firebird-support] wrote: > > > Hei Jardar, I have never even heard of Nortek before! > > >We have DB of about 40GB where transaction counter exceeded max and we > had to backup and restore to get the db back up an running. > >However after doing this we have had trouble where the DB consumes 25% > CPU (100% on one core). This typically happens when accessing > >the DB from IIS web pages with quite a bit of transactions. However > normally this work very fine without this 100% CPU core load. > > > >I find it hard to detect the real cause of the problem and also > surprised that this should happen after a restore. > >One thing to mention that after the restore I had to recreate store > procedures and trigger. From documentation on web I got the > >impression that this was caused by a encoding issue of text. > > Did you modify the procedures/triggers at all? Recent changes would be > suspicious, and I agree that a restore being the cause doesn't make much > sense. And how did you restore your database? There ought to be some > free space on each page when restoring, I hope you didn't use the > -USE_ALL_SPACE option (unless the database is read-only). Has anything > else changed with the restore, e.g. the sweep interval (I don't know > where the sweep interval is stored, just that gfix can set it)? And are > there any queries being held open for a prolonged period that didn't > exist before? > > >Another thing to mention is that we are running the DB on Win 2008 > Server and the fb version is 2.5.1. We also have the .GDB extension > >on the database, but since this has not been a trouble before, I doubt > that this is the problem. > > GDB can matter since it is (or was?) monitored by Windows > (https://msdn.microsoft.com/en-us/library/aa378870(VS.85).aspx), making > Windows make a backup of the file when connecting. However, it is a long > time since I last heard of someone having an issue with this, and have > no clue whether or not it is still of importance with Win 2008. > Moreover, I presume you just restored the database, not reinstall Windows. > > 2.5.1 might or might not be OK, since it contains a bug regarding > multi-field indexes. Here's what I found in the release notes for 2.5.2 > > "Warning re Databases Created or Restored under Firebird 2.5.1 > All users upgrading from Firebird 2.5.1 to a higher sub-release are > strongly advised to migrate databases using gbak backup/restore. If this > is impracticable, at least rebuild all compound indices in the databases > being migrated. > Databases being upgraded from older Firebird versions (ODS 11.1 and > lower) or v.2.5.0 are not affected by this regression." > > We (Kreftregisteret) also use Fb 2.5.1 (or used, I wonder if we upgraded > to 2.5.3 or 2.5.4 a while ago), but we almost exclusively use single > field indexes and has never had serious problems caused by the bug (I > think I experienced duplicates with SELECT , , > COUNT(*) FROM GROUP BY 1, 2 due to this bug when we had a > combined index on FieldName1 and FieldName2 and one of the fields were > , but am far from certain this bug was the culprit). > > >What I have done so far is to: > >* validate the db > >* run manual sweep on it > >* restarted fbserver > >* restarted server > >* restarted IIS > > > >Right now I am starting each of the websites/services that are > accessing the DB. > > > >Any suggestions on how t > >feel like I am just guessing about the cause and applying different > potential fixes at "random". > > One "problem" with Firebird, is that one or two (very) bad queries can > be enough make everything come to a halt. However, this would only be > another random guess. What I would recommend you to try though, is to > run a query similar to SELECT * FROM MON$STATEMENTS WHERE MON$TIMESTAMP > IS NOT NULL ORDER BY MON$TIMESTAMP when things are slow. This could(*) > give you the queries currently running on the server and then you could > take the queries that ran when the problem manifested itself, prepare > them and see if the generated PLAN seems sensible or not. If you don't
Re: [firebird-support] High CPU use after restore
Hei Jardar, I have never even heard of Nortek before! >We have DB of about 40GB where transaction counter exceeded max and we had to backup and restore to get the db back up an running. >However after doing this we have had trouble where the DB consumes 25% CPU (100% on one core). This typically happens when accessing >the DB from IIS web pages with quite a bit of transactions. However normally this work very fine without this 100% CPU core load. > >I find it hard to detect the real cause of the problem and also surprised that this should happen after a restore. >One thing to mention that after the restore I had to recreate store procedures and trigger. From documentation on web I got the >impression that this was caused by a encoding issue of text. Did you modify the procedures/triggers at all? Recent changes would be suspicious, and I agree that a restore being the cause doesn't make much sense. And how did you restore your database? There ought to be some free space on each page when restoring, I hope you didn't use the -USE_ALL_SPACE option (unless the database is read-only). Has anything else changed with the restore, e.g. the sweep interval (I don't know where the sweep interval is stored, just that gfix can set it)? And are there any queries being held open for a prolonged period that didn't exist before? >Another thing to mention is that we are running the DB on Win 2008 Server and the fb version is 2.5.1. We also have the .GDB extension >on the database, but since this has not been a trouble before, I doubt that this is the problem. GDB can matter since it is (or was?) monitored by Windows (https://msdn.microsoft.com/en-us/library/aa378870(VS.85).aspx), making Windows make a backup of the file when connecting. However, it is a long time since I last heard of someone having an issue with this, and have no clue whether or not it is still of importance with Win 2008. Moreover, I presume you just restored the database, not reinstall Windows. 2.5.1 might or might not be OK, since it contains a bug regarding multi-field indexes. Here's what I found in the release notes for 2.5.2 "Warning re Databases Created or Restored under Firebird 2.5.1 All users upgrading from Firebird 2.5.1 to a higher sub-release are strongly advised to migrate databases using gbak backup/restore. If this is impracticable, at least rebuild all compound indices in the databases being migrated. Databases being upgraded from older Firebird versions (ODS 11.1 and lower) or v.2.5.0 are not affected by this regression." We (Kreftregisteret) also use Fb 2.5.1 (or used, I wonder if we upgraded to 2.5.3 or 2.5.4 a while ago), but we almost exclusively use single field indexes and has never had serious problems caused by the bug (I think I experienced duplicates with SELECT , , COUNT(*) FROM GROUP BY 1, 2 due to this bug when we had a combined index on FieldName1 and FieldName2 and one of the fields were , but am far from certain this bug was the culprit). >What I have done so far is to: >* validate the db >* run manual sweep on it >* restarted fbserver >* restarted server >* restarted IIS > >Right now I am starting each of the websites/services that are accessing the DB. > >Any suggestions on how t >feel like I am just guessing about the cause and applying different potential fixes at "random". One "problem" with Firebird, is that one or two (very) bad queries can be enough make everything come to a halt. However, this would only be another random guess. What I would recommend you to try though, is to run a query similar to SELECT * FROM MON$STATEMENTS WHERE MON$TIMESTAMP IS NOT NULL ORDER BY MON$TIMESTAMP when things are slow. This could(*) give you the queries currently running on the server and then you could take the queries that ran when the problem manifested itself, prepare them and see if the generated PLAN seems sensible or not. If you don't find anything, well, then you've at least made bad queries less likely to be your culprit. HTH, Set (Svein Erling) (*) I know it gives me the queries, but my experience is exclusively using SuperServer on smaller databases (up to just a few GB) and normally connecting as SYSDBA.
Re: [firebird-support] High CPU use after restore
[firebird-support] High CPU use after restore
Hi We have DB of about 40GB where transaction counter exceeded max and we had to backup and restore to get the db back up an running. However after doing this we have had trouble where the DB consumes 25% CPU (100% on one core). This typically happens when accessing the DB from IIS web pages with quite a bit of transactions. However normally this work very fine without this 100% CPU core load. I find it hard to detect the real cause of the problem and also surprised that this should happen after a restore. One thing to mention that after the restore I had to recreate store procedures and trigger. From documentation on web I got the impression that this was caused by a encoding issue of text. Another thing to mention is that we are running the DB on Win 2008 Server and the fb version is 2.5.1. We also have the .GDB extension on the database, but since this has not been a trouble before, I doubt that this is the problem. What I have done so far is to: * validate the db * run manual sweep on it * restarted fbserver * restarted server * restarted IIS Right now I am starting each of the websites/services that are accessing the DB. Any suggestions on how to proceed? I am also interested in understanding better how I can better debug these situations. Right now I feel like I am just guessing about the cause and applying different potential fixes at "random". -- Jardar Maatje Nortek Data Services AS C.J. Hambros Plass 2C 0164 Oslo tlf: +47 95184034
