forked from iarv/meshcore-stats
0f8b0a3492
A Python-based monitoring system for MeshCore LoRa mesh networks. Collects metrics from companion and repeater nodes, stores them in a SQLite database, and generates a static website with interactive SVG charts and statistics. Features: - Data collection from local companion and remote repeater nodes - SQLite database with EAV schema for flexible metric storage - Interactive SVG chart generation with matplotlib - Static HTML site with day/week/month/year views - Monthly and yearly statistics reports (HTML, TXT, JSON) - Light and dark theme support - Circuit breaker for unreliable LoRa connections - Battery percentage calculation from 18650 discharge curves - Automated releases via release-please Live demo: https://meshcore.jorijn.com
44 lines
1.1 KiB
Bash
Executable File
44 lines
1.1 KiB
Bash
Executable File
#!/bin/bash
|
|
# Database maintenance script
|
|
#
|
|
# Runs VACUUM and ANALYZE on the SQLite database to compact it and
|
|
# update query statistics.
|
|
#
|
|
# Recommended: Run monthly via cron
|
|
# Example crontab entry:
|
|
# 0 3 1 * * cd /home/jorijn/apps/meshcore-stats && ./scripts/db_maintenance.sh
|
|
#
|
|
# This script will:
|
|
# 1. Run VACUUM to compact the database and reclaim space
|
|
# 2. Run ANALYZE to update query optimizer statistics
|
|
#
|
|
# Note: VACUUM acquires an exclusive lock internally. Other processes
|
|
# using busy_timeout will wait for it to complete.
|
|
|
|
set -e
|
|
|
|
SCRIPT_DIR="$(cd "$(dirname "$0")" && pwd)"
|
|
PROJECT_DIR="$(dirname "$SCRIPT_DIR")"
|
|
DB_PATH="$PROJECT_DIR/data/state/metrics.db"
|
|
|
|
# Check if database exists
|
|
if [ ! -f "$DB_PATH" ]; then
|
|
echo "Database not found: $DB_PATH"
|
|
exit 1
|
|
fi
|
|
|
|
echo "$(date '+%Y-%m-%d %H:%M:%S') Starting database maintenance..."
|
|
echo "Database: $DB_PATH"
|
|
|
|
echo "Running VACUUM..."
|
|
sqlite3 "$DB_PATH" "VACUUM;"
|
|
|
|
echo "Running ANALYZE..."
|
|
sqlite3 "$DB_PATH" "ANALYZE;"
|
|
|
|
# Get database size
|
|
DB_SIZE=$(du -h "$DB_PATH" | cut -f1)
|
|
echo "Database size after maintenance: $DB_SIZE"
|
|
|
|
echo "$(date '+%Y-%m-%d %H:%M:%S') Maintenance complete"
|