To sidebar

Dans les améliorations de performances de la v11, l’une d’elles peut faire gagner beaucoup de temps lors des mises à jour applicatives, voire peut en rendre certaines simplement possibles :

Many other useful performance improvements, including making ALTER TABLE .. ADD COLUMN with a non-null column default faster

Les détails sont chez Brandur. En résumé, lors de l’ajout d’une colonne avec une valeur par défaut, PostgreSQL pré-v11 devait réécrire la table pour la rajouter physiquement. Ce qui prend son temps si ladite table fait 1 To. Et l’application utilisatrice est bloquée, ce qui peut être intolérable. Les contournements, comme l’ajout d’une colonne nullable puis la mise à jour progressive par batchs, introduisent un état transitoire un peu bancal.

Vient la v11, qui se contente de rajouter l’information que la colonne a été ajoutée, et que si elle ne se trouve pas dans la ligne, il faut prendre la valeur par défaut.

Petit test avec une table tellement vide qu’elle n’a même pas de colonnes :

 CREATE TABLE fantome
 AS SELECT  /* rien */ FROM generate_series(1,1000000) ;
 SELECT pg_size_pretty(pg_relation_size('fantome')) ;
  pg_size_pretty 
 ────────────────
  27 MB

Donc 27 Mo d’informations administratives sur 1 millions de lignes de néant. Soit, c’est un cas dégénéré.

Ajout d’une colonne avec une valeur par défaut :

 ALTER TABLE fantome ADD COLUMN a1 TEXT NOT NULL DEFAULT 'azertyuiopqsdfghjklmwxcvbn,;:!0123456789';
 ALTER TABLE
 Temps : 24,337 ms

C’est instantané en v11, qui se contente de stocker la valeur par défaut des colonnes non remplies (alors que cette opération durait une bonne seconde en v10 sur mon portable à SSD). Qu’il y ait un NOT NULL ou pas ne change rien.

En toute rigueur PostgreSQL applique toujours sur la table le verrou de plus haut niveau, AccessExclusive, qui va bloquer toutes vos requêtes (ce qui se verra très vite si lui-même est bloqué et qu’elles « s’empilent » derrière lui). Et la valeur par défaut doit être une constante, du moins pendant la durée de l’ordre (donc un timetz DEFAULT now() sera ajouté instantanément, mais la même en DEFAULT clock_timestamp() donne des valeurs différentes presque sur chaque ligne, donc n’évitera pas la réécriture de la table).

En conséquence, la taille de la table n’a pas bougé :

 SELECT pg_size_pretty(pg_relation_size('fantome')) ;
  pg_size_pretty 
 ────────────────
  27 MB

Si on se met à jour cette nouvelle colonne, évidemment on va devoir en payer le prix (en gros ici 1 million * 19 octets du nouveau texte + les Mo d’administration des lignes + la place des anciennes lignes marquées comme périmées ) :

 UPDATE fantome SET a1='beaucoup plus court' ;
 UPDATE 1000000
 Durée : 4823,868 ms (00:04,824)
 SELECT pg_size_pretty(pg_relation_size('fantome')) ;
  pg_size_pretty 
 ────────────────
  77 MB

Ensuite on va se faire plaisir en rajoutant plein d’autres colonnes aux valeurs par défaut :

   ALTER TABLE fantome ADD COLUMN a2 TEXT NOT NULL DEFAULT 'azertyuiopqsdfghjklmwxcvbn,;:!0123456789';
   ALTER TABLE fantome ADD COLUMN a3 TEXT NOT NULL DEFAULT 'azertyuiopqsdfghjklmwxcvbn,;:!0123456789';
   ALTER TABLE fantome ADD COLUMN a4 TEXT NOT NULL DEFAULT 'azertyuiopqsdfghjklmwxcvbn,;:!0123456789';
   ALTER TABLE fantome ADD COLUMN a5 TEXT NOT NULL DEFAULT 'azertyuiopqsdfghjklmwxcvbn,;:!0123456789';
   ALTER TABLE fantome ADD COLUMN a6 TEXT NOT NULL DEFAULT 'azertyuiopqsdfghjklmwxcvbn,;:!0123456789';
   ALTER TABLE fantome ADD COLUMN a7 TEXT NOT NULL DEFAULT 'azertyuiopqsdfghjklmwxcvbn,;:!0123456789';
   ALTER TABLE fantome ADD COLUMN a8 TEXT NOT NULL DEFAULT 'azertyuiopqsdfghjklmwxcvbn,;:!0123456789';
   ALTER TABLE fantome ADD COLUMN a9 TEXT NOT NULL DEFAULT 'azertyuiopqsdfghjklmwxcvbn,;:!0123456789';
   ALTER TABLE fantome ADD COLUMN a10 TEXT NOT NULL DEFAULT 'azertyuiopqsdfghjklmwxcvbn,;:!0123456789';
   ALTER TABLE fantome ADD COLUMN a11 TEXT NOT NULL DEFAULT 'azertyuiopqsdfghjklmwxcvbn,;:!0123456789';
   ALTER TABLE fantome ADD COLUMN a12 TEXT NOT NULL DEFAULT 'azertyuiopqsdfghjklmwxcvbn,;:!0123456789';
   ALTER TABLE fantome ADD COLUMN a13 TEXT NOT NULL DEFAULT 'azertyuiopqsdfghjklmwxcvbn,;:!0123456789';
   ALTER TABLE fantome ADD COLUMN a14 TEXT NOT NULL DEFAULT 'azertyuiopqsdfghjklmwxcvbn,;:!0123456789';

Comme pour le premier ordre, chaque ordre fait 1 ms, et la taille n’a pas bougé :

 77 MB

Ce n’est pas une arnaque, les valeurs sont bien là (du moins elles sont facilement reconstituées) :

 SELECT a14 FROM fantome LIMIT 1 ;
                    a14                    
 ──────────────────────────────────────────
  azertyuiopqsdfghjklmwxcvbn,;:!0123456789

Maintenant on suppose qu’il faut reconstruire la table :

 VACUUM FULL fantome ;
 SELECT pg_size_pretty(pg_relation_size('fantome')) ;
 601 MB

Ce qui est la « vraie » taille, qu’on aurait obtenue d’entrée en v10, avec les lignes contenant toute en dur la valeur par défaut.

Par contre, on est très loin de la politique habituelle « pour récupérer de l’espace avec un VACUUM FULL, prévoyez transitoirement un espace supplémentaire équivalent à la taille de la table » : ici la taille de la table reconstruite explose.

Big bang à effet retard

Quel est le use case pour cette nouvelle fonctionnalité ? Les grosses tables que l’on ne pouvait plus modifier parce qu’une réécriture serait bloquante trop longtemps. Ce problème-là est résolu. La croissance du volume se fera de manière plus progressive au fil des mises à jour (pas seulement des anciennes colonnes, puisqu’on met à jour toute la ligne en cas de mise à jour de n’importe quelle colonne).

Ce peut aussi être une fonctionnalité sympa sur une table « presque vide » aux très nombreuses colonnes presque toujours toutes avec la valeur par défaut, et jamais mises à jour (ça doit bien exister quelque part). Elle prendra très peu de place par rapport à son volume théorique.

Par contre, après une purge, on peut penser que l’on va récupérer de la place et on passe enfin ce VACUUM FULL qui a tant tardé… pour voir la taille exploser.

Idem en cas de restauration logique (même si on l’évite sur de grosses tables) : la table restaurée peut prendre beaucoup plus de place que l’ancienne.

Dans les deux cas, avec de la chance, cela sera compensé par des DROP COLUMNS plus anciens, dont la place est gaspillée jusqu’à reconstruction de la table.

mardi 10 juillet 2018

Combien de partitions dans PostgreSQL 10 ?

Certains types de requêtes sont condamnées au seq scan (parcours de table complet), par exemple des requêtes décisionnelles portant sur un historique assez long. Mais on n’a pas forcément envie de parcourir tout l’historique non plus, et les index ne peuvent pas tout. Les tables énormes sont également peu maniables (VACUUM FULL impossible, difficulté à les répartir sur des disques différents…). Pour accélérer malgré tout ces requêtes, tout en se simplifiant l’administration, il est plus confortable de tronçonner la table en partitions, stockées parfois dans différents tablespaces, par exemple en données mensuelles que l’on pourra au besoin parcourir intégralement.

C’est le but du tout nouveau partitionnement déclaratif de PostgreSQL 10. Il y avait déjà un système de partitionnement par héritage, mais peu pratique et peu utilisé.

