劍道第一仙

導(dǎo)航

首頁
新隨筆
聯(lián)系
管理

公告

gaussdb通過編寫shell腳本自動化執(zhí)行查詢和結(jié)果收集

轉(zhuǎn)：https://support.huaweicloud.com/pwp-dws/dws_13_00033.html

1、登錄ECS，進(jìn)入到/opt目錄下，使用vim命令生成query.conf和run_query.sh兩個腳本文件。腳本內(nèi)容如下，編輯后按:wq!保存腳本配置：

run_query.sh腳本如下：

#!/bin/bash
script_path=$(cd `dirname $0`;pwd)
query_mode=$1
query_path=$2
query_object=$3
query_log=${script_path}/query_log_`date +%y%m%d_%H%M%S`
source ${script_path}/query.conf

function usage()
{
    echo "[NOTICE]: This script is used to run queries and collect cost time, according to sepcified path and query file name."
    echo "          You can run the script as below:" 
    echo -e ""
    echo "          1. config the query.conf file."
    echo "          2. run the script in batch mode. "
    echo "          eg. sh run_query.sh batch [query file's absolute path]"
    echo -e ""
    echo "          3. run the script in single mode."
    echo "          eg. sh run_query.sh single [query file's absolute path] [specified query file name]"
}

function log_file_init()
{
    mkdir -p ${query_log}/explain_log
    mkdir -p ${query_log}/pre_warm_log
    mkdir -p ${query_log}/query_test_log
    touch ${query_log}/query_result.csv
    echo "query name,cost time1,cost time2,cost time3,average cost" > ${query_log}/query_result.csv
}

function single_query()
{
    echo "[INFO]: Single mode query is to start."
    echo "*****${query_object} begin*****"
    collect_plan
    pre_warm
    query_test
    echo "*****${query_object} end*****"
    echo "[INFO]: Single mode query is finished."
    echo "[NOTICE]: Get more details by query_result.csv and other logs in ${query_log}."
}

function batch_query()
{
    echo "[INFO]: Batch mode query is to start."
    for query_object in `ls ${query_path}`
    do
        echo "*****${query_object} begin*****"
        collect_plan
        pre_warm
        query_test
        echo "*****${query_object} end*****"
    done
    echo "[INFO]: Batch mode query is finished."
    echo "[NOTICE]: Get more details by query_result.csv and other logs in ${query_log}."
}

function collect_plan()
{
    echo "[STEP1]: Starting to collect plan."
    echo "explain performance" > ${query_log}/explain_log/${query_object}.tmp
    cat ${query_path}/${query_object} >> ${query_log}/explain_log/${query_object}.tmp
    gsql -h ${cluster_ip} -d ${db_name} -p ${db_port} -U ${db_user} -W "${user_pwd}" -f ${query_log}/explain_log/${query_object}.tmp > ${query_log}/explain_log/${query_object}.explain 2>&1
    echo "[STEP1]: Finished."
}

function pre_warm()
{
    echo "[STEP2]: Starting to pre-warm."
    for i in {1..2}
    do
        gsql -h ${cluster_ip} -d ${db_name} -p ${db_port} -U ${db_user} -W "${user_pwd}" -f ${query_path}/${query_object} > ${query_log}/pre_warm_log/${query_object}.pre${i} 2>&1
    done
    echo "[STEP2]: Finished."
}

function query_test()
{
    time1=""
    time2=""
    time3=""
    echo "[STEP3]: Starting to do real test."
    for i in {1..3}
    do
        gsql -h ${cluster_ip} -d ${db_name} -p ${db_port} -U ${db_user} -W "${user_pwd}" -f ${query_path}/${query_object} > ${query_log}/query_test_log/${query_object}.real${i} 2>&1
        let "`eval echo "time"${i}`=`cat ${query_log}/query_test_log/${query_object}.real${i}|grep "total time:"|awk {'print$3'}`"
    done
    time_ave=`echo "scale=2;(${time1}+${time2}+${time3})/3"|bc -l`
    echo "${query_object},${time1},${time2},${time3},${time_ave}" >> ${query_log}/query_result.csv
    echo "[step3]: Finished. The average time:${time_ave} ms."
}
case ${query_mode} in
    single)log_file_init;single_query;;
    batch)log_file_init;batch_query;;
    *)usage;;
esac

query.conf為集群信息配置文件，包含如下五個變量

cluster_ip=127.0.0.1                集群主cn節(jié)點內(nèi)網(wǎng)ip 
db_name=tpcds_test                  數(shù)據(jù)庫名稱
db_port=6000                        數(shù)據(jù)庫端口號
db_user=tpcds_user                  數(shù)據(jù)庫用戶
user_pwd=123456                     數(shù)據(jù)庫用戶密碼

2、編輯query.conf為集群對應(yīng)的信息后，先執(zhí)行source gsql_env變量后，執(zhí)行sh run_query.sh即可開始查詢執(zhí)行和結(jié)果收集。

示例：sh run_query.sh batch query1000x/

參數(shù)1：批量執(zhí)行選擇batch，單個query執(zhí)行選擇single。

參數(shù)2：tpcds1000x或者tpch1000x query存放的絕對路徑。

參數(shù)3：如果參數(shù)1選擇batch，此參數(shù)忽略；如果參數(shù)1選擇single，此參數(shù)為具體執(zhí)行的query名稱，例如Q1。

須知：
1. gsql客戶端的使用需要每次連接后，source gsql_env，執(zhí)行查詢腳本前請確認(rèn)gsql可執(zhí)行。
2. query1000x文件夾中必現(xiàn)包含tpch 22個標(biāo)準(zhǔn)查詢文件Q1~Q22或者tpcds 99個標(biāo)準(zhǔn)查詢文件Q1~Q99。
3. run_query.sh腳本依賴bc命令，執(zhí)行前確認(rèn)bc命令存在。
4、 每個查詢默認(rèn)會跑6次，第1次收集執(zhí)行計劃，第2，3次預(yù)熱，第4到第6次正式查詢，最終結(jié)果取后3次查詢的平均值。
5、 查詢腳本執(zhí)行后會立即生成query_log_yymmdd_hhmmss名稱的目錄，其中
    exlain_log子目錄存放查詢計劃。
    pre_warm子目錄存放預(yù)熱執(zhí)行結(jié)果。
    query_test子目錄存放正式查詢執(zhí)行結(jié)果。
    query_result.csv文件，csv格式匯總所有查詢的執(zhí)行結(jié)果。

posted on 2024-04-18 14:20 劍道第一仙閱讀(743) 評論(0) 收藏舉報

刷新頁面返回頂部