Traqueur, le chargement interminable

      Dans une entreprise lointaine, vous êtes sollicité car un chargement en base PostgreSQL est "lent". La 1ère partie constituant une table de travail prend déjà plus de 10 heures. Un environnement de test existe, il est à l’image de la production mais traite 5% de la volumétrie habituellement chargée. Plusieurs intervenants ont déjà des avis "Il faudrait une baie full flash" "Le chargement ne parvient pas à charger la machine donc parallélisons-le" etc.
      Vous n’avez pas d’avis. Vous avez le traqueur que vous décidez de faire tourner pendant cette 1ère partie :

      Le chargement a duré 37min36. Pendant ce temps, vous avez fait tourner 4 fois le traqueur avec différentes options et vous avez recueilli beaucoup d’informations.

• La 1ère session cible l’application BATCH3 avec l’option -w mais les autres options sont laissées par défaut. Le traitement est bien actif sur PostgreSQL à plus de 90% et une instruction d’INSERT assez basique est seule en cause dans l’activité.
• La 2ème session interroge de manière plus agressive pg_stat_activity (option -s 1). Elle montre qu’individuellement chaque instruction n’est pas si lente. Quasi toutes les exécutions ne sont relevées qu’une fois : 445 exécutions distinctes sur les 469 exécutions. Cependant la durée du chargement indique que des centaines de milliers d’exécutions sont effectuées. Leur temps cumulé finit par être très significatif.
• La 3ème session utilise l’option -p pour donner les informations de charge CPU, mémoire etc. Elle montre que l’information fournie par un des experts qui affirmait que le chargement était lent mais peu coûteux en ressources était exacte. Le système de test est un quadriprocesseur et le chargement ne parvient pas à occuper un CPU : 85% de temps non CPU au niveau du process de chargement et 5% de charge CPU globale au niveau de l’ensemble du système. Cette incapacité à charger le serveur est suspecte.
• La 4ème session est effectuée sur une autre colonne de pg_stat_activity grâce à l’option -o. La colonne xact_start donne les débuts de transaction et 422 débuts de transaction différents sont relevés. Le chargement valide de manière excessive et sans aucune logique transactionnelle. Chaque partie du chargement est du "tout ou rien". La constitution de la table de travail dans sa globalité constitue une de ces parties et donc logiquement UNE transaction.

      Vous avez modifié la persistence des données de la table de travail. Impossible de passer cette table en table temporaire puisque ses données sont interrogées par d’autres sessions dans la suite du traitement. Mais il ne sert rien de les protéger en cas de crash du serveur ou autre puisqu’elles sont inutiles une fois le traitement terminé.
      Le chargement est relancé après cette modification et vous le suivez encore une fois avec le traqueur :

      Le chargement n’est plus actif qu’à 54% au niveau PostgreSQL mais la part non CPU est tombée à 32%. Au niveau global le système est à présent chargé à 21%. Autrement dit le travail est plus intense. La durée du chargement est passée à 3min25s. Cependant passer une table en UNLOGGED n’est pas applicable dans la majeure partie des cas et vous conseillez de modifier le programme. COPY ne peut pas être utilisé car il ne s’agit pas d’un simple chargement de données brutes, des fonctions PostgreSQL sont appelées à l’insertion. Mais les transactions inutiles doivent être éliminées !
      Une modification est effectuée pour ouvrir une transaction (START TRANSACTION) et valider en fin de chargement (COMMIT) au lieu de valider après chaque INSERT. Le chargement est relancé après cette modification et vous le suivez encore une fois avec le traqueur :

      La 1ère session du traqueur montre que le travail de modification a bien été effectué. Vous relevez une seule transaction pour 354 exécutions distinctes.
      La 2ème session montre que le taux d’activité au niveau PostgreSQL a augmenté à 80% et on vous dit que la durée du chargement est tombée à 2min15.
      Ces multiples transactions étaient coûteuses mais pourquoi ne pas réduire aussi le nombre d’exécutions ? Il est possible d’insérer de multiple lignes avec un seul INSERT et une seule clause VALUES avec PostgreSQL (voir cet article). Le programme est modifié en ce sens et vous relancez le traqueur :

      BOUM !!!!! Le mieux est l’ennemi du bien. Une seul instruction INSERT ça ne passe pas. Le traqueur montre que le process de chargement consomme toute la RAM du serveur, tout le SWAP et finit par faire crasher PostgreSQL. Le programme est modifié pour que chaque INSERT charge 100 lignes à la fois. Le programme est modifié en ce sens et vous relancez le traqueur :

      Le chargement qui durait 37 minutes au départ s’achève ici en 37s. 37s d’activité intense comme le montre le traqueur (92% et un CPU entièrement sollicité) mais le gain est spectaculaire. En production cette partie du chargement durait plus de 10 heures, elle s’achèvera logiquement en moins de 15 minutes, mission réussie.

Mise à jour : 19/12/2016