Internal services traffic statistics.sh

==APP==

#!/bin/bash

cd `dirname $0`
CURDIR=`pwd`
CURL="/usr/bin/curl "

MYSQL_CLIENT="mysql -ujob_idx -p123456 -h10.0.0.162 --default-character-set=utf8 -Didx_db -t "
MYSQL_CLIENT2="mysql -ujob_querytrac -p123456 -h10.0.0.162 --default-character-set=utf8 -Dquery_tracker_db -t "
MYSQL_CLIENT3="mysql -ureadonly -p123456 -h10.2.0.112 --default-character-set=utf8 -Dsyslog_db -t "

SITE_URL="http://tc.corp.anjuke.com"

usage()
{
        echo "Usage: $0 -D 1";
        exit 1;
}

if [ $# -lt 1 ] ; then
        usage;
fi
while getopts H:D:S:cde opt
do
        case "$opt" in
                D) export D=$OPTARG;;
                c) echo "c is selected";;
                d) echo "d is selected";;
                e) echo "e is selected";;
                \?) usage;;
        esac
done

#-gt,-ge,-lt,-le,-eq

#发布
C_DATE=`date +%Y%m%d -d "$D days ago"`
C_DATE_s=`date +%y%m%d -d "$D days ago"`
C_DATE_f=`date +%Y"-"%m"-"%d -d "$D days ago"`
Y_DATE=`date +%Y%m%d -d "$((D+1)) days ago"`
T_DATE=`date +%Y%m%d -d "$((D-1)) days ago"`
#echo "$C_DATE"
#echo "$C_DATE_s"
#echo "$C_DATE_f"
#echo "$Y_DATE"
#echo "$T_DATE"
#exit

PAGE_TITLE="Internal_Services_Traffic_Statistics_$C_DATE"

APF_SQL="select a.Hostname,t.Program,t.Date,t.Logs as Rows from (select program,host,date,format(count(*),0) as logs from logs_$C_DATE where program!='last' group by host,program) as t left join hosts a on t.host=a.host;"

APF_LOG_ANALYSIS=`$MYSQL_CLIENT3 -e "$APF_SQL" | sed "s/^/ /g"`

APP_SQL1="select Hours,format(Request,0) as Request, format(Slow,0) as Slow,concat(format(Slow/Request*100,2),'%') as 'S/R' from (select substr(logtime,1,13) as Hours,sum(request_times) as Request, sum(request_slow) as Slow from idx_request where datei='$C_DATE_f' and port in ('20080','40080') and request_times>0 group by hours order by hours asc) as a;"

APP_HOURS_DIST=`$MYSQL_CLIENT -e "$APP_SQL1" | sed "s/^/ /g"`

APP_SQL2="select Date,format(Request,0) as Request, format(Slow,0) as Slow,concat(format(Slow/Request*100,2),'%') as 'S/R' from (select datei as Date,sum(request_times) as Request, sum(request_slow) as Slow from idx_request where datei='$C_DATE_f' and port in ('20080','40080') and request_times>0 ) as a;"

APP_OVERVIEW=`$MYSQL_CLIENT -e "$APP_SQL2" | sed "s/^/ /g"`

LSQL1="select Hours,format(Request,0) as Request, format(Slow,0) as Slow,concat(format(Slow/Request*100,2),'%') as 'S/R' from (select substr(logtime,1,13) as Hours,sum(request_times) as Request, sum(request_slow) as Slow from idx_request where datei='$C_DATE_f' and port in ('8983','8984','8986') and request_times>0  group by hours order by hours asc) as a;"

SOLR_HOURS_DIST=`$MYSQL_CLIENT -e "$LSQL1" | sed "s/^/ /g"`

LSQL2="select Date,format(Request,0) as Request, format(Slow,0) as Slow,concat(format(Slow/Request*100,2),'%') as 'S/R' from (select datei as Date,sum(request_times) as Request, sum(request_slow) as Slow from idx_request where datei='$C_DATE_f' and port in ('8983','8984','8986') and request_times>0 ) as a;"

SOLR_OVERVIEW=`$MYSQL_CLIENT -e "$LSQL2" | sed "s/^/ /g"`

MSQL1="select Hours,format(Request,0) as Get, format(Slow,0) as Misses,concat(format(Slow/Request*100,2),'%') as 'M/G' from (select substr(logtime,1,13) as Hours,sum(request_times) as Request, sum(request_slow) as Slow from idx_request where datei='$C_DATE_f' and port in ('11211') and request_times< '30000000' and request_times>0  group by hours order by hours asc) as a;"

MEMC_HOURS_DIST=`$MYSQL_CLIENT -e "$MSQL1" | sed "s/^/ /g"`

MSQL2="select Date,format(Request,0) as Get, format(Slow,0) as Misses,concat(format(Slow/Request*100,2),'%') as 'M/G' from (select datei as Date,sum(request_times) as Request, sum(request_slow) as Slow from idx_request where datei='$C_DATE_f' and port in ('11211') and request_times< '30000000' and request_times>0 ) as a;"

