#---------------------------------- cut here ---------------------------------- # This is a shell archive. Remove anything before this line, # then unpack it by saving it in a file and typing "sh file". # # Wrapped by Jacob L. Salomon on Thu Jan 20 16:32:59 2000 # # This archive contains: # monitor-space.txt dbspace-pages.sh # monitor-space.sh monitor-space.cron # # Modification/access file times will be preserved. # Error checking via wc(1) will be performed. # Error checking via sum(1) will be performed. LANG=""; export LANG PATH=/bin:/usr/bin:/usr/sbin:/usr/ccs/bin:$PATH; export PATH if sum -r /dev/null 2>&1 then sumopt='-r' else sumopt='' fi echo x - monitor-space.txt cat >monitor-space.txt <<'@EOF' Title: monitor-space Purpose: To be made aware of dbspaces that are approaching an unsafe level of fullness Files: dbspace-pages.sh monitor-space.sh monitor-space.cron Author: Jacob Salomon JakeSalomon@bn.com Date: 2000-01-20 Motivation: ---------- Several months ago, I submitted to the IIUG archives a script named dbspace-space.sh. This is based on a none-too-original idea: an SQL query on the SMI database to calculate the amount of space in each dbspace in the Informix DSA system. It works just fine, though it spits out the usual "Database selected" and "17 rows retrieved" messages to stderr. It also does not work if the system is in quiescent mode because no SQL can work in quiescent mode. It also takes a long time to run, a surprising (albeit common) behavior in some SMI queries. For the above reasons, I have stopped using dbspace-space.sh and replaced it with a new script, dbspace-pages.sh. The latter script is mostly a wrapped awk script that operates on the output of the familiar command: onstat -d Script: dbspace-pages.sh ------------------------ dbspace-pages.sh produces output identical to that of dbspace-space.sh (minus the informative messages from dbaccess). Therefore, like dbspace-space.sh, it requires that beautify-unl.sh be in your $PATH. What does the output look like? Here's a sample, pasted right off the system I am typing this on: $ dbspace-pages.sh DB-Space |DBS-Num|NumChunks|TotPages|FreePages|%-Full| rootdbs | 1| 1| 100000| 88079| 11.92| phys_dbs | 2| 1| 250000| 1947| 99.22| log_dbs | 3| 2| 1512000| 521944| 65.48| tmp_dbs1 | 4| 1| 256000| 255387| 0.24| tmp_dbs2 | 5| 1| 256000| 255523| 0.19| imm_dbs | 6| 3| 2500000| 866424| 65.34| imm_frag1 | 7| 1| 1000000| 583250| 41.68| imm_frag2 | 8| 1| 1000000| 582048| 41.80| book_dbs | 9| 1| 500000| 311326| 37.73| indexdbs | 10| 1| 524288| 109206| 79.17| ordfrag1_dbs| 11| 1| 1048575| 1009522| 3.72| alpha_dbs | 12| 1| 1048575| 66206| 93.69| ordfrag2_dbs| 13| 1| 1048575| 1048522| 0.01| order_dbs | 14| 2| 2097150| 2031320| 3.14| Note a couple of items about this output, mainly the data under the "%-Full" heading. I see that phys_dbs is 99.22% full. If I did not know better, I would panic as DBA because [I would fear the] all applications are about to dir because of a full dbspace. As it happens, I do happen to know better in this case: That dbspace is wholly occupied by the physical log and is in no danger of filling up. On the other hand, alpha_dbs is 93.69% full and may merit watching. If alpha_dbs reaches 95% full, I might want to start dickering with the Unix SA's to get me more disk space so I can add a chunk to it before I get a middle-of-the-night call. While I am proud of dbspace-pages.sh, I know it can't run itself. And the system cannot depend on a human being to run this every day on every server just to check on how full the dbspaces are getting; human eyeballs are notorious for their great propensity to overlook. While I could have modified dbspace-pages.sh to generate warnings, it is more in the Unix spirit to let another program run it and make its own decisions on what is dangerous. Thus was born the next script in this set: Script: monitor-space.sh ------------------------ This script runs dbspace-pages and, if any dbspace is full by over 95% it generates a warning. The 95% is a default; actually, this can be a command line parameter. The -h parameter generates help text: $ monitor-space.sh -h Usage: ./monitor-space.sh [-h] [-c integer] [-i dbspace [dbspace...]] A space between an option and its parameter is mandatory ----- ------ --------- -h : Print this help text and exit. (Ignores all other parameters) -c : Cutoff point for percent full: Generate a warning if dbspaces are more than % full -i : List of dbspaces to ignore. eg: Name of dbspace dedicated to logical logs, which is usually nearly full but will not fill further. --- OK, let's try it with no parameters: $ monitor-space.sh Warning: DBSpace phys_dbs is 99.22% full --- Suppose I want to set the warning cutoff at 90%. I would enter the command: $ monitor-space.sh -c 90 Warning: DBSpace phys_dbs is 99.22% full Warning: DBSpace alpha_dbs is 93.69% full --- Hey, I don't want to be warned about phys-dbs; I have no tables there and it's not in danger of filling up. Similarly, log-dbs, though it is only 65% full now, when I fill it ith as many logs as intended, it wal allso hover around 99% full. Any FULL warning on that one would also be a red herring. What's a DBA to do? Well, said DBA could read the help text and note the -i parameter. Let's run it again but tell it to ignore a couple of dbspaces: $ monitor-space.sh -c 90 -i phys_dbs log_dbs Warning: DBSpace alpha_dbs is 93.69% full --- Thus we have solved the problem of getting false wanings and setting a desired threshold of fullness that determines when to issue a warning message. However, we have exascerbated the human factor. If it hard to get a human DBA to run a single command like dbspace-pages.sh, how will you get'em to enter "monitor-space.sh -c 90 -i phys_dbs log_dbs"? To complicate this further, bear in mind that that the names of the dbspaces to ignore will not be identical across multiple servers. So your human DBA would have to remember which dbspaces to ignore on which servers. This ain't gonna fly; it won't even walk! The most obvious solution is to put the above command in a cron job, customized for each server we want to monitor. This is actually a great idea but fails in the details department. If you peruse the script, you will note that is simply assumes that: - All the programs it calls are sitting neatly in the $PATH . - The vital Informix environment variables - INFORMIXSERVER, INFORMIXDIR, and possibly ONCONFIG - are all neatly in place. These assumptions are necessary in order to be able to run monitor-space.sh from the command line. When a user logs is, they are made true by a .profile (or .login - whatever). But they are totally false when you run a cron job. When running ANY command from cron, it is vital to wrap the command in another script that sets the appropriate environment variables. monitor-space.cron ------------------ Well, since I need a wrapper anyway (in lieu of a .profile) to set environment variables, I might as well use the same wrapper to customize the call to monitor-space.sh. This set includes a template script that wraps the invokation of monitor-space.sh. It is up to you, the DBA, to fill in all the blanks. As supplied, it should generate errors if any question mark remains. It would be a good idea to name the wrapping script according to the server. For example, if INFORMIXSERVER=yutz then the filled-in cron script should be named "monitor-space.yutz.cron". Finally, here's what the cron line would look like: # Every morning, 09:15 : Check out how full the dbspaces are and # complain about those that are too full # 15 09 * * * /whatever/path/monitor-space.yutz.cron ======================================================================== @EOF set `sum $sumopt dbspace-pages.sh <<'@EOF' #!/usr/bin/ksh # dbspace-pages.sh # Similar in purpose to dpspace-space.sh: Display the number of free # pages and the percentage used for each dbspace. Since this uses # the output of onstat -d, it should execute far faster than # dbspace-space.sh. # # Author: Jacob Salomon # JakeSalomon@netscape.net # Date: 1999-Sep-09 # Release: 1.0 # onstat -d | awk ' BEGIN { hex_digit_pattern = "[0-9a-f]" hex_pattern="" for (lc=1; lc<=8; lc++) hex_pattern = hex_pattern hex_digit_pattern } $1 == "address" {next} # Skip informational lines that might /active,/ {next} # otherwise sneak past other filters /Dbspaces/ { dbspace_section = 1 next } /Chunks/ { dbspace_section = 0 chunk_section = 1 next } dbspace_section == 1 { if (!($1 ~ hex_pattern)) next # If not on a real data line, skip this dbspnum = $2 # dbspace number high_dbsnum = dbspnum # Limit my looping at END dbspname[dbspnum] = $NF # Keep the dbspace name as well } chunk_section == 1 { if (!($1 ~ hex_pattern)) next # If not on a real data line, skip this dbspnum = $3 # Again, index by dbspace number chunk_count[dbspnum]++ # Tally number of chunks in this dbspace dbsp_pages[dbspnum] += $5 # Add up number of pages to this dbspace chunk_freepg = $6 # Free page count for chunk. If blob, will # be proceeded by a ~ ; get rid of it! if (substr(chunk_freepg,1,1) == "~") # If this is the case, chunk_freepg = substr(chunk_freepg,2) # then lose the tilde dbsp_freepg[dbspnum] += chunk_freepg # NOW I can tally free pages in dbspace } END { out_format = "%s|%d|%d|%d|%d|%6.2f|\n" printf("DB-Space |DBS-Num|NumChunks|TotPages|FreePages|%%-Full|\n") for (lc=1; lc<= high_dbsnum; lc++) { ratio_full = ((dbsp_pages[lc] - dbsp_freepg[lc])/dbsp_pages[lc]) percent_full[lc] = ratio_full * 100 printf(out_format, dbspname[lc], lc, chunk_count[lc], dbsp_pages[lc], dbsp_freepg[lc], percent_full[lc]) } } ' | beautify-unl.sh @EOF set `sum $sumopt monitor-space.sh <<'@EOF' #!/usr/bin/ksh # monitor-space.sh - Guard for dbspace getting too full. # This script runs the script "dbspace-pagtes.sh" and filters for the # dbspace names and percentage of used pages. If any dbspace is full by # more than a certain (admittedly arbitrary) percentage, it generates a # mail to the dba group. # # Author: Jacob Salomon # JakeSalomon@netscape.net # Release 1.0: September 10, 1999 # Release 1.1: December 7, 1999 # Release 1.2: December 23, 1999 # # Of course, some dbspaces are expected to be nearly full - like those # containing the physical and logical logs. Those need to be ignored. # # Release 1.1: # Rather than require ignored dbspaces to be hard-coded, use para- # meters. Also, start logic to parametetrize spaces to monitor # (rather than ignore) dbspaces. # # Release 1.2: # Parametrize the percent-full cutoff, rather than hard-coding it. # -------------------------------------------------------------------- # Usage help: # proc_name=$0 usage() { cat <<%% Usage: $proc_name [-h] [-c integer] [-i dbspace [dbspace...]] A space between an option and its parameter is mandatory ----- ------ --------- -h : Print this help text and exit. (Ignores all other parameters) -c : Cutoff point for percent full: Generate a warning if dbspaces are more than % full -i : List of dbspaces to ignore. eg: Name of dbspace dedicated to logical logs, which is usually nearly full but will not fill further. %% } # Arbitrarily, I am setting 98% as the default cutoff point to generate # a warning of dbspace too full. Change this by using the -c option. # PCT_CUTOFF=98 # -------------------------------------------------------------------- # # Define function to handle command-line parameters # parse_params() # 1-use function to parse the parameters, if any. { # Set up list of dbspaces to ignore based on the parameters following # the -i option. # full_cutoff=$PCT_CUTOFF # Set with default value ign_count=0 # Initial counter for non-counted dbspaces ign_list="" # Ignore list starts empty mon_list="" # Monitor list starts empty cur_list="" # Not in any particular list now while [ $# -gt 0 ] do case $1 in -h) # Asking for help usage exit 0 ;; -i) # Begin an ignore list cur_list=ign_space ;; -m) # Begin a monitor list cur_list=mon_space echo The -m option is not yet implemented exit 3 ;; -c) # Preface to a cutoff value cur_list=cutoff_value ;; *) cur_param=$1 # Current dbspace in list - On what list does it go? case $cur_list in ign_space) ign_count=$((ign_count+1)) # Tally up array counter ign_space[$ign_count]=$cur_param # Save dbspace in array ;; mon_space) mon_count=$((mon_count+1)) # Tally up array counter mon_space[$mon_count]=$dsp # Save dbspace in array ;; cutoff_value) full_cutoff=$cur_param # Reset cutoff factor ;; esac ;; esac shift done # Now piece together the grep command to ignore some lines # Note that the dbspace-pages.sh command also produces a header line; # might as well filter this out as well. # grep_cmd="grep -v -e DB-Space" lc=1 # Prep a loop counter/array index while [ $lc -le $ign_count ] do # Append another ingorable to command grep_cmd=${grep_cmd}" -e ${ign_space[$lc]}" lc=$((lc + 1)) # Set for next round done #echo $grep_cmd } # -------------------------------------------------------------------- # # Set up the grep command to filter out the dbspaces to be ignored # parse_params $* TFILE=/tmp/dbspace-warnings.$$ # # We are now ready to execute a command to display dbspace information # and filter it through the ignore-list # dbspace-pages.sh | $grep_cmd | awk -v cutoff=$full_cutoff -F'|' ' $6 >= cutoff { warn_count += 1 # Count up warnings; array index warning[warn_count] = sprintf("Warning: DBSpace %s is %6.2f%% full\n", $1, $6) } END { if (warn_count > 0) # If I generated any warnings { for (lc = 1; lc <= warn_count; lc++) print warning[lc] } } ' >$TFILE # Get the warnings into the file # # Now, was there any warning generated? # If file size is 0, it's a sure bet the above generated no warning. # if [ -s $TFILE ] then # The file has substance cat $TFILE #else fi rm $TFILE # Get rid of the temp file clutter #---- @EOF set `sum $sumopt monitor-space.cron <<'@EOF' #!/usr/bin/ksh # monitor-space.cron - Cron-wrapper for monitor-space.sh # Author: Jacob Salomon # Date: 1999-12-23 # # This script is a wrapper for another shell script. Here I do for the # cron job what a .profile does for a login session. Except that THIS # one is merely a template - it's up the the DBA to fill the appropriate # blanks. # # First, set vital environment variables: # DBCENTURY=C INFORMIXDIR=[?????????????] INFORMIXSERVER=[?????????????] ONCONFIG=[?????????????] export DBCENTURY INFORMIXDIR INFORMIXSERVER ONCONFIG PATH=[Whatever is necessary to make all comands accessible] PATH=${PATH}:${INFORMIXDIR}/bin export PATH #---- # Customize these lines to vary behavior # # Example: PERCENT=95 PERCENT=[??] # Example: IGNORE_LIST="phys_dbs log_dbs" IGNORE_LIST="????????????????" # Example: MAIL_LIST=dba MAIL_LIST="???????? ?????? ???????" # End Customize # Now do the main task: # OFILE=/tmp/monitor-space.$$.out monitor-space.sh -i $IGNORE_LIST -c $PERCENT 2>&1 >$OFILE # Issue e-mail only if monitor-space.sh generated some output. # if [ -s $OFILE ] then cat $OFILE | mailx -s "Host/Server: $(hostname)/$INFORMIXSERVER : Full dbspaces" \ $MAIL_LIST fi rm $OFILE @EOF set `sum $sumopt