ASM Metrics Collector
This is a set of scripts for collecting and processing Oracle ASM Metrics.
When the data is collected from the ASM instance, data for all disks, diskgroups and databases is collected.
As this is file based CSV data, it is fairly easy to filter and aggregate the data as needed.
Following are descriptions of many of the scripts. A later section has examples of processing the data and charting it with Excel.
Note: These are Bash scripts, and as you might expect, these work on Linux and other Unix like OS's.
The Excel charting requires Perl with the Excel::Writer::XLSX
module installed.
Of course, you can always just manually load the CSV into Excel for charting.
Scripts
asm-metrics-collector.pl
Collect ASM metrics for further processing.
asm-metrics-collector.sh
bash driver example
asm-metrics-aggregator.pl
This script aggregates metrics based on one column from raw metrics data. eg. Aggregate IO metrics per diskgroup
asm-metrics-aggregator.sh
bash driver example - aggregates by diskgroup
asm-metrics-aggregator_bydisk.sh
bash driver example - aggregates by disk
asm-metrics-chart.pl
Create Excel file with charts of metrics
asm-metrics-chart.sh
bash driver example using aggregates by diskgroup
asm-metrics-chart_bydisk.sh
bash driver example using aggregates by diskname
asm-metrics-validate.pl
Perl script used to look for negative values in raw CSV files. Used for some debugging. This script can be used to look for any value with minor adjustment
verify.sh
bash script used for verifying aggregates during development
asm-metrics-fix.pl
asm-metrics-collector.pl had a bug where HH was used in the timestamp format when really it should have been HH24.
This bug resulted in PM timestamps that were 12 hours off. In addition when the time changed from 12:59 to 13:00, this bug resulted in negative elapsed times.
This script will fix the snaptime and elapsed time in output files. A new filename with '-corrected' inserting in the name will be created.
example:
asm-metrics-fix.pl asm-metrics-1.csv asm-metrics-2.csv asm-metrics-3.csv
Three new corrected files will be created: asm-metrics-1-corrected.csv asm-metrics-2-corrected.csv asm-metrics-3-corrected.csv
Processing ASM Metrics Data
Data is raw. Metrics are per diskgroup and disk every 20 seconds.
So, one line for each disk.
For analysis and charting, the data is being aggregated.
Some detail is lost, but this level of detail is not necessary for charting trends.
Aggregate
Make a copy of asm-metrics-aggregator.sh
, and modifify the default file names.
Or to run against an entire directory of files , use asm-metrics-aggregator-loop.sh
instead.
To see the effect, the asm-metrics-aggregator.sh
script was used to process 10000 lines of ASM metrics data in the file before.csv
.
wc -l before.csv after.csv
10000 before.csv
51 after.csv
10051 total
The metrics are provided per diskgroup, per snapshot time.
The metric values have all been summed, so the the average values can be calculated as needed.
before.csv
$ head -5 before.csv
DISPLAYTIME,SNAPTIME,ELAPSEDTIME,INSTNAME,DBNAME,GROUP_NUMBER,DISK_NUMBER,DISKGROUP_NAME,READS,WRITES,READ_TIME,AVG_READ_TIME,WRITE_TIME,AVG_WRITE_TIME,BYTES_READ,BYTES_WRITTEN,COLD_BYTES_READ,COLD_BYTES_WRITTEN,COLD_READS,COLD_USED_MB,COLD_WRITES,CREATE_DATE,DISK_NAME,FAILGROUP,FAILGROUP_TYPE,FREE_MB,HEADER_STATUS,HOT_BYTES_READ,HOT_BYTES_WRITTEN,HOT_READS,HOT_USED_MB,HOT_WRITES,LABEL,MOUNT_DATE,OS_MB,PATH,PREFERRED_READ,PRODUCT,READ_ERRS,REDUNDANCY,REPAIR_TIMER,SECTOR_SIZE,TOTAL_MB,UDID,VOTING_FILE,WRITE_ERRS
2020-07-31 08:55:35,2020-07-31 08:55:35.629847,20.444252,PRDBTS13,PRDBTS1,3,24,DATA,0,206,0,0,0.483442000004288,0.00234680582526354,0,1843200,0,1843200,0,1673632,206,2018-04-13 13:46:15,DATA_0024,DATA_0024,REGULAR,374540,MEMBER,0,0,0,48980,0,,2019-10-12 22:52:43,2097152,/dev/mapper/asm_data_25p1,U,,0,UNKNOWN,0,512,2097152,,N,0
2020-07-31 08:55:35,2020-07-31 08:55:35.629847,20.444252,PRDBTS11,PRDBTS1,3,48,DATA,0,4,0,0,0.003153999998176,0.000788499999544001,0,49152,0,49152,0,1673236,4,2020-02-25 15:53:42,DATA_0048,DATA_0048,REGULAR,374916,MEMBER,0,0,0,49000,0,,2020-02-25 15:53:44,2097152,/dev/mapper/asm_data_49p1,U,,0,UNKNOWN,0,512,2097152,,N,0
2020-07-31 08:55:35,2020-07-31 08:55:35.629847,20.444252,PRDBTS14,PRDBTS1,4,0,FRA,0,0,0,0,0,0,0,0,0,0,0,504391,0,2017-06-14 13:54:53,FRA_0000,FRA_0000,REGULAR,1592761,MEMBER,0,0,0,0,0,,2019-10-12 22:52:43,2097152,/dev/mapper/asm_fra_1p1,U,,0,UNKNOWN,0,512,2097152,,N,0
2020-07-31 08:55:35,2020-07-31 08:55:35.629847,20.444252,PRDBTS14,PRDBTS1,6,7,REDO,752,8,0.343787999998312,0.000457164893614777,0.0216940000009345,0.00271175000011681,12435456,131072,12435456,131072,752,55,8,2020-02-26 11:38:29,REDO_0007,REDO_0007,REGULAR,20865,MEMBER,0,0,0,81480,0,,2020-02-26 11:38:29,102400,/dev/mapper/asm_redo_8p1,U,,0,UNKNOWN,0,512,102400,,N,0
after.csv
$ head -6 after.csv
DISPLAYTIME,ELAPSEDTIME,DISKGROUP_NAME,READS,WRITES,READ_TIME,WRITE_TIME,BYTES_READ,BYTES_WRITTEN,READ_ERRS,WRITE_ERRS
2020-07-31 08:55:35,20.444252,FRA,80,807,31.6627970000747,46.6677670000765,1671680,21086720,0,0
2020-07-31 08:55:35,20.444252,GRID,64,196,0.0423570000002656,0.273379000002024,991232,8220672,0,0
2020-07-31 08:55:35,20.444252,ACFS,0,0,0,0,0,0,0,0
2020-07-31 08:55:35,20.444252,REDO,6259,43555,781.33763800023,64.7069339999862,436242432,336217088,0,0
2020-07-31 08:55:35,20.444252,DATA,187799,88229,1004.5424800001,875.308896999404,47032052224,1512292352,0,0
Usually asm-metrics-aggregator-loop.sh
script is used, which reads files from the logs
directory, and writes to the output
directory.
Synthesize Averages
So as to avoid doing this step in Excel, Google Sheets, or other charting tools, the averages can be calcualted for average read and write times, and written to a new file.
The scripts that do this:
- asm-metrics-synth.pl
- asm-metrics-synth.sh
The asm-metrics-synth.sh
script is just a driver for asm-metrics-synth.pl
, and gets its input from the output
directory of the previous step.
New files are written to the synth
directory.
Let's try a test on the after.csv
file created earlier
$ ./asm-metrics-synth.pl after.csv synth.csv
> head -6 after.csv synth.csv
==> after.csv <==
DISPLAYTIME,ELAPSEDTIME,DISKGROUP_NAME,READS,WRITES,READ_TIME,WRITE_TIME,BYTES_READ,BYTES_WRITTEN,READ_ERRS,WRITE_ERRS
2020-07-31 08:55:35,20.444252,FRA,80,807,31.6627970000747,46.6677670000765,1671680,21086720,0,0
2020-07-31 08:55:35,20.444252,GRID,64,196,0.0423570000002656,0.273379000002024,991232,8220672,0,0
2020-07-31 08:55:35,20.444252,ACFS,0,0,0,0,0,0,0,0
2020-07-31 08:55:35,20.444252,REDO,6259,43555,781.33763800023,64.7069339999862,436242432,336217088,0,0
2020-07-31 08:55:35,20.444252,DATA,187799,88229,1004.5424800001,875.308896999404,47032052224,1512292352,0,0
==> synth.csv <==
DATE,TIME,ELAPSEDTIME,DISKGROUP_NAME,AVG_READ_TIME,AVG_WRITE_TIME,READS,WRITES,READ_TIME,WRITE_TIME,BYTES_READ,BYTES_WRITTEN,READ_ERRS,WRITE_ERRS
2020-07-31,08:55:35,20.444252,FRA,0.395785,0.057829,80,807,31.6627970000747,46.6677670000765,1671680,21086720,0,0
2020-07-31,08:55:35,20.444252,GRID,0.000662,0.001395,64,196,0.0423570000002656,0.273379000002024,991232,8220672,0,0
2020-07-31,08:55:35,20.444252,ACFS,0,0,0,0,0,0,0,0,0,0
2020-07-31,08:55:35,20.444252,REDO,0.124834,0.001486,6259,43555,781.33763800023,64.7069339999862,436242432,336217088,0,0
2020-07-31,08:55:35,20.444252,DATA,0.005349,0.009921,187799,88229,1004.5424800001,875.308896999404,47032052224,1512292352,0,0
There are 2 changes made to the synth.csv
file.
- DISPLAYTIME has been split into DATE and TIME columns
- New columns are AVG_READ_TIME and AVG_WRITE_TIME
Breaking out diskgroups
To further simplify charting, the data can be split into diskgroup based files.
The asm-diskgroup-breakout.sh
is a fairly simple Bash script that creates diskgroup based files, taking its input from the synth
directory of the previous step.
New files are written to the diskgroup-breakout
directory.
Remove Outliers and Spikes
Sometimes there may be outliers and very large spikes. These tend to skew the data and make the charts hard to read.
In extreme cases a chart may consist of some spike connected by a flat line.
Normally you would like to see that data charted.
The data for each file is piped through two Python scripts, outlier-remove.py
and flatten.py
.
These CSV files and XLS files are placed in directories with a suffix of '-chart'.
Should you not want these, simply comment out the asm-*clean*.sh
lines in runall.sh
.
Charting Data
The data can now be charted using asm-metrics-chart.pl
.
There are already a couple of shell driver scripts for this task:
- asm-metrics-chart.sh
- asm-metrics-chart-synth.sh
asm-metrics-chart.sh
This script is hardcoded to create a single Excel file from an ASM Metrics file.
It can be used as the basis for a script that processes an entire directory of files.
asm-metrics-chart-synth.sh
This script is configured to process all the csv files in a single directory.
The input is taken from the diskgroup-breakout
directory created in the previous step.
The output is Excel files, written to the xlsx-by-diskgroup
directory.
Running all scripts
The commands to process all data were put into a the Bash script, runall.sh
.
#!/usr/bin/env bash
./asm-metrics-aggregator-loop.sh
./asm-metrics-synth.sh
./asm-diskgroup-breakout.sh
./asm-metrics-chart-synth.sh
$ time ./runall.sh
working on output/asm-data-20200731-085514.csv
working on output/asm-data-20200801-084753.csv
working on output/asm-data-20200802-083738.csv
working on output/asm-data-20200803-083430.csv
working on output/asm-data-20200804-083016.csv
working on output/asm-data-20200805-083703.csv
working on synth/asm-data-20200731-085514.csv
working on synth/asm-data-20200801-084753.csv
working on synth/asm-data-20200802-083738.csv
working on synth/asm-data-20200803-083430.csv
working on synth/asm-data-20200804-083016.csv
working on synth/asm-data-20200805-083703.csv
working on synth/asm-data-20200731-085514.csv
working on synth/asm-data-20200801-084753.csv
working on synth/asm-data-20200802-083738.csv
working on synth/asm-data-20200803-083430.csv
working on synth/asm-data-20200804-083016.csv
working on synth/asm-data-20200805-083703.csv
working on xlsx-by-diskgroup/ACFS.xlsx
working on xlsx-by-diskgroup/DATA.xlsx
working on xlsx-by-diskgroup/FRA.xlsx
working on xlsx-by-diskgroup/GRID.xlsx
working on xlsx-by-diskgroup/REDO.xlsx
real 4m53.885s
user 3m44.880s
sys 0m23.900s
Running all scripts for a collection of files per RAC system
ASM Metrics files are to be organized like this:
cluster-01/
node-01/logs
node-02/logs
node-03/logs
cluster-02
node-01/logs
node-02/logs
node-03/logs
node-04/logs
...
Then:
./runall-cluster.sh