Monitoratge del SGBD

Què cal monitorar

Un SGBD en producció és un sistema complex amb múltiples dimensions que poden degradar el rendiment o causar interrupcions del servei. El monitoratge ha de cobrir les àrees següents:

Àrea	Indicadors clau	Impacte si es degrada
Connexions	Connexions actives, en espera, idle	Esgotament del connection pool, errors d'aplicació
Consultes lentes	Temps d'execució, nombre de consultes/s	Latència per a l'usuari final
Bloquejos	Deadlocks, temps d'espera per bloqueig	Transaccions penjades, timeouts
I/O de disc	Bytes llegits/escrits, temps d'espera d'I/O	Consultes lentes per latència de disc
Memòria	Ràtio d'encerts de caché, ús de memòria de treball	Accés a disc en lloc de RAM
Replicació	Lag de rèplica, estat dels esclaus	Dades obsoletes en rèpliques, risc de pèrdua de dades
Espai en disc	Mida de taules, índexs, WAL/binlog	Falta d'espai → aturada del SGBD
CPU	Ús de CPU per consultes	Coll d'ampolla per consultes computacionalment cares

---

Vistes i eines natives de monitoratge

PostgreSQL

PostgreSQLMySQL / MariaDBSQL ServerOracle

-- Connexions actives i el que estan fent
SELECT
    pid,
    usename AS usuari,
    application_name AS aplicació,
    client_addr AS ip_client,
    state,
    wait_event_type,
    wait_event,
    now() - query_start AS durada,
    LEFT(query, 80) AS consulta_truncada
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY durada DESC NULLS LAST;

-- Estadístiques per taula: lectures, escriptures, escanejos
SELECT
    relname AS taula,
    seq_scan,
    seq_tup_read,
    idx_scan,
    idx_tup_fetch,
    n_tup_ins AS insercions,
    n_tup_upd AS actualitzacions,
    n_tup_del AS eliminacions,
    n_live_tup AS files_vives,
    n_dead_tup AS files_mortes,
    last_vacuum,
    last_autovacuum,
    last_analyze
FROM pg_stat_user_tables
ORDER BY seq_scan DESC;

-- Bloquejos actuals
SELECT
    l.pid,
    l.locktype,
    l.relation::regclass AS taula,
    l.mode,
    l.granted,
    a.query,
    now() - a.query_start AS espera
FROM pg_locks l
JOIN pg_stat_activity a ON l.pid = a.pid
WHERE NOT l.granted
ORDER BY espera DESC;

-- Activitat del background writer (escriptures al disc)
SELECT
    checkpoints_timed,
    checkpoints_req,
    checkpoint_write_time,
    checkpoint_sync_time,
    buffers_checkpoint,
    buffers_clean,
    buffers_backend,
    buffers_alloc
FROM pg_stat_bgwriter;

-- Ràtio d'encerts de caché per base de dades
SELECT
    datname,
    blks_hit,
    blks_read,
    ROUND(blks_hit::NUMERIC / NULLIF(blks_hit + blks_read, 0) * 100, 2) AS cache_hit_ratio
FROM pg_stat_database
WHERE datname = current_database();

-- Índexs amb informació d'ús
SELECT
    indexrelname AS index_nom,
    relname AS taula,
    idx_scan AS vegades_usat,
    pg_size_pretty(pg_relation_size(indexrelid)) AS mida_index
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC;

-- Processos actuals (equival a pg_stat_activity)
SHOW FULL PROCESSLIST;

-- O des de INFORMATION_SCHEMA
SELECT
    id AS pid,
    user AS usuari,
    host AS host_client,
    db AS base_dades,
    command AS comanda,
    time AS temps_s,
    state AS estat,
    LEFT(info, 80) AS consulta
FROM information_schema.PROCESSLIST
WHERE command != 'Sleep'
ORDER BY time DESC;

-- Estadístiques globals del servidor
SHOW GLOBAL STATUS LIKE 'Questions';
SHOW GLOBAL STATUS LIKE 'Slow_queries';
SHOW GLOBAL STATUS LIKE 'Threads_connected';
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_%';

