AIX Tip of the Week

Subject: Store nmon Data in a Relational Database

Audience: All

Date: April 7, 2007

The scripts in the nmon2mysql.tar.gz 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

    1. gunzip -dc nmon2mysql.tar.gz | tar -xvf-

    Create the SQL tables (do this only once!)

    1. Create a nmon output file "nmon -f"
    2. 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)
      • nmon2mysql.pl
    3. 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
    4. 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)
      • mysql -u root < nmon.ddl

    Load the nmon data to the Database (do this daily)

    1. Collect nmon data using "nmon -f" (assume output filename is filename.nmon)
      Comment: you can consolidate data from multiple servers into one database.
    2. Create the "insert into" SQL commands
      • Put the nmon files into $NMON_DIR
      • nmon2mysql.pl
      • The SQL files will be saved in $OUTPUT_DIR
    3. Load the data (this command is specific to MySQL. Use the equivalent for your database)
      • mysql -u root nmon < filename.sql

    Done!

    Suggestions and Limitations

    1. 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.

    2. 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.
    3. 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.

    Charts

    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 completed.

    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


    Bruce Spencer,
    baspence@us.ibm.com

    April 7, 2007