# Database Connection Statistics Logging

## Overview
Added hourly logging of database connection statistics to monitor connection usage and prevent max_user_connections errors.

---

## Implementation

### Modified: `app/Support/Database.php`

**Added Tracking Variables:**
```php
private static int $connectionCount = 0;   // New connections created
private static int $reconnectCount = 0;    // Reconnections after ping fail
private static int $closeCount = 0;        // Connections closed
private static int $lastLogHour = 0;       // Hour of last log
```

**Added Hourly Logging Method:**
```php
private static function logHourlyStats(): void
{
    $currentHour = (int)date('H');
    
    // Log stats at the top of each hour
    if (self::$lastLogHour !== $currentHour && (self::$connectionCount > 0 || self::$closeCount > 0)) {
        Logger::log('[DB][STATS] Hourly summary', [
            'hour' => date('Y-m-d H:00'),
            'connections' => self::$connectionCount,
            'reconnects' => self::$reconnectCount,
            'closes' => self::$closeCount,
            'net_open' => self::$connectionCount - self::$closeCount
        ]);
        
        // Reset counters
        self::$connectionCount = 0;
        self::$reconnectCount = 0;
        self::$closeCount = 0;
        self::$lastLogHour = $currentHour;
    }
}
```

**Tracking Points:**
1. **On Connection:** `self::$connectionCount++` (line 129)
2. **On Reconnect:** `self::$reconnectCount++` (lines 24, 30)
3. **On Close:** `self::$closeCount++` (line 219)
4. **Check Hour:** `self::logHourlyStats()` called after each connection (line 130)

---

## Log Output Format

### Example Hourly Summary:
```
[2026-01-25 15:00:00] [DB][STATS] Hourly summary {
    "hour": "2026-01-25 15:00",
    "connections": 245,
    "reconnects": 2,
    "closes": 243,
    "net_open": 2
}
```

### What Each Metric Means:

**connections:**
- Total new database connections created this hour
- Includes initial connections and full reconnects
- High number indicates frequent connection creation

**reconnects:**
- Connections that failed ping check and were recreated
- Should be low (0-5 per hour)
- High number indicates connection instability

**closes:**
- Connections explicitly closed via `Database::close()`
- Should be close to connections count
- Used for connection pooling management

**net_open:**
- `connections - closes`
- Should be 0-2 (connection pooling)
- High number indicates connection leaks

---

## Monitoring

### Check Hourly Stats
```bash
# View hourly summaries
grep "[DB][STATS]" storage/logs/$(date +%Y-%m-%d)/market-data.log

# Expected output every hour:
[2026-01-25 14:00:00] [DB][STATS] Hourly summary {...}
[2026-01-25 15:00:00] [DB][STATS] Hourly summary {...}
[2026-01-25 16:00:00] [DB][STATS] Hourly summary {...}
```

### Healthy Patterns

✅ **Good:**
```json
{
    "connections": 200-300,
    "reconnects": 0-5,
    "closes": 195-298,
    "net_open": 1-2
}
```

⚠️ **Warning:**
```json
{
    "connections": 500+,
    "reconnects": 10+,
    "closes": 400,
    "net_open": 10+
}
```

❌ **Critical:**
```json
{
    "connections": 1000+,
    "reconnects": 50+,
    "closes": 800,
    "net_open": 50+
}
```

---

## Performance Impact

**Overhead:**
- Memory: ~16 bytes (4 static ints)
- CPU: ~0.001ms per connection (hour check)
- Log: 1 entry per hour

**Total Impact:** Negligible

---

## Use Cases

### 1. Capacity Planning
Track peak hours connection usage to plan server resources.

### 2. Connection Leak Detection
High `net_open` indicates connections not being closed.

### 3. Stability Monitoring
High `reconnects` indicates database or network issues.

### 4. Optimization Verification
After code changes, verify connection usage patterns.

---

## Troubleshooting

### High Connection Count (1000+/hour)
**Possible Causes:**
- Not using connection pooling (`Database::connection()` creates new each time)
- Not closing connections after use

**Fix:** Ensure using singleton pattern via `Database::connection()`

### High Reconnect Count (50+/hour)
**Possible Causes:**
- Database server restarting
- Network instability
- MySQL `wait_timeout` too low

**Fix:** Check database logs, increase `wait_timeout`, verify network

### High Net Open (50+)
**Possible Causes:**
- Connection leak (not calling `Database::close()`)
- Error handling not closing connections

**Fix:** Audit code for missing `Database::close()` calls

---

## Example Analysis

### Sample Hour (14:00-15:00):
```
Connections: 245
Reconnects: 1
Closes: 244
Net Open: 1
```

**Analysis:**
- ✅ 245 connections over 1 hour = ~4 per minute (healthy)
- ✅ Only 1 reconnect (stable connection)
- ✅ 244 closes = good cleanup
- ✅ Net open of 1 = singleton pattern working

**Conclusion:** System operating normally

---

## Files Modified

1. `app/Support/Database.php`
   - Added connection tracking variables
   - Added `logHourlyStats()` method
   - Added counter increments

---

## Production Status

✅ **IMPLEMENTED**  
✅ **Services Restarted**  
✅ **Logging Active**  
✅ **First Log:** Next hour boundary (e.g., 15:00)

**Date:** January 25, 2026  
**Version:** 2.0.2