- 论坛徽章:
- 0
|
[标题]:
实时监控数据库表的数据插入情况
环境 产品,平台,机型,软件版本,等)
与具体的环境无关
问题描述:
当我们在向数据库表装载数据或插入数据的时候,需要对其插入的效率进行实时监控,例如:当前对这张表的插入效率如何,XX笔/秒,对索引空间的使用情况如何,XX页/秒。通过对这些具体数据的实时监控,我们可以对插入数据的方式、方法进行适时调整,以获得最佳的插入效率
解答:
#!/bin/sh
###############################################################################
#
# Module: count_insert.sh
# Author: Richard ZHAN
# Description: Trace and calculate the inserting effectiveness by every partition
# For tables calculated by nubmer of rows
# For indices calculated by nubmer of used pages
#
# Change Log
#
# Date Name Description.................
# 25/02/2009 Richard ZHAN Start Program
#
###############################################################################
usage()
{
printf "usage: count_insert.sh database tablename\n"
exit 1
}
# check usage; exit if incorrect. valid argument count is 2.
[ $# -ne 2 ] && usage
AWK=""
if [ -z "$AWK" ]
then for awker in gawk nawk awk
do
for dir in `echo $PATH | sed 's/:/ /g'`
do
if [ -x "$dir/$awker" ]
then AWK="$dir/$awker"
break 2
fi
done
done
fi
################################################################################
# #
# MAIN BODY OF SCRIPT #
# #
################################################################################
#Initialize environment
cnt=0
count_insert="count_insert.txt"
tmp_count_insert="${count_insert}.$$"
result_count_insert="result_${count_insert}"
rm -f ${count_insert}
rm -f ${tmp_count_insert}
#rm -f ${result_count_insert}
while [ ${cnt} -lt 2000 ]
do
date=`date "+%H%M%S"`
# seconds=`expr ${date} / 10000 \* 3600 + ${date} / 100 % 100 \* 60 + ${date} % 100`
(onstat -d;oncheck -pt $1 2) | ${AWK} -v date=${date} -v count_insert=${count_insert} -v tmp_count_insert=${tmp_count_insert} '
BEGIN {flag=0;Dbspaces_flag=0;}
#For dbspace
/^Dbspaces/ {Dbspaces_flag=1;}
Dbspaces_flag == 1 {
if ( $2 ~ /[0-9]+/ ) {
Dbspaces[$2] = $NF;
} else if ( $0 ~ /maximum$/ ) {
Dbspaces_flag = 0;
}
}
#For table
/^TBLspace Report for/ || /Table fragment.*in/ {
if ( $0 ~ /^TBLspace Report for/ ) {
tablename=$4
flag = 1; #For nonfragmented table
} else {
printf "%s %s %s ", date, tablename, $5 >> count_insert; #For IDS 9.4x
printf "%s %s %s ", date, tablename, $5 >> tmp_count_insert; #For IDS 9.4x
# printf "%s %s %s ", date, tablename, $7 >> count_insert; #For IDS 10.0x
# printf "%s %s %s ", date, tablename, $7 >> tmp_count_insert; #For IDS 10.0x
flag = 2; #For fragmented table
}
}
#For index
/Index.*fragment.*in/ {
printf "%s %s %s ", date, $2, $6 >> count_insert; #For IDS 9.4x
printf "%s %s %s ", date, $2, $6 >> tmp_count_insert; #For IDS 9.4x
# printf "%s %s %s ", date, $2, $8 >> count_insert; #For IDS 10.0x
# printf "%s %s %s ", date, $2, $8 >> tmp_count_insert; #For IDS 10.0x
flag = 3;
}
/Number of rows/ && (flag == 1 || flag == 2) {
#For nonfragmented table
if ( flag == 1 ) {
nrows = $4;
#For fragmented table
} else {
printf "%d\n", $4 >> count_insert;
printf "%d\n", $4 >> tmp_count_insert;
flag = 0;
}
}
#For index
/Number of pages used/ && flag == 3 {
printf "%d\n", $5 >> count_insert;
printf "%d\n", $5 >> tmp_count_insert;
flag = 0;
}
#For nonfragmented table
/Partition partnum/ && flag == 1 {
printf "%s %s %s %d\n", date, tablename, Dbspaces[int($3/1048576)], nrows >> count_insert;
printf "%s %s %s %d\n", date, tablename, Dbspaces[int($3/1048576)], nrows >> tmp_count_insert;
flag = 0;
}
'
${AWK} 'BEGIN {date=0;}
{
if ( date == 0 ) {
date = $1;
second = int((date / 10000) % 100) * 3600 + int((date / 100) % 100) * 60 + date % 100;
}
if ( NF == 4 && date != $1 ) {
second1 = int(($1 / 10000) % 100) * 3600 + int(($1 / 100) % 100) * 60 + $1 % 100;
# printf "%s %s %s %d %d %d %d %d\n", $1, $2, $3, ($4 - array[$2,$3]) / (second1 - second), $4, array[$2,$3], second1, second;
printf "%s %s %s %d\n", $1, $2, $3, ($4 - array[$2,$3]) / (second1 - second);
} else {
array[$2,$3] = $4;
}
}
' ${count_insert} >> ${result_count_insert}
mv ${tmp_count_insert} ${count_insert}
sleep 1
cnt=`expr ${cnt} + 1`
done |
|