To sidebar

jeudi 10 août 2017

CREATE STATISTICS dans PostgreSQL 10

Parmi toutes les 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’éxécution. Par exemple : il utilisera l’index pour chercher une poignée de lignes ; mais si le résultat implique un grande partie de la table PostgreSQL préférera son parcours complet ; et ce en fonction d’une multitude d’autres critères comme 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 %.

Mais il n’est pas si trivial de trouver de tels exemples idéaux 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… 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 des personnes de tous âges mais où les seniors seront massivement représentés :

 -- paramétrage de l'optimiseur : sans parallélisme pour simplifier ici,
 -- supposant des disques durs classiques, donc coûteux pour
 -- les accès aléatoires
 SET max_parallel_workers_per_gather TO 0 ;
 SET seq_page_cost TO 1 ;
 SET random_page_cost TO 4 ;
 SET work_mem TO 128MB ;
 CREATE TABLE personnes (
     id serial,
     age INT,
     tranche_age TEXT,
     statut TEXT
 );
 INSERT INTO personnes (age, tranche_age, statut)
 SELECT
     p.age,
     CASE WHEN p.age < 18 THEN        '0-17'
         WHEN p.age <= 25 THEN        '18-25'
         WHEN p.age <= 35 THEN        '26-35'
         WHEN p.age <= 45 THEN        '36-45'
         WHEN p.age <= 59 THEN        '46-59'
     ELSE        '60+'
     END AS tranche_age,
     CASE WHEN p.age >= 65 THEN        'Retraité'
         WHEN p.age >= 60 AND mod(x, 10) <9 THEN 'Retraité'
         WHEN p.age < 18 THEN 'Jeune'
         WHEN p.age < 6 THEN 'Petits enfants'
         WHEN  mod(x, 10) <= 7 THEN 'Actif'
     ELSE 'Inactif'
     END AS statut
 FROM (
     SELECT mod(x, 100) AS age, x
     FROM generate_series(1, 3000000) x) p
 -- « dosage » des retraités par filtrage des plus jeunes
 WHERE p.age >= 55    OR mod(x, 101) <= 81
     ;

Et des index :

 CREATE INDEX ix_age ON personnes (age);
 CREATE INDEX ix_statut ON personnes (statut);
 CREATE INDEX ix_tr_age ON personnes (tranche_age);
 VACUUM ANALYZE personnes ;

On obtient donc une table d’environ 2,7 millions de personnes où la tranche d’âge « Retraité » est surreprésentée (44,6 %) et surtout, n’y figurent quasiment que des 60 ans et plus, et vice-versa :

 SELECT statut, tranche_age, min(age), max(age), COUNT(*), ROUND(100 * (COUNT(*) / SUM(COUNT(*)) OVER () ),1) AS "%"
 FROM personnes
 GROUP BY statut, tranche_age
 ORDER BY COALESCE (statut,'aa') DESC, MIN(age);
 statut   | tranche_age | min | max |  count  |  %
 -+-+-+-+-+--
 Retraité | 60+         |  60 |  99 | 1200000 | 44.6
 Jeune    | 0-17        |   0 |  17 |  438422 | 16.3
 Inactif  | 18-25       |  18 |  19 |   48714 |  1.8
 Inactif  | 26-35       |  28 |  29 |   48714 |  1.8
 Inactif  | 36-45       |  38 |  39 |   48714 |  1.8
 Inactif  | 46-59       |  48 |  59 |  108714 |  4.0
 Actif    | 18-25       |  20 |  25 |  146142 |  5.4
 Actif    | 26-35       |  26 |  35 |  194856 |  7.2
 Actif    | 36-45       |  36 |  45 |  194856 |  7.2
 Actif    | 46-59       |  46 |  57 |  260499 |  9.7
 (10 lignes)

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 44% de retraités/plus de 60 ans s’y retrouve :

 \x auto
 SELECT * FROM  pg_stats
 WHERE tablename = 'personnes';
 ...
 tablename              | personnes
 attname                | tranche_age
 ...
 n_distinct             | 6
 most_common_vals       | {60+,0-17,46-59,36-45,26-35,18-25}
 most_common_freqs      | {0.448533,0.161233,0.137067,0.0917667,0.0897,0.0717}
 ...
 correlation            | 0.256408
 ...
 tablename              | personnes
 attname                | statut
 ...
 null_frac              | 0
 ...
 n_distinct             | 4
 most_common_vals       | {Retraité,Actif,Jeune,Inactif}
 most_common_freqs      | {0.448533,0.296333,0.161233,0.0939}
 ...
 correlation            | 0.318975

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 (1,2 millions) sont à peu près identiques :

 EXPLAIN (ANALYZE)
 SELECT count(id), min(age), max(age)
 FROM personnes
 WHERE statut = 'Retraité' ;
                                                         QUERY PLAN
 ---
 Aggregate  (cost=59800.31..59800.32 rows=1 width=16) (actual time=423.976..423.977 rows=1 loops=1)
   ->  Seq Scan on personnes  (cost=0.00..50752.39 rows=1206389 width=8) (actual time=0.010..289.959 rows=1200000 loops=1)
         Filter: (statut = 'Retraité'::text)
         Rows Removed by Filter: 1489631
 Planning time: 0.109 ms
 Execution time: 424.006 ms
 (6 lignes)
 EXPLAIN (ANALYZE)
 SELECT count(id), min(age),max(age)
 FROM personnes
 WHERE tranche_age = '60+' ;
                                                         QUERY PLAN
 ---
 Aggregate  (cost=59800.31..59800.32 rows=1 width=16) (actual time=388.674..388.674 rows=1 loops=1)
   ->  Seq Scan on personnes  (cost=0.00..50752.39 rows=1206389 width=8) (actual time=0.015..265.437 rows=1200000 loops=1)
         Filter: (tranche_age = '60+'::text)
         Rows Removed by Filter: 1489631
 Planning time: 0.109 ms
 Execution time: 388.717 ms
 (6 lignes)

