Nexus - AI Task Orchestrator

16-database-connection-pool-monitor
Procedimiento: Monitor de Pool de Conexiones

Base de Datos
1 plugin(s)
Titulo
Descripcion
Contenido Markdown
13268 caracteres
# Procedimiento: Monitor de Pool de Conexiones

## Metadata
- **ID**: PROC-16
- **Frecuencia**: Diario o bajo demanda (ante problemas de conectividad)
- **Duraci├│n estimada**: 10-20 min
- **Requiere**: Acceso SQL Server (VIEW SERVER STATE) o PostgreSQL (pg_stat_activity)
- **Dependencias**: Ninguna
- **Bloquea**: Ninguno (solo lectura)
- **Agentes**: database-optimizer

---

## Objetivo

Monitorizar el uso del pool de conexiones para:
1. Detectar conexiones hu├®rfanas (idle demasiado tiempo)
2. Identificar saturaci├│n del pool (> 80% uso)
3. Encontrar queries bloqueadas o de larga duraci├│n
4. Prevenir "connection pool exhausted" errors

---

## Umbrales de Alerta

| M├®trica | WARNING | CRITICAL |
|---------|---------|----------|
| Pool usage | >70% | >90% |
| Conexiones idle > 30 min | >10 | >50 |
| Queries activas > 5 min | >5 | >20 |
| Conexiones bloqueadas | >5 | >20 |

---

## Detecci├│n de Configuraci├│n

Buscar en `appsettings.json`:
```json
{
  "ConnectionStrings": {
    "Default": "...;Max Pool Size=100;Min Pool Size=5;..."
  }
}
```

Default Max Pool Size:
- SQL Server: 100
- PostgreSQL (Npgsql): 100

---

## Checklist Ejecutable

### 1. Pre-verificaciones

- [ ] Identificar Max Pool Size configurado
- [ ] Conectar a la base de datos

---

## Para SQL Server

### 2. Conexiones Actuales por Base de Datos

```sql
-- Total de conexiones por DB
SELECT
    DB_NAME(database_id) AS database_name,
    COUNT(*) AS connection_count,
    SUM(CASE WHEN status = 'running' THEN 1 ELSE 0 END) AS running,
    SUM(CASE WHEN status = 'sleeping' THEN 1 ELSE 0 END) AS sleeping,
    SUM(CASE WHEN status = 'suspended' THEN 1 ELSE 0 END) AS suspended
FROM sys.dm_exec_sessions
WHERE is_user_process = 1
GROUP BY database_id
ORDER BY COUNT(*) DESC;
```

- [ ] Documentar conexiones totales
- [ ] Verificar si cerca del l├¡mite

### 3. Conexiones por Aplicaci├│n/Host

```sql
-- Conexiones agrupadas por aplicaci├│n y host
SELECT
    program_name,
    host_name,
    login_name,
    COUNT(*) AS connection_count,
    SUM(CASE WHEN status = 'sleeping' THEN 1 ELSE 0 END) AS idle_count,
    MIN(login_time) AS oldest_connection,
    MAX(last_request_end_time) AS last_activity
FROM sys.dm_exec_sessions
WHERE is_user_process = 1
GROUP BY program_name, host_name, login_name
ORDER BY COUNT(*) DESC;
```

- [ ] Identificar aplicaciones con muchas conexiones
- [ ] Verificar si hay hosts con conexiones anormales

### 4. Conexiones Idle de Larga Duraci├│n

```sql
-- Conexiones idle por m├ís de 30 minutos
SELECT
    session_id,
    login_name,
    host_name,
    program_name,
    DB_NAME(database_id) AS database_name,
    status,
    login_time,
    last_request_end_time,
    DATEDIFF(MINUTE, last_request_end_time, GETDATE()) AS idle_minutes,
    (SELECT TOP 1 text FROM sys.dm_exec_sql_text(most_recent_sql_handle)) AS last_query
FROM sys.dm_exec_sessions
WHERE is_user_process = 1
    AND status = 'sleeping'
    AND DATEDIFF(MINUTE, last_request_end_time, GETDATE()) > 30
ORDER BY last_request_end_time;
```

