Parallélisme

(sujet préalablement traité avec les versions 9.6 devel - 9.6 - 10 devel )

Diviser pour mieux régner

      Dans cette page présentant les index BRIN et le partitionnement nous avons vu qu’il était souvent possible d’optimiser une requête, souvent en réduisant les pages visitées à l’exécution. Vous avez une valeur ajoutée puisque vous savez quelles données peuvent être archivées etc.
      Cette fois, nous allons prendre pour exemple la table VENTES de l’herboristerie d’Euclidio contenant les ventes par seconde de chacun de ses 200 produits sur un an. Pour le produit ID_PROD, le montant de vente MTV a été réalisé dans la seconde suivant le temps stocké dans la colonne DTV.
      Euclidio vous demande de trouver les 3 plus gros MTV cumulés pour une sélection de produits.
      Inutile de partitionner sur ID_PROD, la documentation PostgreSQL parlait en version 9 d’une centaine de partitions. La version 10 propose de grandes avancées au niveau du partitionnement mais l’herboristerie du frère de Margiono compte déjà 200 références produit donc nous écartons cette option. Un index sur ID_PROD pourrait-il alors être utile ? La colonne ID_PROD n’est pas candidate à un index HASH ou BRIN. Un index B-Tree sera probablement assez inefficace puisque la requête concerne 20% des produits.
      Peu d’optimisations possibles au niveau de la requête et des structures, il nous reste la force brute. Paralléliser n’est PAS optimiser mais parfois c’est la seule solution. Nous allons simplement utiliser plus de ressources matérielles pour traiter la requête. PostgreSQL-XL permettait déjà de paralléliser mais c’est à présent aussi possible avec la version classique de PostgreSQL depuis la 9.6.
      Consultez l’état du système avant de tabler sur cette fonctionnalité. Inutile d’espérer un gain si votre serveur est déjè saturé au niveau CPU, en train de swapper etc.
      En version 10, le paramètre max_parallel_workers_per_gather est fixé par défaut à 2. Dans l’exemple qui suit, il sera fixé à 0 puis 3. Avec max_parallel_workers_per_gather à 3, le process exécutant la requête peut potentiellement appeler à l’aide 3 travailleurs supplémentaires. Si le planner le juge nécessaire, 4 process peuvent donc travailler pour exécuter notre requête. Avec les 4 coeurs Intel x64 dédiés à la machine virtuelle sur laquelle j’exécute le test, inutile d’aller plus loin. En pratique :

