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] <firebird-support@yahoogroups.com> 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 <FieldName1>, <FieldName2>, > COUNT(*) FROM <Table> GROUP BY 1, 2 due to this bug when we had a > combined index on FieldName1 and FieldName2 and one of the fields were > <NULL>, 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. > > -- Jardar Maatje Nortek Data Services AS C.J. Hambros Plass 2C 0164 Oslo tlf: +47 95184034