- [ ] Identificar conexiones idle > 30 min
- [ ] Evaluar si son hu├®rfanas (para terminar)

### 5. Queries Activas de Larga Duraci├│n

```sql
-- Queries ejecut├índose por m├ís de 1 minuto
SELECT
    r.session_id,
    r.status,
    r.command,
    r.wait_type,
    r.wait_time / 1000 AS wait_seconds,
    r.blocking_session_id,
    DATEDIFF(SECOND, r.start_time, GETDATE()) AS duration_seconds,
    s.login_name,
    s.host_name,
    s.program_name,
    t.text AS query_text
FROM sys.dm_exec_requests r
JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.session_id > 50  -- Excluir procesos de sistema
    AND DATEDIFF(SECOND, r.start_time, GETDATE()) > 60
ORDER BY r.start_time;
```

- [ ] Listar queries > 1 minuto
- [ ] Identificar si hay bloqueos

### 6. An├ílisis de Bloqueos

```sql
-- Cadena de bloqueos
SELECT
    blocked.session_id AS blocked_session,
    blocked.wait_type,
    blocked.wait_time / 1000 AS wait_seconds,
    blocker.session_id AS blocking_session,
    blocker.status AS blocker_status,
    DB_NAME(blocked.database_id) AS database_name,
    (SELECT text FROM sys.dm_exec_sql_text(blocked.sql_handle)) AS blocked_query,
    (SELECT text FROM sys.dm_exec_sql_text(blocker.most_recent_sql_handle)) AS blocker_query
FROM sys.dm_exec_requests blocked
JOIN sys.dm_exec_sessions blocker ON blocked.blocking_session_id = blocker.session_id
WHERE blocked.blocking_session_id > 0;
```

- [ ] Identificar cadenas de bloqueo
- [ ] Determinar query ra├¡z que bloquea

### 7. Terminar Conexiones Hu├®rfanas (Solo si Necesario)

```sql
-- PRECAUCI├ôN: Solo ejecutar con confirmaci├│n
-- Terminar conexi├│n espec├¡fica
-- KILL [session_id];

-- Generar scripts para conexiones hu├®rfanas
SELECT
    'KILL ' + CAST(session_id AS VARCHAR(10)) + '; -- ' + program_name + ' idle ' + CAST(DATEDIFF(MINUTE, last_request_end_time, GETDATE()) AS VARCHAR(10)) + ' min'
FROM sys.dm_exec_sessions
WHERE is_user_process = 1
    AND status = 'sleeping'
    AND DATEDIFF(MINUTE, last_request_end_time, GETDATE()) > 60;
```

- [ ] Generar scripts de KILL (no ejecutar autom├íticamente)
- [ ] Requiere confirmaci├│n manual

---

## Para PostgreSQL

### 2. Conexiones Actuales por Base de Datos

```sql
-- Total de conexiones por DB
SELECT
    datname AS database_name,
    COUNT(*) AS connection_count,
    COUNT(*) FILTER (WHERE state = 'active') AS active,
    COUNT(*) FILTER (WHERE state = 'idle') AS idle,
    COUNT(*) FILTER (WHERE state = 'idle in transaction') AS idle_in_transaction,
    COUNT(*) FILTER (WHERE wait_event_type IS NOT NULL) AS waiting
FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY datname
ORDER BY COUNT(*) DESC;

-- L├¡mite de conexiones
SHOW max_connections;
SELECT COUNT(*) AS current_connections FROM pg_stat_activity;
```

- [ ] Documentar conexiones totales
- [ ] Calcular % de max_connections usado

### 3. Conexiones por Aplicaci├│n/Host

```sql
-- Conexiones por aplicaci├│n
SELECT
    application_name,
    client_addr,
    usename,
    COUNT(*) AS connection_count,
    COUNT(*) FILTER (WHERE state = 'idle') AS idle_count,
    MIN(backend_start) AS oldest_connection,
    MAX(state_change) AS last_activity
FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY application_name, client_addr, usename
ORDER BY COUNT(*) DESC;
```