select version(); version ----------------------------------------------------------------------------------------------------- PostgreSQL 10beta1 on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-18) 6.3.0 20170516, 64-bit (1 ligne) set max_parallel_workers_per_gather=0; SET create unlogged table ventes(dtv timestamp, id_prod integer, mtv integer) ; CREATE TABLE WITH serie(i) AS (SELECT generate_series(157680000,1,-1)) INSERT INTO ventes (SELECT current_timestamp - (ceil(i/5) || ' seconds')::interval, trunc(random() * 200 + 1), trunc(random() * 100 + 1) from serie); INSERT 0 157680000 Time: 507131,412 ms (08:27,131) \dt+ ventes Liste des relations Schéma | Nom | Type | Propriétaire | Taille | Description --------+--------+-------+--------------+---------+------------- public | ventes | table | postgres | 6660 MB | analyze ventes; ANALYZE Temps : 727,691 ms explain select id_prod, sum(mtv) from ventes where id_prod in(5,11,14,21,24,31,36,42,43,51,57,61,68,72,78,81,87,92,94,101,108,110,113,120,122,130,135,141,149,150,157,161,168,170,171,182,183,191,197,199) group by id_prod order by 2 desc fetch first 3 rows only; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=10470584.96..10470584.97 rows=3 width=12) -> Sort (cost=10470584.96..10470585.46 rows=200 width=12) Sort Key: (sum(mtv)) DESC -> HashAggregate (cost=10470580.38..10470582.38 rows=200 width=12) Group Key: id_prod -> Seq Scan on ventes (cost=0.00..10313132.68 rows=31489540 width=8) Filter: (id_prod = ANY ('{5,11,14,21,24,31,36,42,43,51,57,61,68,72,78,81,87,92,94,101,108,110,113,120,122,130,135,141,149,150,157,161,168,170,171,182,183,191,197,199}'::integer[])) (7 lignes) select id_prod, sum(mtv) from ventes_agg where id_prod in(5,11,14,21,24,31,36,42,43,51,57,61,68,72,78,81,87,92,94,101,108,110,113,120,122,130,135,141,149,150,157,161,168,170,171,182,183,191,197,199) group by id_prod order by 2 desc fetch first 3 rows only; id_prod | sum ---------+---------- 108 | 39918720 168 | 39890047 120 | 39871067 (3 lignes) Time: 74351,637 ms (01:14,352) ... Time: 74562,719 ms (01:14,563) ... Time: 66885,918 ms (01:06,886) create index ventes_i1 on ventes(id_prod); CREATE INDEX Time: 421136,724 ms (07:01,137) \di+ Liste des relations Schéma | Nom | Type | Propriétaire | Table | Taille | Description --------+-----------+-------+--------------+--------+---------+------------- public | ventes_i1 | index | postgres | ventes | 3378 MB | (1 ligne) explain select id_prod, sum(mtv) from ventes where id_prod in(5,11,14,21,24,31,36,42,43,51,57,61,68,72,78,81,87,92,94,101,108,110,113,120,122,130,135,141,149,150,157,161,168,170,171,182,183,191,197,199) group by id_prod order by 2 desc fetch first 3 rows only; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=3457290.64..3457290.65 rows=3 width=12) -> Sort (cost=3457290.64..3457291.14 rows=200 width=12) Sort Key: (sum(mtv)) DESC -> HashAggregate (cost=3457286.05..3457288.05 rows=200 width=12) Group Key: id_prod -> Bitmap Heap Scan on ventes (cost=558142.58..3299838.48 rows=31489515 width=8) Recheck Cond: (id_prod = ANY ('{5,11,14,21,24,31,36,42,43,51,57,61,68,72,78,81,87,92,94,101,108,110,113,120,122,130,135,141,149,150,157,161,168,170,171,182,183,191,197,199}'::integer[])) -> Bitmap Index Scan on ventes_i1 (cost=0.00..550270.20 rows=31489515 width=0) Index Cond: (id_prod = ANY ('{5,11,14,21,24,31,36,42,43,51,57,61,68,72,78,81,87,92,94,101,108,110,113,120,122,130,135,141,149,150,157,161,168,170,171,182,183,191,197,199}'::integer[])) (9 lignes) select id_prod, sum(mtv) from ventes where id_prod in(5,11,14,21,24,31,36,42,43,51,57,61,68,72,78,81,87,92,94,101,108,110,113,120,122,130,135,141,149,150,157,161,168,170,171,182,183,191,197,199) group by id_prod order by 2 desc fetch first 3 rows only; id_prod | sum ---------+---------- 108 | 39918720 168 | 39890047 120 | 39871067 (3 lignes) Time: 78451,122 ms (01:18,451) ... Time: 78316,422 ms (01:18,316) ... Time: 70897,778 ms (01:10,898) set max_parallel_workers_per_gather=3; SET explain select id_prod, sum(mtv) from ventes where id_prod in(5,11,14,21,24,31,36,42,43,51,57,61,68,72,78,81,87,92,94,101,108,110,113,120,122,130,135,141,149,150,157,161,168,170,171,182,183,191,197,199) group by id_prod order by 2 desc fetch first 3 rows only; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=2071830.37..2071830.38 rows=3 width=12) -> Sort (cost=2071830.37..2071830.87 rows=200 width=12) Sort Key: (sum(mtv)) DESC -> Finalize GroupAggregate (cost=2071821.29..2071827.79 rows=200 width=12) Group Key: id_prod -> Sort (cost=2071821.29..2071822.79 rows=600 width=12) Sort Key: id_prod -> Gather (cost=2071731.60..2071793.60 rows=600 width=12) Workers Planned: 3 -> Partial HashAggregate (cost=2070731.60..2070733.60 rows=200 width=12) Group Key: id_prod -> Parallel Bitmap Heap Scan on ventes (cost=558142.58..2019942.06 rows=10157908 width=8) Recheck Cond: (id_prod = ANY ('{5,11,14,21,24,31,36,42,43,51,57,61,68,72,78,81,87,92,94,101,108,110,113,120,122,130,135,141,149,150,157,161,168,170,171,182,183,191,197,199}'::integer[])) -> Bitmap Index Scan on ventes_i1 (cost=0.00..550270.20 rows=31489515 width=0) Index Cond: (id_prod = ANY ('{5,11,14,21,24,31,36,42,43,51,57,61,68,72,78,81,87,92,94,101,108,110,113,120,122,130,135,141,149,150,157,161,168,170,171,182,183,191,197,199}'::integer[])) select id_prod, sum(mtv) from ventes where id_prod in(5,11,14,21,24,31,36,42,43,51,57,61,68,72,78,81,87,92,94,101,108,110,113,120,122,130,135,141,149,150,157,161,168,170,171,182,183,191,197,199) group by id_prod order by 2 desc fetch first 3 rows only; id_prod | sum ---------+---------- 108 | 39918720 168 | 39890047 120 | 39871067 (3 lignes) Time: 20855,258 ms (00:20,855) ... Time: 21450,014 ms (00:21,450) ... Time: 22054,058 ms (00:22,054)

      La requête prenait autour de 1min10 sans index. Un index B-Tree, même utilisé de manière spéciale, ne permettait pas de diminuer cette durée.
      En utilisant toutes les ressources de la machine virtuelle grâce au parallélisme, le temps a pu être abaissé autour des 22 secondes. Le parallélisme est à manier avec précaution et ne dispense pas d’optimiser. Mais, parfois, c’est la seule option pour gagner du temps sur une requête particulière.
      Dès PostgreSQL 9.6, le parallélisme peut s’appliquer sur les balayages complets de table (seq scan) mais aussi sur les jointures par hachage (hash join) et aggrégations. La version 10 ajoute les index scan, les bitmap heap scan et un type de jointure (merge join).

Mise à jour : 20/05/2017