Interbase Backup Time

[Donny Philip]

Hi All

I am in quite a troublesome situation
History
My client migrated from IB5.6 to IB 7 three months back.
They have a weekly maintenence of back up and restore.
They use Win2000, Xeon processor, dual CPU 1 GB RAM server for
backup restore process.Page size -8192, DBCache -60,000
The database was 2 GB three months back and now its 2.4 GB
Normally backup takes 20 minutes(normally using gbak- script).

Problem
Last week the backup didnt complete in 12 hrs.
Yesterday I did a remote login to their system and invoked a
backup thru console version x.x.14

Till 700GB it was growing at 100MB/min. Then the verbose
output came to backing up my largest table. The file was growing
very slowly from then on. At 10MB/min..

Today morning I realize that its taken 8 hrs to complete.
Server was running at full CPU from the moment it slowed down.
The largest table has 54 columns and around 2.5 million records.
Any possible reasons why this can happen??
It was taken only 20 mts till last week!!!

They also reproduced the situation in 3 different server machines...

Thanks a ton

With Best Regards
Donny Cherian Philip.

[Alexey Kovyazin]

Hello, Donny!

Can you post your exact gbak command?
I can suppose that you didn't use -g options while backing up to avoid
garbage collection.
Please try to use the following combination of switches:

gbak -b -g ...

Sincerely yours,
Alexey Kovyazin,
www.ib-aid.com

"Donny Philip" <do...@ibsplc.com> сообщил/сообщила в новостях следующее:
news:3f98e251$1...@newsgroups.borland.com...

[Donny Philip]

Hi Alexey

Thanks for response. I think -g is present.
Pls take a look

thx
Donny.

@echo off

SET IBDATA=D:\Data\IBSERVER
SET IBBACKUP=D:\Data\IBSERVER\BACKUP
SET IBBIN=D:\applns\InterB~1\BIN
SET PASSWORD=masterkey

if not exist %IBDATA%\TOPDAT.GDB goto err0
echo BACKUP IN PROGRESS...
if exist %IBBACKUP%\topdat%1.gbk del %IBBACKUP%\topdat%1.gbk
if exist %IBBACKUP%\backup%1.txt del %IBBACKUP%\backup%1.txt

%IBBIN%\gbak.exe -b -user SYSDBA -pass %PASSWORD% %IBDATA%\topdat.gdb %IBBACKUP%\topdat%1.gbk -v -g -y %IBBACKUP%\backup%1.txt
if errorlevel 1 goto err
echo BACKUP SUCCESSFUL ...
goto end

:err0
echo DATABASE NOT FOUND ...
goto end

:err
echo BACKUP UNSUCCESSFUL ...
if exist %IBBACKUP%\topdat%1.gbk del %IBBACKUP%\topdat%1.gbk
notepad %IBBACKUP%\backup%1.txt
goto end

:end
SET IBDATA=
SET IBBIN=
SET PASSWORD=

[Craig Stuntz [TeamB]]

Donny Philip wrote:

> Any possible reasons why this can happen??
> It was taken only 20 mts till last week!!!

There are many reasons why it can happen:

o Other connections with long-running transactions on the IB server.
o Uneven distribution of records / versions in the DB.
o Other activity on the server machine.
o More data in the DB (note that DB size is not strictly proportional
to the amount of data in the DB).
o Old record versions which haven't been garbage collected.

If I were in your position the first thing I'd try is restoring the DB
from a backup during down time. This recreates the DB from scratch and
will rebalance indices and page distribution.

-Craig

--
Craig Stuntz [TeamB] . Vertex Systems Corp. . Columbus, OH
Delphi/InterBase Weblog : http://delphi.weblogs.com
InterBase in Multi-tier World -- Use multiple RDMs, IB features in
your appserver: http://delphi.weblogs.com/stories/storyReader$195

[Bill Todd]

Have you verified the database with gfix to see if any errors are
reported?

How long has it been since the database was swept or a backup and
restore was performed?

--
Bill (TeamB)
(TeamB cannot respond to questions received via email)

[Donny Philip]

Hi Bill, Craig, Alexey and Friends

