Watch, Follow, &
Connect with Us

For forums, blogs and more please visit our
Developer Tools Community.


Welcome, Guest
Guest Settings
Help

Thread: Slow Performance After Backups


This question is not answered. Helpful answers available: 2. Correct answers available: 1.


Permlink Replies: 6 - Last Post: Dec 6, 2017 9:08 PM Last Post By: Sriram Balasubr...
Jeff Cope

Posts: 88
Registered: 1/27/01
Slow Performance After Backups  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Dec 4, 2017 1:46 PM
We have had a few customers complain of application performance issues first thing in the morning. After running a few of their normal options, everything evens out.

After lots of trial and error we found that we can duplicate the behavior by running a backup prior to our tests. Once the backup is complete, options take significantly longer. For example, a SQL that normally takes a few seconds takes over 5 minutes to run. The rest of the day it runs at normal speed.

That leaves us looking at backup options to try and see what might be occurring. From all indications a sweep is not running (nothing in the logs) during these slow times. However, would we generally expect more consistent performance if we set Sweep Interval to zero in the backup and instead ran it on a scheduled basis?

Is there anything else with the backup that would cause performance degradation? Does it clear out interbase's cache?
MT Kelly

Posts: 2
Registered: 6/9/15
Re: Slow Performance After Backups  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Dec 6, 2017 10:12 AM   in response to: Jeff Cope in response to: Jeff Cope
Which version of InterBase are you using?

Jeff Cope wrote:
We have had a few customers complain of application performance issues first thing in the morning. After running a few of their normal options, everything evens out.

After lots of trial and error we found that we can duplicate the behavior by running a backup prior to our tests. Once the backup is complete, options take significantly longer. For example, a SQL that normally takes a few seconds takes over 5 minutes to run. The rest of the day it runs at normal speed.

That leaves us looking at backup options to try and see what might be occurring. From all indications a sweep is not running (nothing in the logs) during these slow times. However, would we generally expect more consistent performance if we set Sweep Interval to zero in the backup and instead ran it on a scheduled basis?

Is there anything else with the backup that would cause performance degradation? Does it clear out interbase's cache?
Jeff Cope

Posts: 88
Registered: 1/27/01
Re: Slow Performance After Backups  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Dec 6, 2017 10:29 AM   in response to: MT Kelly in response to: MT Kelly
MT Kelly wrote:
Which version of InterBase are you using?

Jeff Cope wrote:
We have had a few customers complain of application performance issues first thing in the morning. After running a few of their normal options, everything evens out.

After lots of trial and error we found that we can duplicate the behavior by running a backup prior to our tests. Once the backup is complete, options take significantly longer. For example, a SQL that normally takes a few seconds takes over 5 minutes to run. The rest of the day it runs at normal speed.

That leaves us looking at backup options to try and see what might be occurring. From all indications a sweep is not running (nothing in the logs) during these slow times. However, would we generally expect more consistent performance if we set Sweep Interval to zero in the backup and instead ran it on a scheduled basis?

Is there anything else with the backup that would cause performance degradation? Does it clear out interbase's cache?
Jeff Cope

Posts: 88
Registered: 1/27/01
Re: Slow Performance After Backups  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Dec 6, 2017 10:31 AM   in response to: Jeff Cope in response to: Jeff Cope
Jeff Cope wrote:
MT Kelly wrote:
Which version of InterBase are you using?

We have been able to duplicate the behavior on the following versions:

• XE3 11.0.4.828
• XE3 11.0.4.820
• XE7 12.0.5.366
Jeff Overcash (...

Posts: 1,529
Registered: 9/23/99
Re: Slow Performance After Backups  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Dec 6, 2017 11:04 AM   in response to: Jeff Cope in response to: Jeff Cope
On 12/4/2017 4:46 PM, Jeff Cope wrote:
We have had a few customers complain of application performance issues first thing in the morning. After running a few of their normal options, everything evens out.

After lots of trial and error we found that we can duplicate the behavior by running a backup prior to our tests. Once the backup is complete, options take significantly longer. For example, a SQL that normally takes a few seconds takes over 5 minutes to run. The rest of the day it runs at normal speed.

That leaves us looking at backup options to try and see what might be occurring. From all indications a sweep is not running (nothing in the logs) during these slow times. However, would we generally expect more consistent performance if we set Sweep Interval to zero in the backup and instead ran it on a scheduled basis?

Is there anything else with the backup that would cause performance degradation? Does it clear out interbase's cache?

The cache is cleared every time the DB is unloaded from the server. So when the
last connection is disconnected the cache is cleared. When the DB server is
restarted the cache is cleared etc. The Backup/Restore has nothing to do with
the cache.

All a Backup/restore does are

1) Rebuilds the indexes and therefor rebalances them if any are way out of balance
2) repack the data into fewer data pages at either 80% capacity (default) or
100% is you override the default. 80% is better so versions of a record can be
written to the same data page.

