Comprehensive comparison for Database technology in Software Development applications

import time
import graphite
from typing import Dict, List, Optional
import logging

# Configure logging
logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)

class DatabaseMetricsCollector:
    """
    Production-grade metrics collector for database operations using Graphite.
    Tracks query performance, connection pool stats, and error rates.
    """
    
    def __init__(self, graphite_host: str, graphite_port: int, prefix: str = "app.database"):
        """
        Initialize Graphite connection for database metrics.
        
        Args:
            graphite_host: Graphite server hostname
            graphite_port: Graphite plaintext protocol port (typically 2003)
            prefix: Metric namespace prefix
        """
        self.graphite_host = graphite_host
        self.graphite_port = graphite_port
        self.prefix = prefix
        self.client = graphite.GraphiteClient(graphite_host, graphite_port)
        
    def record_query_execution(self, query_type: str, duration_ms: float, 
                              success: bool, table_name: str) -> None:
        """
        Record database query execution metrics.
        
        Args:
            query_type: Type of query (SELECT, INSERT, UPDATE, DELETE)
            duration_ms: Query execution time in milliseconds
            success: Whether query succeeded
            table_name: Target table name
        """
        timestamp = int(time.time())
        metrics = []
        
        try:
            # Record query duration
            duration_metric = f"{self.prefix}.queries.{query_type.lower()}.{table_name}.duration"
            metrics.append((duration_metric, duration_ms, timestamp))
            
            # Record query count
            count_metric = f"{self.prefix}.queries.{query_type.lower()}.{table_name}.count"
            metrics.append((count_metric, 1, timestamp))
            
            # Record success/failure
            status = "success" if success else "failure"
            status_metric = f"{self.prefix}.queries.{query_type.lower()}.{status}"
            metrics.append((status_metric, 1, timestamp))
            
            # Send all metrics in batch
            self.client.send_multiple(metrics)
            logger.info(f"Recorded {len(metrics)} metrics for {query_type} on {table_name}")
            
        except Exception as e:
            logger.error(f"Failed to send metrics to Graphite: {e}")
            # Don't raise - metrics failures shouldn't break application
    
    def record_connection_pool_stats(self, active: int, idle: int, 
                                    max_connections: int) -> None:
        """
        Record database connection pool statistics.
        
        Args:
            active: Number of active connections
            idle: Number of idle connections
            max_connections: Maximum pool size
        """
        timestamp = int(time.time())
        
        try:
            utilization = (active / max_connections) * 100 if max_connections > 0 else 0
            
            metrics = [
                (f"{self.prefix}.pool.active", active, timestamp),
                (f"{self.prefix}.pool.idle", idle, timestamp),
                (f"{self.prefix}.pool.utilization", utilization, timestamp),
                (f"{self.prefix}.pool.total", active + idle, timestamp)
            ]
            
            self.client.send_multiple(metrics)
            logger.debug(f"Connection pool: {active} active, {idle} idle, {utilization:.1f}% utilized")
            
        except Exception as e:
            logger.error(f"Failed to record pool stats: {e}")
    
    def record_transaction_metrics(self, duration_ms: float, 
                                  operations_count: int, committed: bool) -> None:
        """
        Record database transaction metrics.
        
        Args:
            duration_ms: Transaction duration in milliseconds
            operations_count: Number of operations in transaction
            committed: Whether transaction was committed (vs rolled back)
        """
        timestamp = int(time.time())
        
        try:
            status = "committed" if committed else "rollback"
            
            metrics = [
                (f"{self.prefix}.transactions.duration", duration_ms, timestamp),
                (f"{self.prefix}.transactions.operations", operations_count, timestamp),
                (f"{self.prefix}.transactions.{status}", 1, timestamp)
            ]
            
            self.client.send_multiple(metrics)
            
        except Exception as e:
            logger.error(f"Failed to record transaction metrics: {e}")
    
    def close(self) -> None:
        """Close Graphite client connection."""
        try:
            self.client.close()
            logger.info("Graphite client connection closed")
        except Exception as e:
            logger.error(f"Error closing Graphite client: {e}")


# Example usage in a database service
if __name__ == "__main__":
    # Initialize metrics collector
    metrics = DatabaseMetricsCollector(
        graphite_host="graphite.example.com",
        graphite_port=2003,
        prefix="myapp.production.database"
    )
    
    # Simulate query execution
    start_time = time.time()
    # ... execute SELECT query ...
    duration = (time.time() - start_time) * 1000
    
    metrics.record_query_execution(
        query_type="SELECT",
        duration_ms=duration,
        success=True,
        table_name="users"
    )
    
    # Record connection pool stats
    metrics.record_connection_pool_stats(
        active=15,
        idle=5,
        max_connections=25
    )
    
    # Record transaction
    metrics.record_transaction_metrics(
        duration_ms=45.2,
        operations_count=3,
        committed=True
    )
    
    metrics.close()