-- Ràtio d'encerts de la InnoDB buffer pool
SELECT
    (1 - (SELECT variable_value FROM performance_schema.global_status WHERE variable_name = 'Innodb_buffer_pool_reads') /
         (SELECT variable_value FROM performance_schema.global_status WHERE variable_name = 'Innodb_buffer_pool_read_requests')
    ) * 100 AS cache_hit_ratio_pct;

-- Taules d'I/O (Performance Schema)
SELECT object_schema, object_name, count_read, count_write,
       sum_timer_read / 1e12 AS read_s, sum_timer_write / 1e12 AS write_s
FROM performance_schema.table_io_waits_summary_by_table
WHERE object_schema NOT IN ('mysql', 'sys', 'information_schema', 'performance_schema')
ORDER BY sum_timer_wait DESC
LIMIT 20;

-- Esperes globals (tipus de coll d'ampolla)
SELECT event_name, count_star, sum_timer_wait / 1e12 AS total_espera_s
FROM performance_schema.events_waits_summary_global_by_event_name
WHERE count_star > 0
ORDER BY sum_timer_wait DESC
LIMIT 20;

-- Consultes en execució ara mateix
SELECT
    r.session_id,
    r.status,
    r.wait_type,
    r.wait_time / 1000.0 AS espera_s,
    r.cpu_time,
    r.total_elapsed_time / 1000.0 AS elapsed_s,
    r.reads,
    r.writes,
    r.logical_reads,
    LEFT(qt.text, 100) AS consulta
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) qt
WHERE r.session_id > 50
ORDER BY r.total_elapsed_time DESC;

-- Estadístiques d'esperes (indica colls d'ampolla globals)
SELECT TOP 20
    wait_type,
    waiting_tasks_count,
    wait_time_ms,
    max_wait_time_ms,
    signal_wait_time_ms
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN (
    'SLEEP_TASK','BROKER_TO_FLUSH','BROKER_TASK_STOP','CLR_AUTO_EVENT',
    'DISPATCHER_QUEUE_SEMAPHORE','FT_IFTS_SCHEDULER_IDLE_WAIT',
    'HADR_WORK_QUEUE','LAZYWRITER_SLEEP','LOGMGR_QUEUE',
    'REQUEST_FOR_DEADLOCK_SEARCH','RESOURCE_QUEUE','SERVER_IDLE_CHECK',
    'SLEEP_DBSTARTUP','SLEEP_DCOMSTARTUP','SLEEP_MASTERDBREADY',
    'SLEEP_MASTERMDREADY','SLEEP_MASTERUPGRADED','SLEEP_MSDBSTARTUP',
    'SLEEP_TEMPDBSTARTUP','SNI_HTTP_ACCEPT','SP_SERVER_DIAGNOSTICS_SLEEP',
    'SQLTRACE_BUFFER_FLUSH','WAITFOR','XE_DISPATCHER_WAIT','XE_TIMER_EVENT'
)
ORDER BY wait_time_ms DESC;

-- Consultes amb major consum acumulat
SELECT TOP 20
    qs.execution_count,
    qs.total_elapsed_time / qs.execution_count / 1000 AS avg_elapsed_ms,
    qs.total_logical_reads / qs.execution_count AS avg_reads,
    qs.total_worker_time / qs.execution_count / 1000 AS avg_cpu_ms,
    LEFT(qt.text, 100) AS consulta
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
ORDER BY qs.total_elapsed_time DESC;

-- Sessions actives
SELECT
    sid,
    serial#,
    username,
    status,
    osuser,
    machine,
    program,
    sql_id,
    last_call_et AS temps_desde_darrera_crida_s,
    event AS event_espera
FROM v$session
WHERE status = 'ACTIVE' AND username IS NOT NULL
ORDER BY last_call_et DESC;

