Salta el contingut

Fonaments del rendiment

El cicle de l'optimització

L'optimització del rendiment (performance tuning) és el procés sistemàtic d'identificar i eliminar els colls d'ampolla que limiten la velocitat, la capacitat o l'escalabilitat d'un sistema gestor de bases de dades. No es tracta d'una acció puntual, sinó d'un cicle continu d'observació, mesura, intervenció i validació.

flowchart LR
    A[Observar] --> B[Mesurar]
    B --> C[Diagnosticar]
    C --> D[Intervenir]
    D --> E[Validar]
    E --> A

Les quatre capes de l'optimització

1. Índexs

Els índexs permeten al motor localitzar files sense escanejar tota la taula. Una base de dades sense índexs adequats és com un llibre sense índex: per trobar qualsevol cosa cal llegir-lo sencer. L'objectiu és disposar dels índexs justos: ni de menys (consultes lentes) ni de massa (escriptures lentes i espai malgastat).

2. Plans d'execució

El query planner (o query optimizer) de cada motor decideix com executar una consulta SQL: quin índex usar, en quin ordre fer els joins, si ordenar primer o filtrar primer. Llegir i entendre els plans d'execució és l'habilitat central de l'optimització.

3. Configuració de memòria

La memòria RAM és el recurs més valuós per al rendiment. Els paràmetres com shared_buffers (PostgreSQL), innodb_buffer_pool_size (MySQL) o buffer cache (Oracle) determinen quanta informació es manté a la memòria cau i quant sovint cal llegir del disc.

4. I/O i disc

Quan la memòria no és suficient, el motor ha d'accedir al disc. L'I/O de disc és ordres de magnitud més lent que l'accés a memòria. Cal minimitzar les lectures físiques, distribuir les dades en volums ràpids (SSD NVMe) i evitar operacions d'ordenació excessives.

Àrees principals d'optimització

mindmap
  root((Optimització SGBD))
    Índexs
      B-tree
      Hash
      Full-text
      Parcials
      Compostos
    Plans d'execució
      EXPLAIN ANALYZE
      Seq Scan vs Index Scan
      Join algorithms
      Estadístiques
    Configuració
      Memòria shared_buffers
      Work_mem
      Connexions
      Checkpoints
    Monitoratge
      Consultes lentes
      Bloquejos
      Connexions actives
      I/O estadístiques
    Consultes SQL
      Antipatrons
      Reescriptura
      CTEs
      Window functions

Rendiment des del punt de vista de l'usuari

Un usuari espera que una consulta OLTP (transaccional) respongui en menys de 10 ms. Una consulta analítica complexa pot admetre fins a alguns segons, però mai minuts. Quan el rendiment es degrada, els efectes en cascada son ràpids:

  • Les connexions s'acumulen esperant.
  • El connection pool s'omple.
  • Les aplicacions web retornen errors 503.
  • La base de dades pot arribar a bloquejar-se completament.

Per evitar-ho, l'administrador de SGBD necessita eines de diagnosi (plans d'execució, vistes de sistema, logs) i coneixement dels patrons d'optimització.

Miniactivitat — AC0377/05/01

Miniactivitat — AC0377/05/01 · Benchmark bàsic amb pgbench i sysbench

Part 1 — PostgreSQL amb pgbench.

docker exec -it postgres-nom-cognom bash
# pgbench sol venir amb les eines client de PostgreSQL
pgbench -i -s 10 -U gbd_user sgbd_nom_cognom   # inicialitza dades de prova (escala 10)
pgbench -c 10 -j 2 -T 30 -U gbd_user sgbd_nom_cognom   # 10 clients, 2 fils, 30 segons

Anota el resultat de tps (transaccions per segon) que mostra pgbench en acabar.

Part 2 — MySQL amb sysbench.

docker exec -it mysql-nom-cognom bash
sysbench oltp_read_write --db-driver=mysql --mysql-user=gbd_user \
    --mysql-password=gbd2025 --mysql-db=sgbd_nom_cognom --tables=10 --table-size=100000 prepare
sysbench oltp_read_write --db-driver=mysql --mysql-user=gbd_user \
    --mysql-password=gbd2025 --mysql-db=sgbd_nom_cognom --tables=10 --table-size=100000 \
    --threads=10 --time=30 run

Anota el resultat de transactions/sec (TPS) i queries/sec (QPS) del resum final.

Anàlisi:

  1. Compara el TPS obtingut a PostgreSQL i a MySQL: són comparables directament? Per què cal anar amb compte en comparar benchmarks entre motors diferents (maquinari compartit, configuració per defecte, tipus de càrrega)?
  2. Repeteix el benchmark de pgbench augmentant el nombre de clients (-c 50). Què passa amb el TPS? Segueix pujant proporcionalment o s'estabilitza?
  3. Relaciona els resultats amb el cicle d'optimització de la secció inicial: si el TPS és baix, per quina de les 4 capes (índexs, plans, memòria, I/O) començaries a investigar i per què?

Temps estimat: 40 minuts.

Recursos recomanats