Performance Monitor By Ed Barlow Copyright(c) 1993-6 by Edward Barlow INTRODUCTION: This software written by Ed Barlow. This file contains information on a simple system/server monitor that can be used to monitor historical performance. perfmon is currently published as free software and can be given away / reused at will provided all copyrights remain intact. perfmon provides a nice way to see how your server is performing and whether your system is running into problems. The output will consist of about 20 graphs (time of day, julian day) depicting various metrics like cpu (from vmstat), disk io (from io stat), and some sybase related items (#users, #locks...). The tool uses gnuplot (not included) which produces very nice graphs. Enjoy Ed March, 1996 ------------------------- This package is composed of two parts, a monitor (perfmon) and a reporter (run_plots). The unix monitor runs out of cron on your unix system and collects iostat, vmstat, and sybase performance information into tables located in the monitored server. I suggest you set up a small database called perfmon. You choose the monitoring interval (crontab files are provided for every 3, 5, and 10 minutes). Values higher than 10 minutes will start to give choppy looking graphs. The monitoring software runs a 'constant' time' query (currently select * from sysdatabases) and measures time for the run as a proxy for relatvive performance. In addition to the monitoring script, a reporting script exists (run_plots). The reporting tool can run a central location to collate this data and generate nice graphical output using gnuplot. The plotting software produces really nice reports and should be run monthly and distributed to your business people - who like that kind of stuff. I believe this tool provides information that is necessary for us to understand historical performance on sybase. Output of run_plots is either an X window (if the config file is set to interactive) or to a postscript file. You get to figure out what to do with the postscript output... Io statistics are checked for the first 8 disks. To figure out which disks these are, do an 'iostat -D -l 8' and you will see them in order. If you want to see more, it should be a simple hack. Cpu stats are generated from vmstat. I dont know what this number means for an mp system (but it does mean something). The reporting software can be a bit slow on some systems - it does a space check in each database which tends to wait for locks. CHANGES TO EXISTING ENVIRONMENT each unix system: cron job to run perfmon task sybase server: perfmon_sys_stats & perfmon_serv_stats tables sp__monitor proc anywhere: reporter process INSTALLATION NOTES: Note: This is simple software. Data is collected on the *monitored* server. There is currently *no* way to change this. Note2: The plotting routines require the gnuplot utility in your path. Note3: If you want to be able to measure space in all databases, the monitoring login (whatever it is) must have access to those databases. I'd grant it sa_role (i think you need that level but you might get away with something lower). To install, o create a dbo account in the database you intend to use for stats. make sure default db for this account is the appropriate database. I recommend a separate (small) database for this tool. # create database mis on datadev=20 log on logdev=5 # sp_addlogin mis,password,mis # use mis # sp_addalias mis,dbo o create environment file - use enviroment.sample as baseline. rename it environment.DSQUERY. Be sure to change all the environment variables as appropriate. o run "configure environment.DSQUERY" - which creates ddl in your default db. If this does not run, your environment file is messed. o test your cofiguration by running 'perfmon environment.DSQUERY' THIS IS IMPORTANT. There is nothing more exasperating that logging in two weeks later and having no data. Check the database tables to see if there are rows after the run before continuing. o set up cron. Use as a basis the crontab.* files provided o check to see that data is being collected a few hours later. To Plot, o run_plot . If INTERACTIVE is specified in your environment file, be sure DISPLAY is set appropriately. Make sure that NUM_DAYS and CRON_MINUTES are out there. BUGS: Space can fill up - it does not clean itself up. Sorry. You can delete old data by hand. Note that at a row every few minutes, it will take a long time to fill up even a small 20MB database. Single datapoints can mess up averages - should be filtered on times that have >1 datapoint & Should print #datapoints by time chart CHANGES: 8/24/93 by EMB Fixed time interval between letter and cron by modifying env file Only override DISPLAY if not already set allow postscript or interactive via env file Tested login before continuing 8/26/93 by EMB Fixed divide by 0 in cover letter Modified stored procedure procs Made finance1 work Cleaned up doc 10/21/94 by EMB Modified 10/94 to add more disks and to give alternate cpu stats when in mp environment. Fixed 2 bugs - iostat seems to have changed when 4.1.3 was installed (no cpu stats anymore) and Sun 10's have mp architecture but it is silly to treat them as mp systems. 4/8/96 by EMB Improved documentation. SAMPLE OUTPUT ------------- I do not include sample postscript output for the charts (which are quite useful). The following is the cover page for the server sample: TO: DISTRIBUTION SUBJECT: System Performance Statistics for the sample Server DATE: 04/08/96 Attached are the performance metrics for the sample server. Data is collected every 10 minutes and saved in the server. The below summary numbers are calculated using 9AM to 6PM data averaged across 1 hour intervals. The enclosed charts are based on all available data (although many present results from only the past 30 days) . Please contact your System Administrator for explanations of any of the numbers. Data available from Apr 3 1996 5:56PM to Apr 8 1996 6:05PM Number of samples: 346 USER ACTIVITY USER CONNECTIONS: AVERAGE: 29.65 MAXIMUM: 50 SERVER PERFORMANCE BENCHMARK TIME: AVERAGE: 0.24 MAXIMUM: 1.16 PROCESSOR STATISTICS CPU Busy: AVERAGE: 51.00 MAXIMUM: 94.00 Runnable Procs: AVERAGE: 0.00 MAXIMUM: 5.00 Page Ins: AVERAGE: 6.00 MAXIMUM: 47.00 DISK ACCESS DISK2 Utilisation: AVERAGE: 1.00 MAXIMUM: 19.00 DISK3 Utilisation: AVERAGE: 0.00 MAXIMUM: 16.00 DISK4 Utilisation: AVERAGE: 0.00 MAXIMUM: 6.00 DISK5 Utilisation: AVERAGE: 0.00 MAXIMUM: 1.00 DISK6 Utilisation: AVERAGE: 6.00 MAXIMUM: 26.00 DISK7 Utilisation: AVERAGE: 5.00 MAXIMUM: 18.00 DISK8 Utilisation: AVERAGE: 0.00 MAXIMUM: 6.00 CURRENT DATABASE DISK UTILIZATION: Database Data Amount Name ize (MB) Used (MB) Percent xxx 60.00 13.45 22.42 abc 200.00 16.21 8.11 def 200.00 8.28 4.14 master 4.00 2.29 57.18 model 2.00 0.82 41.21 pubs 2.00 1.32 66.11 sybsecurity 25.00 1.24 4.98 sybsystempro 26.00 14.81 56.98 tempdb 102.00 1.01 0.99