Mais jusqu’où peut-on monter dans le nombre de partitions ? (Spoiler : pas trop haut). Petit test :

Config

Mon portable raisonnablement récent a 2 cœurs 4 threads et SSD fait tourner un PostgreSQL 10.3 avec shared_buffers = 512 Mo ; et pour figer les choses :

 SET max_parallel_workers to 4 ;
 SET max_parallel_workers_per_gather to 4 ;
 SET work_mem to '128MB' ;
 SET effective_io_concurrency TO 100;
 SET random_page_cost TO 0.1 ;  
 SET seq_page_cost TO 0.1 ;

Enfin, ce paramètre ne vise qu’à accélérer les tests ; ne faites pas en prod sans savoir ce que cela implique :

  SET synchronous_commit TO off;

Sans partition

Premier exemple avec 500 millions de petites lignes dans une table monolithique de 2 212 390 blocs et 17 Go, sans index :

 CREATE TABLE bigtable (id int, x int);
 INSERT INTO bigtable SELECT i, mod (i, 777777)
 FROM generate_series (1, 500*1000*1000) i;
 \d+
 Liste des relations
 Schéma |      Nom       | Type  | Propriétaire | Taille  | Description
 public | bigtable       | table | postgres     | 17 GB   |

Ne pas oublier les statistiques, par principe après chaque insertion massive (ça ne prend qu’un quart d’heure ici, après tout) :

 VACUUM VERBOSE ANALYZE bigtable ;

Dans ce qui suit, le manque d’index est délibéré, je ne recherche que des parcours de table complets.

On commence petit, en sommant juste 100 lignes en plein milieu de la table. Je ne m’intéresse pas au contenu mais à la manière dont PostgreSQL va se débrouiller, donc je demande le plan avec tous les détails :

 EXPLAIN (ANALYZE, VERBOSE, BUFFERS)
 SELECT sum(x)FROM bigtable
 WHERE id BETWEEN 199000001 AND 199000100 ;

Et on n’est pas déçu : 33 secondes passées à scanner intégralement la table, 4 workers partageant avec le processus maître la lecture et le filtrage des lignes, puis la somme.

 Finalize Aggregate  (cost=2097239.49..2097239.50 rows=1 width=8) (actual time=33015.147..33015.148 rows=1 loops=1)
   Output: sum(x)
   Buffers: shared hit=14373 read=430763 dirtied=936 written=11
   I/O Timings: read=21404.854 write=0.075
   ->  Gather  (cost=2097239.07..2097239.48 rows=4 width=8) (actual time=33015.001..33015.142 rows=5 loops=1)
         Output: (PARTIAL sum(x))
         Workers Planned: 4
         Workers Launched: 4
         Buffers: shared hit=14373 read=430763 dirtied=936 written=11
         I/O Timings: read=21404.854 write=0.075
         ->  Partial Aggregate  (cost=2096239.07..2096239.08 rows=1 width=8) (actual time=32990.839..32990.839 rows=1 loops=5)
               Output: PARTIAL sum(x)
               Buffers: shared hit=55304 read=2157086 dirtied=5855 written=30
               I/O Timings: read=107067.211 write=0.200
               Worker 0: actual time=32989.611..32989.612 rows=1 loops=1
                 Buffers: shared hit=12089 read=426532 dirtied=1361 written=19
                 I/O Timings: read=21461.396 write=0.125
               Worker 1: actual time=32986.118..32986.118 rows=1 loops=1
                 Buffers: shared hit=6723 read=416321 dirtied=1310
                 I/O Timings: read=21437.665
               Worker 2: actual time=32974.608..32974.608 rows=1 loops=1
                 Buffers: shared hit=8438 read=441747 dirtied=1278
                 I/O Timings: read=21433.336
               Worker 3: actual time=32989.476..32989.476 rows=1 loops=1
                 Buffers: shared hit=13681 read=441723 dirtied=970
                 I/O Timings: read=21329.960
               ->  Parallel Seq Scan on public.bigtable  (cost=0.00..2096239.00 rows=29 width=4) (actual time=26784.787..32990.833 rows=20 loops=5)
                     Output: x
                     Filter: 
                     Rows Removed by Filter: 99999980
                     Buffers: shared hit=55304 read=2157086 dirtied=5855 written=30
                     I/O Timings: read=107067.211 write=0.200
                     Worker 0: actual time=17474.477..32989.605 rows=6 loops=1
                       Buffers: shared hit=12089 read=426532 dirtied=1361 written=19
                       I/O Timings: read=21461.396 write=0.125
                     Worker 1: actual time=17471.005..32986.104 rows=94 loops=1
                       Buffers: shared hit=6723 read=416321 dirtied=1310
                       I/O Timings: read=21437.665
                     Worker 2: actual time=32974.603..32974.603 rows=0 loops=1
                       Buffers: shared hit=8438 read=441747 dirtied=1278
                       I/O Timings: read=21433.336
                     Worker 3: actual time=32989.472..32989.472 rows=0 loops=1
                       Buffers: shared hit=13681 read=441723 dirtied=970
                       I/O Timings: read=21329.960
 Planning time: 0.584 ms
 Execution time: 33034.340 ms

Et encore, ce pourrait être pire sans le parallélisme (on passerait à 45 secondes). Dans l’autre sens, PostgreSQL utiliserait 8 workers si max_workers_per_gather le permettait (mais je n’ai que 4 threads sur cette machine, il y en a même un de trop ici).

Notez le temps de planification ridicule d’une demi-milliseconde.

3 partitions

Avant d’en créer plus, j’ai voulu tester avec juste 3 partitions, dont une ne représentant qu’un centième de la volumétrie, soit 5 millions de lignes. On recrée la table :

 DROP TABLE bigtable ;
 CREATE TABLE bigtable (id int, x int)
 PARTITION BY RANGE (id);

Le partitionnement nouveau style (par range ici) est tout bête avec juste 3 partitions  ; il faut savoir que les bornes supérieures sont exclues :

 CREATE TABLE bigtable_grande1
 PARTITION OF bigtable
 FOR VALUES FROM (1) TO (199000000) ;
 CREATE TABLE bigtable_petite
 PARTITION OF bigtable
 FOR VALUES FROM (199000000) TO (200000001) ;
 CREATE TABLE bigtable_grande2
 PARTITION OF bigtable
 FOR VALUES FROM (200000001) TO (500000001) ;

L’insertion de données se fait de la même manière. Qu’il suffise de dire que le temps d’insertion est à peine plus élevé (autour de 10 minutes contre 9 et demi), ce qui est un des plus gros progrès par rapport à l’ancienne méthode de partionnement.

 INSERT INTO bigtable SELECT i, mod (i, 777777) FROM generate_series (1, 500*1000*1000) i;
 VACUUM VERBOSE ANALYZE bigtable ;

Chaque table (la partitionnée et ses filles) apparaissent comme des tables indépendantes :

  Schéma |       Nom        | Type  | Propriétaire | Taille   | Description
  public | bigtable         | table | postgres     | 0 bytes  |
  public | bigtable_grande1 | table | postgres     | 6915 MB  |
  public | bigtable_grande2 | table | postgres     | 10200 MB |
  public | bigtable_petite  | table | postgres     | 173 MB   |

Si je relance un SELECT sur 100 lignes de la petite partition centrale, PostgreSQL ignore totalement les deux grosses partitions, et ne lance que 3 workers supplémentaires pour faire le filtrage et la somme (la partition n’est pas assez grande pour en justifier un quatrième). On descend à 529 ms (62 fois mieux qu’auparavant). Notez le temps de planification toujours bien inférieur à la milliseconde.

 EXPLAIN (ANALYZE, VERBOSE, BUFFERS)
 SELECT sum(x)FROM bigtable
 WHERE id BETWEEN 200000000 AND 200000000+100 ;
 Aggregate  (cost=27406.06..27406.07 rows=1 width=8) (actual time=527.984..527.984 rows=1 loops=1)
   Output: sum(bigtable_petite.x)
   Buffers: shared read=5881
   I/O Timings: read=344.078
   ->  Gather  (cost=1000.00..27406.05 rows=1 width=4) (actual time=3.473..527.966 rows=101 loops=1)
         Output: bigtable_petite.x
         Workers Planned: 3
         Workers Launched: 3
         Buffers: shared read=5881
         I/O Timings: read=344.078
         ->  Append  (cost=0.00..26405.95 rows=1 width=4) (actual time=383.565..514.677 rows=25 loops=4)
               Buffers: shared hit=32 read=22092
               I/O Timings: read=1332.761
               Worker 0: actual time=510.606..510.606 rows=0 loops=1
                 Buffers: shared hit=10 read=5752
                 I/O Timings: read=328.896
               Worker 1: actual time=511.069..511.069 rows=0 loops=1
                 Buffers: shared hit=10 read=4978
                 I/O Timings: read=325.920
               Worker 2: actual time=510.807..510.807 rows=0 loops=1
                 Buffers: shared hit=12 read=5481
                 I/O Timings: read=333.869
               ->  Parallel Seq Scan on public.bigtable_petite  (cost=0.00..26405.95 rows=1 width=4) (actual time=383.564..514.674 rows=25 loops=4)
                     Output: bigtable_petite.x
                     Filter: 
                     Rows Removed by Filter: 1249975
                     Buffers: shared hit=32 read=22092
                     I/O Timings: read=1332.761
                     Worker 0: actual time=510.606..510.606 rows=0 loops=1
                       Buffers: shared hit=10 read=5752
                       I/O Timings: read=328.896
                     Worker 1: actual time=511.069..511.069 rows=0 loops=1
                       Buffers: shared hit=10 read=4978
                       I/O Timings: read=325.920
                     Worker 2: actual time=510.806..510.806 rows=0 loops=1
                       Buffers: shared hit=12 read=5481
                       I/O Timings: read=333.869
 Planning time: 0.728 ms
 Execution time: 529.829 ms

