#!/bin/bash ################################################################################# # do_analyze_by_batch.sh # # Using this query as the source... # # SELECT database, schema || '.' || "table" AS "table", stats_off # FROM svv_table_info # WHERE stats_off > 5 # ORDER BY 3 DESC, 2; # # ...which displays all tables and their stats_off percentage in descending order. # # # Then this file is processed like this... # # ALLTABS=`cat tables.txt | cut -f2 -d'|'` # # for TAB in ${ALLTABS} # do # echo "ANALYZE ${TAB};" # done # # ...which reads in the raw listing, splits on the bar, and creates a huge .sql file # with all the ANALYZE commands (if a table is underneat the standard threshhold for # Redshift, ANALYZE will skip it). # # Finally, the huge .sql file is split into groups of XX statements (in the case below, 10) # # split -l 10 analyze.sql --additional-suffix=".sql" pass # # Then THIS script reads all those passXX.sql files in, in order, and executes them with a # 3 minute wait between each group. # # v1.00 2018-09-30 rlbyrd Initial creation # # TODO: Utilize a sourced bash file with usernames and passwords, as with other scripts # in the library. # ################################################################################# # CONSTANTS PGUSER="xxxxxxxxxxxx" PGPASS="xxxxxxxxxxxx" countdown() { secs=$1 shift msg=$@ while [ $secs -gt 0 ] do printf "\r\033[KWaiting %.d seconds $msg" $((secs--)) sleep 1 done echo } ALLTABS=`ls -1 pass*.sql|sort` for TAB in ${ALLTABS} do echo "Analyzing tables in ${TAB} ..." echo "------------------------------------------------" cat ${TAB} echo "------------------------------------------------" time PGPASSWORD=${PGPASS} psql -h warehouse.vacasa.services -p 5439 -U${PGUSER} -dwarehouse < ${TAB} countdown 180 "to process next batch; ctrl-c to abort." done