PostgreSQL sur K8S: Latence aléatoire et 'idle in transaction'

Idle in transaction avec des resets c'est toujours chiant. Ça peut être la base de données, l'opérateur, Kubernetes ou même l'appli cliente.

Comme les metrics classiques sont ok, on va creuser le stockage. Tes gp3 sont provisionnés comment ? C'est quoi les IOPS/MBps max ? Et côté noeud Kubernetes, iostat -x 1 sur les devices de tes PVs pendant un pic ? Regarde avgqu-sz et await.

On est provisionné pour 16k IOPS et 250 MB/s. On est très loin d'atteindre ça, même pendant les pics de latence. iostat sur le node montre un await de 2-3ms max, avgqu-sz sous les 5, et %util sous les 20%. Zéro iowait apparent sur htop ou vmstat.

Donc le stockage brut n'est pas le problème.

Ok storage check. `idle in transaction` peut être un symptôme d'un problème plus large si l'opérateur ou Patroni a du mal avec les locks distribués. Ton opérateur s'appuie sur `etcd` pour l'HA de Patroni non ?

Quelle est la latence de `etcd` depuis tes pods ? etcdctl endpoint health --cluster ou des métriques Prometheus sur `etcd` si tu en as.

Oui l'opérateur utilise Patroni qui s'appuie sur etcd pour le leader election. On a un monitoring etcd dédié, la latence est très basse, sous les 5ms p99. Pas d'erreurs ni de soucis de quorum.

Donc pas de problème etcd.

Bon. Donc etcd clean, storage clean. Reviens à PostgreSQL pur. C'est quoi tes réglages de `wal_sync_method` et `fsync` ?

Un `fsync` qui bloque sur un filesystem lent peut causer ces pics. Même sur du gp3, si le kernel est super chargé ailleurs ou si un bug dans le driver ou le CSI se déclenche.

Alors :

SHOW wal_sync_method;
 fdatasync
SHOW fsync;
 on

Ce sont les valeurs par défaut pour une bonne raison. On ne veut pas désactiver `fsync` en prod. On a aussi `checkpoint_timeout` à 5min et `max_wal_size` à 1GB.

Ces réglages sont standards et n'ont jamais posé problème avant.

Ok `fsync` est `on` c'est normal. Mais le comportement `idle in transaction` est le plus gros indice ici. C'est pas un blocage interne à PG pour `fsync`. Si c'était ça tu verrais des `waiting on wal writer` ou `waiting on checkpointer`.

`idle in transaction` ça veut dire qu'une connexion cliente a commencé une transaction mais n'a pas encore fait de `COMMIT` ou `ROLLBACK`. Elle attend juste.

Utilises-tu un pooler de connexions entre tes applications et PostgreSQL ? Style `pgbouncer` ou un truc custom ?

Oui on utilise `pgbouncer` en mode `transaction pooling`. On a configuré des timeouts assez standard :

server_lifetime = 3600
server_idle_timeout = 60
client_idle_timeout = 30

Nos applications ont aussi des timeouts client de 30 secondes pour les requêtes.

Ah. `pgbouncer` en `transaction pooling`. C'est l'explication probable.

En mode transaction, `pgbouncer` va dédier une connexion serveur à un client pour la durée d'une transaction. Si le client ouvre une transaction (`BEGIN`), puis fait des opérations, puis ne fait rien pendant un long moment, la connexion serveur va rester en `idle in transaction` côté PostgreSQL.

Exactement. Et si ce client ne `COMMIT` ou `ROLLBACK` pas, et qu'il dépasse son propre timeout applicatif de 30 secondes, l'application va fermer la connexion côté client.

Ça force pgbouncer à réinitialiser cette connexion backend qui est en idle in transaction, ce qui peut générer un reset TCP ou une annulation de requête. Et pendant ce temps, cette connexion backend est bloquée, d'où les pics de latence si pgbouncer n'a plus de connexions disponibles dans son pool.

Ok je suis le raisonnement. Donc ce n'est pas un problème de performance du tout. C'est l'application qui ouvre une transaction et la laisse active beaucoup trop longtemps sans faire de `COMMIT` ou `ROLLBACK`. Et le pgbouncer en mode transaction pooling expose ce comportement.

Les pics de latence c'est quand un client prend une connexion du pool, commence une transaction, et qu'il est lent à la conclure, bloquant cette connexion. Quand il y a plusieurs de ces cas, ça crée des goulots d'étranglement sur le pool de pgbouncer.

Exact. La solution n'est pas dans PostgreSQL ou Kubernetes, mais dans le code applicatif. Il faut s'assurer que les transactions sont aussi courtes que possible et qu'elles sont toujours explicitement terminées (`COMMIT` ou `ROLLBACK`).

Tu peux ajouter un statement_timeout sur `pgbouncer` aussi si tu veux tuer les transactions trop longues plus agressivement, mais ça ne résout pas la cause racine.

Je vois clair maintenant. Le idle in transaction était le symptôme clair d'un problème applicatif mal géré sur les transactions.

On va se pencher sur l'audit du code applicatif pour identifier les transactions zombie et les corriger. C'est une interaction classique entre appli et base de données mais le pooling `pgbouncer` a rendu ça plus visible.

Merci pour l'aide précieuse à débugger ça les gars !