Une petite partie des blocs était dans les shared buffers (j’avais pourtant pris des données « au milieu » pour éviter cet effet). Répétée souvent, la requête finit par n’utiliser que des blocs en cache et peut descendre en-dessous de 200 ms.

Mais on ne fait pas des partitions pour des requêtes de 100 lignes, il y a les index pour ça. Si nous voulons l’intégralité de la partition centrale :

 EXPLAIN (ANALYZE, VERBOSE, BUFFERS)
 SELECT sum(x)FROM bigtable
 WHERE id BETWEEN 200000000 AND 205000000 ;

Le résultat est à peine plus lent (607 ms), même sans lire uniquement dans le cache de PostgreSQL (mais le cache de l’OS a pu jouer). Remarquez que le plan est subtilement différent et rajoute un nœud Partial Aggregate entre le Append et le Gather car le Append ramène plus de ligne que ci-dessus ; et on a un nœud Finalize Aggregate tout en haut (détails : https://docs.postgresql.fr/10/paral…) :

 Finalize Aggregate  (cost=31437.72..31437.73 rows=1 width=8) (actual time=605.167..605.167 rows=1 loops=1)
   Output: sum(bigtable_petite.x)
   Buffers: shared hit=66 read=5522
   I/O Timings: read=13.272
   ->  Gather  (cost=31437.41..31437.72 rows=3 width=8) (actual time=605.158..605.164 rows=4 loops=1)
       Output: (PARTIAL sum(bigtable_petite.x))
       Workers Planned: 3
       Workers Launched: 3
       Buffers: shared hit=66 read=5522
       I/O Timings: read=13.272
       ->  Partial Aggregate  (cost=30437.41..30437.42 rows=1 width=8) (actual time=600.835..600.835 rows=1 loops=4)
             Output: PARTIAL sum(bigtable_petite.x)
             Buffers: shared hit=288 read=21836
             I/O Timings: read=53.247
             Worker 0: actual time=599.521..599.521 rows=1 loops=1
               Buffers: shared hit=77 read=5420
               I/O Timings: read=12.596
             Worker 1: actual time=598.198..598.198 rows=1 loops=1
               Buffers: shared hit=72 read=5533
               I/O Timings: read=14.573
             Worker 2: actual time=600.653..600.653 rows=1 loops=1
               Buffers: shared hit=73 read=5361
               I/O Timings: read=12.805
             ->  Append  (cost=0.00..26405.95 rows=1612581 width=4) (actual time=0.021..434.163 rows=1250000 loops=4)
                   Buffers: shared hit=288 read=21836
                   I/O Timings: read=53.247
                   Worker 0: actual time=0.021..432.354 rows=1242322 loops=1
                     Buffers: shared hit=77 read=5420
                     I/O Timings: read=12.596
                   Worker 1: actual time=0.026..429.925 rows=1266730 loops=1
                     Buffers: shared hit=72 read=5533
                     I/O Timings: read=14.573
                   Worker 2: actual time=0.019..437.050 rows=1228061 loops=1
                     Buffers: shared hit=73 read=5361
                     I/O Timings: read=12.805
                   ->  Parallel Seq Scan on public.bigtable_petite  (cost=0.00..26405.95 rows=1612581 width=4) (actual time=0.020..300.729 rows=1250000 loops=4)
                         Output: bigtable_petite.x
                         Filter: 
                         Buffers: shared hit=288 read=21836
                         I/O Timings: read=53.247
                         Worker 0: actual time=0.021..298.749 rows=1242322 loops=1
                           Buffers: shared hit=77 read=5420
                           I/O Timings: read=12.596
                         Worker 1: actual time=0.025..298.769 rows=1266730 loops=1
                           Buffers: shared hit=72 read=5533
                           I/O Timings: read=14.573
                         Worker 2: actual time=0.019..302.519 rows=1228061 loops=1
                           Buffers: shared hit=73 read=5361
                           I/O Timings: read=12.805
 Planning time: 0.116 ms
 Execution time: 607.095 ms

Noter que le temps de planification reste en-dessous de la milliseconde dans toutes ces requêtes.

100 partitions

C’est plus réaliste, mais cette fois il va falloir un peu scripter pour créer les partitions (non, la création automatique de partitions n’est pas (encore ?) prévue) ; et merci au passage à Corey Huinker pour \gexec :

 SELECT 'CREATE TABLE bigtable_'||p||'
 PARTITION OF bigtable
 FOR VALUES FROM ('||p*5000000+1||') TO ('||(p+1)*5000000+1||') ;'
 FROM generate_series (0,99) p
 \gexec

Le reste de la création est identique, y compris le temps d’insertion similaire. Au final on obtient 100 partitions d’1 million de lignes et 173 Mo :

  Schéma |      Nom       | Type  | Propriétaire | Taille  | Description
  public | bigtable       | table | postgres     | 0 bytes |
  public | bigtable_0     | table | postgres     | 173 MB  |
  public | bigtable_1     | table | postgres     | 173 MB  |
  public | bigtable_10    | table | postgres     | 173 MB  |
  ...
  public | bigtable_98    | table | postgres     | 173 MB  |
  public | bigtable_99    | table | postgres     | 173 MB  |

On pourrait se dire que le SELECT de 100 lignes va ramener la même chose que ci-dessus puisqu’il porte sur une partition de même taille :

 EXPLAIN (ANALYZE, VERBOSE, BUFFERS)
 SELECT sum(x)FROM bigtable
 WHERE id BETWEEN 199000001 AND 199000100 ;

C’est effectivement à peu près le cas, aux erreurs de mesure près :

 Finalize Aggregate  (cost=27406.34..27406.35 rows=1 width=8) (actual time=379.974..379.974 rows=1 loops=1)
   Output: sum(bigtable_39.x)
   Buffers: shared read=6097
   I/O Timings: read=240.967
   ->  Gather  (cost=27406.02..27406.33 rows=3 width=8) (actual time=379.890..379.968 rows=4 loops=1)
         Output: (PARTIAL sum(bigtable_39.x))
         Workers Planned: 3
         Workers Launched: 3
         Buffers: shared read=6097
         I/O Timings: read=240.967
         ->  Partial Aggregate  (cost=26406.02..26406.03 rows=1 width=8) (actual time=353.810..353.810 rows=1 loops=4)
               Output: PARTIAL sum(bigtable_39.x)
               Buffers: shared read=22124
               I/O Timings: read=998.870
               Worker 0: actual time=345.610..345.610 rows=1 loops=1
                 Buffers: shared read=4596
                 I/O Timings: read=252.704
               Worker 1: actual time=345.636..345.636 rows=1 loops=1
                 Buffers: shared read=7394
                 I/O Timings: read=238.834
               Worker 2: actual time=345.647..345.647 rows=1 loops=1
                 Buffers: shared read=4037
                 I/O Timings: read=266.366
               ->  Append  (cost=0.00..26405.95 rows=28 width=4) (actual time=336.382..353.804 rows=25 loops=4)
                     Buffers: shared read=22124
                     I/O Timings: read=998.870
                     Worker 0: actual time=345.608..345.608 rows=0 loops=1
                       Buffers: shared read=4596
                       I/O Timings: read=252.704
                     Worker 1: actual time=345.634..345.634 rows=0 loops=1
                       Buffers: shared read=7394
                       I/O Timings: read=238.834
                     Worker 2: actual time=345.646..345.646 rows=0 loops=1
                       Buffers: shared read=4037
                       I/O Timings: read=266.366
                     ->  Parallel Seq Scan on public.bigtable_39  (cost=0.00..26405.95 rows=28 width=4) (actual time=336.381..353.801 rows=25 loops=4)
                           Output: bigtable_39.x
                           Filter: 
                           Rows Removed by Filter: 1249975
                           Buffers: shared read=22124
                           I/O Timings: read=998.870
                           Worker 0: actual time=345.607..345.607 rows=0 loops=1
                             Buffers: shared read=4596
                             I/O Timings: read=252.704
                           Worker 1: actual time=345.634..345.634 rows=0 loops=1
                             Buffers: shared read=7394
                             I/O Timings: read=238.834
                           Worker 2: actual time=345.646..345.646 rows=0 loops=1
                             Buffers: shared read=4037
                             I/O Timings: read=266.366
 Planning time: 10.324 ms
 Execution time: 382.220 ms

Notez que le temps de planification a bondi d’un facteur 10. C’est reproductible. En fait, ça l’est même si les partitions sont vides :

 Aggregate  (cost=34.93..34.94 rows=1 width=8) (actual time=0.011..0.012 rows=1 loops=1)
   Output: sum(bigtable_39.x)
   ->  Append  (cost=0.00..34.90 rows=11 width=4) (actual time=0.008..0.008 rows=0 loops=1)
         ->  Seq Scan on public.bigtable_39  (cost=0.00..34.90 rows=11 width=4) (actual time=0.007..0.007 rows=0 loops=1)
               Output: bigtable_39.x
               Filter: 
 Planning time: 12.979 ms
 Execution time: 0.095 ms

1000 partitions

On tente avec 1000 partitions de 17 Mo. Après tout, je peux avoir besoin de beaucoup moins de lignes que les 5 millions de l’exemple précédent.

 SELECT 'CREATE TABLE bigtable_'||p||'
 PARTITION OF bigtable
 FOR VALUES FROM ('||p*500000+1||') TO ('||(p+1)*500000+1||') ;'
 FROM generate_series (0,999) p
 \gexec
  Schéma |      Nom       | Type  | Propriétaire | Taille  | Description
  public | bigtable       | table | postgres     | 0 bytes |
  public | bigtable_0     | table | postgres     | 17 MB   |
  public | bigtable_1     | table | postgres     | 17 MB   |
  ...
  public | bigtable_998   | table | postgres     | 17 MB   |
  public | bigtable_999   | table | postgres     | 17 MB   |

Le plan pour 100 lignes se simplifie : un seul worker suffit pour parcourir une petite partition, le temps d’exécution est descendu à 52 ms.

 Finalize Aggregate  (cost=5633.22..5633.23 rows=1 width=8) (actual time=49.966..49.966 rows=1 loops=1)
   Output: sum(bigtable_398.x)
   Buffers: shared read=1444
   I/O Timings: read=21.991
   ->  Gather  (cost=5633.10..5633.21 rows=1 width=8) (actual time=49.959..49.962 rows=2 loops=1)
         Output: (PARTIAL sum(bigtable_398.x))
         Workers Planned: 1
         Workers Launched: 1
         Buffers: shared read=1444
         I/O Timings: read=21.991
         ->  Partial Aggregate  (cost=4633.10..4633.11 rows=1 width=8) (actual time=38.506..38.506 rows=1 loops=2)
               Output: PARTIAL sum(bigtable_398.x)
               Buffers: shared read=2213
               I/O Timings: read=38.675
               Worker 0: actual time=30.326..30.326 rows=1 loops=1
                 Buffers: shared read=769
                 I/O Timings: read=16.685
               ->  Append  (cost=0.00..4633.06 rows=16 width=4) (actual time=15.263..38.495 rows=50 loops=2)
                     Buffers: shared read=2213
                     I/O Timings: read=38.675
                     Worker 0: actual time=30.323..30.323 rows=0 loops=1
                       Buffers: shared read=769
                       I/O Timings: read=16.685
                     ->  Parallel Seq Scan on public.bigtable_398  (cost=0.00..4633.06 rows=16 width=4) (actual time=15.262..38.489 rows=50 loops=2)
                           Output: bigtable_398.x
                           Filter: 
                           Rows Removed by Filter: 249950
                           Buffers: shared read=2213
                           I/O Timings: read=38.675
                           Worker 0: actual time=30.322..30.322 rows=0 loops=1
                             Buffers: shared read=769
                             I/O Timings: read=16.685
 Planning time: 32.769 ms
 Execution time: 52.248 ms

On pourrait se dire « les micro-partitions c’est chouette, abusons-en » !

Mais le temps de planification a encore augmenté (et il est similaire sur une table vide). En fait il atteint à présent le même ordre de grandeur que l’exécution elle-même.

10 000 partitions

Pourquoi s’arrêter en si bon chemin ? Créons 10 000 partitions de 1,8 Mo et découvrons un petit phénomène pénible entre la création de la première et celle de la dernière :

 CREATE TABLE
 Temps : 3,512 ms
 ...
 CREATE TABLE
 Temps : 57,067 ms

Le catalogue de PostgreSQL n’est manifestement pas taillé pour manipuler des dizaines de milliers de tables, ce qui est un peu gênant. Certes, on ne perd qu’une poignée de minutes dans un flux d’alimentation normalement beaucoup plus lourd.

L’insertion des 500 millions de lignes n’est pas plus longue que d’habitude, le VACUUM ANALYZE a un temps similaire.

Si je veux récupérer 100 lignes (sur 2 partitions dans cet exemple) :

  Aggregate  (cost=1544.87..1544.88 rows=1 width=8) (actual time=10.389..10.389 rows=1 loops=1)
    Output: sum(bigtable_3999.x)
    Buffers: shared hit=222 read=222
    I/O Timings: read=4.094
    ->  Append  (cost=0.00..1544.40 rows=188 width=4) (actual time=7.094..10.369 rows=200 loops=1)
        Buffers: shared hit=222 read=222
        I/O Timings: read=4.094
        ->  Seq Scan on public.bigtable_3999  (cost=0.00..772.20 rows=43 width=4) (actual time=7.093..7.099 rows=50 loops=1)
              Output: bigtable_3999.x
              Filter: 
              Rows Removed by Filter: 49950
              Buffers: shared read=222
              I/O Timings: read=4.094
        ->  Seq Scan on public.bigtable_4000  (cost=0.00..772.20 rows=145 width=4) (actual time=0.015..3.254 rows=150 loops=1)
              Output: bigtable_4000.x
              Filter: 
              Rows Removed by Filter: 49850
              Buffers: shared hit=222
Planning time: 164.257 ms
Execution time: 10.883 ms

Quelques millisecondes pour parcourir une table ridicule… et 16 fois plus à planifier le bon plan d’exécution ! Je n’ai pas cherché à creuser un éventuel effet de cache sur le catalogue. Et, là encore, la planification avec 10 000 partitions vides est aussi longue.

J’ai testé la récupération du contenu de 10 partitions sur 10 000, vides ou pleines : le temps de planification est similaire.

Exclusion de contraintes

Et si vous voulez tenter de simplifier la tâche à l’optimiseur en lui disant de ne pas chercher à optimiser en fonction des contraintes des tables (SET constraint_exclusion TO off ; au lieu du défaut partition), vous augmenterez encore ce temps de planification car il devra considérer toutes les partitions, et les parcourra toutes, pour un temps global catastrophique :

  Planning time: 235.568 ms
  Execution time: 53080.031 ms

Jointure

Quelque dixièmes de secondes de planification, ce n’est pas énorme comparé au temps d’exécution d’une requête traditionnelle classique, pourrait-on se dire. C’est tout de même énorme par rapport aux partitionnements plus légers vus précédemment. C’est aussi oublier que les tables sont rarement prises isolément. Dans le cas le pire, les tables jointes sont également partitionnées. En testant une autojointure sur la même table :

 EXPLAIN (ANALYZE, VERBOSE, BUFFERS)
 SELECT sum(b.x) FROM bigtable a INNER JOIN bigtable b ON (a.id=b.id)
 WHERE a.id BETWEEN 300000001 AND 300000001+100 ;

Je vous épargne le plan, qui, faute d’index, consiste à faire un hash join entre la partition bigtable_6000 (a) et les 10 000 partitions (b). Le temps d’exécution de 100 s n’est pas étonnant. Mais il y a aussi ça :

  Planning time: 7031.163 ms

…qui est un peu inquiétant.

BRIN

La requête précédente bénéficierait bien d’un index. Je ne veux pas sacrifier encore plusieurs Go : un index BRIN (une nouveauté 9.5), bien compact, fera donc l’affaire. Nos données sont ici triées, c’est souvent le cas dans des tables de fait, mais ce n’est pas le plus important dans notre cas.

Malheureusement PostgreSQL 10 ne gère pas les index couvrant plusieurs partitions, il faudra donc générer 10 000 index à la main :

 SELECT 'CREATE INDEX '||relname||'_brin ON '
 ||relname ||' USING brin(id);' 
 FROM pg_class where relname like 'bigtable_%' \gexec

…et cela fera donc 10 000 objets de plus à gérer.

En pratique la simple somme sur 100 lignes est bien accélérée, mais cela n’a au final aucun intérêt puisqu’on passe 100 fois plus de temps à planifier la requête qu’à l’exécuter complètement :

  Aggregate  (cost=397.79..397.80 rows=1 width=8) (actual time=3.287..3.287 rows=1 loops=1)
    ->  Append  (cost=0.36..397.56 rows=93 width=4) (actual time=0.108..3.277 rows=101 loops=1)
        ->  Bitmap Heap Scan on bigtable_8000  (cost=0.36..397.56 rows=93 width=4) (actual time=0.108..3.271 rows=101 loops=1)
              Recheck Cond: 
              Rows Removed by Index Recheck: 28827
              Heap Blocks: lossy=128
              ->  Bitmap Index Scan on bigtable_8000_brin  (cost=0.00..0.33 rows=25000 width=0) (actual time=0.094..0.094 rows=1280 loops=1)
                    Index Cond: 
  Planning time: 591.540 ms
  Execution time: 3.948 ms

Et en v11 (bêta 2)?

Ce qui précède a été mené avec une 10.3. J’ai eu envie de voir ce que donnait la version en développement, à paraître à l’automne.

La v11 (bêta 2) m’a craché quelques insultes, que je n’avais pas en v10, vite disparues après augmentation du paramètre indiqué :

 2018-04-22 16:45:07.721 CEST 2801 ERROR:  out of shared memory
 2018-04-22 16:45:07.721 CEST 2801 HINT:  You might need to increase max_locks_per_transaction.

Le partition pruning, une nouveauté, remplace en pratique la classique exclusion par contrainte.

Pour la recherche simple sur 100 lignes dans 10 000 partitions (sans index) :

 Planning Time: 672.276 ms
 Execution Time: 3.969 ms

Pour la requête avec autojointure (sans index) :

  Planning Time: 25156.639 ms
  Execution Time: 96427.987 ms

Bref, du délire.

Et en v11 (celle qui sortira) ?

Cette explosion des temps de planification pour un grand nombre de partitions a été reproduit et remonté par mon collègue Thomas sur hackers. Je n’ai pas cherché à comprendre le détail des discussions. Une nouvelle fonction n’était pas optimisée parce que cela ne semblait pas important à l’époque. L’important est que le problème a été reconnu, et en bonne partie corrigé il y a quelques jours.

D’après les différents essais peu scientifiques que j’ai faits, les ralentissements restent sensibles pour les très petits temps de planification (c’est sans grande importance), ou ceux déjà énormes (> 1 s). À revoir de manière plus rigoureuse avec des jeux de test plus réalistes et la version finale.

Conclusion

Dixit la doc : Partitioning using these techniques will work well with up to perhaps a hundred partitions; don’t try to use many thousands of partitions.

Cela reste donc valable à cause de ce temps de planification délirant au-delà de 1 000 partitions. Cela dit, nombre de requêtes décisionnelles de plusieurs minutes se contrefichent de quelques secondes de planification. Il y a donc peut-être quelques cas où on acceptera ce prix pour réduire la taille des partitions. À tester soigneusement.

dimanche 13 mai 2018

VACUUM et agressivité

Le modèle MVCC de PostgreSQL a un avantage : une session ne s’embête pas à supprimer physiquement les données qu’elle a modifiées, et gagne ainsi du temps. De plus, ces lignes pourraient encore être utiles à une autre transaction. L’inconvénient, c’est qu’il va bien falloir s’en occuper plus tard — en théorie à un moment moins chargé.

Il faut donc effectuer régulièrement un VACUUM sur les tables modifiées pour éviter le bloat, la boursouflure, le gonflement, l’embonpoint né de ces lignes mortes qu’aucune transaction ne peut plus voir. Dans les versions antiques de PostgreSQL, le VACUUM devait être passé manuellement ; à présent il est déclenché en arrière-plan par le démon autovacuum.

La configuration par défaut suffit en général, mais quand il faut y toucher, les nombreux paramètres ne rendent pas la chose très lisible. Il y a bien sûr la doc que l’on peut déjà trouver en ligne, l’officielle ou chez Dalibo, ou encore ce billet chez 2nd Quadrant. Mais rien ne vaut un bon test.

Je laisse de côté VACUUM FREEZE, VACUUM ANALYZE, VACUUM FULL pour me limiter ici au VACUUM basique, purgeur de lignes mortes.

VACUUM manuel

Créons une table et passons dessus le VACUUM :

 DROP TABLE IF EXISTS vc ;
 CREATE TABLE vc (i int) ;
 INSERT INTO vc SELECT i FROM generate_series (1,10000000) i ;
 VACUUM VERBOSE vc ;

(Au passage il peut sembler idiot de vacuumer une table fraîchement créée. Cependant à cette occasion PostgreSQL crée la visibility map : elle permet de savoir si une ligne trouvée dans un index est dans un bloc assez récemment modifié pour qu’il soit besoin d’aller vérifier dans la table si la ligne est visible par la transaction en cours. De plus cela va accélérer les VACUUM suivants. Finissez donc vos chargements massifs par VACUUM ANALYZE et pas juste ANALYZE.)

Sur mon portable avec SSD le VACUUM avec option VERBOSE renvoie ceci :

 INFO:  vacuuming "public.vc"
 INFO:  "vc": found 0 removable, 10000000 nonremovable row versions in 44248 out of 44248 pages
 DÉTAIL : 0 dead row versions cannot be removed yet, oldest xmin: 8570578
 There were 0 unused item pointers.
 Skipped 0 pages due to buffer pins, 0 frozen pages.
 0 pages are entirely empty.
 CPU: user: 4.16 s, system: 0.04 s, elapsed: 4.27 s.
 VACUUM
 Durée : 5296,537 ms (00:05,297)

Aucune ligne nettoyée (normal). 44 248 pages (362 Mo) en 5,3 s nous donnent 68 Mo/s.

Si on repasse le VACUUM dessus, il ne traite plus qu’un bloc en quelques millisecondes car la visibility map a déjà été créée et lui permet de savoir qu’il n’y a pas besoin de repasser nulle part.

Paramètres

Le VACUUM incrémente un compteur avec les valeurs suivantes pour chaque bloc selon qu’il est en mémoire cache de PostgreSQL, qu’il n’y est pas et/ou qu’il est modifié :

 vacuum_cost_page_hit = 1
 vacuum_cost_page_miss = 10
 vacuum_cost_page_dirty =20

jusqu’à atteindre ce seuil :

 vacuum_cost_limit = 200

et alors le VACUUM fait une pause de :

 vacuum_cost_delay = 0 ms

C’est-à-dire que par défaut le VACUUM va le plus vite possible et tant pis pour les autres sessions. C’est surtout au niveau des accès disques qu’un VACUUM manuel peut être gênant. Mais pour paraphraser mes collègues : quand on lance un VACUUM explicite, c’est en général la nuit (planification régulière ou au milieu d’un batch) ou c’est une urgence.

Autovacuum

L’autovacuum cherche à être discret et va étaler son activité.

Il attend qu’un certain seuil de modification soit atteint avant de lancer un VACUUM (paramètres autovacuum_vacuum_scale_factor, autovacuum_vacuum_threshold) ou un ANALYZE (autovacuum_analyze_threshold, autovacuum_analyze_scale_factor) sur une table. On touche souvent ces paramètres pour qu’il s’intéresse plus souvent aux grosses tables.

De plus, quand l’autovacuum lance un « worker » sur une table, il suit ces paramètres :

 autovacuum_vacuum_cost_limit = -1  (donc 200, valeur de vacuum_cost_limit)
 autovacuum_vacuum_cost_delay = 20 ms

en plus des vacuum_cost_page_hit, vacuum_cost_page_miss, vacuum_cost_page_dirty déjà évoqués.

Donc, dans le cas le meilleur, en travaillant dans le cache (vacuum_cost_page_hit à 1), le worker atteint la limite avec 200 blocs de 8 ko (1,6 Mo), puis attend 20 ms avant de traiter la suite. Mais fréquemment les blocs seront à écrire (pénalité : vacuum_cost_page_dirty = 20), soit 10 blocs (82 ko) au maximum avant la pause, donc un temps total pour ma table d’exemple de 44 248 blocs / 10 blocs * 20 ms = 88 s, ou encore un débit de 10 blocs /20 ms = 4 Mo/s… au mieux.

Simulation

Pour tester, on va lancer un VACUUM mais auparavant on pose dans la session la valeur par défaut de l’autovacuum :

 SET vacuum_cost_delay = '20 ms';

Pour ne pas perturber le test, j’ajoute la mention que l’autovacuum ne doit pas s’occuper de la table (il peut être assez rapide à intervenir pour fausser le test) :

 ALTER TABLE vc SET ( autovacuum_enabled = off ) ;

Je rajoute un DELETE presque complet pour forcer le VACUUM à réécrire tous les blocs :

 DELETE FROM vc WHERE i<10000000 ;

Un VACUUM VERBOSE vc renvoie ensuite :

 INFO:  vacuuming "public.vc"
 INFO:  "vc": removed 9999999 row versions in 44248 pages
 INFO:  "vc": found 9999999 removable, 1 nonremovable row versions in 44248 out of 44248 pages
 DÉTAIL : 0 dead row versions cannot be removed yet, oldest xmin: 8570610
 There were 0 unused item pointers.
 Skipped 0 pages due to buffer pins, 0 frozen pages.
 0 pages are entirely empty.
 CPU: user: 4.52 s, system: 0.06 s, elapsed: 85.65 s.
 VACUUM
 Durée : 85681,197 ms (01:25,681)

On est un rien en-dessous des 88 secondes calculées. En toute rigueur, le test peut souvent donner des valeurs inférieures. Le mécanisme n’est sans doute pas aussi primaire que décrit ci-dessus. Mais on est bien deux ordres de grandeur au-dessus de la seconde que dure un VACUUM non freiné par vacuum_cost_delay.

Un autre moyen de vérifier ce début est iotop (ici sur une bien plus grosse table) : on s’approche des 4 Mo/s calculés.

 TID    PRIO  USER     DISK READ  DISK WRITE  SWAPIN      IO    COMMAND
 23336 be/4 postgres    2.33 M/s    3.94 M/s           0.00 %       0.59 % postgres: 10/maousse: postgres vac 192.168.74.150(46240) VACUUM

Nombre de workers

Dans le cas d’une énorme table, le VACUUM (lancé par l’autovacuum) peut être très long : par exemple il a eu besoin de 8 h pour une table de 100 Go (2 milliards de lignes) sur mon autre PC doté d’un disque classique.

On pourrait être tenté d’augmenter autovacuum_max_workers, par défaut à 3. Cela n’a pas d’intérêt ici : une table n’est en pratique traitée que par un seul et unique worker à la fois. La doc mériterait d’être plus explicite sur le sujet.

Là encore, est-ce un problème si le VACUUM est lent ? Le bloat augmente-t-il indéfiniment parce que les écritures sont intensives ? Si oui, on peut soit le rendre plus agressif en jouant sur vacuum_cost_delay ou vacuum_cost_limit, soit le lancer manuellement (à priori avec une agressivité maximale) à des périodes de moindre activité.

Par contre, dans le cas d’un schéma avec de nombreuses tables toutes fréquemment mises à jour, ou de tables avec des partitions toutes mises à jour, augmenter le nombre de workers est envisageable, et là effectivement on parallélise… mais le débit global reste constant ! Heureusement, sinon un grand nombre de workers entraînerait la saturation des accès disques lors de mises à jour réparties.

mercredi 14 février 2018

CREATE STATISTICS dans PostgreSQL 10

(Refonte d’un billet d’août dont je n’étais pas content.)

Parmi toutes la pléthore de nouveautés de PostgreSQL 10, une un peu planquée est liée à la prise en compte de la corrélation des données par l’optimiseur.

En général, si les statistiques sont bien à jour, PostgreSQL estime correctement la volumétrie ramenée par un critère de filtrage portant sur un index, et s’en sert pour choisir le bon plan d’exécution. Par exemple : si un critère porte sur une colonne, il utilisera un index sur cette colonne pour chercher une poignée de lignes, et se reportera à la table ensuite. Mais si le résultat attendu implique une très grande partie de la table, PostgreSQL oubliera l’index et lira la table entièrement quitte à jeter les lignes ne vérifiant pas le critère.

La limite entre les deux comportements est fonction d’une multitude de critères comme les volumétries impliquées, le coût relatif d’un accès direct à un bloc (paramètre random_page_cost) par rapport à un bloc au sein d’un parcours complet (seq_page_cost), mais encore le parallélisme, l’estimation de l’utilisation du cache de l’OS, etc.

Ça se corse quand on veut mélanger deux critères de filtrage différents. S’ils sont totalement indépendants tout va bien : PostgreSQL suppose que deux critères ramenant chacun 10% de la table en ramèneront une fois combinés 10 % * 10 % = 1 %.

Cependant, dans le monde réel, les corrélations entre données plus ou moins directes abondent : nombre de données de gestion dépendent de l’heure (pas d’activité la nuit) ; des clients ne commandent pas des produits en ligne au hasard mais selon leur âge, leur situation financière, leur localisation ; ou les colonnes représentent une hiérarchie plus ou moins explicite (géographie…). En fait, les distributions uniformes des exemples des documentations sont plus l’exception que la règle.

J’ai donc tenté d’inventer un exemple grossièrement réaliste… et il n’est pas si évident d’en trouver un où le nouveau CREATE STATISTICS mène à un changement réel de plan d’exécution.

Exemple

Prenons une table de personnes de tous âges mais où les seniors seront massivement représentés. On crée des tranches d’âges et des statuts (« Retraités », « Jeunes », « Actifs »), tout cela étant évidemment fortement redondant. Rajoutons encore un flag « propriétaire » digne d’une gérontocratie où les retraités possèdent presque tout :

 -- paramétrage de l'optimiseur : sans parallélisme pour simplifier
 SET max_parallel_workers_per_gather TO 0 ;
 -- disques durs classiques, donc coûteux pour les accès aléatoires
 SET seq_page_cost TO 4 ;
 SET random_page_cost TO 1 ;
 -- paramétrage mémoire figé
 SET work_mem TO '16MB';
 SET effective_cache_size to '512MB';  -- shared_buffers+cache de l'OS
 CREATE TABLE personnes (
   id serial,
   age INT NOT NULL,
   tranche_age TEXT NOT NULL,
   statut TEXT NOT NULL,
   proprietaire BOOLEAN NOT NULL
 );
 INSERT INTO personnes (age, tranche_age, statut, proprietaire)
 SELECT * FROM (
   SELECT mod(i,18) as age, '0-17','Jeune', false
   from generate_series(1,1000000) i
   UNION ALL
   SELECT 18+mod(i,42) , '18-59','Actif', mod(i,10)=0
   from generate_series(1,1000000) i
   UNION ALL
   SELECT 60+mod(i,40), '60+','Retraité', mod(i,10)!=0
   from generate_series(1,7000000) i
 ) z
 ORDER BY random() ;  -- on mélange bien les lignes

Et plein d’index pour les critères de recherche :

 CREATE INDEX ix_statut ON personnes (statut);
 CREATE INDEX ix_tr_age ON personnes (tranche_age);
 CREATE INDEX ix_st_tr_age ON personnes (statut,tranche_age);
 CREATE INDEX ix_st_tr_age_prop ON personnes (statut,tranche_age, proprietaire);
 
 VACUUM ANALYZE personnes ;

On obtient donc une table d’environ 448 Mo (et le double en index…) comprenant 9 millions de personnes, où la tranche d’âge « Retraité » est surreprésentée (78 %) constituée de 60 ans et plus, presque tous propriétaires, et vice-versa :

 SELECT statut, tranche_age, min(age), max(age), proprietaire, COUNT(*), ROUND(100 * (COUNT(*) / SUM(COUNT(*)) OVER () ),1) AS "%"
 FROM personnes
 GROUP BY statut, tranche_age, proprietaire
 ORDER BY COALESCE (statut,'aa') DESC, MIN(age);
 
   statut  │ tranche_age │ min │ max │ proprietaire │  count  │  %   
 ──────────┼─────────────┼─────┼─────┼──────────────┼─────────┼──────
  Retraité │ 60+         │  60 │  90 │ f            │  700000 │  7.8
  Retraité │ 60+         │  61 │  99 │ t            │ 6300000 │ 70.0
  Jeune    │ 0-17        │   0 │  17 │ f            │ 1000000 │ 11.1
  Actif    │ 18-59       │  18 │  59 │ f            │  900000 │ 10.0
  Actif    │ 18-59       │  18 │  58 │ t            │  100000 │  1.1

On peut aller voir les statistiques de l’optimiseur (SELECT * FROM pg_stats WHERE tablename = 'personnes'; ) mais on y constatera juste que les données dans les colonnes ne sont pas corrélées à leur emplacement physique, et que la proportion de retraités/plus de 60 ans s’y retrouve (les chiffres exacts dépendent de l’échantillonnage) :

 \x auto
 SELECT * FROM  pg_stats
 WHERE tablename = 'personnes';
 ...
 tablename              | personnes
 attname                | tranche_age
 ...
 n_distinct             | 3
 most_common_vals       | {60+,0-17,18-59}
 most_common_freqs      | {0.7751,0.112733,0.112167}
 ...
 correlation            | 0.630976
 ...
 tablename              | personnes
 attname                | statut
 ...
 null_frac              | 0
 ...
 n_distinct             | 4
 most_common_vals       | {Retraité,Actif,Jeune}
 most_common_freqs      | {0.7751,0.112733,0.112167}
 ...
 correlation            | 0.631054
 tablename              │ personnes
 attname                │ proprietaire
 n_distinct             │ 2
 most_common_vals       │ {t,f}
 most_common_freqs      │ {0.709433,0.290567}
 ...
 correlation            │ 0.594379

On peut vérifier qu’une requête sur statut = 'Retraité' ou tranche_age = '60+' mène à un parcours complet (Seq Scan). Dans les deux cas le nombre de lignes estimées et le nombre de lignes effectivement ramenées (les deux valeurs de rows) sont à peu près identiques (7 millions). Malgré l’index sur le statut, PostgreSQL préfère donc lire les 9 millions de ligne et en jeter 2 millions :

 EXPLAIN (ANALYZE)
 SELECT count(id), min(age), max(age)
 FROM personnes
 WHERE statut = 'Retraité' ;
  Aggregate  (cost=394265.26..394265.27 rows=1 width=16)
              (actual time=2086.261..2086.261 rows=1 loops=1)
    ->  Seq Scan on personnes  (cost=0.00..341799.84 rows=6995390 width=8)
             (actual time=0.030..1270.082 rows=7000000 loops=1)
          Filter: (statut = 'Retraité'::text)
          Rows Removed by Filter: 2000000
  Planning time: 3.341 ms
  Execution time: 2086.337 ms

On aura le même comportement pour les « 60+ » ou les propriétaires. Le temps exact dépendra notamment des disques et des données dans les divers caches

Le problème

Les besoins fonctionnels sont souvent plus fins (et la réalité moins tranchée que ma requête d’insertion). On voudra par exemple préciser que l’on veut les retraités, de plus de 60 ans, et propriétaires. Mais là l’estimation de l’optimiseur est erronée :

 EXPLAIN (ANALYZE)
 SELECT count(id), min(age),max(age)
 FROM personnes
 WHERE tranche_age = '60+' AND statut = 'Retraité' and proprietaire ;
 
 Aggregate  (cost=380991.88..380991.89 rows=1 width=16)
           (actual time=2635.944..2635.944 rows=1 loops=1)
    ->  Bitmap Heap Scan on personnes
              (cost=64403.09..351895.62 rows=3879502 width=8)
              (actual time=834.780..1984.364 rows=6300000 loops=1)
          Recheck Cond: ( (statut = 'Retraité'::text) AND (tranche_age = '60+'::text) )
          Filter: proprietaire
          Heap Blocks: exact=57325
          ->  Bitmap Index Scan on ix_st_tr_age_prop
                         (cost=0.00..63433.21 rows=3879502 width=0)
                         (actual time=823.539..823.539 rows=6300000 loops=1)
                Index Cond: 
 Planning time: 0.290 ms
 Execution time: 2636.024 ms

L’optimiseur pense qu’il y a 3 879 502 lignes (9 millions * 0,7751 * 0,7751 * 0,7094 d’après les statistiques) alors qu’il en trouve en fait 6 300 000 : presque un facteur deux. D’où le choix d’un plan qui consiste à parcourir une bonne partie de l’index pour repérer les blocs intéressants (Bitmap Index Scan), puis à aller les lire dans la table même (Bitmap Heap Scan, 57 325 blocs soit… toute la table, lue dans son ordre physique et non dans celui, logique, trouvé dans l’index), et à chercher les lignes intéressantes dedans (clause Recheck qui reprend la clause de filtrage de l’index).

Est-ce une catastrophe ? Pas forcément, si le temps d’exécution est acceptable. Mais pourquoi passer par l’index si l’on lit tous les blocs de la table au final ? (Noter qu’avec des données moins mélangées ce serait peut-être différent).

Objet statistique

L’amélioration de PostgreSQL 10 consiste à créer un objet « statistique », dont le seul but sera de calculer la corrélation entre deux (ou plus) colonnes. L’ordre de création est simple, et je note que la clause FROM suggère que dans le futur on pourrait avoir beaucoup plus qu’une corrélation entre simples colonnes d’une table. Les perspectives sont alléchantes mais pour le moment il ne peut y avoir qu’une table.

Pour le moment on retiendra surtout que cet objet statistique doit être créé explicitement et qu’il est maintenu par les clauses ANALYZE habituelles (en arrière-plan grâce à l’autovacuum, ou explicite aussi pour être certain que les statistiques soient pris en compte dans la suite) :

 CREATE STATISTICS  pers_tr_age_statut_proprio
 ON tranche_age, statut, proprietaire
 FROM personnes ;
 
 ANALYZE personnes ;

Une clause dependencies oriente PostgreSQL vers la recherche de corrélations fonctionnelles entre colonnes. L’alternative (ndistinct) vise plutôt à résoudre les erreurs dans le nombre de valeurs distinctes dans les regroupements (voir la doc pour les détails). On peut utiliser les deux types dans le même objet par fainéantise, comme ici.

L’objet apparaît sous la table dans un \d+ :

 Objets statistiques :
   "public"."pers_tr_age_statut_proprio" (ndistinct, dependencies)
       ON tranche_age, statut, proprietaire
       FROM personnes

Qu’y a-t-il en fait dans cet objet statistique ? On peut voir son contenu dans la nouvelle vue pg_statistic_ext. Les keys mentionnées ci-dessous sont dans l’ordre de la table : tranche_age, statut et proprietaire. Leur corrélation est calculée dans les deux sens :

 SELECT * FROM pg_statistic_ext
 WHERE stxname = 'pers_tr_age_statut_proprio' ;
 -RECORD 1-+--- 
 stxrelid        │ 10033457
 stxname         │ pers_tr_age_statut_proprio
 stxnamespace    │ 2200
 stxowner        │ 10
 stxkeys         │ 3 4 5
 stxkind         │ {d,f}
 stxndistinct    │ {"3, 4": 3, "3, 5": 5, "4, 5": 5, "3, 4, 5": 5}
 stxdependencies │ {"3 => 4": 1.000000, "3 => 5": 0.113533, 
                    "4 => 3": 1.000000, "4 => 5": 0.113533,
                    "3, 4 => 5": 0.113533, "3, 5 => 4": 1.000000, 
                    "4, 5 => 3": 1.000000}

On note qu’il s’agit d’une moyenne sur la colonne. On est encore loin de détecter les corrélations réduites à certaines valeurs des données. Notamment, l’information sur proprietaire (5) n’indique pas grand-chose sur les autres colonnes. Par contre, les combinaisons de colonnes sont gérées.

Résultat

Quel est l’effet sur notre requête ?

 EXPLAIN (ANALYZE,buffers)
 SELECT count(id), min(age),max(age)
 FROM personnes
 WHERE tranche_age = '60+' AND statut = 'Retraité' and proprietaire;
 Aggregate  (cost=403078.89..403078.90 rows=1 width=16)
             (actual time=2196.764..2196.764 rows=1 loops=1)
   Buffers: shared hit=16211 read=41114
    ->  Seq Scan on personnes
               (cost=0.00..364300.02 rows=5170516 width=8)
               (actual time=6.715..1438.755 rows=6300000 loops=1)
          Filter: (proprietaire AND (tranche_age = '60+'::text) AND (statut = 'Retraité'::text))
          Rows Removed by Filter: 2700000
          Buffers: shared hit=16211 read=41114
 Planning time: 2.258 ms
 Execution time: 2197.469 ms

Grâce au nouvel objet statistique, la volumétrie attendue avant l’agrégation a augmenté (5,1 millions au lieu de 3,2 précédemment et 6,3 réels), d’où le changement de stratégie.

Le plan est à présent un parcours complet (Seq Scan) : PostgreSQL calcule que finalement il vaut mieux parcourir directement toute la table pour en lire et filtrer les valeurs directement que d’aller voir dans un index pas si discriminant que ça finalement. Tout l‘overhead de lecture de l’index, de son chargement en cache éventuel, de construction du bitmap index… passe à la trappe.

On pourrait s’étonner que le coût ait monté (403 078 contre 380 991 précédemment) : en fait c’est l’ancien chiffre qui était faux puisque la volumétrie estimée était fausse. En interdisant les parcours complets (SET enable_seqscan TO off ;) pour revenir à l’ancien plan, PostgreSQL recalcule un coût de 431 471.

Quant au temps d’exécution : je passe sur mon portable avec SSD de 2,6 à 2,0 s. Je reproduis des écarts comparables sur une instance avec un disque dur classique, ou sur un Raspberry Pi B. Cependant, vues les volumétries, la présence ou non des données dans le cache a une grande importance.

Sensibilité aux paramètres

Évidemment, PostgreSQL ne change pas de plan à chaque petite évolution du coût : j’ai choisi un endroit proche du « point de bascule » entre deux plans pour mon exemple, et celui-ci est extrêmement sensible aux valeurs de work_mem, seq_page_cost, random_page_cost, effective_cache_size, voire aux statistiques qui varient après chaque ANALYZE, etc.

À titre d’exemple, un changement d’effective_cache_size à 3 Go fait basculer le plan vers un Index Scan. PostgreSQL juge que l’index et la table étant probablement dans le cache, il n’est pas trop coûteux de parcourir l’index et d’aller chercher chacun des blocs concernés dans la table pour y compter les id et voir les age. (Ne pas confondre avec l’Index Only Scan qui consisterait à se satisfaire uniquement de l’index quand toutes les colonnes intéressantes y figurent déjà.)

Moralité

On a obtenu ici un changement de plan en construisant un exemple encore un peu artificiel. Dans la réalité on étudie un plan d’une requête un peu lente et l’on trouve une volumétrie estimée aberrante. Plus le nombre de critères corrélés augmente, plus l’estimation est éloignée de la réalité. L’ajout d’un objet statistique peut corriger la chose, mais il a un coût en mise à jour : comme les index, on n’en ajoutera que si leur utilisation apporte un réel bénéfice. Mais il vaut mieux les utiliser que torturer une requête jusqu’à l’illisibilité ou dénormaliser le modèle.

Noter qu’un objet statistique reste utile même si l’on crée un index sur les deux champs concernés : celui-ci ne connaît ni ne contient la corrélation entre les champs qu’il indexe !

Les statistiques ne sont utiles que pour les égalités (ou une clause IN par exemple), pas si des inégalités sont impliquées. Selon la documentation :

« Les dépendances fonctionnelles sont pour le moment uniquement appliquées pour les conditions sur une simple égalité entre une colonne et une valeur constante. Elles ne sont pas utilisées pour améliorer l’estimation sur les conditions d’égalité entre deux colonnes ou la comparaison d’une colonne avec une expression, ni pour les clauses d’intervalle, LIKE ou tout autre type de condition. »

Évidemment, cela suppose déjà que les statistiques sont à jour : vérifiez que l’autovacuum passe assez régulièrement sur la table ; et que l’échantillonnage est suffisant pour les grosses tables.

Bibliographie

Quelques liens vers la doc officielle pour finir :

jeudi 26 janvier 2017

Réplication avec PostgreSQL 9.6 : l'exemple minimaliste

Ce qui suit décrit le strict minimum pour mettre en place deux instances en réplication maître/esclave avec PostgreSQL 9.6.

Les chemins par défaut sont ceux sur CentOS 6, PostgreSQL étant installé depuis les dépôts PGDG. Les instances tourneront sur la même machine sur les ports 9601 et 9602.

Création du maître

sudo -iu postgres /usr/pgsql-9.6/bin/initdb -D /var/lib/pgsql/9.6/I1

Modifier les options suivantes dans postgresql.conf dans ce dernier répertoire :

port = 9601
wal_level = replica
max_wal_senders = 5
max_replication_slots = 5

Dans pg_hba.conf, autoriser la réplication à l’utilisateur postgres en connexion locale sans mot de passe (!) :

local   replication     postgres                                trust

Lancement du maître :

/usr/pgsql-9.6/bin/pg_ctl -D /var/lib/pgsql/9.6/I1 start

Création d’un « slot de réplication » qui évitera de mettre en place l’archivage des journaux de transaction :

psql -p 9601
postgres=# select pg_create_physical_replication_slot ('slot_i2') ;

Création de l’esclave

Recopie des fichiers du maître (même si le maître est potentiellement en train de les modifier) :

 /usr/pgsql-9.6/bin/pg_basebackup -D /var/lib/pgsql/9.6/I2/ -X stream -S 'slot_i2'
--checkpoint=fast --verbose --port 9601 --write-recovery-conf

Cette recopie inclut les fichiers de configuration, mais il faut les adapter :

Dans postgresql.conf :

port 9602
hot_standby = on

Un fichier /var/lib/pgsql/9.6/I2/recovery.conf a été créé ; il faut lui rajouter la dernière ligne ;

standby_mode = 'on'
primary_conninfo = 'user=postgres port=9601 sslmode=prefer sslcompression=1 krbsrvname=postgres'
primary_slot_name = 'slot_i2'
recovery_target_timeline = 'latest'

Démarrage de l’esclave :

/usr/pgsql-9.6/bin/pg_ctl -D /var/lib/pgsql/9.6/I2 start

Contrôler que tout va bien en consultant les logs dans /var/lib/pgsql/9.6/I2/pg_log/.

Fin de la mise en place. Toute création ou modification de données sur le maître doit se répercuter sur l’esclave, qui n’est accessible qu’en lecture seule.

Hors du labo

Pour une mise en prod en conditions réelles, il eût fallu :

  • utiliser deux serveurs : la configuration sur une machine n’offre aucun intérêt en sécurité ni performance ;
  • intégrer les scripts de démarrage et d’arrêt au système local (ici en Red Hat 6 : dans /etc/init.d/postgresql-9.6-I1 et -I2, compléter /etc/sysconfig/postgresql ou l’usine à gaz qu’est systemd) et sous Debian tenir compte des wrappers qui enrobent pg_ctl ainsi que des chemins différents (postgresql.conf dans /etc/postgresql.conf/9.6/, données dans /val/lib/postgresql, binaires dans /usr/lib/postgresql/9.6/bin/, etc. ) ;
  • dans le pg_hba.conf, utiliser un utilisateur dédié à la réplication et surtout avec mot de passe (ici j’ai juste décommenté la partie par défaut dans l’installation sur CentOS, ça ne fonctionne que parce qu’on utilise la même machine pour les deux instances) ;
  • éventuellement : configurer un archivage des journaux de transactions (ceux de pg_xlog) dans postgresql.conf : archive_mode = on, archive_command = 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f' (ou plus complexe et plus sûr…) ; et les récupérer dans l’esclave avec dans recovery.conf la restore_command et éventuellement archive_cleanup_command ; toute choses à intégrer aux sauvegardes PITR (via les journaux de transaction) ;
  • configurer éventuellement d’autres paramètres permettant d’utiliser plus sereinement l’esclave quand on requête dessus, notamment hot_standby_feedback = on, max_standby_archive_delay, max_standby_streaming_delay.

Database In Depth - C.J. DateVoilà un livre dont je ne sais quoi trop penser. La cible avouée de l’auteur : les professionnels qui ont besoin d’une bonne dose de rappel sur la théorie des bases de données relationnelles, et qu’on leur rappelle ou apprenne les limites du SQL.

Lire la suite...

lundi 16 janvier 2017

Premier billet pour meubler

Bientôt ici :

  • mes découvertes sur PostgreSQL ;
  • des résumés de bouquins sur le SQL ;
  • des copies des anciens articles du blog perso sur ces mêmes sujets.

Pour le moment on en est au choix du thème…

© SQL & éléphant, after the WP Dusk To Dawn theme Propulsé par Dotclear