Hey there! Let's work through your system metrics collection and storage setup—sounds like you've got a solid start, but there are plenty of ways to make this more reliable, maintainable, and performant. Here's my breakdown of key improvements and fixes based on your setup:

Running multiple scattered Python programs on each server is a pain to maintain. Let's consolidate into a single, reusable script with built-in robustness:

• Use the psutil library (install via pip install psutil) for most system metrics—it simplifies fetching CPU load, network I/O, and more. For CPU temperature, pair it with lm-sensors (install via sudo apt install lm-sensors on Ubuntu) to read hardware temps.
• Enforce a consistent data schema across all servers to avoid messy database entries.
• Add retry logic for database pushes to prevent data loss during transient network issues.

Here's a sample consolidated script:

import psutil
import mysql.connector
import logging
from tenacity import retry, stop_after_attempt, wait_exponential

# Configure logging for debugging
logging.basicConfig(
    filename='/var/log/system-metrics.log',
    level=logging.INFO,
    format='%(asctime)s - %(levelname)s - %(message)s'
)

def get_cpu_temp():
    """Parse CPU temperature from sensors output (adjust for your hardware)"""
    try:
        import subprocess
        result = subprocess.run(["sensors", "-u"], capture_output=True, text=True, check=True)
        for line in result.stdout.splitlines():
            if "temp1_input" in line:
                return round(float(line.split(":")[1].strip()), 2)
        return None
    except Exception as e:
        logging.error(f"Failed to get CPU temp: {str(e)}")
        return None

def collect_system_data():
    """Gather all required system metrics into a single dict"""
    try:
        net_io = psutil.net_io_counters()
        return {
            "cpu_load_1min": psutil.getloadavg()[0],
            "total_net_bytes": net_io.bytes_sent + net_io.bytes_recv,
            "cpu_temp": get_cpu_temp(),
            "server_id": "web-server-01"  # Unique ID for each server
        }
    except Exception as e:
        logging.error(f"Failed to collect system data: {str(e)}")
        return None

@retry(stop=stop_after_attempt(3), wait=wait_exponential(multiplier=1, min=2, max=10))
def push_to_mysql(metrics):
    """Push metrics to MySQL with retries"""
    if not metrics:
        return
    try:
        conn = mysql.connector.connect(
            host="your-mysql-server-ip",
            user="metrics-write-user",
            password="your-secure-password",
            database="system_metrics"
        )
        cursor = conn.cursor()
        insert_query = """
        INSERT INTO metrics (timestamp, cpu_load_1min, total_net_bytes, cpu_temp, server_id)
        VALUES (NOW(), %s, %s, %s, %s)
        """
        cursor.execute(insert_query, (
            metrics["cpu_load_1min"],
            metrics["total_net_bytes"],
            metrics["cpu_temp"],
            metrics["server_id"]
        ))
        conn.commit()
        logging.info(f"Successfully pushed metrics for {metrics['server_id']}")
    except Exception as e:
        logging.error(f"Failed to push metrics: {str(e)}")
        raise  # Trigger retry
    finally:
        if conn.is_connected():
            cursor.close()
            conn.close()

if __name__ == "__main__":
    metrics = collect_system_data()
    push_to_mysql(metrics)

With multiple clients writing and querying, you'll need to tweak your MySQL setup for performance and stability:

• Indexing: Add a composite index on your metrics table to speed up historical queries:

  CREATE INDEX idx_server_timestamp ON metrics (server_id, timestamp DESC);
  

• Permission Lockdown: Create dedicated database users—one with only INSERT permissions for collection scripts, another with only SELECT permissions for query scripts. This minimizes security risks.
• Configuration Tweaks: Edit /etc/mysql/my.cnf (Ubuntu 16.04) to adjust key parameters:
  • max_connections: Increase this to match the number of concurrent clients (start with 100 if you have 10-20 servers).
  • innodb_buffer_pool_size: Set to 50-70% of your server's RAM to speed up read/write operations.
• Partitioning: If you plan to store months of data, partition your metrics table by month to avoid slow queries on large datasets.

• Run Scripts as Systemd Services: Ensure your collection script starts automatically on server reboot. Create a service file at /etc/systemd/system/metrics-collector.service:

  [Unit]
  Description=System Metrics Collector
  After=network.target
  
  [Service]
  User=ubuntu
  ExecStart=/usr/bin/python3 /opt/metrics-collector/collector.py
  Restart=always
  RestartSec=10
  
  [Install]
  WantedBy=multi-user.target
  

  Enable and start the service with:

  sudo systemctl daemon-reload
  sudo systemctl enable metrics-collector.service
  sudo systemctl start metrics-collector.service
  

• Monitor MySQL Performance: Use mysqladmin status or run SHOW PROCESSLIST; in MySQL to check for stuck queries or connection bottlenecks. You can also set up alerts for high CPU/memory usage on the MySQL server.

• Add a Message Queue: For large fleets of servers, use Redis or RabbitMQ to buffer metrics before writing to MySQL. This reduces direct load on the database and prevents data loss during spikes.
• Visualize Metrics: Replace custom query scripts with Grafana—connect it to your MySQL database to build real-time dashboards for CPU load, temperature, and network traffic.

内容的提问来源于stack exchange，提问作者Mausy5043