-- Consultes en caché: les més costoses
SELECT
    sql_id,
    executions,
    ROUND(elapsed_time / executions / 1000) AS avg_elapsed_ms,
    ROUND(cpu_time / executions / 1000) AS avg_cpu_ms,
    disk_reads,
    buffer_gets,
    SUBSTR(sql_text, 1, 80) AS consulta
FROM v$sql
WHERE executions > 0
ORDER BY elapsed_time DESC
FETCH FIRST 20 ROWS ONLY;

-- Bloquejos
SELECT
    l.sid,
    s.username,
    s.machine,
    o.object_name,
    l.type,
    l.lmode,
    l.request
FROM v$lock l
JOIN v$session s ON l.sid = s.sid
LEFT JOIN dba_objects o ON l.id1 = o.object_id
WHERE l.block = 1 OR l.request > 0
ORDER BY l.sid;

-- Estadístiques globals del sistema
SELECT name, value
FROM v$sysstat
WHERE name IN (
    'physical reads', 'physical writes', 'consistent gets',
    'db block gets', 'redo writes', 'parse count (total)',
    'parse count (hard)', 'execute count', 'user commits', 'user rollbacks'
);

---

Registre de consultes lentes

Configuració del slow query log

PostgreSQLMySQL / MariaDBSQL ServerOracle

# postgresql.conf — registre de consultes lentes

# Registra totes les consultes que triguin més de 500ms
log_min_duration_statement = 500   # en mil·lisegons (-1 = desactivat, 0 = totes)

# Format de la línia de log
log_line_prefix = '%t [%p] %u@%d '

# Registra detalls de bloquejos que esperin més de 1 segon
log_lock_waits = on
deadlock_timeout = 1s

# Registra plans d'execució de consultes lentes (PostgreSQL 14+)
compute_query_id = on
log_min_duration_plan = 1000  # plans de consultes > 1000ms

# Recarregar sense reiniciar
-- SELECT pg_reload_conf();

-- Revisar la configuració actual
SHOW log_min_duration_statement;
SHOW log_destination;
SHOW log_directory;

-- Canviar temporalment per a la sessió actual
SET log_min_duration_statement = 100;

# my.cnf (secció [mysqld]) — slow query log

slow_query_log          = 1
slow_query_log_file     = /var/log/mysql/mysql-slow.log
long_query_time         = 0.5          # en segons (0.5 = 500ms)
log_queries_not_using_indexes = 1      # registra consultes sense índex
log_throttle_queries_not_using_indexes = 10  # màxim 10/minut
min_examined_row_limit  = 100          # ignora consultes sobre < 100 files

-- Activar/desactivar en calent
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 0.5;
SET GLOBAL log_queries_not_using_indexes = 1;

-- Veure configuració actual
SHOW VARIABLES LIKE 'slow%';
SHOW VARIABLES LIKE 'long_query_time';

-- Analitzar el slow query log
-- (des de la línia de comandes del sistema)
-- mysqldumpslow -s t -t 20 /var/log/mysql/mysql-slow.log
-- pt-query-digest /var/log/mysql/mysql-slow.log (Percona Toolkit)

-- SQL Server usa el Query Store per a l'anàlisi de consultes lentes

-- Activar Query Store a la base de dades
ALTER DATABASE NomBaseDades
SET QUERY_STORE = ON (
    OPERATION_MODE = READ_WRITE,
    CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 90),
    DATA_FLUSH_INTERVAL_SECONDS = 900,
    MAX_STORAGE_SIZE_MB = 1000,
    QUERY_CAPTURE_MODE = AUTO,    -- AUTO, ALL, NONE
    SIZE_BASED_CLEANUP_MODE = AUTO
);

-- Consultes amb el major temps mig d'execució (últimes 24h)
SELECT TOP 20
    q.query_id,
    qt.query_sql_text,
    rs.avg_duration / 1000.0 AS avg_ms,
    rs.count_executions,
    rs.avg_cpu_time / 1000.0 AS avg_cpu_ms,
    rs.avg_logical_io_reads
