Consultor Eletrônico

Performance Instructions for Beginning to Tune your Database

Tuning Your Database for 6.3 and 7.2

Tuning your db is a very inexact science and for that
reason technical support is only supposed to offer assistance
to a certain extent. It really requires someone to analyze your
system which can be very time consuming. For that reason we
recommend that you speak with our consulting group. Although
it is not a free service they can send a consultant on site for
two days to help tune your system to its best capability.

Please note that this is only offered as one method to begin
tuning. There are probably several methods for tuning your
database, however the two theories described here have been
used by some of our customers and have been successful.


Starting Asynchronous Page Writers:

To begin, you should start one page writers for each disk your db
resides on and possibly an additional one. To start a page writer,
once the broker is started, type:

proapw <dbname>

Page writers occupy one user entry with the broker, therefore if
you start three page writers, you should increase your -n setting
on the broker by three. Since they take one user entry, they can
be stopped the same way a user is, by disconnecting them through
the proshut menu.

To set the initial values for your page writers there are
essentially two common theories. Some people have had success with
one of these theories, others have not. There are probably more
configurations being used and you may find a configuration different
from these that works best for you. However, this is simply offered
as a starting point.

NOTE: The -pwscan and -pwwmax parameters are used at broker start-up
(with proserve).

The two theories are:

Theory 1: set -pwscan to 10% of your -B startup parameter.
set -pwwmax to 10% of -pwscan.

Example: -B 8000 3 APW's
-pwscan = 800
-pwwmax = 80

Theory 2: set -pwscan to the value of -B divided by the number of
asynchronous page writes (APW's) running for this db.
set -pwwmax to the value of -pwscan divided by 2.

Example: -B 8000 3 APW's
-pwscan = 2700 (approx)
-pwwmax = 1850

Once you have set these values on startup, you can tune the
page writers while the system is up and running. You do not
have to keep stopping and starting the server with new values.

Run promon against your db and at the prompt for a selection type
in R&D in capital letters with no spaces and press return. You
will then see an expanded menu. The three options you will be working
with are 9. Activity, 16. Buffer Pool and M. Modify Defaults.
?
Under option 9.Activity you will need to check Buffers Flushed.
If this value is anything but 0 then these buffers are being
flushed by some other means than the APW's. These buffers being
flushed are going to cause a delay. You want this value at 0.
Divide the buffers flushed by the number of checkpoints and this
will tell you how many buffers are being flushed at each checkpoint.
Then divide the result by the number of disk i/o's per second.
This will tell you how long the pause is for each checkpoint, for
example, if this comes out to 240 seconds that is 4 minutes of
waiting.

Tuning Your Database:

Pick a point in the day when it is the busiest. This is when
you want to begin tuning. If you tune at the quietest time of
the day, the more active part will see a drop in performance.

First choose option 16. On this screen you can type U to see
an uninterrupted display. This will refresh itself every 8 seconds
with current data. The lines you are watching are the last three
beginning with "Buffers left at previous checkpoint". The value
to the right indicates the buffers that were left behind by the
APW's after they have woken up and cleaned out as much of the
buffer pool as they can. Again, there are two theories to your
goal as far as this value.

Theory 1: Get this value to zero by increasing the value of
the -pwscan and -pwwmax startup options. If there
are any buffers left at the checkpoint, then the client
is going to pay the price for writing them out.

The drawback to this theory is that there is no
way to determine if you are overtuned.

Theory 2: Get this value to 1. This way you are only writing
out 1 buffer, and you are guaranteed that you are NOT
overtuned.

The bottom two values describe the checkpoint that is about to
occur. If you watch the values in the parentheses, the second to
last one should grow slightly faster than the last one. When you
see a checkpoint go by (when the number in parentheses reaches 100)
you should see the values change in the buffers left at previous
checkpoint.

When you are ready to begin changing the values of -pwscan and
-pwwmax, press CTRL-C to stop the uninterrupted display and go
back to the main menu. Then choose option M. Modify Defaults.
From this menu, option 9 indicates the value of -pwscan. Option
a indicates the value of -pwwmax. To change them interactively
type: 9 <CR> and it will prompt you for the new value. Do the same
for the a option. Please remember to try to keep them in the same
ratios that they were in when you started. Example: 10 to 1 for
theory 1, or 2 to 1 for theory 2.

Then go back to the main menu and choose option 16 for the buffer
pool. You should be able to immediately see the results of your
changes.

This is how to tune the db. This will take some time to get
it just right. And please note that when it is tuned for the
busy period in the day it will be overtuned for the quiet period
There is not much of a price for this though. 7.3A of Progress
has self tuning page writers which is a big improvement.


Progress Software Technical Support Note # 13768