- [ ] Identificar aplicaciones con muchas conexiones

### 4. Conexiones Idle de Larga Duraci├│n

```sql
-- Conexiones idle por m├ís de 30 minutos
SELECT
    pid,
    usename,
    client_addr,
    application_name,
    datname,
    state,
    backend_start,
    state_change,
    EXTRACT(EPOCH FROM (NOW() - state_change))/60 AS idle_minutes,
    LEFT(query, 100) AS last_query
FROM pg_stat_activity
WHERE backend_type = 'client backend'
    AND state = 'idle'
    AND state_change < NOW() - INTERVAL '30 minutes'
ORDER BY state_change;
```

- [ ] Identificar conexiones idle > 30 min

### 5. Conexiones "Idle in Transaction"

```sql
-- Conexiones en transacci├│n abierta sin actividad (PELIGROSO)
SELECT
    pid,
    usename,
    client_addr,
    application_name,
    datname,
    state,
    xact_start,
    state_change,
    EXTRACT(EPOCH FROM (NOW() - xact_start))/60 AS transaction_minutes,
    LEFT(query, 200) AS query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
    AND xact_start < NOW() - INTERVAL '5 minutes'
ORDER BY xact_start;
```

- [ ] ALERTA: Transacciones abiertas > 5 min
- [ ] Pueden causar bloat y bloqueos

### 6. Queries Activas de Larga Duraci├│n

```sql
-- Queries ejecut├índose por m├ís de 1 minuto
SELECT
    pid,
    usename,
    client_addr,
    application_name,
    datname,
    state,
    wait_event_type,
    wait_event,
    query_start,
    EXTRACT(EPOCH FROM (NOW() - query_start)) AS duration_seconds,
    LEFT(query, 500) AS query
FROM pg_stat_activity
WHERE state = 'active'
    AND query_start < NOW() - INTERVAL '1 minute'
    AND query NOT LIKE '%pg_stat_activity%'
ORDER BY query_start;
```

- [ ] Listar queries > 1 minuto

### 7. An├ílisis de Bloqueos

```sql
-- Identificar bloqueos
SELECT
    blocked.pid AS blocked_pid,
    blocked.usename AS blocked_user,
    blocked.query AS blocked_query,
    blocking.pid AS blocking_pid,
    blocking.usename AS blocking_user,
    blocking.query AS blocking_query,
    blocking.state AS blocking_state
FROM pg_stat_activity blocked
JOIN pg_locks blocked_locks ON blocked.pid = blocked_locks.pid
JOIN pg_locks blocking_locks ON blocked_locks.locktype = blocking_locks.locktype
    AND blocked_locks.database IS NOT DISTINCT FROM blocking_locks.database
    AND blocked_locks.relation IS NOT DISTINCT FROM blocking_locks.relation
    AND blocked_locks.page IS NOT DISTINCT FROM blocking_locks.page
    AND blocked_locks.tuple IS NOT DISTINCT FROM blocking_locks.tuple
    AND blocked_locks.virtualxid IS NOT DISTINCT FROM blocking_locks.virtualxid
    AND blocked_locks.transactionid IS NOT DISTINCT FROM blocking_locks.transactionid
    AND blocked_locks.classid IS NOT DISTINCT FROM blocking_locks.classid
    AND blocked_locks.objid IS NOT DISTINCT FROM blocking_locks.objid
    AND blocked_locks.objsubid IS NOT DISTINCT FROM blocking_locks.objsubid
    AND blocked_locks.pid != blocking_locks.pid
JOIN pg_stat_activity blocking ON blocking_locks.pid = blocking.pid
WHERE NOT blocked_locks.granted;
```

- [ ] Identificar cadenas de bloqueo

### 8. Terminar Conexiones (Solo si Necesario)