Well I guess you pinned the bug for me.
All the help did work.
The problem was coz of the OIT NAT difference I guess.
It was quite huge. Wouldn’t want to put the value down here
coz you might wonder how our application might have caused
such a huge difference...:(-

We are working on a product which was earlier owned by a
different company. They had developed it for around 12 years.
So, the db is showing strains because of the poor transaction
management in the application.

We are changing TIBTransaction to have commitretaining set for
a specified transaction Idle time. This might give us the
performance customers are looking for and some facelift that we
need badly..

thanks again

cheers
Donny.

[Craig Stuntz [TeamB]]

Donny Philip wrote:

> We are changing TIBTransaction to have commitretaining set for
> a specified transaction Idle time.

This will not make any difference at all. Commit Retaining does *not*
end the transaction. It will not affect the OIT or the OAT one bit.

It sounds from your message like you're confusing the OIT and the OAT.
They are different. The difference between the OAT and the Next
Transaction is what can slow down the server, so in this context it's
the OAT you have to be concerned with.

The difference between the OIT and the OAT controls when the sweep
runs.

-Craig

--
Craig Stuntz [TeamB] . Vertex Systems Corp. . Columbus, OH
Delphi/InterBase Weblog : http://delphi.weblogs.com

InterBase Performance Monitor -- Analyze and control your IB7
server: http://delphi.weblogs.com/IBPerformanceMonitor

[Bill Todd]

To add to Craig's post, it would be helpful to know the values of the
OIT, OAT and NAT. A big difference between the OIT and OAT has a very
different meaning from a big difference between the OAT and NAT. In
the first case something has caused the OIT to get stuck. In the
second you have transactions that are running for a long time.

[Donny Philip]

Hi Bill, Craig and Friends

I would like to start by thanking you for the responses. I was also great to know the significance of
OIT OAT and NAT put so clearly.

Would be greatful if you could read thru "my story" to understand my problem in the better perspective.

It started of in Feb2003 when we took over the support contract for a product for a comapny in UK.
According to them application performance was the only core problem they were facing.
Back in Feb they were using IB5.6. Because of their "Platinum contract" I guess they are able to bring in
people from Borland UK to consult on the product that we are maintaining. Also in Sept02 a
consultant from Borland UK had suggested that the application be move to the laterst version of IB.
By July 2003 we convert the full application from IB5.6 to IB7.0 and deployed the same at customer site.
We at development team were novices to IB databases and its concepts. The application is
used on a 24*7 basis with a weekly shutdown for backup and restore. After the migration to IB7.0 the
department which works 24*7 came with a 50% improvement feedback and the rest four
departments said that performance hasnot improved.

So the company in UK decided to call back their old Borland consultant to take a relook at the situation.
And we had a couple of three way teleconfrences, we the support team, customer and Borland UK.

At this stage the Borland UK tells us that our application has a problem with transactions getting stuck
and clogging TIP.( OIT OAT and NAT values I guess -1 month back)

So now we are trying to address this issue in our application. On taking a closer look of our 25 modules,
9 use IB components to connect to db and the rest are through BDE. At client side application
uses Delphi5.

Trying to find what goes wrong in our app that keeps OIT and OAT move slowly wrt NAT we find that
grids connected directly from Delphi to DB when using IBComponents cause a transaction to be
created and till this screen is closed the transaction remains live.

So to reach the final objective of performance improvement we are trying to keep our OIT and OAT
jumping to keep in pace with NAT. For this we use commit retaining with idle time setting
in our transaction component. With this we are hoping that any user who would keep the OIT stuck by
simply leaving a screen open which directly connects to db would not happen as the
commit retaining would fire and cause the transaction id to jump.

Today when I did a remote login to live enviornment I found
OIT - 88799
OAT - 3227889
NAT - 3229670

First, could you please validate how we are going ahead with solving the performance issue
Secondly, with the above mentioned values of OIT OAT and NAT will backup be really slow?

Thanks a lot

With BRegards
Donny.

[Craig Stuntz [TeamB]]

Donny Philip wrote:

> At this stage the Borland UK tells us that our application has a
> problem with transactions getting stuck and clogging TIP.( OIT OAT
> and NAT values I guess -1 month back)

The TIP is a special page in the DB which keeps track of transactions.
It's discussed here:

http://community.borland.com/article/0,1410,25260,00.html

> So now we are trying to address this issue in our application. On
> taking a closer look of our 25 modules, 9 use IB components to
> connect to db and the rest are through BDE. At client side
> application uses Delphi5.

In the BDE make sure that COMMIT RETAIN is FALSE. I think the driver
flags can also affect this.

> Trying to find what goes wrong in our app that keeps OIT and OAT move
> slowly wrt NAT we find that grids connected directly from Delphi to
> DB when using IBComponents cause a transaction to be created and till
> this screen is closed the transaction remains live.

That's correct. The best solution for this, IMHO, is to use DataSnap
when working with live data controls in IBX. Here's how:

http://www.dbginc.com/tech_pprs/ibxcds.html

[Bill Todd]

Let me make some comments in addition to those made by Craig.

On 27 Oct 2003 22:17:37 -0700, "Donny Philip" <do...@ibsplc.com>
wrote:

>At this stage the Borland UK tells us that our application has a problem with transactions getting stuck
>and clogging TIP.( OIT OAT and NAT values I guess -1 month back)

The question is, why is the OIT getting stuck. Normally, the only way
the OIT can get stuck is if a user rolls back a transaction that has
made more than 10,000 changes to the database. The other thing that
can cause the OIT to stop moving is to call CommitRetaining without
ever calling commit. The OIT is moved when Commit is called and a new
transaction is started. You must call Commit periodically.

>
>So now we are trying to address this issue in our application. On taking a closer look of our 25 modules,
>9 use IB components to connect to db and the rest are through BDE. At client side application
>uses Delphi5.
>
>Trying to find what goes wrong in our app that keeps OIT and OAT move slowly wrt NAT we find that
>grids connected directly from Delphi to DB when using IBComponents cause a transaction to be
>created and till this screen is closed the transaction remains live.

This can cause a gap between the OIT and the NAT but not between the
OIT and the OAT.

>
>So to reach the final objective of performance improvement we are trying to keep our OIT and OAT
>jumping to keep in pace with NAT. For this we use commit retaining with idle time setting

If you only call CommitRetaining the OIT will not move. You must call
Commit and start a new transaction.

>in our transaction component. With this we are hoping that any user who would keep the OIT stuck by
>simply leaving a screen open which directly connects to db would not happen as the
>commit retaining would fire and cause the transaction id to jump.
>
>Today when I did a remote login to live enviornment I found
>OIT - 88799
>OAT - 3227889
>NAT - 3229670
>
>First, could you please validate how we are going ahead with solving the performance issue
>Secondly, with the above mentioned values of OIT OAT and NAT will backup be really slow?

Probably since the database will contain many old record versions. Run
gbak with the -g switch to disable garbage collection. Run a sweep
every night when system load is low.

[Donny Philip]

Hello Bill, Craig
Would like to thank you for your responses.
Just had a quick query regarding one of the points mentioned.

>>So to reach the final objective of performance improvement we are trying to keep our OIT and OAT
>>jumping to keep in pace with NAT. For this we use commit retaining with idle time setting
>If you only call CommitRetaining the OIT will not move. You must call
>Commit and start a new transaction.

I would beg to differ with you on the above statement. May I put forward an example please.
I am using IB7.0, Delphi 5

I have a screen(screen A) connecting to the database through IB components using a grid.
Let us put a transaction number 15501 for the connection made to the db from
this screen at 0900 hrs. And also lets assume that this was the first db connection on a
particular day. And lots of other activities are happening on the db from 0930 onwards.
If I check my db header page stats will be like(at 0945)
OIT - 15500
OAT -15501
NAT - 18789(lets assume)

Now if my initial screen A was connecting to the db using a transaction component having
commit retaining and Idle time set to 60 mts. Lets believe that screen A will not be closed
during the day even for once and no activity happens in the screen.
I will have the following figures in my header page (at 1100)
OIT - 19900
OAT -19901
NAT - 21234(lets assume)
Where 19901 will be some other transaction like another sql against the db.

But by 1115 the new transaction number created by Screen A due to
commit retaining property would become the oldest coz all other transactions had committed.
Lets put this number as 20201

If I check my db header page stats will be like(at 1120)
OIT - 20200
OAT -20201
NAT - 21334(lets assume)

My finding
Commit retaining with idle time gives a new transaction number to an existing transaction
after the specified idle time while retaining the transaction conext(and no data is lost from grid).
This would ensure that OIT jumps. In our Labs OAT which is a value OIT-1 would also jump.
These are the results of tests we did in our labs

My Doubt
In my above finding I see that the idele timer if specified equal to 60 minutes would for
the very first time only fire after 120 minutes and then it would fire after every 60 mts.

I would like if you could destroy my findings with some explanations.
I would by no means want to take it to the customer if something is wrong.

Good Day

Also pls let me know how I can make this response come to the top of the list...:)-

With Best Regards
Donny.

[Donny Philip]

Sorry Would like to slightly modify my Finding
coz I confused b/w OIT and OAT

My finding
Commit retaining with idle time gives a new transaction number to an existing transaction
after the specified idle time while retaining the transaction conext(and no data is lost from grid).

This would ensure that OAT jumps. In our Labs OIT which is a value OAT-1 would also jump.

These are the results of tests we did in our labs

Cheers

[Bill Todd]

That suggests one of two things.

1) Somewhere in your app Commit is being called. If any transaction
commits the OIT will move.

2) The problem with CommitRetaining causing the OIT to stick was fixed
in IB 7.