On Wed, Oct 28, 2015 at 6:43 PM, setysvar setysvar@... [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