1010      阿裏雲  技術社區[雲棲]

Deepgreen(Greenplum)數據表集中Analyze腳本

數據加載完成以後，通常需要更新分析計劃，因為單獨做一個表的Analyze比較麻煩，整庫做Analyze會比較耗時，我們可以通過下麵這個腳本，指定Analyze部分表。
具體實踐時，隻需修改下麵對應的SQL語句、數據庫psql命令地址、IP端口及數據庫名即可：

#!/bin/bash
# filename: analyze_table.sh
# start time
start_time=$(date)
echo "-------- Start time is $start_time --------"
start_seconds=$(date +%s)
# get no partition tables
cmd_get_nopartitions="select schemaname||'.'||tablename as tablename from pg_tables where schemaname like 'rwnas_%' and tablename not like '%_1_prt_%' and schemaname||'.'||tablename not in (select schemaname||'.'||tablename from pg_partitions group by schemaname,tablename)"
exec_get_nopartitions=$(/app/greenplum-db-4.1.1.1/bin/psql -h 127.0.0.1 -p 5432 -d rwnas -U rwnas -t -c "${cmd_get_nopartitions}")
echo "${exec_get_nopartitions}" > nopartitions_tables.txt
echo "-------- No Partitions Tables List Below --------"
nopartitions_file=$(cat nopartitions_tables.txt)
echo "$nopartitions_file"
# analyze nopartition tables
rm -rf analyze_nopartitions_tables.txt
function for_np_file(){
for i in $nopartitions_file
do
echo "$(/app/greenplum-db-4.1.1.1/bin/psql -h 127.0.0.1 -p 5432 -d rwnas -U rwnas -c "analyze $i")"
echo "$i" >> analyze_nopartitions_tables.txt
done
}
for_np_file
# get partition tables
cmd_get_partitions="select schemaname||'.'||tablename as tablename from pg_partitions where schemaname like 'rwnas_%' group by schemaname, tablename"
exec_get_partitions=$(/app/greenplum-db-4.1.1.1/bin/psql -h 127.0.0.1 -p 5432 -d rwnas -U rwnas -t -c "${cmd_get_partitions}")
echo "${exec_get_partitions}" > partitions_tables.txt
echo "-------- Partitions Tables List Below --------"
partitions_file=$(cat partitions_tables.txt)
echo "$partitions_file"
# analyze partition tables
rm -rf analyze_partitions_tables.txt
function for_p_file(){
for q in $partitions_file
do
echo "$(/app/greenplum-db-4.1.1.1/bin/psql -h 127.0.0.1 -p 5432 -d rwnas -U rwnas -c "analyze $q")"
echo "$q" >> analyze_partitions_tables.txt
done
}
for_p_file
# end time
end_time=$(date)
echo "-------- End time is $end_time --------"
end_seconds=$(date +%s)
diff=$((end_seconds - start_seconds))
echo "Total $diff seconds."

最後更新：2017-06-12 00:31:51

  上一篇：  Tideways和xhgui打造PHP非侵入式監控平台

  下一篇：  Deepgreen數據庫日誌清理腳本