Board index » delphi » Very small sweep interval

Very small sweep interval

I have a client that is quite IT literate and he decided to alter the sweep
interval down to 50!
It has performed relatively ok but the DB is now 1GB.

I am about to make the following changes to boost performance: (any comments
appreciated)
1. Up the DB buffers to 10,000 from 256
2. Turn off sweeps and set up a nightly batch job to do it.
3. Restore with a 8192 page size.

Other info:
1. App: D6,BDE,IB5.6,50 users,WAN
2. Some of the tables are long and thin > 1million rows.
3. Nature of app is a 50:50 mixture of querying and updating data

Any thoughts?

    Tom

****************************************
Tom Conlon
Tel: 020 8545 9980
Fax: 020 8545 9989
email: t...@2LS.com
www: http://www.2LS.com

****************************************
The information in this E-mail is intended only
for the named recipient and may be privileged or
confidential. If you are not the intended recipient
please notify us immediately and do not copy,
distribute or take any action based on this e-mail.
If this email is marked 'personal'
2LS LTD is not liable in any way for its content.
****************************************

 

Re:Very small sweep interval


Quote
> I have a client that is quite IT literate and he decided to alter the sweep
> interval down to 50!

This means that the sweep jumps in when OAT - OIT is greater than 50.
A sweep will visit every record in each table trying to remove older record
versions, which can surely impact the performance negatively.

Quote
> It has performed relatively ok but the DB is now 1GB.

> I am about to make the following changes to boost performance: (any comments
> appreciated)
> 1. Up the DB buffers to 10,000 from 256
> 2. Turn off sweeps and set up a nightly batch job to do it.
> 3. Restore with a 8192 page size.

With the above changes you should keep the following in mind. Increasing
the page buffers and the page size means that for this particular database
the InterBase server can use maximal 80 MB memory for caching. This
is fine as long you don't have serveral databases configured like that way,
because with for example 10 databases the InterBase server process is
"allowed" to use 800 MB memory server-wide.

So, how many databases are you running on that server, and what's the
size of the main memory?

--
Regards,
Thomas Steinmaurer
http://www.iblogmanager.com

Re:Very small sweep interval


Quote
"Tom Conlon" <t...@gripsystems.com> wrote in message

news:3eccae23$1@newsgroups.borland.com...

Quote
> I have a client that is quite IT literate and he decided to alter the
sweep
> interval down to 50!
> It has performed relatively ok but the DB is now 1GB.

> I am about to make the following changes to boost performance: (any
comments
> appreciated)
> 1. Up the DB buffers to 10,000 from 256
> 2. Turn off sweeps and set up a nightly batch job to do it.
> 3. Restore with a 8192 page size.

> Other info:
> 1. App: D6,BDE,IB5.6,50 users,WAN
> 2. Some of the tables are long and thin > 1million rows.
> 3. Nature of app is a 50:50 mixture of querying and updating data

You'll have to see if the change of page size makes any difference, for long
thin tables I would have thought that on a highly fragmented drive with 4K
clusters, it could under certain conditions cause performance degradation.

So I would definitely do 1& 2 and also do a backup and restore from time to
time.  IF all users should disconnect over night, then I would down the
server service prior to doing a sweep, otherwise you get someone who is away
for a week, being logged in stopping the sweep from being able to do its job
and you'll be hunting for performance porblems.  Finally as the DB grows,
the access mechanism (BDE) with poorly defined live TQueries or TTables will
seriously degrade performance.  See my other post to you in IB.bugs for
shameless plug in "servicing" installations.

Jason Chapman
JAC2 Consultancy

Training - Development - Consultancy
Delphi, InterBase, Firebird, OOAD, Development lifecycle assistance,
Troubleshooting projects, QA.....
www: www.jac2.co.uk
Mob: (+44) 07966 211 959 (preferred)
Tel: (+44) 01928 751088

- Come and see me at DCON2003 - 3 talks and a day of preconf training.
- Completed London Marathon 13/4/03 beating my PB by 30 seconds - only 500
short of sponsorship, so if you're feeling generous, throw money at me at
DCON (VICTA - For blind kids).

Re:Very small sweep interval


Quote
On Thu, 22 May 2003 12:03:52 +0100, Tom Conlon wrote:
> ...
> I am about to make the following changes to boost performance: (any
> comments appreciated)
> 1. Up the DB buffers to 10,000 from 256
> 2. Turn off sweeps and set up a nightly batch job to do it.
> 3. Restore with a 8192 page size.

> Other info:
> 1. App: D6,BDE,IB5.6,50 users,WAN
> 2. Some of the tables are long and thin > 1million rows.
> 3. Nature of app is a 50:50 mixture of querying and updating data

I think all the changes will be for the good.  You should take a look at
the statistics for the indexes (before and after) to see whether the index
depths were reduced - if not, 4K page size would probably be fine.  On the
other hand, larger page size also allows for using more RAM for cache -
probably a good thing.

If you also moved away from the BDE ...

Regards,
Aage J.

Other Threads