MEMC_OVERVIEW=`$MYSQL_CLIENT -e "$MSQL2" | sed "s/^/ /g"`

DSQL1="select Hours,format(Request,0) as COM_SELECT, format(Slow,0) as Slow,concat(format(Slow/Request*100,2),'%') as 'S/C'  from (select substr(logtime,1,13) as Hours, sum(com_select) as 'Request',sum(slow_queries) as 'Slow' from questions_statistics_by_minutes where datei='$C_DATE_f' and date(logtime)='$C_DATE_f' group by hours order by hours asc) as a;"

DB_HOURS_DIST=`$MYSQL_CLIENT2 -e "$DSQL1" | sed "s/^/ /g"`

DSQL2="select Date,format(Request,0) as COM_SELECT, format(Slow,0) as Slow,concat(format(Slow/Request*100,2),'%') as 'S/C'  from (select datei as Date, sum(com_select) as 'Request',sum(slow_queries) as 'Slow' from questions_statistics_by_minutes where datei='$C_DATE_f' and date(logtime)='$C_DATE_f') as a;"

DB_OVERVIEW=`$MYSQL_CLIENT2 -e "$DSQL2" | sed "s/^/ /g"`

HTML_CONTENTS='
==Paginated==
[[Internal Services Traffic Statistics '$Y_DATE'|<< Older]] '$C_DATE' [[Internal Services Traffic Statistics '$T_DATE'|Newer >>]]
==[http://syslog01-001.corp.anjuke.com/ APF Log Analysis]==
'$APF_LOG_ANALYSIS'
==[[IDC1 App V2 Monitor|PHP Web Applications]]==
===Overview===
'$APP_OVERVIEW'
===Hours Distribution===
'$APP_HOURS_DIST'
==[[IDC1 IDX Monitor|Solr]]==
===Overview===
'$SOLR_OVERVIEW'
===Hours Distribution===
'$SOLR_HOURS_DIST'
==[[Memcached Monitor|Memcached]]==
===Overview===
'$MEMC_OVERVIEW'
===Hours Distribution===
'$MEMC_HOURS_DIST'
==[[DB Queries Monitor|Database]]==
===Overview===
'$DB_OVERVIEW'
===Hours Distribution===
'$DB_HOURS_DIST'
==Invocation==
* APF Log
 '$APF_SQL'
* PHP 20080&40080
 '$APP_SQL1'
 '$APP_SQL2'
* SOLR 24 Hours
 '$LSQL1'
 '$LSQL2'
* Memcached
 '$MSQL1'
 '$MSQL2'
* DB
 '$DSQL1'
 '$DSQL2'
{{InternalServicesTrafficStatisticsComments}}
==References==
[[Image:Top.orig.png|thumb|right|[[#Paginated|TOP]]]]
* [[DB Queries Monitor]]
* [[IDC1 Anjuke V1 Monitor]]
* [[IDC1 App V2 Monitor]]
* [[IDC2 App V2 Monitor]]
* [[Memcached Monitor]]
* [[IDC1 IDX Monitor]]
==See Also==
* [[Anjuke Optimizing Trac tiger '$C_DATE_s']]
* [[Anjuke Optimizing Trac idx01 003 '$C_DATE_s']]
* [[Anjuke Optimizing Trac app01 003 '$C_DATE_s']]
* [[Sample db01 001 '$C_DATE_s'.log analyzes]]

[[Category:Internal Services Traffic Statistics]]
'

PAGE_CONTENT_FILE="/tmp/internal_services_traffic_statistics.txt"

echo "$HTML_CONTENTS" > "$PAGE_CONTENT_FILE"

VERSION_INFORMATION=`curl -s "http://tc.corp.anjuke.com/index.php?title=$PAGE_TITLE&action=edit" | grep --color -E 'name="wpAutoSummary"|name="wpStarttime"|name="wpEdittime"' |  sed 's/.*value="\([0-9a-z]\+\)".*/\1/g' | tr "\n" " "`

wpStarttime=`echo "$VERSION_INFORMATION" | awk '{print $1}'`
wpEdittime=`echo "$VERSION_INFORMATION" | awk '{print $2}'`
wpAutoSummary=`echo "$VERSION_INFORMATION" | awk '{print $3}'`

$CURL -H "" -i \
        --data-urlencode "wpSection=" \
        --data-urlencode "wpStarttime=$wpStarttime" \
        --data-urlencode "wpEdittime=$wpEdittime" \
        --data-urlencode "wpScrolltop=0" \
        --data-urlencode wpTextbox1@$PAGE_CONTENT_FILE \
        --data-urlencode "wpSummary=" \
        --data-urlencode "wpSave=Save page" \
        --data-urlencode "wpEditToken=+\\" \
        --data-urlencode "wpAutoSummary=$wpAutoSummary" \
        --user-agent "Opera/9.63 (X11; Linux i686; U; en) Presto/2.1.1" --referer "$SITE_URL/index.php?title=$PAGE_TITLE&action=edit" --silent \
        "$SITE_URL/index.php?title=$PAGE_TITLE&action=submit"

==References==