System Monitoring with Python and Real-Time Data Storage

Blogs

Dynamic Data Masking in SQL Server: Securing Sensitive Data
December 31, 2024
Azure SQL Database Serverless Tier
December 31, 2024

System Monitoring with Python and Real-Time Data Storage

Monitoring system performance is critical for maintaining the health and efficiency of any computing environment. Python, with its robust libraries and simplicity, offers an excellent way to achieve real-time monitoring while integrating with databases for data storage and analysis. In this blog, we will explore how to build a basic system monitoring solution using Python, psutil for performance metrics, and pyodbc for storing the collected data in a SQL Server database.

Why Monitor System Performance?

System performance monitoring helps in:

  1. Identifying bottlenecks: CPU, memory, or disk usage spikes can point to resource constraints.
  2. Capacity planning: Understanding resource usage trends aids in scaling infrastructure.
  3. Troubleshooting: Anomalies in system metrics often correlate with application issues.
  4. Maintaining SLAs: Continuous monitoring ensures systems meet defined service level agreements.

Blog: System Monitoring with Python and Real-Time Data Storage

Monitoring system performance is critical for maintaining the health and efficiency of any computing environment. Python, with its robust libraries and simplicity, offers an excellent way to achieve real-time monitoring while integrating with databases for data storage and analysis. In this blog, we will explore how to build a basic system monitoring solution using Python, psutil for performance metrics, and pyodbc for storing the collected data in a SQL Server database.


Why Monitor System Performance?

System performance monitoring helps in:

  1. Identifying bottlenecks: CPU, memory, or disk usage spikes can point to resource constraints.
  2. Capacity planning: Understanding resource usage trends aids in scaling infrastructure.
  3. Troubleshooting: Anomalies in system metrics often correlate with application issues.
  4. Maintaining SLAs: Continuous monitoring ensures systems meet defined service level agreements.

The Tools We’ll Use

  1. psutil: A cross-platform library to retrieve information on system utilization (CPU, memory, disk, network, etc.).
  2. pyodbc: A Python library for connecting to databases using ODBC.
  3. SQL Server: Used as the backend database to store performance metrics.

Code Walkthrough

Here is the Python code to monitor system performance and save it to a SQL Server database:

1. Setup

The script connects to a SQL Server database using pyodbc. Replace connection parameters such as Server and Database with your actual server details.

con = pyodbc.connect(‘Driver={SQL Server};’
‘Server=lochan\mssqlserver01;’
‘Database=ds;’
‘Trusted_Connection=yes;’)
cursor = con.cursor()

2. Collecting System Metrics

Using psutil, the script gathers the following metrics:

  • CPU usage: Percentage of CPU utilization.
  • Memory usage: Percentage of memory in use.
  • Disk usage: Disk space usage as a percentage.
  • Network I/O: Bytes sent and received over the network.
  • CPU stats: CPU interrupts and context switches.

cpu_usage = psutil.cpu_percent()
memory_usage = psutil.virtual_memory()[2] cpu_interrupts = psutil.cpu_stats()[1] cpu_calls = psutil.cpu_stats()[3] memory_used = psutil.virtual_memory()[3] memory_free = psutil.virtual_memory()[4] bytes_sent = psutil.net_io_counters()[0] bytes_received = psutil.net_io_counters()[1] disk_usage = psutil.disk_usage(‘/’)[3]

3. Storing Metrics in the Database

Each metric is inserted into a database table named performance. The table should be predefined with columns matching the metrics collected.

CREATE TABLE performance (
time DATETIME,
cpu_usage FLOAT,
memory_usage FLOAT,
cpu_interrupts INT,
cpu_calls INT,
memory_used BIGINT,
memory_free BIGINT,
bytes_sent BIGINT,
bytes_received BIGINT,
disk_usage FLOAT
);

cursor.execute(‘INSERT INTO performance (time, cpu_usage, memory_usage, cpu_interrupts, cpu_calls, memory_used, memory_free, bytes_sent, bytes_received, disk_usage) VALUES (GETDATE(), ?, ?, ?, ?, ?, ?, ?, ?, ?)’,
(cpu_usage, memory_usage, cpu_interrupts, cpu_calls, memory_used, memory_free, bytes_sent, bytes_received, disk_usage))
con.commit()

4. Error Handling and Script Timeout

The script runs for a specified duration (timeout), inserts data every second, and handles errors gracefully.

try:
# Data collection and insertion logic
except Exception as e:
print(f”Error occurred: {e}”)
con.rollback()

Key Highlights

  • Real-time monitoring: Metrics are collected and stored every second.
  • Database integration: Collected data is stored for analysis and visualization.
  • Scalable and extensible: The script can be extended to monitor additional metrics or log alerts.

Applications and Benefits

  1. Historical analysis: By storing performance data, you can analyze trends over time.
  2. Alerting: Use thresholds to generate alerts when metrics exceed acceptable ranges.
  3. Dashboarding: Combine the stored data with visualization tools (e.g., Power BI, Tableau) to create live dashboards.

Conclusion

This Python script demonstrates how easy it is to implement a basic system monitoring solution that integrates with a database. With slight modifications, it can be customized for more complex scenarios or scaled to handle large infrastructures. System monitoring is vital for ensuring reliability, and Python’s flexibility makes it a go-to choice for such tasks.

What’s next? Enhance this script to include real-time alerts, log file monitoring, or integrate it with a cloud-based monitoring solution.


Lochan R

Leave a Reply

Your email address will not be published. Required fields are marked *