PostgreSQL: fermer toutes les connexions sur une base de données

PostgreSQL dispose d'une table d'état très utile appelée pg_stat_activity. Cette table est similaire au "SHOW PROCESSLIST" qu'on retrouve en MySQL, mais a le net avantage d'être requêtable et dispose d'informations plus précises que MySQL.

En voici un extrait:

 12407 | postgres | 51572 | 10 | postgres | | 127.0.0.1 | | 44244 | 2017-07-26 07:41:44.454929+00 | | 2017-07-26 12:48:44.474222+00 | 2017-07-26 12:48:44.474335+00 | | | idle | | | SELECT CASE WHEN pg_is_in_recovery = 'false' THEN 0 ELSE COALESCE(ROUND(EXTRACT(epoch FROM now() - pg_last_xact_replay_timestamp())), 0) END AS seconds FROM pg_is_in_recovery()<br> 349593 | db02 | 51573 | 10 | postgres | | 127.0.0.1 | | 44245 | 2017-07-26 07:41:46.305319+00 | | 2017-07-26 12:48:46.336722+00 | 2017-07-26 12:48:46.33685+00 | | | idle | | | SELECT xact_commit,xact_rollback FROM pg_stat_database WHERE datname=$1;<br> 24816 | db01 | 51575 | 10 | postgres | | 127.0.0.1 | | 44246 | 2017-07-26 07:41:46.588503+00 |

Dans certains cas, il peut être utile de couper toutes les connexions à une base de données précise (par exemple des connexions dormantes en masse). Voici une simple requête SQL à jouer sur votre PostgreSQL (9.2 et plus) permettant de couper toutes les connexions:

SELECT
pg_terminate_backend(pid) FROM
pg_stat_activity
WHERE pid <> pg_backend_pid() AND datname = 'target_database';

Cette requête va lancer un ordre de fermeture sur toutes les connexions de la base target_database excepté la connexion en cours.

Si vous souhaitez uniquement tuer les requêtes inactives vous pouvez utiliser la variante suivante:

SELECT
pg_terminate_backend(pid) FROM
pg_stat_activity
WHERE pid <> pg_backend_pid() AND datname = 'target_database' AND state = 'idle';

PostgreSQL: changer le owner de toutes les tables/séquences d’un schéma

Lors d'une restauration de base de données, parfois il se peut que vous ayez besoin de changer le propriétaire d'une table ou d'une séquence pour un autre user, par exemple si vous prenez une base de production pour la mettre sur votre intégration pour vos développeurs (anonymisées, bien sûr 😉 ).

Plutôt que de devoir faire fastidieusement un ALTER TABLE table par table, voici 2 requêtes SQL qui vont vous permettre de générer les SQL pour changer rapidement le owner de toutes les tables et séquences:

select 'alter table ' || relname || ' owner to mynewowner;' from pg_class join pg_namespace on pg_class.relnamespace = pg_namespace.oid where pg_namespace.nspname = 'public' and relkind='r';
select 'alter sequence ' || relname || ' owner to mynewowner;' from pg_class join pg_namespace on pg_class.relnamespace = pg_namespace.oid where pg_namespace.nspname = 'public' and relkind='S';

Résultat pour les tables

                           ?column?                             
-----------------------------------------------------------------
 alter table batch_job_execution owner to mynewowner;
 alter table purchase owner to mynewowner;
 alter table serviceprice owner to mynewowner;
 alter table account owner to mynewowner;
 alter table product owner to mynewowner;

Résultat pour les séquences:

                           ?column?                            
----------------------------------------------------------------
 alter sequence batch_job_seq owner to mynewowner;
 alter sequence batch_step_execution_seq owner to mynewowner;
 alter sequence cgufile_id_seq owner to mynewowner;

Vous n'avez plus qu'à recopier ce résultat directement ou à l'intégrer dans un script shell qui va le jouer automatiquement pour vous.

PostgreSQL: supprimer toutes les tables d’un schéma rapidement

Parfois, il peut être utile de supprimer toutes les tables d'un schéma directement, notamment si vous souhaitez réimporter un backup depuis un autre environnement. Il peut être fastidieux de faire des DROP TABLE unitaires, heureusement les schémas PostgreSQL respectent la norme SQL. Contrairement à MySQL qui mélange la notion de base de données et schéma, sur PostgreSQL les bases de données peuvent comporter plusieurs schémas. Généralement on ne regarde pas, mais il faut savoir que par défaut les tables se trouvent dans le schéma public. Nous allons exploiter la notion de schéma PostgreSQL pour procéder à ce nettoyage. Pour supprimer toutes les tables de votre schéma, entrez simplement les commandes SQL suivantes:

