Progress database monitoring with SAMS

SAMS runs on a Redhat 9 linux box and monitor Progress v9.1 Database

I ) General overview

SAMS is a set of shell scripts allowing a SysAdmin to monitor performance of Unix servers and PROGRESS databases. This page is dedicated to the database modules. If you want to access the system modules, click here. SAMS consists of 2 main tasks :
- Collect performance data from database servers
- Produce graphs.

Here is an exemple of output graphs produced by SAMS :

I.1 ) Collecting data

All the performance information are stored in RRD databases. You have to installed this software prior to the installation of SAMS. You will find everything about RRD by clicking here. The 2 scripts that collects data are called PROExtractStat.ksh and PROgetdata.ksh. The first one has to be installed on the database server. It is fairly simple. It calls promon and redirect the output to a text file. The second one, PROgetdata.ksh runs on the monitoring server (it can be the same machine). It collects information about your database(s) from the text file produced by PROExtractStat.ksh. The information collected are :

Total number of database blocks

High water mark

Size of the database, the BI and the AI

Number of records read, commited, undone, deleted, created...

Number of DB, AI and BI reads and writes

Number of checkpoints

and performance indicator like buffer hits... PROgetdata.ksh stores all these information in its RRD databases.

I.2 ) Graph generation

PROMakeGraph.ksh generates all the graphs. It reads SAMS configuration file to know which graph to produce. For each graph category (information collected has be grouped by category) it produces 4 graphs : last 24 hours, last 7 days, last month and last year.
Here are some exemples of graph for the last 24 hours :

II ) SAMS configuration

To be able to work, SAMS needs a certain configuration that is done manually. First, one needs to configure the database servers which will be monitored, then one needs to configure SAMS.

II.1 ) Configuring the database servers to monitor

I recommand the creation of a user samsclient on your server. In its home directory, you need only 2 files : PROExtractStats.ksh and a text file to be used as a cron list file. From sams archive, copy PROExtractStats.ksh to its home directory.
For version 1.0 of SAMS, SAMS server uses rcp to copy generated text files from the monitored server to its local directory.
Modify sams user cron by adding one line per database you would like to monitor. In the example below, sams user has been configured to monitor 5 dbs on server MyServer :
sams@MyServer:crontab -l
40 10 13 07 * /home/sams/PROExtractStat.ksh /path/to/db/firstdb
41 10 13 07 * /home/sams/PROExtractStat.ksh /path/to/db/seconddb
42 10 13 07 * /home/sams/PROExtractStat.ksh /path/to/db/thirddb
43 10 13 07 * /home/sams/PROExtractStat.ksh /path/to/db/fourthdb
44 10 13 07 * /home/sams/PROExtractStat.ksh /path/to/db/lastdb
NOTE : The text file containing extracted information from the db, will be produced in the directory where PROExtractStat.ksh will be run. In this example : /home/sams.
NOTE 2 : you don't need to specify .db at the end of the database name.
IMPORTANT : make sure to activate an entry only once because this script never ends. It will collect data forever. So if you program an entry like 40 10 * * *, you will spawn a new instance of the script every day ! You don't want that !

II.2 ) SAMS configuration

We can now configure SAMS on the monitoring server. We begin with the creation of the user samsuser. Then we create the directory /usr/local/. Decompress SAMS archive in that directory.
You should see the conf directory. Change directory to it and edit PROsams.conf.
This is the main configuration file. It is comprised of section. Each section begin with the keyword servername= and each following line correspond to what you would like to monitor. Let's look at the exemple below :
servername=server01:sun server01:REMOTEDATA:/users/samsuser/ server01:PRO:/db/prod:main server01:PRO:/db/dev:main This section tells SAMS that we want to monitor DB on server01. Its OS is SUN Solaris. Please, note that :sun is for future use. The following line tells SAMS where to collect the data file from. This is usefull when you have a lot of different servers with home directory located at different places.
The following lines describe several DBs to be monitored. Let's look at the first line. It's comprised of 4 fields. Each field on this line is separated with ":". The first field indicates the server name. The second one indicates that it is a DataBase (DB). The third field is the path to the DB and the fourth one is the name of the DB.

Save your changes and you're done.

III ) RRD database initialisation

We need to initialize the databases before using them. We go to /usr/local/sams/bin and execute the script : PROcreate_rrd_db.ksh. This script will parse the configuration file and create all relevant RRD databases.
IMPORTANT : Before using the scripts in sams/bin, edit them and change the first few environment variables to match your configuration :
DBPATH=/usr/local/sams/Progress_db
RRDTOOL=/usr/local/rrdtool-1.0.46
If rrdtool is not installed in the above directory (RRDTOOL=) you have to edit every single scripts (the ones begining with PRO) and change them.
If everything is OK, PROcreate_rrd_db.ksh will create all necessary databases with the information you provided in PROsams.conf.

IV ) Web page creation

Although this is not required, PROproducehtml.ksh can generate automatically all html pages necessary to easily view all the graphs. This script creates all necessary web pages in /usr/local/sams/html. Each database will have a daily__.html. For example daily_server_path_to_bd_dbname.html.
To view these web pages, you will need to configure your web server. As a example, the following lines should be added to the httpd.conf file of an Apache server :
Alias /perf/ "/usr/local/sams/html/"

Options None
#AllowOverride AuthConfig
Order allow,deny
Allow from all

NOTE : Replace "/perf/" with whatever you need. To access your monitoring pages, open your favorite browser and go to : http://my_web_server/perf/daily_server_path_to_db_dbname.html
If you have more than one server, I recommand the creation of an index.html file that would be comprised of links to your different server pages.

V ) SAMS Automation

The last thing to do is to activate samsuser's cron. When you uncompressed the archive, you should have a file named cronlist in sams/conf. It is already in cron format. As I said at the beginning, SAMS can monitor servers and dbs. As we are only interested in dbs here, you should only have the following lines in your cronlist file :
##############################################################################
# Getting Information about DBs
##############################################################################
0,5,10,15,20,25,30,35,40,45,50,55 * * * * /usr/local/sams/bin/PROgetdata.ksh -d > /usr/local/sams/conf/cron.out 2>&1
##############################################################################
# Producing graphs about servers
##############################################################################
3,8,13,18,23,28,33,38,43,48,53,58 * * * * /usr/local/sams/bin/PROMakeGraph.ksh > /dev/null 2>&1
0,10,20,30,40,50 * * * * /usr/local/sams/bin/PROMakeGraphXREF.ksh > /dev/null 2>&1

As you can see, the installation is not that long, and the result is worth the effort ! You will be able to monitor your dbs closely.
If you have comments or need more information, please send me an email : jerome.boismartel@primus.ca.

VI ) Download SAMS

The latest version of SAMS is 1.0. This package is provided to you freely. You can use it or modify it the way you want.
If you decide to use SAMS, you do that on your own risks and I cannot be responsible for your use of SAMS package or any damages it may cause.
Click here to download SAMS.