> Does anyone have a clue of what could cause this slowdown_and_back_to_normal
> thing ?

No clue, but your case seems similar to problems I encountered several months ago
and reported on fb-devel list (and have not time to create reproducible test case
since then yet). Here is copy:


========================================
From: "Ivan Prenosil" <Ivan.Prenosil@...>
To: <firebird-devel@...>
Subject: Strange speed problems.
Date: Tue, 11 Nov 2003 19:12:13 +0100

Hi all,
When trying to optimize import speed into my database
I encountered some strange results. I would appreciate
if somebody could explain what is going on. (I know this is more
support like question, but I believe firebird-devel is the only place
where I could get answer on this one.)

Short summary:
Firebird-1.5RC6, WinXP (no system restore "feature" etc.),
Single table with approx. 50000 rows, no indexes except primary key.

I need to update the database using file with approx. 50000 rows,
where only 60 rows are updated all other are identical with data already
stored in database. I wanted to compare speed of two updating SP
(called by application for each row of external data) that look like this:

A: INSERT (PK, ...) VALUES ...
WHEN unique_key_violation DO
UPDATE ... WHERE PK=:PK
(i.e. update all 50000 rows)

B: INSERT (PK, ...) VALUES ...
WHEN unique_key_violation DO
IF new_values_and_existing_values_are_not_identical THEN
UPDATE ... WHERE PK=:PK
(i.e. update only 60 rows)

After two days of playing with Altering procedure from A to B and from B to A,
sweeping, restargin server etc. I am still confused by results and have two questions:


1) Why the sweep after metadata change is so sloooow ?

Here are some times to compare
- UPDATE tab SET X=X; ... 4 sec.
- sweep after that update ... less than 2 sec.
- sweep after ALTER PROCEDURE ... 14 sec !!!

Even with fresh new database, containing only single SP, the times are:
- sweep ... unmeasurable
- sweep after ALTER PROCEDURE ... 4 sec !



2) Why the procedure B (unlike A) is much slower on swept database (or garbage collected) ?

Here are times of some operations
(each row represents separate operation using extra connection, without overlapping)

- update A (i.e. calling procedure A 50000x times) ... 45 sec
- update A ... 45 sec
- update A ... 45
- sweep ... 2
- update A ... 45 << o.k. still the same time after sweep
- update A ... 45
- ALTER PROCEDURE A->B
- update B ... 40 << o.k. procedure B seems to be faster as expected
- update B ... 40
- sweep ... 16 (we already know that first sweep after Alter Procedure is slow)
- update B ... 90 <<<<< first import using B is always that slow after sweep ! Why ??
- update B ... 80
- update B ... 70
- update B ... 40 <<<<< now it returned to normal
- update B ... 40
- sweep ... less that 1 sec
- update B ... 90 <<<<< slow, slow, slow
- update B ... 80
- update B ... 70
- update B ... 40

=====
Now try the same, but restart Firebird server after altering SP:

- update A ... 45
- update A ... 45
- ALTER PROC A->B
- Restart
- update B ... 42
- update B ... 95 <<<<< the second import using B after Alter Procedure and Restart is always that
slow ! Why ?
- update B ... 65
- update B ... 40

Since Firebird disconnect completely from the database after each operation,
I did not expect that restarting server could have such impact.

Ivan

========================================

----- Original Message -----
From: "Jerome Bouvattier" <JBouvattier@...>
To: <firebird-support@yahoogroups.com>
Sent: Tuesday, May 11, 2004 11:40 AM
Subject: [firebird-support] UPs and DOWNs after bulk inserts.


> Hello,
>
> Recently I'm working on a significantly bigger db compared to those I used
> to work on so far. Since then, I'm starting to face all sorts of slowdowns
> that I cannot seem to understand/anticipate.
>
> I thought I understood the main traps with IB/FB, but apparently, this is
> not true ;-)
> My db is about 10 Gb big and its biggest table, the one that is causing me
> the most troubles, is about 55 million rows.
>
> Here is a situation I faced twice and that I cannot explain myself.
>
> - DB is Swept, OIT/OAT are in line.
> - During the following ops, only one connection/transaction accesses the DB
> and the FB server has the whole machine for itself.
>
> - I import (Insert) a fair amount (e.g. 800000) of records in the said "big"
> table.
> - During the import, I commit every 50000 inserts.
> - After the import, OIT/OAT still look nicely in line.
>
> - I run an selectable SP that computes some stats and always return the same
> resultset.
> - The SP should take about 20s to return.
> - First execution of that SP returns in 28s. That's ok.
> - Commit.
> - Second one returns in 1m28s. That's a not so OK.
> - Commit.
> - Third one returns in 1m50s. It's getting worse.
> - Commit.
> - I run a Select count(*) on the given DB just in case it requires GC (but I
> guess it's useless since only inserts where done)
> - Commit.
> - Recompute all indices on the given table.
> - Close/reopen connection
> - Check Fbserver CPU usage => 0 %
> - Check Server CPU usage => 0%
> - Running the SP again, it now takes 2m32s !!!
> - Commit.
> - Guessing a few minutes.... at lost.
> - Start SP again "just in case". Surprise ! it now returns in 20s as it
> should.
> - Each subsequent call also returns in 20s.
> - Is this because it is cached ? I shutdown/restart fbserver.
> - Rerun the SP. 20s again. Each time.
>
> Does anyone have a clue of what could cause this slowdown_and_back_to_normal
> thing ?
>
> Thanks a lot.
>
> --
> Jerome
>
>
>
>
>
> Yahoo! Groups Links
>
>
>
>
>
>