DROP SCHEMA public CASCADE;
CREATE SCHEMA public;

L'intérêt du mot clef cascade est ici de résoudre les suppressions bloquées par des clefs étrangères. Vous savez maintenant supprimer toutes les tables d'un schéma PostgreSQL simplement.

Optimisations BDD: indexes en doublon

Les indexes prennent de l'espace disque et de la mémoire. Les moteurs MySQL/MariaDB/PostgreSQL savent utiliser des indexes plus complexes afin de lire des données nécessitant un seul index donc les champs conditionnant sont inclus dans un index plus complexe.

Exemple ici avec 3 indexes

struct index_a {
 id_1 int,
 id_2 int,
};

struct index_b {
 id_1 int,
};

struct index_c {
 id_2 int,
};

Si nous faisons une requête filtrant sur le champ _id1 uniquement (SELECT field1 FROM table1 where id_1 = 3), le moteur de BDD est capable d'utiliser 2 indexes: _indexb, qui ne contient que _id1 et _indexa qui contient _id1 puis _id2.

En revanche, si nous faisons une requête filtrant sur le champ _id2 uniquement (SELECT field1 FROM table1 where id_2 = 6), le moteur de BDD ne pourra utiliser que _indexc.

Si nous reprenons _indexa, il n'est pas valide car le moteur de BDD lira cet index en trouvant les records discriminants sur _id1 avant les records discriminant sur _id2. Ce n'est pas un chemin optimal pour trouver un résultat dépendant uniquement de _id2. L'ordre des champs d'un index est donc important.

Pour finir, _indexa filtrant tout d'abord sur _id1 avant de filtrer sur _id2, il sait donc filtrer uniquement sur _id1 et donc _indexb est inutile (il prend des ressources disque et mémoire inutiles). Supprimez donc _indexb qui prend de l'espace disque et mémoire pour rien.

PostgreSQL: nettoyer complètement un schéma sans effort

Dans certains cas, comme par exemple dans un environnement d'intégration, il peut être intéressant de nettoyer toutes entrées dans votre BDD PostgreSQL. Lorsque le nombre de tables devient grand et surtout lorsque vous avez construit un schéma robuste à base de clefs étrangères, il peut être fastidieux de nettoyer ce schéma. Heureusement, il est possible de nettoyer votre base en demandant à PostgreSQL de générer la requête qui va lister les tables du schéma et les nettoyer. La requête ci-dessous va générer la requête de nettoyage des tables dans la base courante du schéma public. Elle ne s'éxecutera pas, il s'agit uniquement d'un résultat SQL sous forme de chaîne.

select 'truncate ' || string_agg(nspname || '.' || relname, ',') || ';' from pg_class join pg_namespace on pg_class.relnamespace = pg_namespace.oid where pg_namespace.nspname = 'public' and relkind='r';

Il ne vous reste plus qu'à exécuter la requête générée. Note: Si vous souhaitez filtrer sur le nom des tables il vous suffit de filtrer sur le champ relname.

Benchmark comparatif: PostGreSQL 9.1

Suite à une volonté de tester l'arbre de dports de DragonFlyBSD, et voulant voir ce que vallaient les performances de PostGreSQL sur DragonFlyBSD j'ai décidé de monter un rapide benchmark de performances afin de comparer plusieurs systèmes ayant une version de PostGreSQL commune. J'ai choisi pour ces raisons PostGreSQL 9.1.

Lors de mon benchmark sous Linux (Debian), je me suis retrouvé confronté à des statistiques étonnantes en terme de performances, de ce fait, j'ai décidé de voir ce que vallait une Redhat, j'ai donc pris Centos 6.4.

Les tests ont été effectués sur les plateformes suivantes:

  • DragonFlyBSD 3.4.1 (système de fichiers Hammer)
  • FreeBSD 9.1-p3 (système de fichiers UFS2+J)
  • FreeBSD 9.1-p3 (système de fichiers ZFS v28)
  • Debian 7: Wheezy (système de fichiers ext4)
  • Centos 6.4 (système de fichiers ext4)

En terme de hardware, le test a été effectué sur un système de virtualisation de type KVM (libvirt) avec 24GBit de mémoire et un processeur Phenom x6 1055T

qemu 1.4.1-3
libvirt 1.0.5-4

Chaque machine virtuelle possède les caractéristiques suivantes:

  • 50Go de disque (virtio pour tous les OS sauf FreeBSD)
  • 12Go de mémoire
  • 4 coeurs de processeur