FROM sys.query_store_query q
JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
JOIN sys.query_store_plan p ON q.query_id = p.query_id
JOIN sys.query_store_runtime_stats rs ON p.plan_id = rs.plan_id
JOIN sys.query_store_runtime_stats_interval rsi ON rs.runtime_stats_interval_id = rsi.runtime_stats_interval_id
WHERE rsi.start_time >= DATEADD(HOUR, -24, GETUTCDATE())
ORDER BY rs.avg_duration DESC;

-- Consultes lentes: les 20 amb major temps transcorregut total
SELECT
    sql_id,
    ROUND(elapsed_time / 1e6, 2) AS elapsed_total_s,
    executions,
    ROUND(elapsed_time / executions / 1e3) AS avg_elapsed_ms,
    ROUND(cpu_time / executions / 1e3) AS avg_cpu_ms,
    disk_reads,
    buffer_gets,
    SUBSTR(sql_text, 1, 100) AS sql_text
FROM v$sql
WHERE executions > 0
  AND elapsed_time / executions > 500000  -- > 500ms de mitja
ORDER BY elapsed_time DESC
FETCH FIRST 20 ROWS ONLY;

-- AWR (Automatic Workload Repository) — requereix licència Diagnostics Pack
-- Informe HTML de rendiment per a un interval de temps
-- @$ORACLE_HOME/rdbms/admin/awrrpt.sql

---

Detecció de bloquejos i consultes bloquejants

PostgreSQLMySQL / MariaDBSQL ServerOracle

-- Detectar sessions bloquejades i qui les bloqueja
SELECT
    blocked.pid AS pid_bloquejat,
    blocked_activity.usename AS usuari_bloquejat,
    blocked_activity.query AS consulta_bloquejada,
    now() - blocked_activity.query_start AS temps_bloquejat,
    blocking.pid AS pid_bloquejant,
    blocking_activity.usename AS usuari_bloquejant,
    blocking_activity.query AS consulta_bloquejant
FROM pg_catalog.pg_locks blocked
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked.pid = blocked_activity.pid
JOIN pg_catalog.pg_locks blocking
    ON blocking.locktype = blocked.locktype
    AND blocking.database IS NOT DISTINCT FROM blocked.database
    AND blocking.relation IS NOT DISTINCT FROM blocked.relation
    AND blocking.page IS NOT DISTINCT FROM blocked.page
    AND blocking.tuple IS NOT DISTINCT FROM blocked.tuple
    AND blocking.classid IS NOT DISTINCT FROM blocked.classid
    AND blocking.objid IS NOT DISTINCT FROM blocked.objid
    AND blocking.objsubid IS NOT DISTINCT FROM blocked.objsubid
    AND blocking.pid != blocked.pid
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking.pid = blocking_activity.pid
WHERE NOT blocked.granted;

-- Terminar una consulta bloquejant (sense trencar la connexió)
SELECT pg_cancel_backend(pid_bloquejant);

-- Terminar la connexió completament
SELECT pg_terminate_backend(pid_bloquejant);

-- Veure transaccions i bloquejos (InnoDB)
SELECT
    r.trx_id AS trx_bloquejada,
    r.trx_mysql_thread_id AS thread_bloquejat,
    r.trx_query AS consulta_bloquejada,
    b.trx_id AS trx_bloquejant,
    b.trx_mysql_thread_id AS thread_bloquejant,
    b.trx_query AS consulta_bloquejant
FROM information_schema.innodb_lock_waits w
JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;

-- MySQL 8.0+ usa performance_schema.data_lock_waits
SELECT
    waiting_thread_id,
    waiting_query,
    blocking_thread_id,
    blocking_query
FROM sys.innodb_lock_waits;

-- Terminar el procés bloquejant
KILL 12345; -- substituir 12345 per el thread_id

-- Bloquejos actuals amb informació detallada
SELECT
    blocking_session_id AS bloquejant,
    session_id AS bloquejat,
    wait_type,
    wait_time / 1000.0 AS espera_s,
    LEFT(qt.text, 100) AS consulta_bloquejada
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) qt
WHERE blocking_session_id > 0;

