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 <jardar.maa...@nds.nortek.no> 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] <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 > > -- Jardar Maatje Nortek Data Services AS C.J. Hambros Plass 2C 0164 Oslo tlf: +47 95184034
