gleu's blog

Aller au contenu | Aller au menu | Aller à la recherche

Début de la traduction du manuel 9.5

J'ai enfin fini le merge du manuel de la version 9.5. Très peu de temps avant la 9.5, le peu de temps que j'avais étant consacré à mon livre. Mais là, c'est bon, on peut bosser. D'ailleurs, Flavie a déjà commencé et a traduit un paquet de nouveaux fichiers. Mais il reste du boulot. Pour les intéressés, c'est par là : https://github.com/gleu/pgdocs_fr/wiki/Translation-9.5

N'hésitez pas à m'envoyer toute question si vous êtes intéressé pour participer.

Parution de mon livre : "PostgreSQL, architecture et notions avancées"

Après pratiquement deux ans de travail, mon livre est enfin paru. Pour être franc, c'est assez étonnant de l'avoir entre les mains : un vrai livre, avec une vraie reliure et une vraie couverture, écrit par soi. C'est à la fois beaucoup de fierté et pas mal de questionnements sur la façon dont il va être reçu.

Ceci étant dit, sans savoir si le livre sera un succès en soi, c'est déjà pour moi un succès personnel. Le challenge était de pouvoir écrire un livre de 300 pages sur PostgreSQL, le livre que j'aurais aimé avoir entre les mains quand j'ai commencé à utiliser ce SGBD il y a maintenant plus de 15 ans sous l'impulsion de mon ancien patron.

Le résultat est à la hauteur de mes espérances et les premiers retours sont très positifs. Ce livre apporte beaucoup d'explications sur le fonctionnement et le comportement de PostgreSQL qui, de ce fait, n'est plus cette espèce de boîte noire à exécuter des requêtes. La critique rédigée par Jean-Michel Armand dans le GNU/Linux Magazine France numéro 190 est vraiment très intéressante. Je suis d'accord avec son auteur sur le fait que le début est assez ardu : on plonge directement dans la technique, sans trop montrer comment c'est utilisé derrière, en production. Cette partie-là n'est abordée qu'après. C'est une question que je m'étais posée lors de la rédaction, mais cette question est l'éternel problème de l'oeuf et de la poule ... Il faut commencer par quelque chose : soit on explique la base technique (ce qui est un peu rude), puis on finit par montrer l'application de cette base, soit on fait l'inverse. Il n'y a certainement pas une solution meilleure que l'autre. Le choix que j'avais fait me semble toujours le bon, même maintenant. Mais en effet, on peut avoir deux façons de lire le livre : en commençant par le début ou en allant directement dans les chapitres thématiques.

Je suis déjà prêt à reprendre le travail pour proposer une deuxième édition encore meilleure. Cette nouvelle édition pourrait se baser sur la prochaine version majeure de PostgreSQL, actuellement numérotée 9.6, qui comprend déjà des nouveautés très excitantes. Mais cette édition ne sera réellement intéressante qu'avec la prise en compte du retour des lecteurs de la première édition, pour corriger et améliorer ce qui doit l'être. N'hésitez donc pas à m'envoyer tout commentaire sur le livre, ce sera très apprécié.

Version finale du livre

Elle n'est pas encore sortie. Elle est pratiquement terminée, on attend d'avoir le livre en version imprimée.

Néanmoins, je peux déjà dire les nouveautés par rapport à la beta 0.4 :

  • Global
    • mise à jour du texte pour la 9.5
    • ajout du chapitre sur la sécurité
    • ajout du chapitre sur la planification
    • mise à jour des exemples avec PostgreSQL 9.5 beta 1
  • Fichiers
    • Ajout d'un schéma sur les relations entre tables, FSM et VM
    • Ajout de la description des répertoires pg_dynshmem et pg_logical
  • Contenu des fichiers
    • Ajout d'informations sur le stockage des données, colonne par colonne
    • Ajout d'un schéma sur la structure logique et physique d'un index B-tree
    • Ajout de la description des index GIN
    • Ajout de la description des index GiST
    • Ajout de la description des index SP-GiST
  • Architecture mémoire
    • calcul du work_mem pour un tri
    • calcul du maintenance_work_mem pour un VACUUM
  • Gestion des transactions
    • Gestion des verrous et des accès concurrents
  • Maintenance
    • Description de la sortie d'un VACUUM VERBOSE

J'avoue que j'ai hâte d'avoir la version finale entre mes mains :-) Bah, oui, c'est quand même 1 an et demi de boulot acharné !

Version beta 0.4 du livre

La dernière beta datait de mi-mai. Beaucoup de choses se sont passées pendant les 4 mois qui ont suivi. Quatre nouveaux chapitres sont mis à disposition :

  • Sauvegarde
  • Réplication
  • Statistiques
  • Maintenance

Mais ce n'est évidemment pas tout. Dans les nouveautés importantes, notons :

  • Chapitres Fichiers, Processus et Mémoire
    • Ajout des schémas disques/processus/mémoire
  • Chapitre Contenu physique des fichiers
    • Déplacement des informations sur le contenu des journaux de transactions dans ce chapitre
    • Ajout de la description du contenu d'un index B-tree
    • Ajout de la description du contenu d'un index Hash
    • Ajout de la description du contenu d'un index BRIN
    • Restructuration du chapitre dans son ensemble
  • Chapitre Architecture des processus
    • Ajout de sous-sections dans la description des processus postmaster et startup
    • Ajout d'un exemple sur la mort inattendue d'un processus du serveur PostgreSQL
  • Chapitre Architecture mémoire
    • Ajout de plus de détails sur la mémoire cache (shared_buffers)
  • Chapitre Gestion des transactions
    • Ajout d'informations sur le CLOG, le FrozenXid et les Hint Bits
  • Chapitre Gestion des objets
    • Ajout d'une section sur les options spécifiques des vues et fonctions pour la sécurité
    • Ajout d'un paragraphe sur le pseudo-type serial
  • Divers
    • Mise à jour des exemples avec PostgreSQL 9.4.4

Bref, c'est par ici.

