292 lines
8.3 KiB
Bash
292 lines
8.3 KiB
Bash
#!/usr/bin/env bash
|
||
# Autor: Mattia Tadini
|
||
# File: autoscale_cluster.sh
|
||
# Version: 1.00
|
||
# Date 14/11/2025
|
||
# - Supporto PG 14 / 15 / 16
|
||
# - Profili: PRIVATO (100), SHARED (10000), ANALYTICS (10000)
|
||
|
||
set -Ee # niente -u per evitare abort su variabili non settate
|
||
|
||
# --- Guard rail: esecuzione come root
|
||
if [ "${EUID:-$(id -u)}" -ne 0 ]; then
|
||
echo "Errore: lo script deve essere eseguito come root" >&2
|
||
exit 1
|
||
fi
|
||
|
||
show_error() {
|
||
echo "ERRORE: $1" >&2
|
||
if command -v whiptail >/dev/null 2>&1; then
|
||
whiptail --title "Errore Critico" --msgbox "$1" 20 74
|
||
fi
|
||
exit 1
|
||
}
|
||
|
||
show_msg() {
|
||
if command -v whiptail >/dev/null 2>&1; then
|
||
whiptail --title "$1" --msgbox "$2" 20 74
|
||
else
|
||
echo -e "\n[$1]\n$2\n"
|
||
fi
|
||
}
|
||
|
||
# --- Rilevamento risorse
|
||
detect_ram_mb() {
|
||
if [ -r /proc/meminfo ]; then
|
||
awk '/MemTotal:/ { printf "%d\n", $2/1024 }' /proc/meminfo
|
||
else
|
||
free -m | awk '/Mem:/ {print $2}'
|
||
fi
|
||
}
|
||
|
||
detect_cpu_cores() {
|
||
if command -v nproc >/dev/null 2>&1; then
|
||
nproc
|
||
else
|
||
getconf _NPROCESSORS_ONLN 2>/dev/null || echo 1
|
||
fi
|
||
}
|
||
|
||
# --- Selezione versione PostgreSQL tra quelle presenti
|
||
get_postgres_version() {
|
||
local available=() v sel
|
||
for v in 14 15 16; do
|
||
if [ -d "/etc/postgresql/${v}/main" ]; then
|
||
available+=("$v")
|
||
fi
|
||
done
|
||
|
||
if [ "${#available[@]}" -eq 0 ]; then
|
||
show_error "Nessuna installazione trovata in /etc/postgresql/{14,15,16}/main"
|
||
fi
|
||
|
||
if command -v whiptail >/dev/null 2>&1; then
|
||
local menu_args=()
|
||
for v in "${available[@]}"; do
|
||
case "$v" in
|
||
14) menu_args+=("14" "PostgreSQL 14 (profilo Zucchetti)") ;;
|
||
15) menu_args+=("15" "PostgreSQL 15") ;;
|
||
16) menu_args+=("16" "PostgreSQL 16 (profilo Zucchetti)") ;;
|
||
esac
|
||
done
|
||
sel=$(whiptail --title "Versione PostgreSQL" --menu \
|
||
"Seleziona la versione DA TUNARE (già installata):" 20 74 10 \
|
||
"${menu_args[@]}" 3>&1 1>&2 2>&3) || show_error "Operazione annullata."
|
||
echo "$sel"
|
||
else
|
||
echo "Installazioni trovate:"
|
||
printf ' - %s\n' "${available[@]}"
|
||
read -r -p "Versione da tunare [${available[*]}]: " v
|
||
case "$v" in 14|15|16) echo "$v" ;; *) show_error "Versione non valida" ;; esac
|
||
fi
|
||
}
|
||
|
||
# --- Scelta profilo max_connections
|
||
get_connection_profile() {
|
||
local p sel
|
||
if command -v whiptail >/dev/null 2>&1; then
|
||
sel=$(whiptail --title "Profilo Connessioni" --menu \
|
||
"Scegli il profilo server DB:\n- PRIVATO: singolo DB, poche connessioni\n- SHARED: multi-tenant, molte connessioni\n- ANALYTICS: carichi analitici pesanti (molta parallelizzazione)" \
|
||
20 74 12 \
|
||
"PRIVATO" "max_connections = 100 (conservativo)" \
|
||
"SHARED" "max_connections = 10000 (multi-tenant)" \
|
||
"ANALYTICS" "max_connections = 10000 + parallelismo aggressivo" 3>&1 1>&2 2>&3) || show_error "Operazione annullata."
|
||
p="$sel"
|
||
else
|
||
echo "Profili: PRIVATO (100), SHARED (10000), ANALYTICS (10000)"
|
||
read -r -p "Profilo [PRIVATO/SHARED/ANALYTICS]: " p
|
||
fi
|
||
case "$p" in PRIVATO|SHARED|ANALYTICS) echo "$p" ;; *) show_error "Profilo non valido" ;; esac
|
||
}
|
||
|
||
# --- Calcolo parametri
|
||
compute_tuning() {
|
||
local RAM_MB="$1" MAX_CONN="$2" CORES="$3" PROFILE="$4"
|
||
|
||
local sb=$(( RAM_MB / 4 ))
|
||
[ "$sb" -lt 512 ] && sb=512
|
||
[ "$sb" -gt 65536 ] && sb=65536
|
||
SHARED_BUFFERS_MB=$sb
|
||
|
||
local ecs=$(( (RAM_MB * 70) / 100 ))
|
||
[ "$ecs" -lt 512 ] && ecs=512
|
||
EFFECTIVE_CACHE_SIZE_MB=$ecs
|
||
|
||
local mwm=$(( (RAM_MB * 5) / 100 ))
|
||
[ "$mwm" -lt 64 ] && mwm=64
|
||
[ "$mwm" -gt 4096 ] && mwm=4096
|
||
MAINTENANCE_WORK_MEM_MB=$mwm
|
||
|
||
local wm=$(( (RAM_MB / 4) / (MAX_CONN * 2) ))
|
||
[ "$wm" -lt 1 ] && wm=1
|
||
local wm_cap=64
|
||
case "$PROFILE" in
|
||
SHARED) wm_cap=8 ;;
|
||
ANALYTICS) wm_cap=128 ;;
|
||
esac
|
||
[ "$wm" -gt "$wm_cap" ] && wm="$wm_cap"
|
||
WORK_MEM_MB=$wm
|
||
|
||
local avw=3
|
||
|
||
if [ "$PROFILE" = "ANALYTICS" ]; then
|
||
local mwp=$(( CORES * 3 ))
|
||
[ "$mwp" -lt 12 ] && mwp=12
|
||
[ "$mwp" -gt 96 ] && mwp=96
|
||
MAX_WORKER_PROCESSES=$mwp
|
||
|
||
local mpw=$(( mwp - avw - 2 ))
|
||
local mpw_alt=$(( CORES * 3 ))
|
||
[ "$mpw" -gt "$mpw_alt" ] && mpw=$mpw_alt
|
||
[ "$mpw" -lt 4 ] && mpw=4
|
||
MAX_PARALLEL_WORKERS=$mpw
|
||
|
||
local mpwpg=$(( CORES / 2 ))
|
||
[ "$mpwpg" -lt 3 ] && mpwpg=3
|
||
[ "$mpwpg" -gt 6 ] && mpwpg=6
|
||
MAX_PARALLEL_WORKERS_PER_GATHER=$mpwpg
|
||
|
||
local mpmw=$(( CORES / 2 ))
|
||
[ "$mpmw" -lt 3 ] && mpmw=3
|
||
[ "$mpmw" -gt 6 ] && mpmw=6
|
||
MAX_PARALLEL_MAINT_WORKERS=$mpmw
|
||
|
||
PARALLEL_SETUP_COST="200"
|
||
PARALLEL_TUPLE_COST="0.06"
|
||
MIN_PARALLEL_TABLE_SCAN_SIZE="'16MB'"
|
||
MIN_PARALLEL_INDEX_SCAN_SIZE="'32MB'"
|
||
DEFAULT_STATISTICS_TARGET="2000"
|
||
else
|
||
local mwp=$(( CORES * 2 ))
|
||
[ "$mwp" -lt 8 ] && mwp=8
|
||
[ "$mwp" -gt 64 ] && mwp=64
|
||
MAX_WORKER_PROCESSES=$mwp
|
||
|
||
local mpw=$(( mwp - avw - 2 ))
|
||
local mpw_alt=$(( CORES * 2 ))
|
||
[ "$mpw" -gt "$mpw_alt" ] && mpw=$mpw_alt
|
||
[ "$mpw" -lt 2 ] && mpw=2
|
||
MAX_PARALLEL_WORKERS=$mpw
|
||
|
||
local mpwpg=$(( CORES / 2 ))
|
||
[ "$mpwpg" -lt 2 ] && mpwpg=2
|
||
[ "$mpwpg" -gt 4 ] && mpwpg=4
|
||
MAX_PARALLEL_WORKERS_PER_GATHER=$mpwpg
|
||
|
||
local mpmw=$(( CORES / 2 ))
|
||
[ "$mpmw" -lt 2 ] && mpmw=2
|
||
[ "$mpmw" -gt 4 ] && mpmw=4
|
||
MAX_PARALLEL_MAINT_WORKERS=$mpmw
|
||
|
||
PARALLEL_SETUP_COST="500"
|
||
PARALLEL_TUPLE_COST="0.08"
|
||
MIN_PARALLEL_TABLE_SCAN_SIZE="'32MB'"
|
||
MIN_PARALLEL_INDEX_SCAN_SIZE="'64MB'"
|
||
DEFAULT_STATISTICS_TARGET="1000"
|
||
fi
|
||
}
|
||
|
||
main() {
|
||
PG_VERSION=$(get_postgres_version)
|
||
PROFILE=$(get_connection_profile)
|
||
|
||
case "$PROFILE" in
|
||
PRIVATO) MAX_CONN=100 ;;
|
||
SHARED) MAX_CONN=10000 ;;
|
||
ANALYTICS) MAX_CONN=10000 ;;
|
||
esac
|
||
|
||
RAM_MB=$(detect_ram_mb)
|
||
CPU_CORES=$(detect_cpu_cores)
|
||
[ "$RAM_MB" -lt 1024 ] && RAM_MB=1024
|
||
[ "$CPU_CORES" -lt 1 ] && CPU_CORES=1
|
||
|
||
CONF_DIR="/etc/postgresql/${PG_VERSION}/main"
|
||
POSTGRESQL_CONF="${CONF_DIR}/postgresql.conf"
|
||
[ -f "$POSTGRESQL_CONF" ] || show_error "File ${POSTGRESQL_CONF} non trovato."
|
||
|
||
compute_tuning "$RAM_MB" "$MAX_CONN" "$CPU_CORES" "$PROFILE"
|
||
|
||
# include_if_exists sempre
|
||
if ! grep -Eiq "^[[:space:]]*include_if_exists[[:space:]]*=.*zucchetti\.conf" "$POSTGRESQL_CONF"; then
|
||
printf "\ninclude_if_exists = 'zucchetti.conf'\n" >> "$POSTGRESQL_CONF"
|
||
fi
|
||
|
||
ZUCC_CONF="${CONF_DIR}/zucchetti.conf"
|
||
echo "Scrivo ${ZUCC_CONF}..."
|
||
|
||
cat > "$ZUCC_CONF" <<EOF
|
||
# =========================
|
||
# Tuning Zucchetti (PG${PG_VERSION})
|
||
# Profilo: ${PROFILE} | max_connections=${MAX_CONN}
|
||
# RAM totale: ${RAM_MB}MB | CPU cores: ${CPU_CORES}
|
||
# =========================
|
||
|
||
# Connessioni
|
||
max_connections = ${MAX_CONN}
|
||
|
||
# Memory & cache
|
||
shared_buffers = ${SHARED_BUFFERS_MB}MB
|
||
effective_cache_size = ${EFFECTIVE_CACHE_SIZE_MB}MB
|
||
maintenance_work_mem = ${MAINTENANCE_WORK_MEM_MB}MB
|
||
work_mem = ${WORK_MEM_MB}MB
|
||
|
||
# Checkpoint & WAL
|
||
checkpoint_completion_target = 0.9
|
||
wal_buffers = -1
|
||
min_wal_size = 1GB
|
||
max_wal_size = 4GB
|
||
|
||
# Statistiche & IO
|
||
default_statistics_target = ${DEFAULT_STATISTICS_TARGET}
|
||
random_page_cost = 1.1
|
||
effective_io_concurrency = 200
|
||
|
||
# Lock/strings
|
||
max_locks_per_transaction = 1024
|
||
escape_string_warning = on
|
||
standard_conforming_strings = off
|
||
|
||
# Logging/rotation
|
||
log_destination = 'stderr'
|
||
logging_collector = on
|
||
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
|
||
log_rotation_age = 1d
|
||
log_rotation_size = 100MB
|
||
|
||
# Autovacuum (valori ragionevoli per ERP)
|
||
autovacuum = on
|
||
autovacuum_analyze_scale_factor = 0.1
|
||
autovacuum_analyze_threshold = 500
|
||
autovacuum_freeze_max_age = 200000000
|
||
autovacuum_max_workers = 3
|
||
autovacuum_multixact_freeze_max_age = 400000000
|
||
autovacuum_naptime = 5s
|
||
autovacuum_vacuum_cost_delay = 20ms
|
||
autovacuum_vacuum_cost_limit = -1
|
||
autovacuum_vacuum_scale_factor = 0.2
|
||
autovacuum_vacuum_threshold = 1500
|
||
autovacuum_work_mem = -1
|
||
|
||
# ===== Parallel Query =====
|
||
max_worker_processes = ${MAX_WORKER_PROCESSES}
|
||
max_parallel_workers = ${MAX_PARALLEL_WORKERS}
|
||
max_parallel_workers_per_gather = ${MAX_PARALLEL_WORKERS_PER_GATHER}
|
||
max_parallel_maintenance_workers = ${MAX_PARALLEL_MAINT_WORKERS}
|
||
|
||
# Soglie/costi del planner per parallelismo
|
||
parallel_setup_cost = ${PARALLEL_SETUP_COST}
|
||
parallel_tuple_cost = ${PARALLEL_TUPLE_COST}
|
||
min_parallel_table_scan_size = ${MIN_PARALLEL_TABLE_SCAN_SIZE}
|
||
min_parallel_index_scan_size = ${MIN_PARALLEL_INDEX_SCAN_SIZE}
|
||
EOF
|
||
|
||
chown postgres:postgres "$ZUCC_CONF"
|
||
chmod 640 "$ZUCC_CONF"
|
||
|
||
systemctl restart "postgresql@${PG_VERSION}-main" || show_error "Riavvio postgresql@${PG_VERSION}-main fallito."
|
||
|
||
show_msg "Completato" "Tuning completato per PostgreSQL ${PG_VERSION} (profilo ${PROFILE}).\nFile: ${ZUCC_CONF}"
|
||
}
|
||
|
||
main |