-- Arbre de bloquejos (cadenes de bloqueig)
WITH cte_bloquejos AS (
    SELECT session_id, blocking_session_id, wait_type, wait_time
    FROM sys.dm_exec_requests WHERE blocking_session_id > 0
)
SELECT * FROM cte_bloquejos ORDER BY blocking_session_id;

-- Terminar una sessió bloquejant
KILL 55; -- substituir 55 per el session_id

-- Cadena de bloquejos
SELECT
    l1.sid AS sid_bloquejant,
    s1.username AS usuari_bloquejant,
    l2.sid AS sid_bloquejat,
    s2.username AS usuari_bloquejat,
    o.object_name AS objecte
FROM v$lock l1
JOIN v$lock l2 ON l1.id1 = l2.id1 AND l1.id2 = l2.id2
JOIN v$session s1 ON l1.sid = s1.sid
JOIN v$session s2 ON l2.sid = s2.sid
JOIN dba_objects o ON l1.id1 = o.object_id
WHERE l1.block = 1 AND l2.request > 0;

-- Terminar una sessió
ALTER SYSTEM KILL SESSION '45,12345';  -- (sid, serial#)

---

Eines externes de monitoratge

Prometheus + Exporters + Grafana

L'arquitectura de monitoratge moderna més estesa combina:

• Prometheus: motor de sèries temporals que recull mètriques periòdicament (scraping).
• Exporters: adaptadors específics per a cada SGBD que exposen mètriques en format Prometheus.
• Grafana: plataforma de visualització amb dashboards predefinits.

graph LR
    PG["PostgreSQL"] --> PE["postgres_exporter\n:9187"]
    MY["MySQL"] --> ME["mysqld_exporter\n:9104"]
    PROM["Prometheus\n:9090"] --> |scrape cada 15s| PE
    PROM --> |scrape cada 15s| ME
    GRAFANA["Grafana\n:3000"] --> |consulta PromQL| PROM
    ALERT["Alertmanager"] --> |alerta| SLACK["Slack / Email"]
    PROM --> ALERT

Exporters disponibles:

Motor	Exporter	URL
PostgreSQL	postgres_exporter	github.com/prometheus-community/postgres_exporter
MySQL / MariaDB	mysqld_exporter	github.com/prometheus/mysqld_exporter
SQL Server	sql_exporter	github.com/free/sql_exporter
Oracle	oracledb_exporter	github.com/iamseth/oracledb_exporter

Exemple de configuració postgres_exporter:

# docker-compose.yml (fragment)
postgres_exporter:
  image: prometheuscommunity/postgres-exporter
  environment:
    DATA_SOURCE_NAME: "postgresql://monitor_user:password@postgres:5432/postgres?sslmode=disable"
  ports:
    - "9187:9187"

Percona Monitoring and Management (PMM)

PMM és una solució completa de monitoratge per a MySQL, PostgreSQL i MongoDB. Inclou:

• Query Analytics (QAN): anàlisi de consultes lentes amb desglossament detallat.
• Dashboards de Grafana predefinits i optimitzats.
• Alertes configurables.
• Integració amb Percona Toolkit.

# Instal·lació ràpida amb Docker
docker run -d -p 443:443 --name pmm-server percona/pmm-server:latest

# Afegir un client PostgreSQL
pmm-admin add postgresql --username=pmm --password=secret --host=localhost

Zabbix

Zabbix és una plataforma de monitoratge general que inclou templates específics per a SGBD. És habitual en entorns empresarials que ja usen Zabbix per al monitoratge d'infraestructura.

---

Connection Pooling

Les bases de dades en producció han de gestionar moltes connexions concurrents. Crear i destruir connexions és una operació costosa. El connection pooler manté un conjunt (pool) de connexions obertes i les reutilitza per a les peticions de les aplicacions.

graph LR
    A1["App 1\n(100 threads)"] --> CP
    A2["App 2\n(50 threads)"] --> CP
    A3["App 3\n(200 threads)"] --> CP
    CP["Connection Pooler\n(PgBouncer / ProxySQL)"] --> |"20 connexions reals"| DB[("SGBD")]
    style CP fill:#f0ad4e,color:#333
    style DB fill:#337ab7,color:#fff

PgBouncer (PostgreSQL)

PgBouncer és el pooler de connexions estàndard per a PostgreSQL.

# pgbouncer.ini — configuració bàsica
[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb

[pgbouncer]
listen_port     = 6432
listen_addr     = 0.0.0.0
auth_type       = md5
auth_file       = /etc/pgbouncer/userlist.txt
logfile         = /var/log/pgbouncer/pgbouncer.log

# pool_mode:
#   session    - 1 connexió real per sessió client (menys eficient)
#   transaction - 1 connexió real per transacció (recomanat)
#   statement   - 1 connexió real per sentència (restrictiu)
pool_mode       = transaction

max_client_conn = 1000    # màxim de clients que es poden connectar a PgBouncer
default_pool_size = 20    # connexions reals obertes cap a PostgreSQL

ProxySQL (MySQL)

ProxySQL és el pooler/proxy estàndard per a MySQL. A més del pooling, ofereix:

• Enrutament automàtic de lectures a rèpliques.
• Reescriptura de consultes.
• Gestió de failover.
• QoS per usuari/hostgroup.

-- Configuració bàsica de ProxySQL via SQL
INSERT INTO mysql_servers (hostgroup_id, hostname, port, max_connections)
VALUES
    (0, 'mysql-primary', 3306, 200),    -- hostgroup 0: escriptura
    (1, 'mysql-replica1', 3306, 200),   -- hostgroup 1: lectura
    (1, 'mysql-replica2', 3306, 200);

-- Regla: enviar SELECTs a les rèpliques (hostgroup 1)
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply)
VALUES (1, 1, '^SELECT', 1, 1);

LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

Connection pooling: sempre recomanat en producció

En producció, mai connectis les aplicacions directament al SGBD. Usa sempre un pooler com PgBouncer o ProxySQL. Sense pooler, cada request HTTP pot crear una nova connexió de base de dades, i es pot arribar al límit max_connections molt fàcilment, especialment amb frameworks que usen un thread per request.

---

Miniactivitat — AC0505 · AC0506 · AC0507

Objectiu: Configurar el registre de consultes lentes i identificar consultes problemàtiques.

Escenari: Tens una instància PostgreSQL en local (o via Docker). Cal activar el slow query log, generar consultes lentes i analitzar-les.

Passos:

1. Modifica postgresql.conf per activar log_min_duration_statement = 100 (100ms). Recàrrega: SELECT pg_reload_conf();

2. Crea una taula gran:

   CREATE TABLE logs_app (
       id        BIGSERIAL PRIMARY KEY,
       usuari_id INT,
       accio     VARCHAR(50),
       detall    TEXT,
       creat_a   TIMESTAMPTZ DEFAULT NOW()
   );
   INSERT INTO logs_app (usuari_id, accio, detall, creat_a)
   SELECT
       (random()*1000)::INT,
       (ARRAY['login','logout','compra','vista','error'])[ceil(random()*5)],
       repeat('x', (random()*200)::INT),
       NOW() - (random()*365)::INT * INTERVAL '1 day'
   FROM generate_series(1, 1000000);
   

3. Executa SELECT * FROM logs_app WHERE detall LIKE '%xxx%'; i verifica que apareix al log.

4. Consulta pg_stat_activity mentre la consulta s'executa (en una altra sessió).

5. Consulta pg_stat_user_tables per a la taula logs_app. Quin és el seq_scan count?

6. Instal·la pgBadger (si és possible) i analitza el log per obtenir un informe HTML:

   pgbadger /var/log/postgresql/postgresql-*.log -o informe.html
   

Lliura: Informe amb captures del log, de pg_stat_activity i de les estadístiques de la taula. Proposa millores per a les consultes lentes detectades.