Blog entries

  • Compte rendu PGDay France 2013 (Nantes) - partie 1/2

    2013/07/01 by Arthur Lutz

    Quelques personnes de Logilab ont assisté aux PGDay 2013 à Nantes. Voici quelques points qui nous ont marqués.

    http://www.cubicweb.org/file/2932005/raw/hdr_left.png

    Gestion de la capacité des ressources mémoire d'un serveur PostgreSQL par Cédric Villemain

    Cédric Villemain nous a exposé plusieurs pistes d'investigation de la gestion mémoire de Postgresql.

    On peut employer des outils Linux tels que vmstat, pmap, meminfo, numactl, mais aussi des outils spécifiques à Postgresql, tels que pg_stat (hit ratio), pg_buffercache (audit de la mémoire cache), pgfincore (audit du cache de l'OS).

    Il faut mettre des sondes sur les tables et indexes critiques de maniĂšre Ă  avoir une idĂ©e du fonctionnement "normal" pour ensuite dĂ©tecter le fonctionnement "anormal". À Logilab, nous utilisons beaucoup munin, pour lequel plusieurs greffons Postgresql sont disponibles : munin pg plugins et pymunin.

    Pour aller plus loin voir le support de présentation (1).

    Les nouveautés de PostgreSQL 9.3 par Damien Clochard

    Damien Clochard a fait une trÚs synthétique présentation des fonctionnalités de la nouvelle version de PostgreSQL 9.3. Le cycle de release de Postgresql dure 1 an, donc la periode de beta est courte, il faut que la communauté soit impliquée pour tester rapidement. Damien en profite pour chanter les louanges de PostgreSQL, qui est, selon lui, le SGBD le plus dynamique au monde: 1 version majeure par an, 4-5 versions mineures par an, et un support de 5 ans des versions majeures.

    Actuellement, cela signifie que 5 versions majeures sont maintenues (notamment en terme de sécurité) en parallÚle : 8.4, 9.0, 9.1, 9.2, 9.3beta1. Notons donc que la version 9.3 qui sort bientÎt sera donc supportée jusqu'à 2018.

    http://www.logilab.org/file/150442/raw/elephant.png

    Pour les nouveautés, difficiles à résumer, notons néanmoins :

    • des gains de performance,
    • des verrous possibles sur les clĂ©s Ă©trangĂšres,
    • une gestion mĂ©moire plus fine,
    • la possibilitĂ© de faire des pg_dump en parallĂšle (--jobs),
    • scĂ©narios supplĂ©mentaires de rĂ©plication,
    • possibilitĂ© de "bascule rapide" en architecture rĂ©pliquĂ©e,
    • facilitation de mise en place d'un serveur clone (gĂ©nĂ©ration automatique du fichier recovery.conf),
    • vue matĂ©rialisĂ©es,
    • opĂ©rateurs supplĂ©mentaires pour JSON (citation "MongoDB avec la tranquilitĂ© (ACID)"),
    • les requĂštes LATERAL
    • extensibilitĂ© avec des processus supplĂ©mentaires permettant des opĂ©rations de maintenance, de supervision ou d'optimisation,
    • des backends supplĂ©mentaires pour les "Foreign Data Wrappers" (introduits en 9.1),
    • possibilitĂ© de sĂ©parer le fichier de configuration en plusieurs sous-fichiers (utile pour une pilotage par SaltStack par exemple).

    Damien en a profité pour parler un peu des points forts prévus pour la version 9.4 :

    • simplification de la montĂ©e en charge,
    • rĂ©plication logique (rĂ©pliquer une seule table par exemple),
    • parallĂ©lisation des requĂȘtes (multi-coeurs),
    • stockages internes

    En bref, concis et alléchant. Vivement qu'on ait cette version en production.

    En attendant on a profité pour l'installer à partir des entrepÎts Debian gérés par la communauté Postgresql.

    Pour aller plus loin voir le support de présentation (2).

    "Ma base de données tiendrait-elle la charge ?" par Philippe Beaudouin

    Philippe Beaudoin a utilisĂ© pour cette analyse une combinaison de pgbench (injection), et la table pg_stat_statements qui collecte les statistiques sur l'utilisation mĂ©moire des requĂȘtes : produit une table avec les query, nombre d'appels, temps passĂ©, nombre de lignes, etc.

    L'idée générale est d'établir un profil de charge transactionnel sur la production pour pouvoir comparer avec la pré-production ou la future plateforme.

    Pour éviter de devoir copier les données de production (lent, problÚme de confidentialité, etc), il est conseillé d'utiliser "generate_series" pour remplir la base de données de données de test.

    pgbench utilise des scenario TPC-B (Transaction Processing Performance Council Benchmarks) Pour chaque scénario (4 scénarios dans son exemple) on a une cible TPS (transaction par secondes). Il recommande de faire attention à ne pas modifier considérablement la taille de la base avec les scénarios (ex. trop de DELETE, ou trop d'INSERTs).

    Un astuce pour charger le cache linux

    find <files> -exec dd if='{}' of=/dev/null\;
    

    Si on ne sait pas quels fichiers charger, on peut utiliser pg_relation_filepath(oid) FROM pg_class where relname like 'tbl%' pour trouver en SQL quels fichiers contiennent les données.

    Nous avons demandĂ© si un outil de type GOR (flux UDP de la production vers la pre-production ou le serveur de dĂ©veloppement pour les requĂȘtes HTTP) existait pour Postgresql.

    http://www.logilab.org/file/150448/raw/gor.png

    RĂ©ponse : Tsung contient un mode proxy qui permet d'enregistrer la production, ensuite de la rejouer en pre-prod, mais pas en mode live. À priori il serait possible de combiner plusieurs outils existant pour faire cela (pgShark ?). La problĂ©matique n'est pas simple notamment lorsque les bases de donnĂ©es divergent (index, series, etc).

    Pour aller plus loin voir le support de présentation (3).

    PostGIS 2.x et au delĂ  par Hugo Mercier

    Nous avons trouvé la présentation réussie. Elle introduisait les concepts et les nouveautés de PostGIS 2.x. Ayant intégré des fonctionnalités de PostGIS à CubicWeb et travaillant un peu sur la visualisation en WebGL de données stockées dans CubicWeb+Postgresql, nous avons pu réfléchir à des possibilités de délégation de traitement à la base de donnée.

    http://www.logilab.org/file/150441/raw/Screenshot%20from%202013-07-01%2010%3A30%3A00.png

    Nous nous sommes aussi interrogés sur le passage à l'échelle d'applications web qui font de l'affichage de données géographiques, pour éviter d'envoyer au navigateurs un volume de données trop important (geoJSON ou autre). Il y aurait des architectures possible avec une délégation à Postgresql du travail de niveaux de zoom sur des données géographiques.

    Pour aller plus loin voir le support de présentation.


  • Compte rendu PGDay France 2013 (Nantes) - partie 2/2

    2013/07/01 by Arthur Lutz

    Ora2Pg: Migration Ă  Grande Vitesse par Gilles Darold

    L'outil ora2pg permet de jouer une migration d'Oracle Ă  Postgresql. MalgrĂ© notre absence de besoin de ce type de migration, cette prĂ©sentation fut l'occasion de parler d'optimisation diverses qui peuvent ĂȘtre appliquĂ© Ă  des imports massifs tel que nous pouvons en pratiquer Ă  Logilab.

    Par exemple :

    • utilisation prioritaire de COPY plutĂŽt que INSERT,
    • supprimer les indexes/contraintes/triggers/sequences (les crĂ©er en fin d'import),
    • maintenir un _work_mem_ Ă©levĂ© (pour la crĂ©ation des index et contraintes),
    • augmenter les checkpoin_segments (>64/1Gb).

    Quelques rĂ©glages systĂšmes peuvent ĂȘtre mis en place le temps du chargement (on les rebranche une fois que le serveur passe en "production") :

    • fsync=off
    • full_page_writes=off
    • synchronous_commit=off
    • WAL (Write Ahead Log) sur des disques SSD
    • kernel : vm.dirty_background_ratio = 1
    • kernel : vm.dirty_ratio = 2

    Coté Postresql, les paramÚtres suivants sont probablement à modifier (les chiffres sont à titre d'exemple, la configuration matérielle est bien particuliÚre, par exemple 64G de RAM, voir les diapos pour le détail):

    • shared_buffers = 10G
    • maintenacen_work_mem = 2G
    • checkpoint_segments = 61
    • checkpoint_completion_target = 0.9
    • wal_level = minimal
    • archive_mode = off
    • wal_buffer = 32 Mo
    • dĂ©sactiver les logs
    http://ora2pg.darold.net/ora2pg-logo.png

    Pour aller plus loin voir le support de présentation (5).

    Vers le Peta Byte avec PostgreSQL par Dimitri Fontaine

    Dimitri Fontaine a admirablement bien traitĂ© cette question complexe qu'est la montĂ©e Ă  l'Ă©chelle en terme de volume de donnĂ©es. Son approche fut trĂšs didactique, avec, Ă  chaque concept, un rappel de ce dont il s'agisait. Par exemple, parlant du MVCC, il explique qu'il s'agit de plusieurs dĂ©finitions en parallĂšle d'une mĂȘme ligne. Ensuite on dĂ©cide avec VACUUM quelle version doit ĂȘtre visible par tout le monde. Par dĂ©faut AUTOVACCUM se dĂ©clenche quand 20% des donnĂ©es ont changĂ©.

    Face aux difficultĂ©s et aux inconvĂ©nients de stocker la totalitĂ© d'une PetaByte sur un seul serveur, Dimitri Fontaine a Ă©voquĂ© les solutions possible en terme d'architecture distribuĂ©e pour permettre de stocker ce PetaByte sur plusieurs serveurs rĂ©partis. La "Bi-Directional Replication" (qui sera dispo dans le futur) permetterait d'avoir plusieurs bases SQL qui sĂ©parĂ©ment stockent une partie des donnĂ©es (cf EVENT TRIGGERS). Un approche complĂ©mentaire serait d'utiliser plproxy qui par des procĂ©dures stockĂ©es permet de rĂ©partir les requĂȘtes sur plusieurs serveurs.

    http://www.logilab.org/file/150419/raw/Screenshot%20from%202013-07-01%2010%3A25%3A46.png

    Finalement, un point qui nous a paru pertinent : il a parlé de haute disponibilité et des flous techniques qui entourent le sujet. Il faut bien faire la différence entre la disponibilité des données et du service. Par exemple, en WARM STANDBY les données sont disponibles mais il faut redémarrer Postgresql pour fournir le service alors que en HOT STANDBY les données sont disponibles et le serveur peut fournir les services.

    Pour aller plus loin voir le support de présentation (6).

    Comprendre EXPLAIN par Guillaume Lelarge

    Utiliser EXPLAIN permet de dĂ©bugger l’exĂ©cution d'une requĂȘte SQL ou de l'optimiser. On touche assez rapidement au fonctionnement interne de Postgresql qui est relativement bien documentĂ©s. Guillaume Lelarge a donc, Ă  l'aide de nombreux exemples, prĂ©sentĂ© des mĂ©canismes plutĂŽt bas niveau de Postgresql.

    Notons entre autres les différents types de scans dont les noms sont relativement explicites :

    Dans la mĂȘme veine, les types de tris :

    • external sort (sur disque),
    • quicksort (en mĂ©moire).

    Mais aussi, prenez le temps de lire les exemples sur son support de présentation (7)

    Conclusion

    https://www.pgday.fr/_media/pgfr2.png

    PGDay.fr est une conférence que nous pouvons vivement vous recommander, proposant un savant mélange des différentes questions auxquelles nous sommes confrontées lorsque nous travaillons avec des bases de données. Aussi bien en tant qu'administrateur systÚme, développeur, ou utilisateur de SQL. Il va sans dire que le niveau technique était trÚs pointu. Les présentations restaient pourtant accessibles. Les orateurs et organisateurs étaient disponibles pour des interactions, permettant de prolonger la réflexion et les discussions au delà des présentations.

    Nous envisageons d'ores et dĂ©jĂ  d'aller Ă  l'Ă©dition 2014! À l'annĂ©e prochaine...

    http://www.logilab.org/file/150100/raw/2e1ax_default_entry_postgresql.jpg

  • Logilab prĂ©sent Ă  pgDay Toulouse

    2017/06/16 by Philippe Pepiot

    Le 8 juin 2017 nous avons assisté à pgDay, le moment de rencontre et de conférences de la communauté PostgreSQL francophone, qui s'est déroulée au campus de Météo France à Toulouse.

    https://www.logilab.org/file/10126216/raw/logo_pgfr_sans_900_400x400.png

    Partitionement

    Gilles Darold nous a fait un tour d'horizon des solutions de partitionnement, de la méthode manuelle avec des triggers et d'héritage de table en passant par l'extension pg_partman jusqu'au partitionnement déclaratif de la future version 10 avec la syntaxe PARTITION OF

    Le partitionnement permet de gérer plus facilement la maintenance et les performances de tables avec beaucoup d'enregistrements.

    Transaction autonomes

    Le mĂȘme Gilles Darold nous a parlĂ© des transactions autonomes c'est-Ă -dire des transactions qui s'exĂ©cutent dans une transaction parente et qui peut ĂȘtre validĂ©e ou annulĂ©e indĂ©pendamment de celle-ci, ce qui peut ĂȘtre utile pour enregistrer des Ă©vĂ©nements.

    PostgreSQL buffers

    Vik Fearing nous a expliqué le fonctionnement et l'interaction des différents tampons mémoire dans PostgreSQL.

    Les pages sont chargĂ©es du disque vers les shared_buffers, qui sont partagĂ©s par toutes les connexions, et ont un usageCount entre un et cinq qui est incrĂ©mentĂ© Ă  chaque fois qu'elle est accĂ©dĂ©e. Lorsqu'une nouvelle page doit ĂȘtre chargĂ©e, un mĂ©canisme de clock-sweep boucle sur le cache et dĂ©crĂ©mente l'usageCount et quand il vaut zĂ©ro la page est remplacĂ©e par la nouvelle. Ainsi pour une page avec un usageCount Ă  cinq, il faudra au moins cinq tours des shared_buffers par le clock-sweep avant quelle ne soit remplacĂ©e.

    En cas d'un accÚs à une grosse table pour ne pas vider tout le cache, PostgreSQL utilise un tampon circulaire (ou ring buffer) limité en taille pour cette table.

    Les tables temporaires utilisent un tampon dédié, le temp_buffers.

    Quand une page est modifiée, elle l'est d'abord dans les wal buffers qui sont écrits sur disque lors du commit par le processus wal writer.

    Le writer process parcoure les shared_buffers tout les bgwriter_delay (200ms) et écrit sur disque un certain nombre de pages qui ont été modifiées, ce nombre est contrÎlé par les paramÚtres bgwriter_lru_maxpages et bgwriter_lru_multiplier.

    Des checkpoint s'exécutent aussi tout les checkpoint_timeout ou plus fréquemment quand la taille des wals dépasse la valeur du paramÚtre max_wal_size. Lors d'un checkpoint on cherche des pages à écrire (ou dirty pages) et on les trie pour éviter les écritures aléatoires sur le disque. Le paramÚtre checkpoint_completion_target permet d'étaler la vitesse d'écriture entre deux checkpoint. Idéalement on veut qu'ils se déclenchent toujours par timeout et que l'écriture soit la plus étalée pour avoir des performances de lecture et d'écriture constantes.

    Pour déboguer l'utilisation des buffers et les I/O disques il y a la table pg_stat_bgwriter, l'extension pg_buffercache, et le paramÚtre track_io_timing à utiliser avec EXPLAIN (ANALYZE, BUFFERS).

    Les pires pratiques PostgreSQL

    Thomas Reiss et Philippe Beaudoin nous ont prĂ©sentĂ© quelques unes des plus mauvaises pratiques avec PostgreSQL, notamment de celle rĂ©pandue du manque ou d'excĂšs d'index. À ce sujet Dalibo a dĂ©veloppĂ© l'outil PoWA qui analyse l'activitĂ© d'une base et fait des suggestions d'index. Attention aussi Ă  la tentation de (trop) destructurer les donnĂ©es, PostgreSQL possĂšde de nombreux types qui offrent une garantie forte sur la consistance des donnĂ©es et de nombreuses opĂ©rations dessus, par exemple les types ranges.

    La communauté des développeurs de PostgreSQL

    Daniel Vérité nous a fait un historique de Ingres puis Postgres, et enfin PostgreSQL avant de nous montrer des statistiques sur les commits et la liste de diffusion utilisée pour le développement de PostgreSQL

    Les éléphants mangent-ils des cubes ?

    CĂ©dric Villemain nous a parlĂ© des fonctionnalitĂ©s de PostgreSQL pour des requĂȘtes de type OLAP. L'implĂ©mentation de TABLESAMPLE qui permet de faire des requĂȘtes sur un Ă©chantillon alĂ©atoire d'une table. Le paramĂštre default_statistic_target et la nouvelle commande de la version 10 CREATE STATISTICS qui permettent d'obtenir de meilleurs statistiques sur la distribution de la table et donc d'avoir de meilleurs plans d'exĂ©cution.

    Aussi depuis la version 9.4, la syntaxe GROUP BY ROLLUP permet de faire des agrĂ©gats sur plusieurs GROUP BY dans une seule requĂȘte. Auparavant il fallait faire plusieurs UNION pour obtenir le mĂȘme rĂ©sultat.

    À noter aussi l'utilisation d'index BRIN et BLOOM.

    Comment fonctionne la recherche plein texte ?

    Adrien Nayrat nous a présenté les fonctions de recherche plein texte dans PostgreSQL et le moyen de l'améliorer en créant ses propres configurations et dictionnaires de mots, ainsi qu'à la rendre plus performante avec les index GIN et GIST.

    GeoDataScience

    Olivier Courtin nous a montrĂ© avec un exemple concret comment PostgreSQL pouvait ĂȘtre un environnement idĂ©al pour la gĂ©omatique et le machine learning avec les extensions PostGIS, ainsi que plpythonu utilisĂ© pour exĂ©cuter du code python directement sur le serveur PostgreSQL. L'extension dĂ©diĂ©e crankshaft propose des API basĂ©es sur scipy et scikit-learn et peut ĂȘtre appelĂ©e via des procĂ©dures SQL.

    https://www.logilab.org/file/10126217/raw/freefall.gif