Subject: Store nmon Data in a Relational Database
Date: April 7, 2007
The scripts in the
file can be used to
store nmon data from multiple hosts in a centralized SQL database.
Putting performance data into a relational database has several
benefits, including trend analysis, aggregating performance data
across partitions on a physical server, and extracting data for
analysis by other tools.
Here's the setup instructions.
Extract the files
- gunzip -dc nmon2mysql.tar.gz | tar -xvf-
Create the SQL tables (do this only once!)
- Create a nmon output file "nmon -f"
- Create a file containing the "insert into" statements.
- Edit nmon2mysql.pl. Change $NMON_DIR (directory containing the nmon file(s)) and $OUTPUT_DIR (directory where you want the sql files to go)
- Create the DDL commands to create the table. The "sed" command
customizes the field statements. (It was easier to do in sed than perl.)
- nmon2ddl.pl filename.sql | sed -f nmon2ddl.sed > nmon.ddl
- Create all the database tables (I use "nmon" as the database name. If you use a different name, you'll need to change it in nmon.ddl)
Load the nmon data to the Database (do this daily)
- Collect nmon data using "nmon -f" (assume output filename is filename.nmon)
Comment: you can consolidate data from multiple servers into one database.
- Create the "insert into" SQL commands
- Put the nmon files into $NMON_DIR
- The SQL files will be saved in $OUTPUT_DIR
- Load the data (this command is specific to MySQL. Use the equivalent for your database)
- mysql -u root nmon < filename.sql
Suggestions and Limitations
- For capacity planning, I recommend a sampling interval of 15-20 min or higher.
Shorter intervals create a lot of data (disk space), and do not show trends as clearly.
- These scripts are specific to nmon v11. Different nmon versions have different
row columns, which will cause problems for my load program. Some day I'll rewrite
these programs to make column changes less disruptive.
- Extra credit: In Step 3c, you can load the "filename.sql" nmon data directly into
the central database from the remote AIX server. You'd need the "mysql -h hostname" flag
(eg "mysql -h ....") You'll also need to load the MySQL client code
rpm on the remote servers.
I import the nmon data in MySQL directly into Excel Pivot tables/charts. Pivot
tables/charts can be use to display individual server performance or aggregate
performance across micropartitions.
To import MySQL data, use Excel's "Data => Import External Data => New Database Query"
You'll need the Excel ODBC driver (see below). I'm in the middle of writing an
Excel VBA program to automate the process. I'll post the spreadsheet when
How to setup MySQL on AIX: http://www.automateexcel.com/index.php/2005/11/01/connect_excel_to_mysql_database
Excel ODBC driver for MySQL: http://www-941.ibm.com/collaboration/wiki/display/WikiPtype/aixopen
April 7, 2007