```sql
-- Cancelar query (graceful)
-- SELECT pg_cancel_backend(pid);

-- Terminar conexi├│n (force)
-- SELECT pg_terminate_backend(pid);

-- Generar scripts para conexiones hu├®rfanas
SELECT
    'SELECT pg_terminate_backend(' || pid || '); -- ' || application_name || ' idle ' ||
    ROUND(EXTRACT(EPOCH FROM (NOW() - state_change))/60) || ' min'
FROM pg_stat_activity
WHERE backend_type = 'client backend'
    AND state = 'idle'
    AND state_change < NOW() - INTERVAL '60 minutes';
```

- [ ] Generar scripts (no ejecutar autom├íticamente)

---

## Output Esperado

```
====== PROC-16 COMPLETADO [TIMESTAMP] ======
Motor: SQL Server / PostgreSQL
Base de datos: [nombre]

CONEXIONES:
- Max Pool Size: 100
- Conexiones actuales: X
- Uso del pool: Y%

ESTADO:
- Activas: X
- Idle: Y
- Idle in Transaction: Z (PostgreSQL)
- Bloqueadas: W

CONEXIONES HU├ëRFANAS (idle > 30 min): X
- [lista si hay]

QUERIES LARGAS (> 1 min): X
- [lista si hay]

BLOQUEOS ACTIVOS: X
- [cadena de bloqueo si hay]

ALERTAS:
- [lista de alertas]

ACCIONES SUGERIDAS:
- [lista de acciones]
```

---

## Criterios de ├ëxito

- [ ] Pool usage < 70%
- [ ] Conexiones idle > 60 min: 0
- [ ] Idle in transaction > 5 min: 0
- [ ] Queries activas > 5 min: < 5
- [ ] Sin cadenas de bloqueo

---

## Alertas y Escalaci├│n

| Severidad | Condici├│n | Acci├│n |
|-----------|-----------|--------|
| CRITICAL | Pool > 90% | WhatsApp inmediato - revisar aplicaci├│n |
| CRITICAL | Bloqueos > 20 conexiones | Intervenci├│n urgente |
| WARNING | Pool > 70% | Crear tarea Holded |
| WARNING | Idle in transaction > 10 min | Revisar aplicaci├│n |
| INFO | Todo OK | Documentar m├®tricas |

---

## Automatizaci├│n

En ejecuci├│n no-interactiva:
1. Recopilar m├®tricas
2. Generar scripts de limpieza (no ejecutar)
3. Alertar seg├║n umbrales
4. Nunca ejecutar KILL/terminate autom├íticamente

---


---

## Output Estructurado (Nexus)

Al finalizar, el agente DEBE generar un bloque JSON con el siguiente formato para que Nexus pueda procesarlo automaticamente:

```json
{
  "result": "success",
  "summary": "Ejecucion de PROC-16 completada. [Descripcion breve de resultados]",
  "metrics": {
    "issues_found": 0,
    "issues_resolved": 0,
    "queries_analyzed": 50, "slow_queries": 3,
    "custom": {
      "procedure_specific_metric": "value"
    }
  },
  "backlog_items": [
    {
      "title": "Titulo del item de seguimiento",
      "description": "Descripcion detallada si se requiere accion futura",
      "priority": "medium",
      "type": "improvement",
      "tags": ["proc-16"]
    }
  ],
  "next_steps": [
    "Accion recomendada 1",
    "Accion recomendada 2"
  ],
  "warnings": [
    "Advertencias encontradas durante la ejecucion"
  ]
}
```

**Campos requeridos:**
- `result`: `"success"` | `"partial"` | `"failed"`
- `summary`: Resumen ejecutivo en 1-3 lineas

**Metricas especificas de este procedure:**
- pool_size, connections_active, connections_idle

**Criterios de resultado:**
- `success`: Procedimiento completado sin errores criticos
- `partial`: Completado con algunos problemas menores o items pendientes
- `failed`: Error critico o no se pudo completar

## Historial de Ejecuciones

| Fecha | Proyecto | Conexiones | Pool % | Idle >30m | Bloqueadas | Acciones |
|-------|----------|------------|--------|-----------|------------|----------|
| | | | | | | |