Le problème

Par contre si on veut les retraités de plus de 60 ans, l’estimation de l’optimiseur est erronée : il pense ramener 541 106 lignes (44 % * 44 % * 2,7 millions) alors qu’il en trouvera 1,2 millions. PostgreSQL ne sait pas encore que les deux critères concernent les même lignes :

 EXPLAIN (ANALYZE)
 SELECT count(id), min(age),max(age)
 FROM personnes
 WHERE tranche_age = '60+' AND statut = 'Retraité';
                                                         QUERY PLAN
 --
 Aggregate  (cost=60704.11..60704.12 rows=1 width=16) (actual time=347.206..347.206 rows=1 loops=1)
   ->  Bitmap Heap Scan on personnes  (cost=21955.86..56811.18 rows=519057 width=8) (actual time=61.284..237.541 rows=1200000 loops=1)
         Recheck Cond: (tranche_age = '60+'::text)
         Filter: (statut = 'Retraité'::text)
         Heap Blocks: exact=17132
         ->  Bitmap Index Scan on ix_tr_age  (cost=0.00..21826.09 rows=1181555 width=0) (actual time=58.674..58.674 rows=1200000 loops=1)
               Index Cond: (tranche_age = '60+'::text)
 Planning time: 0.095 ms
 Execution time: 347.242 ms

Objet statistiques

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 (explicite ici, ou en arrière plan) :

 CREATE STATISTICS  pers_tr_age_statut (dependencies)
 ON tranche_age, statut
 FROM personnes ;
 ANALYZE personnes ;

La 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.

Résultat

Quel est l’effet sur notre requête ?

 EXPLAIN (ANALYZE)
 SELECT count(id), min(age),max(age)
 FROM personnes
 WHERE tranche_age = '60+' AND statut = 'Retraité';
                                                         QUERY PLAN
 --
 Aggregate  (cost=64605.29..64605.30 rows=1 width=16) (actual time=381.063..381.063 rows=1 loops=1)
   ->  Seq Scan on personnes  (cost=0.00..57476.46 rows=950510 width=8) (actual time=0.009..266.225 rows=1200000 loops=1)
         Filter: 
         Rows Removed by Filter: 1489631
 Planning time: 0.080 ms
 Execution time: 381.086 ms
 (6 lignes)

Le plan est à présent un parcours complet (seq scan) : PostgreSQL calcule que finalement il vaut mieux parcourir toute la table pour en filtrer les valeurs que de faire les va-et-viens entre la table et des index pas si discriminants que ça finalement.

Grâce au nouvel objet statistique, la volumétrie attendue avant l’agrégation a augmenté (950 510 au lieu de 541 106 précédemment et 1 200 000 réels), d’où le changement de stratégie.

On pourrait s’étonner que le coût ait monté (64 605 contre 60 704 précédemment) : en fait c’est l’ancien chiffre qui était faux puisque la volumétrie estimée était fausse. En interdisant les seq scan (SET enable_seqscan TO off ;) pour le forcer à revenir à l’ancien plan, PostgreSQL calcule un coût de 64 702. (Tout cela est une vision simplifiée, l’optimiseur ne regarde pas que le coût final.)

Quant au temps d’exécution, il semble avoir monté dans mon exemple. D’une part on est dans la marge des écarts normaux sur une machine non dédiée ; de l’autre les paramètres de coût affichés au début de ce test ne sont pas réellement adaptés à ma machine de test et son SDD : un seq scan n’y est pas forcément une aussi bonne idée que sur un disque classique ; de plus dans la réalité la table de 135 Mo tient intégralement dans le cache de PostgreSQL et les paramètres de coût seraient à descendre autour de 0.1…

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

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 les colonnes 3 et 4, soit tranche_age et statut. Leur corrélation est calculée dans les deux sens. 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.

SELECT * FROM pg_statistic_ext 
WHERE stxname = 'pers_tr_age_statut'  ;
-RECORD 1-+--- 
stxrelid        | 18462
stxname         | pers_tr_age_statut
stxnamespace    | 2200
stxowner        | 10
stxkeys         | 3 4
stxkind         | {f}
stxndistinct    | *NULL*
stxdependencies | {"3 => 4": 0.611900, "4 => 3": 0.611900}

Moralité

On a obtenu ici un changement de plan en allant chercher un cas un peu tordu. Dans la réalité vous étudierez un plan d’une requête un peu lente et trouverez une volumétrie estimée aberrante. Soit vos stats ne sont pas à jour, soit il y a corrélation plus ou moins évidente. 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. Et il vaut mieux les utiliser que torturer une requête jusqu’à l’illisibilité.

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. »

Bibliographie

Quelques liens vers la doc officielle (en cours de finalisation au moment où j’écris cela) 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