Board index » delphi » Very Long Posting: Slow Interbase 6.5 - Strange problem

Very Long Posting: Slow Interbase 6.5 - Strange problem

Hello,

Sorry for the long post but this is urgent and complicated...

I have a strange problem... Before you read the whole story I believe that
this is a problem with IB6.5 and my hardware and I was hoping someone else
has the same problem. Either IB6.5 is affecting my hard-drives or my
hardware is affecting IB6.5. Don't know yet :-( ... You can read the story
now if still interested...

I have Interbase 6.5 installed on a Windows NT 4 Server. The machine has two
P3 800s with 512MB of RAM and is running SCSI drives with a RAID controller.
My database is about 14gig with about 216 tables. My system is a financial
package.  Every month we run a process to allocate interest and charge
management fees to the clients on the system. There are about 30,000 clients
on the system.

Now the problem. The process used to take about 4 hours before. Now its much
slower. Two months ago it took 42 hours to complete.  The month before that
it took 17 hours. And 8 hours before that. So its getting slower every
month. But its not just getting slower every month. During the process we
loop through our clients performing about 5 transactions per client (thats
financial transactions). In total there about 40 SQL statements performed
per client with about 5 tables queried on and about 7 tables updated. Now if
I look at the first hour of the process (looking at the 42 hour process)
about 2200 clients where done. In the next hour about 2000 clients where
done. In the next 1800 or so where done. And so on. It continually gets
slower. All the others months showed a similar slow-down.

This started around about the time I moved to Interbase 6.5 from Interbase
5.6. I've since discovered that this is not the only process that shows this
behaviour. Other smaller processes show the same pattern. Now, of course,
I've done lots of tests on the whole thing. This didn't happen on any other
machine that I tested the process on. I tested on about 5 other machines
(all with different hardware) and they all ran quicker without any
slow-downs. I tested by restoring (using IB restore) my DB on to the
machines. Everything went fine. So my obvious conclusion is hardware of
course (please don't give up reading yet - its not that obvious yet). So I
wrote a program so write to a file entries to a file. Tested the program and
no problems what so ever. Wrote faster on that machine than any other
machine. Then I wrote another program to write and read to the same file (or
different files)  (lets say read and write 10000 lines to a file). Now this
flew (really quick) on other machines. On my server this took incredibly
long and showed a similar slow-down as my other processes in IB. So good, I
found the problem, the server is messed up badly. So why would I post this
message here you ask???

Last month I ran my process on another machine. I restored the database from
our backup tapes (and not IB restore as this takes 13 hours to restore on a
normal machine). Exactly the same thing happened on this machine (the
machine was fine before with another database that I restored (IB restore)
to this machine). But my file writing test was still quick on this machine
though. So now this tells me there is something wrong with my database
(maybe index structures got messed up on the server machine?). So I backup
and restored my database on this machine (IB backup)  (I dared not try on my
server as 12 hours might turn into 1 week). And now my database was fine (I
tested). So I backed up and restored it on my server (using tape) but now
the database is bad again on the server (same problem as before - I tested
immediately after I restored it on the server and the problem was there).

I've checked preformance using the windows System Monitor and the only
counter that doesn't fit in with ordinary performance on other machines is
"System->File Read Operations/sec". This counter starts high and then
continually goes done (similar curve to my process).

So if you're still reading, any ideas. It started when I moved to IB6.5. Not
all processes show the same behaviour. Some do very similar things but with
no problems. This week-end we will try re-installing the server. But it
still might be the hardware and/or IB6.5. My client is convinced its 6.5 and
wants to go back to 5.6 which is not possible I believe. Has anyone seen
this before?

Hopeful my story makes sense. Thanks in advance...

Regards,
Kevin

 

Re:Very Long Posting: Slow Interbase 6.5 - Strange problem


Quote
Kevindren Naidoo wrote:
>So if you're still reading, any ideas.

Please post the output from "gstat -h".

Re:Very Long Posting: Slow Interbase 6.5 - Strange problem


Thanks for the reply.

Here it is...

Database "MAMDB.gdb"

Database header page information:
        Flags                   0
        Checksum                12345
        Generation              993223
        Page size               4096
        ODS version             10.1
        Oldest transaction      923435
        Oldest active           993202
        Oldest snapshot         993203
        Next transaction        993204
        Bumped transaction      1
        Sequence number         0
        Next attachment ID      0
        Implementation ID       16
        Shadow count            0
        Page buffers            3000
        Next header page        0
        Database dialect        1
        Creation date           Apr 2, 2003 10:40:48

    Variable header data:
        Sweep interval:         100000
        Continuation file:              D:\MAM\DATABASE\MAMDB1.GDB
        Last logical page:              1048575
        *END*

Quote
Anders Melander <and...@aztech.dk> wrote:
>Kevindren Naidoo wrote:

>>So if you're still reading, any ideas.

>Please post the output from "gstat -h".

Re:Very Long Posting: Slow Interbase 6.5 - Strange problem


Quote
Kevindren Naidoo wrote:
> Here it is...

> Database "MAMDB.gdb"

> Database header page information:

        Did you do this when the DB was slow, or after you had restarted it?

        -Craig

--
Craig Stuntz [TeamB] . Vertex Systems Corp. . Columbus, OH
Delphi/InterBase Weblog : http://delphi.weblogs.com
InterBase Perf. Monitor : http://delphi.weblogs.com/IBPerformanceMonitor
InterBase PLANalyzer 1.1: http://delphi.weblogs.com/IBPLANalyzer

Re:Very Long Posting: Slow Interbase 6.5 - Strange problem


This is from my database today. We run a sweep every night. I've written tests to emulate the problem on the database as well and ran it today (after this listing though). Looked pretty similar though.

"Craig Stuntz [TeamB]" <cstu...@vertexsoftware.com> wrote:

Quote
>Kevindren Naidoo wrote:

>> Here it is...

>> Database "MAMDB.gdb"

>> Database header page information:

>    Did you do this when the DB was slow, or after you had restarted it?

>    -Craig

>--
>Craig Stuntz [TeamB] . Vertex Systems Corp. . Columbus, OH
>Delphi/InterBase Weblog : http://delphi.weblogs.com
>InterBase Perf. Monitor : http://delphi.weblogs.com/IBPerformanceMonitor
>InterBase PLANalyzer 1.1: http://delphi.weblogs.com/IBPLANalyzer

Re:Very Long Posting: Slow Interbase 6.5 - Strange problem


Quote
Kevindren Naidoo wrote:

> This is from my database today. We run a sweep every night. I've
> written tests to emulate the problem on the database as well and ran
> it today (after this listing though). Looked pretty similar though.

        We need to see statistics taken while the DB is slow.

        -Craig

--
Craig Stuntz [TeamB] . Vertex Systems Corp. . Columbus, OH
Delphi/InterBase Weblog : http://delphi.weblogs.com
InterBase Perf. Monitor : http://delphi.weblogs.com/IBPerformanceMonitor
InterBase PLANalyzer 1.1: http://delphi.weblogs.com/IBPLANalyzer

Other Threads