VMSTAT Tools
As database administrators, we should have the ability to review and evaluate resources on the underlying hardware where our databases reside. Often this review is done during times of stress when there is a current performance problem. Fortunately, there are a number of tools that are useful for determining the resource usage on these platforms. I will focus on the VMSTAT tool in this blog entry.
VMSTAT is a tool on Unix type plaforms that provides an impressive amount of resource information, but is formatted in such a way that it can be difficult to read much less determine when the snapshot occurred if looking at historic snapshots. Here is an example
[root@new-host ~]# vmstat 2
procs ———–memory———- —swap– —–io—- –system– —–cpu——
r b swpd free buff cache si so bi bo in cs us sy id wa st
0 0 0 1916352 39376 906944 0 0 186 39 1024 1139 1 2 93 4 0
1 0 0 1916352 39384 906968 0 0 0 76 1015 1203 0 2 95 4 0
0 0 0 1916260 39392 906976 0 0 0 58 1014 1118 0 2 98 0 0
Through some simple Unix scripting commands, you can get more user-friendly information from this tool.
The vmstat_tool.sh script below works for most Linux versions:
#!/bin/sh
#vmstat_tool.sh
#vmstat parser script. Makes the information easily readable.
#Ensure that the number of loops and delay parameters
#were passed to this script
if [ $# -ne 2 ]
then
echo “Usage: $0
exit
fi
export LPCNT=$1
export DLAY=$2
c=0
while [ "$c" -lt $LPCNT ]
do
date
vmstat | tail -n 1 | awk ‘{printf(“%d processes are waiting\n%d processes are blocked\n%d virtual memory used, %d idle memory, %d buffer memory, %d cache memory\n%d memory swapped in from disk, %d memory swapped out to disk\n%d blocks per second written\n%d blocks per second read\n%d interrupts per second\n%d context switches per second\nCPU usage: %d%% (user, non-kernel), %d%% (system, kernel), %d%% idle, %d%% wait\n\n”,$1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16)}’
sleep $DLAY
c=$((c+1))
done
Sample Output (Single Loop):
Sat Feb 18 11:15:48 PST 2012
1 processes are waiting
0 processes are blocked
0 virtual memory used, 1990308 idle memory, 32964 buffer memory, 843564 cache memory
0 memory swapped in from disk, 0 memory swapped out to disk
928 blocks per second written
75 blocks per second read
1062 interrupts per second
1128 context switches per second
CPU usage: 4% (user, non-kernel), 2% (system, kernel), 77% idle, 17% wait
As you can see. the output is much more readable than the regular vmstat output.
Should you need to do more than just a cursory examination of the VMSTAT output, you can use these scripts to generate an output file for examination with the following method:
nohup vmstat_tool.sh 288 300 > ./vmstat.log &
In the above example, you are using the native nohup command to run the vmstat_tool.sh script to loop every 5 minutes (300 seconds) for 24 hours (288 5-minute loops). The output will to the vmstat.log script in the local directory and will run in the background by using ‘&’ at the end of the command. Examining the output is a simple matter of using the native cat and grep commands in the following manner:
cat ./vmstat.log | grep “processes are waiting” | more
Sample Output:
0 processes are waiting 0 processes are blocked
0 processes are waiting 0 processes are blocked
0 processes are waiting 0 processes are blocked
0 processes are waiting 0 processes are blocked
0 processes are waiting 0 processes are blocked
0 processes are waiting 0 processes are blocked
0 processes are waiting 0 processes are blocked
0 processes are waiting 0 processes are blocked
You can now see the output of the first line of the VMSTAT tool scrolled one screen at a time. If you see a line that has some abnormal values, you can then use the vi utility to zero in on that particular value and get the timestamp from it.