Passons maintenant au benchmark. La commande utilisée est la suivante: pgbench -T 60 -cX -jX Concrètement, nous demandons à l'utilitaire pgbench de faire un test de 60 secondes sur la base de données, en utilisant X clients et X threads (1 client par thread). Chacune des bases de données est configurée par défaut, avec un support de 300 connexions simultannées.

Première partie: test sur disque virtuel

Le premier graphique représentera le nombre de transations sur la durée, le second par secondes. PGBench1 PGBench2

Le test de performances est surprenant. Nous avons en tête DragonFlyBSD qui surpasse tous les autres systèmes, suivi par FreeBSD. Les performances de DragonFlyBSD sont exceptionnelles, et dépassent de plus de 25% celles de FreeBSD lors de la montée en charge et de près de 200% les Linux.

Loin derrière, avec des performances 2 à 3 fois moindres, nous avons les deux Linux (Debian et Centos) qui plafonnent à près de 7000 transactions sur la durée sans vraiment les dépasser, et peu importe le nombre de clients. Cette courbe est particulièrement étonnante, d'une uniformité sans faille. Seul Debian ne réussit pas à passer le test entièrement en n'arrivant pas à gérer plus de 100 connexions sans devoir à toucher d'autres paramètres.

En fait, cette valeur de 7000 s'explique par l'option barrier du système de fichiers ext4. L'option protège le système de fichiers de la corruption des données en cas de panne matérielle (reboot inopiné...), et bride complètement les performances de PostgreSQL. Dans un second test nous avons ajouté l'option nobarrier/barrier=0 au système de fichiers (via /etc/fstab).

Notez que cette option est risquée, si un redémarrage dû à un problème d'alimentation survient, les fichiers en cours d'écriture sur le disque, voir l'ensemble du système de fichiers peuvent être corrompus. Ne l'utilisez que si vous possédez un contrôleur RAID matériel (Raid 1/5/6) avec une batterie.

Enfin, bon dernier, notre FreeBSD avec ZFS peine à rattraper les Linux. Peut être est-ce dû à la virtualisation ? Ou bien à la conception du système de fichiers lui-même ?

Seconde partie: test sur disque physique

Afin de vérifier nos résultats, il a été décidé de réaliser le même benchmark sur disque Physique. J'ai retenu uniquement les performances avec optimisations, hormis pour ZFS, qui devait avoir un point de comparaison en terme de performances sur disque physique (gérant la couche blocks). Centos a également été écartés n'offrant que peu de différences par rapport à Debian (un peu moins performant). Cette première courbe montre le nombre de transactions totales sur 1 minute: benchpostgrereal1 Cette seconde capture définit le nombre de transactions par secondes traitées: benchpostgrereal2 Etonnament DragonFlyBSD a des performances quasiment identiques à celles sur disque virtuel, ce qui pourrait signifier que les drivers virtio sont très performants et au point. Debian reste aussi performant en virtuel qu'en physique, et plafone à 50K transactions par minute. Les deux points qu'on pourra remarquer sont:

  • Les performances d'UFS (options async et noatime activées), qui doublent voire triplent avec l'optimisation, mais nécessitent les mêmes précautions que le nobarrier de ext4
  • ZFS multiplie par 10-15 ses performances en activant les optimisations sync=disabled et atime=off, surpassant nettement tout autre système de fichier et offrant des performances défiant toute concurrence. De plus l'option sync=disabled est moins dangeureuse que les options de débridages des autres systèmes de fichiers, bien que peu recommandée pour une base de données nécessitant un maximum de fiabilité en cas de panne.

ZFS est donc le leader sur ce benchmark physique, une fois débridé. En virtuel peut-être atteint-il les mêmes performances ?

Conclusion

Vous retrouverez les données complètes du benchmark dans le lien suivant: Benchmarks - PostGreSQL

En conclusion, si vous deviez choisir un système pour vos bases de données PostGreSQL, orientez vous sur un BSD sans hésiter, que ce soit un FreeBSD (UFS) ou un DragonFlyBSD (Hammer) si vous ne possédez pas de contrôleur raid matériel autrement choississez un Linux avec ext4 et nobarrier, ou encore un FreeBSD avec UFS avec async et noatime.

ZFS sur FreeBSD avec les options sync=disabled et atime=off est très tentant (n'oublions pas que NetApp est un FreeBSD avec ZFS), et devra nécessiter le raccordement des disques directements sur la carte mère sans passer par le contrôleur RAID

Merci à Emmanuel Florac et Axel Perrier pour leurs précisions sur l'option nobarrier