That's it. There is actually rarely a reason to do a B/R unless you do a lot of
deletes and your data pages get many but with sparse data in it (causing higher
disk I/O to retrieve data) or your indexes are highly unbalanced. The latter
does not happen often and it can often be faster to just deactivate and
reactivate the individual index that is badly balanced (you can check the
balancing with looking at the DB's statistics)

If something is going that slow afterwards I generally would think something
like a virus scanner is trying to scan the newly created restore. Look at the
Windows performance monitor and look at the disk access is it high for some
process other than IB? It is possible that a bad plan is happening right off
the bat, but less likely.

Do not set sweep to 0. The sweep process yields to active queries and has not
been a performance bottleneck for almost 2 decades. Let the sweep process do
its job. Only problem with the sweep process is if you are trying to unload the
DB for memory for some reason, but need the server to continue to stay up and
running - in that case you have to wait till the sweep is completed.

--
Jeff Overcash (TeamB)
(Please do not email me directly unless asked. Thank You)
Learning is finding out what you already know. Doing is demonstrating that you
know it. Teaching is reminding others that they know it as well as you. We are
all leaners, doers, teachers. (R Bach)

Jeff Cope

Posts: 88
Registered: 1/27/01
Re: Slow Performance After Backups  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Dec 6, 2017 12:13 PM   in response to: Jeff Overcash (... in response to: Jeff Overcash (...
Jeff Overcash (TeamB) wrote:
On 12/4/2017 4:46 PM, Jeff Cope wrote:
We have had a few customers complain of application performance issues first thing in the morning. After running a few of their normal options, everything evens out.

After lots of trial and error we found that we can duplicate the behavior by running a backup prior to our tests. Once the backup is complete, options take significantly longer. For example, a SQL that normally takes a few seconds takes over 5 minutes to run. The rest of the day it runs at normal speed.

That leaves us looking at backup options to try and see what might be occurring. From all indications a sweep is not running (nothing in the logs) during these slow times. However, would we generally expect more consistent performance if we set Sweep Interval to zero in the backup and instead ran it on a scheduled basis?

Is there anything else with the backup that would cause performance degradation? Does it clear out interbase's cache?

The cache is cleared every time the DB is unloaded from the server. So when the
last connection is disconnected the cache is cleared. When the DB server is
restarted the cache is cleared etc. The Backup/Restore has nothing to do with
the cache.

All a Backup/restore does are

1) Rebuilds the indexes and therefor rebalances them if any are way out of balance
2) repack the data into fewer data pages at either 80% capacity (default) or
100% is you override the default. 80% is better so versions of a record can be
written to the same data page.

That's it. There is actually rarely a reason to do a B/R unless you do a lot of
deletes and your data pages get many but with sparse data in it (causing higher
disk I/O to retrieve data) or your indexes are highly unbalanced. The latter
does not happen often and it can often be faster to just deactivate and
reactivate the individual index that is badly balanced (you can check the
balancing with looking at the DB's statistics)

If something is going that slow afterwards I generally would think something
like a virus scanner is trying to scan the newly created restore. Look at the
Windows performance monitor and look at the disk access is it high for some
process other than IB? It is possible that a bad plan is happening right off
the bat, but less likely.

Do not set sweep to 0. The sweep process yields to active queries and has not
been a performance bottleneck for almost 2 decades. Let the sweep process do
its job. Only problem with the sweep process is if you are trying to unload the
DB for memory for some reason, but need the server to continue to stay up and
running - in that case you have to wait till the sweep is completed.

To clarify, we are only talking about running a backup. No restore is done.
Sriram Balasubr...

Posts: 132
Registered: 10/19/99
Re: Slow Performance After Backups  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Dec 6, 2017 9:08 PM   in response to: Jeff Cope in response to: Jeff Cope
Jeff,

Please do not turn off sweep; that is not the problem here. InterBase sweep is now fast and incremental (starting with InterBase XE, all versions you use). In fact, with InterBase 2017, even a freshly restored database backup is setup for "incremental fast sweep". Automatic sweep has many benefits; so, please do not turn it off.

Run backup with the "-g" flag; this will inhibit garbage collection of obsolete versions during backup. Sweep can take care of cleanup later.

Running "backup" normally brings into the database cache all the data and index pages from the database. When garbage collection is not turned off during backup (the default), the database pages from all the tables pollute the database cache and stay resident thus "pushing out" interesting pages used by your earlier applications. Turning off "garbage collection" during backup tells the InterBase cache manager not to pollute the in memory cache with these database pages loaded from disk.

Your applications will continue to function as before and the cache will have those interesting pages, even with "backup" running (with -g flag, of course).

Best wishes,
Sriram

Jeff Cope wrote:
We have had a few customers complain of application performance issues first thing in the morning. After running a few of their normal options, everything evens out.

After lots of trial and error we found that we can duplicate the behavior by running a backup prior to our tests. Once the backup is complete, options take significantly longer. For example, a SQL that normally takes a few seconds takes over 5 minutes to run. The rest of the day it runs at normal speed.

That leaves us looking at backup options to try and see what might be occurring. From all indications a sweep is not running (nothing in the logs) during these slow times. However, would we generally expect more consistent performance if we set Sweep Interval to zero in the backup and instead ran it on a scheduled basis?

Is there anything else with the backup that would cause performance degradation? Does it clear out interbase's cache?
Legend
Helpful Answer (5 pts)
Correct Answer (10 pts)

Server Response from: ETNAJIVE02