Quant à la prochaine version ? cela devrait être la version finale. Elle comportera le chapitre Sécurité (déjà écrit, en cours de relecture) et le chapitre sur le planificateur de requêtes (en cours d'écriture). Elle devrait aussi disposer d'une mise à jour complète concernant la version 9.5 (dont la beta devrait sortir début octobre).

Bonne lecture et toujours intéressé pour savoir ce que vous en pensez (via la forum mis en place par l'éditrice ou via mon adresse email).

Comment quantifier le maintenance_work_mem

Ce billet fait partie d'une série sur l'écriture de mon livre, « PostgreSQL - Architecture et notions avancées ».

Je suis en train d'écrire le chapitre sur la maintenance. Parmi les opérations de maintenance se trouve l'ordre VACUUM. Beaucoup de choses ont déjà été écrites dans le livre sur le VACUUM mais j'avais bizarrement oublié une chose. Une bonne configuration du paramètre maintenance_work_mem permet d'avoir un VACUUM performant. Mais comment peut-on savoir que la valeur du maintenance_work_mem est suffisante ?

J'ai donc creusé hier soir dans les sources de PostgreSQL à la recherche de ce qui est stocké dans cette mémoire. Tout se trouve dans src/backend/commands/vacuumlazy.c, principalement dans la fonction lazy_space_alloc(). En gros, PostgreSQL y place un tableau de la structure ItemPointerData. Cette structure prend six octets. Donc une estimation (très grosse) serait de dire qu'on peut stocker maintenance_work_mem/6 positions d'enregistrements morts dans cette mémoire. Un patch rapide (voir le fichier joint) nous prouve cette théorie :

Nous plaçons le paramètre client_min_messages au niveau log pour voir les traces ajoutées par le patch :

postgres=# SET client_min_messages TO log;
SET

Nous créons la table et désactivons l'autovacuum sur cette table pour le gérer nous-même :

postgres=# DROP TABLE IF EXISTS t1;
DROP TABLE
postgres=# CREATE TABLE t1(id INTEGER PRIMARY KEY);
CREATE TABLE
postgres=# ALTER TABLE t1 SET (autovacuum_enabled = OFF);
ALTER TABLE

Nous insérons un million de lignes, puis en supprimons 900000 :

postgres=# INSERT INTO t1 SELECT generate_series(1, 1000000);
INSERT 0 1000000
postgres=# DELETE FROM t1 WHERE id<900000;
DELETE 899999

Nous configurons maintenance_work_mem à 1 Mo (en fait, suffisamment petit pour voir que le VACUUM a besoin de plusieurs passes dû au manque de mémoire) :

postgres=# SET maintenance_work_mem TO '1MB';
SET
postgres=# VACUUM t1;
LOG:  patch - vac_work_mem: 1024
LOG:  patch - sizeof(ItemPointerData): 6
LOG:  patch - maxtuples: 174762
LOG:  patch - step 1
LOG:  patch - step 2
LOG:  patch - step 3
LOG:  patch - step 4
LOG:  patch - step 5
LOG:  patch - step 6
VACUUM

La fonction de calcul de la taille mémoire a bien noté le maintenance_work_mem à 1 Mo (1024 Ko). La taille de la structure est bien de 6 octets. Il est donc possible de stocker 1024*1024/6 enregistrements, soit 174762 enregistrements. Ayant supprimé 900000 enregistrements, il me faut 6 passes (l'arrondi supérieur de l'opération 900000/174762) pour traiter la table entière. Pas efficace.

Essayons dans les mêmes conditions mais avec un maintenance_work_mem trois fois plus gros :

postgres=# TRUNCATE t1;
TRUNCATE TABLE
postgres=# INSERT INTO t1 SELECT generate_series(1, 1000000);
INSERT 0 1000000
postgres=# DELETE FROM t1 WHERE id<900000;
DELETE 899999
postgres=# SET maintenance_work_mem TO '3MB';
SET
postgres=# VACUUM t1;
LOG:  patch - vac_work_mem: 3072
LOG:  patch - sizeof(ItemPointerData): 6
LOG:  patch - maxtuples: 524288
LOG:  patch - step 1
LOG:  patch - step 2
VACUUM

Nous ne faisons plus que deux passes (tout d'abord 524288 enregistrements, puis 375711), c'est plus efficace mais non optimal.

Essayons maintenant avec le maintenance_work_mem de base (64 Mo) :

postgres=# TRUNCATE t1;
TRUNCATE TABLE
postgres=# INSERT INTO t1 SELECT generate_series(1, 1000000);
INSERT 0 1000000
postgres=# DELETE FROM t1 WHERE id<900000;
DELETE 899999
postgres=# RESET maintenance_work_mem;
RESET
postgres=# VACUUM VERBOSE t1;
INFO:  vacuuming "public.t1"
LOG:  patch - vac_work_mem: 65536
LOG:  patch - sizeof(ItemPointerData): 6
LOG:  patch - maxtuples: 1287675
LOG:  patch - step 1
VACUUM

Seule une passe est réalisée. Il est à noter que la mémoire prise ne correspond pas au 64 Mo. 64 Mo me permet de stocker 11 millions d'enregistrements morts, mais je n'ai dans la table que 1000000 d'enregistrements dont 90% est mort. Autrement dit, j'ai besoin de beaucoup moins de mémoire. C'est bien le cas ici où, au lieu de 11 millions d'enregistrements, on peut en stocker 1287675 (soit un peu plus de 7 Mo).

De tout ça, comment puis-je savoir si mon maintenance_work_mem est bien configuré ? Il faut se baser sur le nombre d'enregistrements (morts) contenus dans les tables. Ça correspond à cette requête pour les tables de ma base de connexion :

SELECT pg_size_pretty(max(n_dead_tup*6)) AS custom_maintenance_work_mem
FROM pg_stat_all_tables;

Dans l'exemple précédent, cela me donnerait ceci :

postgres=# TRUNCATE t1;
TRUNCATE TABLE
postgres=# INSERT INTO t1 SELECT generate_series(1, 1000000);
INSERT 0 1000000
postgres=# DELETE FROM t1 WHERE id<900000;
DELETE 899999
postgres=# SELECT pg_size_pretty(max(n_dead_tup*6)) AS custom_maintenance_work_mem,
           current_setting('maintenance_work_mem') AS current_maintenance_work_mem
          FROM pg_stat_all_tables;

 custom_maintenance_work_mem | current_maintenance_work_mem 
-----------------------------+------------------------------
 5273 kB                     | 64MB
(1 row)

Il me faut au minimum 5,2 Mo. Je suis donc tranquille.

Évidemment, le nombre d'enregistrements morts évolue dans le temps et il est tout à fait possible que la quantité de mémoire nécessaire soit bien plus importante. On peut se baser sur le nombre d'enregistrements total pour avoir le pire des cas comme ici :

b1=# SELECT pg_size_pretty(max((n_live_tup+n_dead_tup)*6)) AS custom_maintenance_work_mem,
     current_setting('maintenance_work_mem') AS current_maintenance_work_mem
     FROM pg_stat_all_tables;

 custom_maintenance_work_mem | current_maintenance_work_mem 
-----------------------------+------------------------------
 472 MB                      | 512MB
(1 row)

Ce qui révèle donc une configuration adéquate pour cet utilisateur.

Différences entre les versions beta du livre

Je me suis rendu compte ce week-end qu'on n'avait pas publié d'informations sur ce qui avait été ajouté entre les différentes versions beta, en dehors des nouveaux chapitres. Voici donc la liste des modifications, un peu éditée pour être plus lisible :

Pour la beta2 :

  • Nouveaux chapitres
    • protocole de communication
    • connexions
  • Chapitre fichiers
    • ajout d'une note sur l'option --no-clean de la commande initdb
    • ajout d'une note sur les versions 9.1 (et inférieures) et la colonne spclocation du catalogue pg_tablespace
    • ajout d'une note sur le fichier pgstat.stat des versions 9.3 et antérieures
  • Chapitre processus
    • refonte des sections pour trier les processus par activité (et non par nom)
    • revue du résumé sur les processus d'écriture dans les fichiers de données suite à une remarque d'un lecteur dans le forum du livre (forum uniquement accessible par les lecteurs actuels)
    • correction des processus équivalents au niveau Oracle, suite là-aussi à un autre commentaire d'un lecteur
  • Chapitre mémoire
    • ajout de deux paragraphes sur l'utilisation (partielle) du cache pour les requêtes
    • présentation de deux colonnes de la vue pg_stat_database permettant de quantifier l'utilisation des fichiers temporaires

Et pour la beta 3 :

  • Nouveaux chapitres
    • gestion des objets
    • transactions
  • Global
    • remplacement du terme maître par serveur primaire et du terme esclave par serveur secondaire (suite à la demande d'un lecteur)
  • Chapitre mémoire
    • ajout d'une note sur l'intérêt du paramètre maintenance_work_mem dans le cadre d'un import de données avec pg_restore
  • Chapitre processus
    • ajout d'une partie sur les écritures dans les fichiers de données suite à un CHECKPOINT, avec quelques graphes, pour mieux expliquer les différents paramètres checkpoint_*
  • Chapitre Fichiers
    • ajout d'un paragraphe sur la génération des relfilnode
    • ajout d'infos sur le LSN et les journaux de transactions

Je publierais dans ce blog les nouveautés de chaque version beta à chaque sortie, ce sera plus facile pour les lecteurs.

Analyse du VACUUM

Ce billet fait partie d'une série sur l'écriture de mon livre, « PostgreSQL - Architecture et notions avancées ».

Et voilà, deux nouveaux chapitres écrits, dont un sur le système transactionnel de PostgreSQL. Ce dernier m'a demandé d' étudier plus attentivement le travail de l'opération VACUUM. J'en connaissais le principe et son fonctionnement, à savoir un fonctionnement en trois phases : recherche des éléments à flagguer comme invisibles, suppression de ces éléments dans les index, puis suppression dans la table (pas physiquement). Cependant, je ne l'avais pas regardé plus précisément.

J'ai donc lu le code, puis écrit un petit patch pour mieux suivre cela (disponible en pièce jointe). J'ai exécuté un script SQL pour visualiser différents comportements. Par exemple, on ajoute dix lignes dans une nouvelle table, puis on met à jour une ligne sur trois, et enfin on exécute un VACUUM sur cette table :

CREATE TABLE t2(c1 integer);
ALTER TABLE t2 SET (autovacuum_enabled=off);
INSERT INTO t2 SELECT generate_series(1, 10);
UPDATE t2 SET c1=-c1 where c1%3=1;
SET client_min_messages to log;
VACUUM t2;

Voici le log fourni par le patch :

psql:script.sql:9: LOG:  patch - vacuum.c - vacuum[117] - VACUUM on 0, toast included, no wraparound
psql:script.sql:9: LOG:  patch - vacuum.c - vacuum[249] - vacuuming 82231
psql:script.sql:9: LOG:  patch - vacuum.c - vacuum_rel[1207] - relation t2 (82231) opened with lockmode 4
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_vacuum_rel[194] - vacuuming...
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[465] - relation has 1 blocks
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[534] - working on block 0
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[629] - reading block
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[762] - pruning HOT update chains...
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune[220] - 4 deleted items found
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[691] - item 1 is now REDIRECTed to item 11
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[691] - item 4 is now REDIRECTed to item 12
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[691] - item 7 is now REDIRECTed to item 13
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[691] - item 10 is now REDIRECTed to item 14
psql:script.sql:9: LOG:  patch - bufpage.c - PageRepairFragmentation[489] - compacting page (10 have storage, 0 are unused)
psql:script.sql:9: LOG:  patch - bufpage.c - PageRepairFragmentation[514] - compacting page (totallen 320)

Il n'y a là qu'une seule étape exécutée. En effet, dû au très petit nombre de lignes dans la table, le seul bloc de 8 Ko n'a pas été entièrement occupé. Du coup, PostgreSQL place les nouvelles versions des lignes mises à jour dans le même bloc que les anciennes versions et utilise le Heap Over Tuple pour lier les enregistrements. De plus, comme il n'y a pas d'index, pas besoin de les mettre à jour.

Maintenant, faisons la même chose avec 400 lignes (en fait, suffisamment pour remplir plus d'un bloc). Les logs sont beaucoup plus importants.

psql:script.sql:9: LOG:  patch - vacuum.c - vacuum[117] - VACUUM on 0, toast included, no wraparound
psql:script.sql:9: LOG:  patch - vacuum.c - vacuum[249] - vacuuming 82234
psql:script.sql:9: LOG:  patch - vacuum.c - vacuum_rel[1207] - relation t2 (82234) opened with lockmode 4
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_vacuum_rel[194] - vacuuming...
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[465] - relation has 3 blocks
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[534] - working on block 0
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[629] - reading block
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[762] - pruning HOT update chains...
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune[220] - 76 deleted items found
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[702] - item 1 is now DEAD
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[702] - item 4 is now DEAD
[...]
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[702] - item 223 is now DEAD
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[702] - item 226 is now DEAD
psql:script.sql:9: LOG:  patch - bufpage.c - PageRepairFragmentation[489] - compacting page (150 have storage, 0 are unused)
psql:script.sql:9: LOG:  patch - bufpage.c - PageRepairFragmentation[514] - compacting page (totallen 4800)
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[817] - item 1 DEAD
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[817] - item 4 DEAD
[...]
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[817] - item 223 DEAD
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[817] - item 226 DEAD
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_vacuum_page[1237] - block 0
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_vacuum_page[1250] - item 1 is now UNUSED
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_vacuum_page[1250] - item 4 is now UNUSED
[...]
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_vacuum_page[1250] - item 223 is now UNUSED
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_vacuum_page[1250] - item 226 is now UNUSED
psql:script.sql:9: LOG:  patch - bufpage.c - PageRepairFragmentation[489] - compacting page (150 have storage, 76 are unused)
psql:script.sql:9: LOG:  patch - bufpage.c - PageRepairFragmentation[514] - compacting page (totallen 4800)
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[534] - working on block 1
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[629] - reading block
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[762] - pruning HOT update chains...
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune[220] - 58 deleted items found
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[702] - item 3 is now DEAD
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[702] - item 6 is now DEAD
[...]
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[702] - item 171 is now DEAD
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[702] - item 174 is now DEAD
psql:script.sql:9: LOG:  patch - bufpage.c - PageRepairFragmentation[489] - compacting page (168 have storage, 0 are unused)
psql:script.sql:9: LOG:  patch - bufpage.c - PageRepairFragmentation[514] - compacting page (totallen 5376)
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[817] - item 3 DEAD
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[817] - item 6 DEAD
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[817] - item 9 DEAD
[...]
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[817] - item 171 DEAD
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[817] - item 174 DEAD
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_vacuum_page[1237] - block 1
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_vacuum_page[1250] - item 3 is now UNUSED
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_vacuum_page[1250] - item 6 is now UNUSED
[...]
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_vacuum_page[1250] - item 171 is now UNUSED
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_vacuum_page[1250] - item 174 is now UNUSED
psql:script.sql:9: LOG:  patch - bufpage.c - PageRepairFragmentation[489] - compacting page (168 have storage, 58 are unused)
psql:script.sql:9: LOG:  patch - bufpage.c - PageRepairFragmentation[514] - compacting page (totallen 5376)
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[534] - working on block 2
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[629] - reading block
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[762] - pruning HOT update chains...
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune[220] - 0 deleted items found

Chaque élément devenu invisible est déclaré DEAD lors de la première étape, puis UNUSED à la troisième étape.

Il est dit que le fillfactor permet d'augmenter l'utilisation du Heap Over Tuple. Voici ce que cela donne avec un fillfactor à 90% :

psql:script.sql:9: LOG:  patch - vacuum.c - vacuum[117] - VACUUM on 0, toast included, no wraparound
psql:script.sql:9: LOG:  patch - vacuum.c - vacuum[249] - vacuuming 82237
psql:script.sql:9: LOG:  patch - vacuum.c - vacuum_rel[1207] - relation t2 (82237) opened with lockmode 4
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_vacuum_rel[194] - vacuuming...
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[465] - relation has 3 blocks
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[534] - working on block 0
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[629] - reading block
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[762] - pruning HOT update chains...
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune[220] - 68 deleted items found
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[691] - item 1 is now REDIRECTed to item 205
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[691] - item 4 is now REDIRECTed to item 206
[...]
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[691] - item 61 is now REDIRECTed to item 225
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[691] - item 64 is now REDIRECTed to item 226
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[702] - item 67 is now DEAD
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[702] - item 70 is now DEAD
[...]
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[702] - item 199 is now DEAD
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[702] - item 202 is now DEAD
psql:script.sql:9: LOG:  patch - bufpage.c - PageRepairFragmentation[489] - compacting page (158 have storage, 0 are unused)
psql:script.sql:9: LOG:  patch - bufpage.c - PageRepairFragmentation[514] - compacting page (totallen 5056)
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[817] - item 67 DEAD
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[817] - item 70 DEAD
[...]
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[817] - item 199 DEAD
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[817] - item 202 DEAD
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_vacuum_page[1237] - block 0
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_vacuum_page[1250] - item 67 is now UNUSED
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_vacuum_page[1250] - item 70 is now UNUSED
[...]
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_vacuum_page[1250] - item 199 is now UNUSED
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_vacuum_page[1250] - item 202 is now UNUSED
psql:script.sql:9: LOG:  patch - bufpage.c - PageRepairFragmentation[489] - compacting page (158 have storage, 46 are unused)
psql:script.sql:9: LOG:  patch - bufpage.c - PageRepairFragmentation[514] - compacting page (totallen 5056)
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[534] - working on block 1
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[629] - reading block
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[762] - pruning HOT update chains...
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune[220] - 66 deleted items found
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[691] - item 1 is now REDIRECTed to item 205
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[691] - item 4 is now REDIRECTed to item 206
[...]
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[691] - item 61 is now REDIRECTed to item 225
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[691] - item 64 is now REDIRECTed to item 226
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[702] - item 67 is now DEAD
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[702] - item 70 is now DEAD
[...]
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[702] - item 193 is now DEAD
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[702] - item 196 is now DEAD
psql:script.sql:9: LOG:  patch - bufpage.c - PageRepairFragmentation[489] - compacting page (160 have storage, 0 are unused)
psql:script.sql:9: LOG:  patch - bufpage.c - PageRepairFragmentation[514] - compacting page (totallen 5120)
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[817] - item 67 DEAD
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[817] - item 70 DEAD
[...]
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[817] - item 193 DEAD
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[817] - item 196 DEAD
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_vacuum_page[1237] - block 1
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_vacuum_page[1250] - item 67 is now UNUSED
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_vacuum_page[1250] - item 70 is now UNUSED
[...]
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_vacuum_page[1250] - item 193 is now UNUSED
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_vacuum_page[1250] - item 196 is now UNUSED
psql:script.sql:9: LOG:  patch - bufpage.c - PageRepairFragmentation[489] - compacting page (160 have storage, 44 are unused)
psql:script.sql:9: LOG:  patch - bufpage.c - PageRepairFragmentation[514] - compacting page (totallen 5120)
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[534] - working on block 2
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[629] - reading block
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[762] - pruning HOT update chains...
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune[220] - 0 deleted items found

Certains enregistrements bénéficient de HOT, mais la majorité deviennent UNUSED. Essayons avec un fillfactor de 50% :

psql:script.sql:9: LOG:  patch - vacuum.c - vacuum[117] - VACUUM on 0, toast included, no wraparound
psql:script.sql:9: LOG:  patch - vacuum.c - vacuum[249] - vacuuming 82240
psql:script.sql:9: LOG:  patch - vacuum.c - vacuum_rel[1207] - relation t2 (82240) opened with lockmode 4
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_vacuum_rel[194] - vacuuming...
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[465] - relation has 4 blocks
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[534] - working on block 0
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[629] - reading block
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[762] - pruning HOT update chains...
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune[220] - 38 deleted items found
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[691] - item 1 is now REDIRECTed to item 114
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[691] - item 4 is now REDIRECTed to item 115
[...]
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[691] - item 109 is now REDIRECTed to item 150
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[691] - item 112 is now REDIRECTed to item 151
psql:script.sql:9: LOG:  patch - bufpage.c - PageRepairFragmentation[489] - compacting page (113 have storage, 0 are unused)
psql:script.sql:9: LOG:  patch - bufpage.c - PageRepairFragmentation[514] - compacting page (totallen 3616)
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[534] - working on block 1
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[629] - reading block
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[762] - pruning HOT update chains...
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune[220] - 38 deleted items found
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[691] - item 2 is now REDIRECTed to item 114
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[691] - item 5 is now REDIRECTed to item 115
[...]
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[691] - item 110 is now REDIRECTed to item 150
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[691] - item 113 is now REDIRECTed to item 151
psql:script.sql:9: LOG:  patch - bufpage.c - PageRepairFragmentation[489] - compacting page (113 have storage, 0 are unused)
psql:script.sql:9: LOG:  patch - bufpage.c - PageRepairFragmentation[514] - compacting page (totallen 3616)
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[534] - working on block 2
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[629] - reading block
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[762] - pruning HOT update chains...
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune[220] - 37 deleted items found
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[691] - item 3 is now REDIRECTed to item 114
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[691] - item 6 is now REDIRECTed to item 115
[...]
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[691] - item 108 is now REDIRECTed to item 149
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[691] - item 111 is now REDIRECTed to item 150
psql:script.sql:9: LOG:  patch - bufpage.c - PageRepairFragmentation[489] - compacting page (113 have storage, 0 are unused)
psql:script.sql:9: LOG:  patch - bufpage.c - PageRepairFragmentation[514] - compacting page (totallen 3616)
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[534] - working on block 3
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[629] - reading block
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[762] - pruning HOT update chains...
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune[220] - 21 deleted items found
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[691] - item 1 is now REDIRECTed to item 62
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[691] - item 4 is now REDIRECTed to item 63
[...]
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[691] - item 58 is now REDIRECTed to item 81
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[691] - item 61 is now REDIRECTed to item 82
psql:script.sql:9: LOG:  patch - bufpage.c - PageRepairFragmentation[489] - compacting page (61 have storage, 0 are unused)
psql:script.sql:9: LOG:  patch - bufpage.c - PageRepairFragmentation[514] - compacting page (totallen 1952)

Dans ce cas, tous les enregistrements bénéficient de HOT, aucun n'est UNUSED. Cela permet des mises à jour et une maintenance plus rapides, mais c'est au prix d'une table plus volumineuse (sur disque et dans le cache des relations de PostgreSQL).

Et du coup, vous vous demandez peut-être quand va sortir la version beta 0.3 du livre ? D'ici peu a priori. Un peu de relecture, quelques ajustements de dernières minutes, et ça devrait être prêt :)

Version 0.2 de mon livre sur PostgreSQL

La version 0.2 de mon livre est sortie hier. En dehors des ajouts/correctifs dans les précédents chapitres, elle ajoute deux nouveaux chapitres.

Le premier concerne le protocole de communication client/serveur de PostgreSQL. Il permet de bien prendre conscience du dialogue et des possibilités d'échange entre ces deux entités. Le second aborde la question des connexions : comment s'établie une connexion, quels paramètres de configuration existent pour les connexions, comment gérer les connexions, etc.

De plus, le site d-booker, éditeur du livre, publie une interview de l'auteur (donc moi).

Si vous avez lu le livre « PostgreSQL, architectures et notions avancées », j'aimerais beaucoup savoir ce que vous en avez pensé. N'hésitez pas à intervenir sur le forum pour me remonter vos impressions ou tout problème que vous aurez constaté.

pgstat 1.0.0 is out!

Since the last time I talked about it, I had quite a few feedbacks, bug issues, pull requests, and so on. Many issues were fixed, the last of it tonight.

I also added two new reports. I had the idea while working on my customers' clusters.

One of them had a lot of writes on their databases, and I wanted to know how much writes occured in the WAL files. vmstat would only tell me how much writes on all files, but I was only interested in WAL writes. So I added a new report that grabs the current XLOG position, and diff it with the previous XLOG position. It gives something like this with a pgbench test:

$ ./pgstat -s xlog 
-------- filename -------- -- location -- ---- bytes ----
 00000001000000000000003E   0/3EC49940        1053071680
 00000001000000000000003E   0/3EC49940                 0
 00000001000000000000003E   0/3EC49940                 0
 00000001000000000000003E   0/3EC875F8            253112
 00000001000000000000003E   0/3ED585C8            856016
 00000001000000000000003E   0/3EE36C40            910968
 00000001000000000000003E   0/3EEFCC58            811032
 00000001000000000000003E   0/3EFAB9D0            716152
 00000001000000000000003F   0/3F06A3C0            780784
 00000001000000000000003F   0/3F0E79E0            513568
 00000001000000000000003F   0/3F1354E0            318208
 00000001000000000000003F   0/3F1F6218            789816
 00000001000000000000003F   0/3F2BCE00            814056
 00000001000000000000003F   0/3F323240            418880
 00000001000000000000003F   0/3F323240                 0
 00000001000000000000003F   0/3F323240                 0

That's not big numbers, so it's easy to find it writes at 253K/s, but if the number were bigger, it might get hard to read. One of my co-worker, Julien Rouhaud, added a human readable option:

$ ./pgstat -s xlog -H
-------- filename -------- -- location -- ---- bytes ----
 00000001000000000000003F   0/3F32EDC0      1011 MB
 00000001000000000000003F   0/3F32EDC0      0 bytes
 00000001000000000000003F   0/3F32EDC0      0 bytes
 00000001000000000000003F   0/3F3ABC78      500 kB
 00000001000000000000003F   0/3F491C10      920 kB
 00000001000000000000003F   0/3F568548      858 kB
 00000001000000000000003F   0/3F634748      817 kB
 00000001000000000000003F   0/3F6F4378      767 kB
 00000001000000000000003F   0/3F7A56D8      709 kB
 00000001000000000000003F   0/3F8413D0      623 kB
 00000001000000000000003F   0/3F8D7590      600 kB
 00000001000000000000003F   0/3F970160      611 kB
 00000001000000000000003F   0/3F9F2840      522 kB
 00000001000000000000003F   0/3FA1FD88      181 kB
 00000001000000000000003F   0/3FA1FD88      0 bytes
 00000001000000000000003F   0/3FA1FD88      0 bytes
 00000001000000000000003F   0/3FA1FD88      0 bytes

That's indeed much more readable if you ask me.

Another customer wanted to know how many temporary files were written, and their sizes. Of course, you can get that with the pg_stat_database view, but it only gets added when the query is done. We wanted to know when the query is executed. So I added another report:

$ ./pgstat -s tempfile
--- size --- --- count ---
         0             0
         0             0
  13082624             1
  34979840             1
  56016896             1
  56016896             1
  56016896             1
         0             0
         0             0

You see the file being stored.

Well, that's it for now. The 1.0.0 release is available on the github project.

Durée d'établissement d'une connexion

Ce billet fait partie d'une série sur l'écriture de mon livre, « PostgreSQL - Architecture et notions avancées ».

J'ai toujours eu en tête qu'une connexion mettait du temps à s'établir entre un client et PostgreSQL. J'avais en tête un nombre qui me semblait plausible mais j'avoue que je n'avais jamais fait réellement le test.

Ce week-end, travaillant sur le chapitre sur la gestion des connexions, je me suis demandé si on pouvait calculer ce temps. J'ai donc regardé le code des processus postmaster/postgres pour ajouter quelques traces, histoire d'en savoir plus. Voici le patch que j'ai réalisé :

diff --git a/src/backend/postmaster/postmaster.c b/src/backend/postmaster/postmaster.c
index f05114d..9d8fb8a 100644
--- a/src/backend/postmaster/postmaster.c
+++ b/src/backend/postmaster/postmaster.c
@@ -2198,6 +2198,8 @@ ConnCreate(int serverFd)
 {
        Port       *port;
 
+       elog(LOG, "patch - ConnCreate(%d)", serverFd);
+
        if (!(port = (Port *) calloc(1, sizeof(Port))))
        {
                ereport(LOG,
@@ -3760,6 +3762,8 @@ BackendStartup(Port *port)
        Backend    *bn;                         /* for backend cleanup */
        pid_t           pid;
 
+       elog(LOG, "patch - BackendStart()");
+
        /*
         * Create backend data structure.  Better before the fork() so we can
         * handle failure cleanly.
@@ -3814,6 +3818,8 @@ BackendStartup(Port *port)
 
                MyProcPid = getpid();   /* reset MyProcPid */
 
+               elog(LOG, "patch - new pid is %d", MyProcPid);
+
                MyStartTime = time(NULL);
 
                /* We don't want the postmaster's proc_exit() handlers */
@@ -3916,6 +3922,8 @@ BackendInitialize(Port *port)
        char            remote_port[NI_MAXSERV];
        char            remote_ps_data[NI_MAXHOST];
 
+       elog(LOG, "patch - BackendInitialize()");
+
        /* Save port etc. for ps status */
        MyProcPort = port;
 
@@ -4096,6 +4104,8 @@ BackendRun(Port *port)
        int                     usecs;
        int                     i;
 
+       elog(LOG, "patch - BackendRun()");
+
        /*
         * Don't want backend to be able to see the postmaster random number
         * generator state.  We have to clobber the static random_seed *and* start
diff --git a/src/backend/tcop/postgres.c b/src/backend/tcop/postgres.c
index bc4eb33..4e1a3f7 100644
--- a/src/backend/tcop/postgres.c
+++ b/src/backend/tcop/postgres.c
@@ -3578,6 +3578,7 @@ PostgresMain(int argc, char *argv[],
        sigjmp_buf      local_sigjmp_buf;
        volatile bool send_ready_for_query = true;
 
+       elog(LOG, "patch - PostgresMain()");
        /*
         * Initialize globals (already done if under postmaster, but not if
         * standalone).
@@ -3845,6 +3846,8 @@ PostgresMain(int argc, char *argv[],
         * were inside a transaction.
         */
 
+       elog(LOG, "patch - PostgresMain() - ready to execute command");
+
        if (sigsetjmp(local_sigjmp_buf, 1) != 0)
        {
                /*
@@ -4056,12 +4059,16 @@ PostgresMain(int argc, char *argv[],
                if (ignore_till_sync && firstchar != EOF)
                        continue;
 
+               elog(LOG, "patch - PostgresMain() - processing command");
+
                switch (firstchar)
                {
                        case 'Q':                       /* simple query */
                                {
                                        const char *query_string;
 
+                                       elog(LOG, "patch - PostgresMain() - executing simple query");
+
                                        /* Set statement_timestamp() */
                                        SetCurrentStatementStartTimestamp();
 
@@ -4279,6 +4286,8 @@ PostgresMain(int argc, char *argv[],
                        case 'X':
                        case EOF:
 
+                               elog(LOG, "patch - PostgresMain() - exiting");
+
                                /*
                                 * Reset whereToSendOutput to prevent ereport from attempting
                                 * to send any more messages to client.

En configurant PostgreSQL pour qu'il ajoute la date (à la milliseconde près) et le PID, et en configurant la trace des connexions et déconnexions :

log_min_duration_statement = 0
log_connections = on
log_disconnections = on
log_line_prefix = '%m [%p] '

et en exécutant la commande suivante :

$ psql -c "select * from t1 limit 200" b1

nous obtenons les traces suivantes :

2015-02-22 22:47:23.022 CET [6087] LOG:  patch - ConnCreate(5)
2015-02-22 22:47:23.022 CET [6087] LOG:  patch - BackendStart()
2015-02-22 22:47:23.023 CET [6283] LOG:  patch - new pid is 6283
2015-02-22 22:47:23.023 CET [6283] LOG:  patch - BackendInitialize()
2015-02-22 22:47:23.023 CET [6283] LOG:  connection received: host=[local]
2015-02-22 22:47:23.023 CET [6283] LOG:  patch - BackendRun()
2015-02-22 22:47:23.023 CET [6283] LOG:  patch - PostgresMain()
2015-02-22 22:47:23.025 CET [6283] LOG:  connection authorized: user=postgres database=b1
2015-02-22 22:47:23.027 CET [6283] LOG:  patch - PostgresMain() - ready to execute command
2015-02-22 22:47:23.027 CET [6283] LOG:  patch - PostgresMain() - processing command
2015-02-22 22:47:23.028 CET [6283] LOG:  patch - PostgresMain() - executing simple query
2015-02-22 22:47:23.028 CET [6283] LOG:  duration: 0.691 ms  statement: select * from t1 limit 200
2015-02-22 22:47:23.736 CET [6283] LOG:  patch - PostgresMain() - processing command
2015-02-22 22:47:23.736 CET [6283] LOG:  patch - PostgresMain() - exiting
2015-02-22 22:47:23.737 CET [6283] LOG:  disconnection: session time: 0:00:00.913 user=postgres database=b1 host=[local]

Autrement dit, il faut compter quelques millisecondes pour établir une connexion sans pooler. Après différents tests (impliquant notamment pgbench), le pire que j'ai vu est 10 millisecondes. Pas bien méchant quand on y pense. J'ai aussi noté que la toute première connexion était bien plus lente (dans les 40 millisecondes), ce qui reste encore bien loin de ce que j'imaginais.

J'ai aussi testé avec différentes valeurs du shared_buffers car il semblerait que la taille mémoire d'un processus a une importance dans la durée d'exécution de l'appel système fork().

Comme quoi il est vraiment préférable de tout tester pour ne pas avoir d'idées préconçues.

PostgreSQL et la mémoire partagée

Ce billet fait partie d'une série sur l'écriture de mon livre, « PostgreSQL - Architecture et notions avancées ».

Lire la suite...

A new vmstat-like tool for PostgreSQL

The simplest tools are usually the best.

One of the tools I usually need when I go see customers is vmstat. Nothing beats vmstat to give me a real overview of what the server is really doing. This overview gives system metrics, such as CPU usage, and disk usage. That's quite useful to check where the bottleneck comes from.

I wish I had a PostgreSQL tool like that. I wished enough to eventually build it. I call it pgstat because I couldn't find a better name for it.

It's an online command tool that connects to a database and grabs its activity statistics. As PostgreSQL has many statistics, you have a command switch to choose the one you want (-s):

  • archiver for pg_stat_archiver
  • bgwriter for pg_stat_bgwriter
  • connection for connections by type
  • database for pg_stat_database
  • table for pg_stat_all_tables
  • tableio for pg_statio_all_tables
  • index for pg_stat_all_indexes
  • function for pg_stat_user_function
  • statement for pg_stat_statements
  • pbpools for pgBouncer pools statistics
  • pbstats for pgBouncer general statistics

It looks a lot like vmstat. You ask it the statistics you want, and the frequency to gather these statistics. Just like this:

$ pgstat -s connection
 - total - active - lockwaiting - idle in transaction - idle -
    1546       15             0                     0   1531  
    1544       17             0                     0   1527  
    1544       14             0                     0   1530  
    1546       26             0                     0   1520  
    1543       21             0                     0   1522 

Yeah, way too many idle connections. Actually, way too many connections. Definitely needs a pooler there.

This is what happens on a 10-secondes 10-clients pgbench test:

$ pgstat -s database 1
- backends - ------ xacts ------ -------------- blocks -------------- -------------- tuples -------------- ------ temp ------ ------- misc --------
                commit rollback     read    hit read_time write_time      ret    fet    ins    upd    del    files     bytes   conflicts deadlocks
         1      224041       17    24768 2803774         0          0   4684398 234716 2105701  16615    113        1  14016512           0         0
         1           0        0        0      0         0          0        0      0      0      0      0        0         0           0         0
         1           3        0        0    205         0          0       92     92      0      0      0        0         0           0         0
        11          20        0        0    500         0          0     1420    184      0      1      0        0         0           0         0
        11          69        0        1   4438         0          0     1736    986     68    204      0        0         0           0         0
        11         136        0       12   4406         0          0     1767    270    135    405      0        0         0           0         0
        11         108        0        0   3434         0          0     1394    214    107    321      0        0         0           0         0
        11          96        0        0   3290         0          0     1240    190     95    285      0        0         0           0         0
        11         125        0        0   4045         0          0     1620    248    124    372      0        0         0           0         0
        11         126        0        0   4222         0          0     1628    250    125    375      0        0         0           0         0
        11         111        0        0   3644         0          0     1436    220    110    330      0        0         0           0         0
        11          78        0        0   2549         0          0     1918    161     75    225      0        0         0           0         0
        11         118        0        0   3933         0          0     1524    234    117    351      0        0         0           0         0
         1         130        0        0   4276         0          0     1685    258    129    387      0        0         0           0         0
         1           1        0        0      0         0          0        0      0      0      0      0        0         0           0         0
         1           1        0        0      0         0          0        0      0      0      0      0        0         0           0         0
         1           1        0        0      0         0          0        0      0      0      0      0        0         0           0         0

You clearly see when it starts, when it stops, and what it did during the 10 seconds. Here is what happens at the tables level:

$ pgstat -s table -d b1 1
-- sequential -- ------ index ------ ----------------- tuples -------------------------- -------------- maintenance --------------
   scan  tuples     scan  tuples         ins    upd    del hotupd   live   dead analyze   vacuum autovacuum analyze autoanalyze
  68553  1467082   264957  266656      7919869  59312    113  57262 4611779   3782   5401      22         10       4          22
      3     430        0       0           0      0      0      0      0      0      0       0          0       0           0
      3     430        0       0           0      0      0      0      0      0      0       0          0       0           0
    231    2351     1116    1222          61    184      0    180     61    124    245       2          0       0           0
    431    1750      240     240         120    360      0    358    120    242    480       0          0       0           0
    385    1640      220     220         110    330      0    327    110     11    440       0          0       0           0
    340    1475      190     190          95    285      0    285     95    189    380       0          0       0           0
    398    1651      222     222         111    333      0    331    111     -2    444       0          0       0           0
    353    1519      198     198          99    297      0    293     99    200    396       0          0       0           0
    335    1453      186     186          93    279      0    274     93   -210    372       0          0       0           0
    446    1838      256     256         128    384      0    381    128    104    512       0          0       0           0
    425    1739      238     238         119    357      0    354    119    241    476       0          0       0           0
    360    1552      204     204         102    306      0    305    102    -10    408       0          0       0           0
    386    1629      218     218         109    327      0    325    109     57    436       0          0       0           0
    437    1761      242     242         121    363      0    363    121   -292    484       0          0       0           0
    373    1563      206     206         103    309      0    305    103     -1    412       0          0       0           0
    323    1442      184     184          92    276      0    273     92    188    368       0          0       0           0
    412    1706      232     232         116    348      0    346    116     76    464       0          0       0           0
    291    1332      164     164          82    246      0    245     82   -216    328       0          0       0           0
    189    1013      106     106          53    159      0    158     53    106    212       0          0       0           0
    346    1508      196     196          98    294      0    290     98    -18    392       0          0       0           0
    304    1376      172     172          86    258      0    258     86   -156    344       0          0       0           0
    442    1794      248     248         124    372      0    368    124   -260    496       0          0       0           0
      9    1371      157     260           0     13      0     13 -11602   -329  -6053       0          2       0           3
      3     430        0       0           0      0      0      0      0      0      0       0          0       0           0
      3     430        0       0           0      0      0      0      0      0      0       0          0       0           0

You can alsop filter by table name with the -f command line switch:

$ pgstat -s table -d b1 -f pgbench_history 1
-- sequential -- ------ index ------ ----------------- tuples -------------------------- -------------- maintenance --------------
   scan  tuples     scan  tuples         ins    upd    del hotupd   live   dead analyze   vacuum autovacuum analyze autoanalyze
      0       0        0       0       21750      0      0      0   2022      0      0       1          0       1           7
      0       0        0       0           0      0      0      0      0      0      0       0          0       0           0
      0       0        0       0          64      0      0      0     64      0     64       0          0       0           0
      0       0        0       0         122      0      0      0    122      0    122       0          0       0           0
      0       0        0       0         106      0      0      0    106      0    106       0          0       0           0
      0       0        0       0          99      0      0      0     99      0     99       0          0       0           0
      0       0        0       0          88      0      0      0     88      0     88       0          0       0           0
      0       0        0       0         116      0      0      0    116      0    116       0          0       0           0
      0       0        0       0          99      0      0      0     99      0     99       0          0       0           0
      0       0        0       0          61      0      0      0     61      0     61       0          0       0           0
      0       0        0       0          42      0      0      0     42      0     42       0          0       0           0
      0       0        0       0         106      0      0      0    106      0    106       0          0       0           0
      0       0        0       0          55      0      0      0     55      0     55       0          0       0           0
      0       0        0       0         121      0      0      0    121      0    121       0          0       0           0
      0       0        0       0          68      0      0      0  -1942      0  -1011       0          0       0           1
      0       0        0       0          99      0      0      0     99      0     99       0          0       0           0
      0       0        0       0         109      0      0      0    109      0    109       0          0       0           0
      0       0        0       0          94      0      0      0     94      0     94       0          0       0           0
      0       0        0       0         120      0      0      0    120      0    120       0          0       0           0
      0       0        0       0         110      0      0      0    110      0    110       0          0       0           0
      0       0        0       0         100      0      0      0    100      0    100       0          0       0           0
      0       0        0       0         115      0      0      0    115      0    115       0          0       0           0
      0       0        0       0           0      0      0      0      0      0      0       0          0       0           0
      0       0        0       0           0      0      0      0      0      0      0       0          0       0           0

We see that the activity on this table is quite different from what happens to the other tables.

Today, I added reporting from the pg_stat_statements extension. It works pretty well:

$ pgstat -s statement -d b1
--------- misc ---------- ----------- shared ----------- ----------- local ----------- ----- temp ----- -------- time --------
  calls      time   rows      hit   read  dirty written      hit   read  dirty written    read written        read   written
 383843   1756456.50 13236523   9277049  38794  50915    1640   1008844  17703   8850    8850    1711    1711        0.00      0.00
      1     0.75      1        0      0      0       0        0      0      0       0       0       0        0.00      0.00
      1     0.50      1        0      0      0       0        0      0      0       0       0       0        0.00      0.00
      1     0.75      1        0      0      0       0        0      0      0       0       0       0        0.00      0.00
    310   2709.88    220     1527     10     63       0        0      0      0       0       0       0        0.00      0.00
    797   8555.00    569     3736     10    109       0        0      0      0       0       0       0        0.00      0.00
    725   9215.25    519     3610     23    115       0        0      0      0       0       0       0        0.00      0.00
    266   7729.38    190     1257      2     43       0        0      0      0       0       0       0        0.00      0.00
    831   10196.12    594     3988     11    112       0        0      0      0       0       0       0        0.00      0.00
    788   8678.38    563     3803      8     92       0        0      0      0       0       0       0        0.00      0.00
    736   9080.62    526     3616      7     89       0        0      0      0       0       0       0        0.00      0.00
    792   8395.50    566     3742     11     96       0        0      0      0       0       0       0        0.00      0.00
    814   9346.75    582     3985      9     84       0        0      0      0       0       0       0        0.00      0.00
    763   8941.12    545     3799      9     84       0        0      0      0       0       0       0        0.00      0.00
    728   8543.25    520     3549      8     62       0        0      0      0       0       0       0        0.00      0.00
    589   9143.62    421     2812      7     45       0        0      0      0       0       0       0        0.00      0.00
    785   8710.00    561     3788      4     60       0        0      0      0       0       0       0        0.00      0.00
    785   9117.25    561     3885      4     60       0        0      0      0       0       0       0        0.00      0.00
    785   8397.12    561     3788      1     52       0        0      0      0       0       0       0        0.00      0.00
    799   9398.12    571     3925      7     60       0        0      0      0       0       0       0        0.00      0.00
    765   9033.88    547     3757      3     43       0        0      0      0       0       0       0        0.00      0.00
    805   8663.25    575     3886      6     57       0        0      0      0       0       0       0        0.00      0.00
    765   8490.50    547     3661      7     39       0        0      0      0       0       0       0        0.00      0.00
    764   8850.00    546     3698      4     41       0        0      0      0       0       0       0        0.00      0.00
    396   6706.50    283     1992      1     14       0        0      0      0       0       0       0        0.00      0.00
      1     0.38      1        0      0      0       0        0      0      0       0       0       0        0.00      0.00
      1     0.62      1        0      0      0       0        0      0      0       0       0       0        0.00      0.00

Of course, it first searchs for the extension, and complains if it isn't there:

$ pgstat -s statement -d b2
pgstat: Cannot find the pg_stat_statements extension.

I'll continue to work on this little tool. It definitely needs more love: better code, and lots of documentation. But it's still a nice tool to have when you work on a PostgreSQL server.

If you want to test it, go to my github page, download the tool, and compile it. Let me know what you think of it on my email address (guillaume@lelarge.info).

PostgreSQL - Architecture et notions avancées

Et voilà, j'ai fini par le faire. J'ai fini par me convaincre d'écrire un livre complet sur PostgreSQL.

C'est quelque chose qui me trottait dans la tête depuis longtemps. Surtout depuis que Thomas Reiss m'avait montré le Concept Guide d'Oracle. Je m'étais dit à ce moment-là : mince, c'est ça qu'il manque à PostgreSQL. Mais bon, ça demande beaucoup de temps, beaucoup de travail. Je n'avais pas très envie de me jeter là-dedans, même si je savais que certains éditeurs cherchaient des auteurs pour des livres sur PostgreSQL.

J'ai eu la chance de connaître Patricia Montcorgé lors de la traduction du livre de Greg Smith sur les performances avec PostgreSQL. Si bien que, après avoir lu le livre de Brendan Gregg sur la recherche de performances (Systems Performance: Enterprise and the Cloud, excellent livre, à mettre entre toutes les mains), je lui ai proposé deux projets : la traduction de ce livre et l'écriture d'un livre sur PostgreSQL. Elle m'a appris qu'elle avait fondé sa propre maison d'édition, qu'elle ne s'occupait plus de traductions, et que le livre sur PostgreSQL pourrait l'intéresser. On s'est rencontré, et, après lui avoir expliqué plus en profondeur mon projet, elle a pu m'expliquer comment elle voyait le travail avec elle. J'ai trouvé que ça ressemblait beaucoup à un projet libre, avec une version beta, du git, du docbook, des mises à jour facile, etc. Bref, j'étais bien accroché.

Je travaille sur ce livre depuis avril 2014. On a déjà subi un gros retard à cause du chapitre sur les processus : étudier le code de chaque processus a été particulièrement long. Mais bon, on a maintenant un rythme de croisière intéressant. On s'est décidé sur un chapitre tous les 4 à 6 semaines.

La version beta est sortie aujourd'hui. Elle est disponible par module ou complète, uniquement en version électronique pour le moment (jusqu'à la version finale en fait). Les chapitres seront livrés au fur et à mesure de leur écriture. Un forum est disponible pour les lecteurs qui voudraient laisser des commentaires ou des demandes ou des corrections.

La version finale sera mise à jour pour intégrer les changements effectués par la version 9.5.

Bon, je retourne bosser sur le livre :)

Preneur de tout commentaire par mail sur guillaume@lelarge.info.

Manuel de la 9.4 et articles GLMF

Trois billets en 2014 (avec celui-là), j'aurais fait fort :)

Néanmoins, quelques informations importantes.

Tout d'abord, la documentation de PostgreSQL 9.4 est enfin disponible entièrement en français, avec les différents formats habituels. C'est comme d'habitude sur http://docs.postgresql.fr.

Ensuite, au niveau des articles pour le GNU/Linux Magazine France, deux articles sont parus sur la version 9.4, un peu avant la sortie (les numéros 175 et 177). La suite de la série sur le planificateur est prévue rapidement avec un article sur les jointures. Il s'agit donc du deuxième article. Les troisième et quatrième articles sont déjà dans le bureau du rédacteur en chef (depuis août en fait). Ils devraient sortir rapidement après, suivant la place disponible dans le magazine.

Et voilà. L'année 2015 devrait être très intéressante :) Quelques infos plus tard à ce sujet.

Nouvelle série d'articles pour GLMF

Après l'article sur les nouveautés de la version 9.3, j'ai entamé une série d'articles sur le fonctionnement du planificateur (aussi appelé optimiseur).

Le premier est sorti ce mois-ci, dans le numéro 170 de GNU/Linux Magazine France. Il traite des différents types de parcours que l'optimiseur peut planifier.

J'ai loupé le coche pour le prochain numéro, donc celui sur les jointures devrait sortir dans le numéro 172 si tout va bien. En fait, je viens tout juste de finir son écriture.

Il y aura certainement deux autres articles, un sur les autres types de nœuds et un sur les outils pour la compréhension des plans d'exécution mais ils restent à écrire.

En attendant, je suis preneur de toute remarque/critique sur mes articles :)

Deux news... (GLMF + traduction)

Ça fait un bon moment que je n'ai pas publié un billet sur ce blog... ça fait peur :)

Bref, deux nouvelles intéressantes.

J'ai mis à jour les manuels français suite aux dernières versions mineures. Ça n'a pas été spécialement long de le faire. Par contre, il n'a pas été simple de trouver le temps pour le faire. Mais bon, c'est fait, les manuels sont à jour.

Il y avait aussi longtemps que je n'avais pas écrit un article pour le GNU/Linux Magazine France. J'ai enfin repris, avec un article sur les nouveautés de la version 9.3. Il est paru sur le GLMF 169. Je pense qu'il y aura d'autres articles, suivant le temps à ma disposition et la motivation que j'ai. Pour l'instant, j'essaie d'écrire sur le planificateur de requêtes, sujet que j'étudie depuis plus d'un an maintenant. Cela étant dit, si vous avez des idées de sujets, je suis preneur :)

Manuels de PostgreSQL en PDF

J'ai fini par m'y remettre. Depuis que je suis passé d'Ubuntu à Fedora, je ne pouvais plus générer le manuel de PostgreSQL au format PDF. Cet après-midi, n'arrivant pas à me mettre à autre chose, je me suis collé à ça.

Pour réussir à générer, j'ai récupéré la dernière version de docbook-xsl (1.78.1), puis j'ai modifié le fichier /opt/docbook-xsl/profiling/profile-mode.xsl pour mettre en commentaire la ligne 214. Ensuite, j'ai modifié le fichier stylesheets/pg-profile.xsl des sources de la documentation pour remplacer l'appel à /opt/docbook-xsl/profiling/profile-mode-pdf.xsl par un appel à /opt/docbook-xsl/profiling/profile-mode.xsl. Enfin, j'ai exporté deux variables :

export PATH=/home/guillaume/bin/fop-0.20.5:$PATH
export JAVA_HOME=/usr

Et, pas de soucis pour générer le PDF des versions 8.4 et 9.0. La 9.1 continue à me donner du fil à retordre mais rien de bien méchant. C'est simplement (très) long car il faut tester la génération, parfois plusieurs fois, pour trouver et comprendre chaque problème rencontré.

Mais bon, ça va venir. Ce n'est plus qu'une question de jours...

Just a few hours left to submit your talks to pgconf.eu 2013

Quick blog post to remind you that you still have a few hours to submit your talks to pgconf.eu if you haven't done so yet. Read our call for paper, and submit your proposals to be part of an awesome PostgreSQL event :)

Expose the estimation of number of changed tuples since last analyze

This is the commit message of commit c87ff71f374652936a6089215a30998492b14d52 in the PostgreSQL git repository. This new feature is really interesting. It was written by Mark Kirkwood, reviewed by Laurenz Albe, and commited by Magnus Hagander.

It allows one to see when the autovacuum will fire an ANALYZE on a table. This is extremely useful. At least, to me, when I do a training course. Before, you only had dead_tuples in pg_stat_all_tuples to guess when autovacuum will fire a VACUUM. You had nothing to guess when it will fire an ANALYZE. As the commit message says, this information is available in PostgreSQL but not publicly available. This new function makes it available for the next PostgreSQL release. The PostgreSQL project only adds new features in the development branch of PostgreSQL. So the older releases won't have it. Unless you have an extension which will do the same thing.

That's what I did today: add the function in an extension, and add the extension on pgxn.org so that anyone can install it.

It's actually quite simple to install. The easier way is to use pgxnclient:

[guillaume@laptop ~]$ pgxnclient install mods_since_analyze
INFO: best version: mods_since_analyze 1.0.0
INFO: saving /tmp/tmpTHjC3c/mods_since_analyze-1.0.0.zip
INFO: unpacking: /tmp/tmpTHjC3c/mods_since_analyze-1.0.0.zip
INFO: building extension
cp mods_since_analyze.sql mods_since_analyze--1.0.sql
sed 's/DROP /ALTER EXTENSION mods_since_analyze ADD /' uninstall_mods_since_analyze.sql > mods_since_analyze--unpackaged--1.0.sql
gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -fpic -I. -I. -I/opt/postgresql-9.2/include/server -I/opt/postgresql-9.2/include/internal -D_GNU_SOURCE -I/usr/include/libxml2   -c -o mods_since_analyze.o mods_since_analyze.c
gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -fpic -L/opt/postgresql-9.2/lib -Wl,--as-needed -Wl,-rpath,'/opt/postgresql-9.2/lib',--enable-new-dtags  -shared -o mods_since_analyze.so mods_since_analyze.o
sed 's/EXTVERSION/1.0/;s/EXTENSION/mods_since_analyze/;s/EXTCOMMENT/Expose the estimation of number of changed tuples since last analyze/' mods_since_analyze.control.in > mods_since_analyze.control
rm mods_since_analyze.o
INFO: installing extension
[sudo] password for guillaume: 
/usr/bin/mkdir -p '/usr/share/pgsql/extension'
/usr/bin/mkdir -p '/usr/share/pgsql/mods_since_analyze'
/usr/bin/mkdir -p '/usr/lib64/pgsql'
/bin/sh /usr/lib64/pgsql/pgxs/src/makefiles/../../config/install-sh -c -m 644 ./mods_since_analyze.control '/usr/share/pgsql/extension/'
/bin/sh /usr/lib64/pgsql/pgxs/src/makefiles/../../config/install-sh -c -m 644 ./mods_since_analyze--unpackaged--1.0.sql ./mods_since_analyze--1.0.sql  '/usr/share/pgsql/mods_since_analyze/'
/bin/sh /usr/lib64/pgsql/pgxs/src/makefiles/../../config/install-sh -c -m 755  mods_since_analyze.so '/usr/lib64/pgsql/'

Done. Easy enough, right? :)

If you don't have pgxnclient, this is still quite easy but you need to do it manually:

  1. Download mods_since_analyze (mods_since_analyze-1.0.0.zip)
  2. Unzip the downloaded file
  3. Compile it
  4. Install it

In other words:

[guillaume@laptop ~]$ wget http://api.pgxn.org/dist/mods_since_analyze/1.0.0/mods_since_analyze-1.0.0.zip
--2013-07-07 18:34:09--  http://api.pgxn.org/dist/mods_since_analyze/1.0.0/mods_since_analyze-1.0.0.zip
Resolving api.pgxn.org (api.pgxn.org)... 88.198.47.126
Connecting to api.pgxn.org (api.pgxn.org)|88.198.47.126|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: 5686 (5.6K) [application/zip]
Saving to: ‘mods_since_analyze-1.0.0.zip’

100%[========================================================================================>] 5,686       --.-K/s   in 0.02s   

2013-07-07 18:34:10 (271 KB/s) - ‘mods_since_analyze-1.0.0.zip’ saved [5686/5686]

[guillaume@laptop ~]$ unzip mods_since_analyze-1.0.0.zip 
Archive:  mods_since_analyze-1.0.0.zip
cfe2bbb6ea83d02d402024821696aef7de01d7be
   creating: mods_since_analyze-1.0.0/
  inflating: mods_since_analyze-1.0.0/.gitignore  
  inflating: mods_since_analyze-1.0.0/AUTHORS  
  inflating: mods_since_analyze-1.0.0/COPYING  
  inflating: mods_since_analyze-1.0.0/INSTALL  
  inflating: mods_since_analyze-1.0.0/META.json  
  inflating: mods_since_analyze-1.0.0/Makefile  
  inflating: mods_since_analyze-1.0.0/README.md  
 extracting: mods_since_analyze-1.0.0/TODO  
  inflating: mods_since_analyze-1.0.0/mods_since_analyze.c  
  inflating: mods_since_analyze-1.0.0/mods_since_analyze.control.in  
  inflating: mods_since_analyze-1.0.0/mods_since_analyze.sql  
  inflating: mods_since_analyze-1.0.0/uninstall_mods_since_analyze.sql  
[guillaume@laptop ~]$ cd mods_since_analyze-1.0.0/
[guillaume@laptop mods_since_analyze-1.0.0]$ make && make install
cp mods_since_analyze.sql mods_since_analyze--1.0.sql
sed 's/DROP /ALTER EXTENSION mods_since_analyze ADD /' uninstall_mods_since_analyze.sql > mods_since_analyze--unpackaged--1.0.sql
gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -fpic -I. -I. -I/opt/postgresql-9.2/include/server -I/opt/postgresql-9.2/include/internal -D_GNU_SOURCE -I/usr/include/libxml2   -c -o mods_since_analyze.o mods_since_analyze.c
gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -fpic -L/opt/postgresql-9.2/lib -Wl,--as-needed -Wl,-rpath,'/opt/postgresql-9.2/lib',--enable-new-dtags  -shared -o mods_since_analyze.so mods_since_analyze.o
sed 's/EXTVERSION/1.0/;s/EXTENSION/mods_since_analyze/;s/EXTCOMMENT/Expose the estimation of number of changed tuples since last analyze/' mods_since_analyze.control.in > mods_since_analyze.control
rm mods_since_analyze.o
/usr/bin/mkdir -p '/opt/postgresql-9.2/share/extension'
/usr/bin/mkdir -p '/opt/postgresql-9.2/share/mods_since_analyze'
/usr/bin/mkdir -p '/opt/postgresql-9.2/lib'
/bin/sh /opt/postgresql-9.2/lib/pgxs/src/makefiles/../../config/install-sh -c -m 644 ./mods_since_analyze.control '/opt/postgresql-9.2/share/extension/'
/bin/sh /opt/postgresql-9.2/lib/pgxs/src/makefiles/../../config/install-sh -c -m 644 ./mods_since_analyze--unpackaged--1.0.sql ./mods_since_analyze--1.0.sql  '/opt/postgresql-9.2/share/mods_since_analyze/'
/bin/sh /opt/postgresql-9.2/lib/pgxs/src/makefiles/../../config/install-sh -c -m 755  mods_since_analyze.so '/opt/postgresql-9.2/lib/'

Then you'll have to activate it on the database where you want to use it. You simply need to execute CREATE EXTENSION:

tests=# CREATE EXTENSION mods_since_analyze;
CREATE EXTENSION

It will add a single function:

tests=# \dx+ mods_since_analyze
  Objects in extension "mods_since_analyze"
             Object Description              
---------------------------------------------
 function pg_stat_get_mod_since_analyze(oid)
(1 row)

This function returns the number of changed tuples since the last ANALYZE (manual or automatic).

tests=# CREATE TABLE t1(id integer);
CREATE TABLE
tests=# SELECT now(), pg_stat_get_last_autoanalyze_time('t1'::regclass),
  pg_stat_get_last_analyze_time('t1'::regclass), pg_stat_get_mod_since_analyze('t1'::regclass);
-[ RECORD 1 ]---------------------+-----------------------------
now                               | 2013-07-07 22:32:41.25594+02
pg_stat_get_last_autoanalyze_time | 
pg_stat_get_last_analyze_time     | 
pg_stat_get_mod_since_analyze     | 0

tests=# INSERT INTO t1 SELECT generate_series(1, 1000000);
INSERT 0 1000000
tests=# SELECT now(), pg_stat_get_last_autoanalyze_time('t1'::regclass),
  pg_stat_get_last_analyze_time('t1'::regclass), pg_stat_get_mod_since_analyze('t1'::regclass);
-[ RECORD 1 ]---------------------+------------------------------
now                               | 2013-07-07 22:33:00.408815+02
pg_stat_get_last_autoanalyze_time | 
pg_stat_get_last_analyze_time     | 
pg_stat_get_mod_since_analyze     | 1000000

tests=# SELECT now(), pg_stat_get_last_autoanalyze_time('t1'::regclass),
  pg_stat_get_last_analyze_time('t1'::regclass), pg_stat_get_mod_since_analyze('t1'::regclass);
-[ RECORD 1 ]---------------------+------------------------------
now                               | 2013-07-07 22:33:04.569035+02
pg_stat_get_last_autoanalyze_time | 
pg_stat_get_last_analyze_time     | 
pg_stat_get_mod_since_analyze     | 1000000

tests=# ANALYZE;
ANALYZE
tests=# SELECT now(), pg_stat_get_last_autoanalyze_time('t1'::regclass),
  pg_stat_get_last_analyze_time('t1'::regclass), pg_stat_get_mod_since_analyze('t1'::regclass);
-[ RECORD 1 ]---------------------+------------------------------
now                               | 2013-07-07 22:33:09.00094+02
pg_stat_get_last_autoanalyze_time | 
pg_stat_get_last_analyze_time     | 2013-07-07 22:33:07.096124+02
pg_stat_get_mod_since_analyze     | 0

tests=# INSERT INTO t1 SELECT generate_series(1, 1000000);
INSERT 0 1000000
tests=# DELETE FROM t1 WHERE id<100000;
DELETE 199998
tests=# SELECT now(), pg_stat_get_last_autoanalyze_time('t1'::regclass),
  pg_stat_get_last_analyze_time('t1'::regclass), pg_stat_get_mod_since_analyze('t1'::regclass);
-[ RECORD 1 ]---------------------+------------------------------
now                               | 2013-07-07 22:33:32.42597+02
pg_stat_get_last_autoanalyze_time | 
pg_stat_get_last_analyze_time     | 2013-07-07 22:33:07.096124+02
pg_stat_get_mod_since_analyze     | 1199998

tests=# SELECT now(), pg_stat_get_last_autoanalyze_time('t1'::regclass),
  pg_stat_get_last_analyze_time('t1'::regclass), pg_stat_get_mod_since_analyze('t1'::regclass);
-[ RECORD 1 ]---------------------+------------------------------
now                               | 2013-07-07 22:34:52.344178+02
pg_stat_get_last_autoanalyze_time | 2013-07-07 22:34:15.050458+02
pg_stat_get_last_analyze_time     | 2013-07-07 22:33:07.096124+02
pg_stat_get_mod_since_analyze     | 0

It works from the 9.0 release to the 9.3 release.

Hope you'll enjoy it.

Plus que 20 jours pour proposer une conférence à pgconf.eu 2013

L'appel à conférencier est disponible depuis un moment. Du coup, il ne reste plus que 20 jours pour proposer une conférence à pgconf.eu 2013. Cela étant dit, ne désespérez pas, il vous reste un peu de temps :)

Ne pensez pas que vous n'avez rien d'intéressant à raconter. Votre expérience, vos compétences, vos outils sont autant de sujets passionnants de conférences.

Bref, n'hésitez pas à proposer une conférence. Et de toute façon, venez participer à cet événement. Les conférences, les tutoriels, les rencontres avec les contributeurs sont des raisons amplement suffisantes pour venir à Dublin.

- page 1 de 69