Linux, Base de données Postgresql, développement, Internet, emailing et déliverabilité.

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

mardi, 18 décembre 2007

L'espace disque de votre base de données

Votre base de données est volumineuse ... vous voyez aisément l'espace utilisé par le répertoire qui stocke vos données, mais quand est-il d'une table en particulier ? Comment se rendre compte facilement du volume que représente ces millions de données que vous gérez au quotidien ? Votre espace disque de stockage principal commence sérieusement à s'amoindrir ... ne serait-il pas temps de créer un nouveau tablespace pour optimiser les accès sur un autre espace disque ?

Des questions qui assurément vous sont venus un jour ou l'autre, et qui sont très faciles à connaître avec PostgreSQL.

Lire la suite...

lundi, 17 décembre 2007

Toute la puissance de DISTINCT ON avec PostgreSQL

Qui n'a pas eu le besoin un jour de ressortir un listing contenant le détail de toutes les dernières commandes de ses clients ?

Jusque là ... il fallait souvent passer par des sous SELECT, un MAX et une vitesse de calcul pas souvent au rendez-vous ...
Du genre :

SELECT c.id, c.nom, f.id, f.date, f.total 
  FROM (clients c INNER JOIN factures f ON c.id = f.id)
       INNER JOIN
       (SELECT MAX(date) AS date_derniere_facture, id 
          FROM factures 
         GROUP BY id) AS mf ON mf.date_derniere_facture = f.date AND mf.id = c.id

Si vous êtes un utilisateur de PostgreSQL, vous pouvez vous simplifier la vie en utilisant la fonction DISTINCT ON, de la façon suivante :

SELECT DISTINCT ON (c.id) c.id, c.nom, f.id, f.date, f.total
  FROM clients c LEFT JOIN factures f ON c.id = f.id
 ORDER BY c.id, f.date DESC, f.id DESC;

Et le tour est joué, dans un temps record.

dimanche, 16 décembre 2007

Un module PostgreSQL pour Nagios

Enfin un véritable plugin PostgreSQL pour le célèbre outil de monitoring Nagios.

Il permet en autre fonctionnalités :

  • backends - contrôler le nombre de processus
  • connection - contrôler l'accessibilité
  • database_size - suivre l'évolution de la taille de la base de données
  • disk_space - contrôler l'espace disque de toutes les partitions (data, pg_log, pg_xlog, et tablespaces)
  • relation_size, index_size, and table_size - contrôler les tailles de ces différents éléments
  • last_analyze, last_vacuum - suivre la date d'exécution de ces tâches
  • locks - contrôler le nombre de locks
  • logfile - contrôler la présence des fichiers de log et leur accessibilité
  • query_runtime - contrôler les temps d'exécution de requêtes particulières
  • query_time - contrôler les temps maximum des requêtes
  • txn_wraparound - contrôler le nombre de transactions abandonnées
  • version - contrôler la version de la base de données
  • etc.

Pour en savoir plus sur ce module : check_postgres.pl

Psql plus pratique combiné à less

Pour ceux qui utilisent PostgreSQL en ligne de commande, donc via le logiciel psql, il n'est pas toujours évident de visualiser les données, de naviguer dans les résultats, ou encore de faire des recherches etc.
Pour se faciliter la vie, et combiner les fonctions de less à psql, il suffit d'éditer votre fichier de profile, en général le .profile de votre home sous Linux, en ajoutant :

export PAGER=less
export LESS="-iMSx4 -FX"

Puis, vous éditez, ou vous créez le cas échéant, toujours dans votre home le fichier .psqlrc de la façon suivante :

\timing
\pset pager always

Sachant que \timing vous permet en plus d'avoir le temps de calcul de chacune de vos requêtes, ce qui est bien pratique.

Essayez, et vous verrez que la présentation de vos résultats est beaucoup plus agréable, et que vous pouvez maintenant profiter de la recherche !

samedi, 9 décembre 2006

Postgresql v8.2 dans les bacs !

Voilà les principales nouveautés de cette nouvelle version très attendues :

Améliorations des performances : la version 8.2 améliore les performances d'environ 20% sur les tests de système OLTP (online transaction processing) de pointe. Les utilisateurs peuvent gagner plus encore dans les rendements d'entreposage de données. Les changements comprennent des tris en mémoire et sur disque plus rapides, un meilleur dimensionnement en multi-processeurs, une meilleure planification des requêtes sur les données partitionnées, des chargements massifs plus rapides et des jointures externes grandement accélérées.

Sauvegarde à chaud de bases de données : grâce à une extension de la fonctionnalité « Point in Time Recovery » (restauration d'un instantané), les administrateurs peuvent désormais aisément créer une copie de basculement (failover) du cluster de bases de données.

Construction des index en ligne : les index peuvent désormais être construits alors que les applications écrivent dans les tables de la base. Cela permet d'optimiser les performances sans temps d'arrêt. Fonctionnalités SQL:2003 : PostgreSQL, bien connu pour son respect des standards, a ajouté un grand nombre des nouvelles fonctionnalités introduites dans les spécifications SQL:2003. Parmi celles-ci, on trouve : les aggrégats de statistiques, les instructions VALUE sur plusieurs lignes, UPDATE RETURNING et les aggrégats multi-colonnes.

Les fonctionnalités de bases de données avancées, présentes dans PostgreSQL 8.2 avant tout autre système de bases de données majeur, incluent : Les index inversés généralisés : supportent une manière plus extensible et programmable d'indexer les données textuelles pleines et semi-structurées ; DTrace : PostgreSQL a également été instrumenté pour le DTrace de Solaris et d'autres outils de traçage avancés à travers le Generic Monitoring Framework.

Pour lire l'annonce officielle : PostgreSQLfr

dimanche, 20 août 2006

dblink et trigger sur PostgreSQL

Après un certain nombre de recherches sur le net ... sans grand succès autour du module dblink de PostgreSQL, je tenais à faire partager à la communauté les possibilités très intéressantes de cet outils.
Le principe de dblink pour ceux qui ne le connaissent pas encore est de permettre de faire des requêtes d'une database vers une autre.
L'intérêt peut paraître limité, mais dès que vous envisagez de manipuler des millions de données, une machine seule ne suffit plus à stocker l'intégralité de la base. Il faut alors envisager la démultiplication physique de la base de données.
Viennent alors, naturellement d'autres problèmes comme par exemple la cohérence, ou l'intégrité de la base de données qui se retrouve répartie sur plusieurs machines et donc plusieurs bases. C'est là encore une fois qu'intervient le module dblink.
Voilà donc un petit exemple de ce qui peut être fait simplement pour par exemple tenir à jour un compteur de données sur la database principale, et qui évitera par la suite d'aller faire des COUNT sur les bases déportées. Cet exemple est assez basique mais démontre les capacités de dblink.
Structure de cet exemple :
Database 1 : base1 sur 192.168.0.1
Database 2 : base2 sur 192.168.0.2
Table sur base1 :
CREATE TABLE compteur_base2 (id_produit integer not null primary key, nb integer default 1);

Table sur base2 :
CREATE TABLE produits (id_produit integer, libelle text, prix float);

On admet donc que la base2 contient des millions de produits, faire un COUNT sur cette table produits est très couteux, surtout pour compter des produits qui ont le même identifiant ... maintenir un compteur de son contenu devient donc une vraie nécessité depuis base1, qui est la base principale.
La solution proposée consiste donc à mettre en place sur la table produits de base2, un trigger qui va être en charge de mettre automatiquement à chaque insertion dans cette table le compteur contenu sur base1 dans la table compteur_base2.
Ce trigger fait appel à une fonction (add_product) écrite en plpgsql ... le language interne de programmation SQL intégré à PostgreSQL.

CREATE OR REPLACE function add_product() RETURNS trigger AS $$
DECLARE
    val RECORD;
BEGIN
    SELECT INTO val id_produit FROM dblink('dbname=base1 host=192.168.0.1'::text, 'SELECT id_produit FROM compteur_base2 where id_produit = ' || NEW.id_produit) AS t1(id_produit integer);
    IF NOT FOUND THEN
         PERFORM dblink_exec('dbname=base1 host=192.168.0.1', 'INSERT INTO compteur_base2 (id_produit) VALUES (' || NEW.id_produit || ')');
    ELSE
        PERFORM dblink_exec('dbname=base1 host=192.168.0.1', 'UPDATE compteur_base2 SET nb = nb + 1 WHERE id_produit = ' || NEW.id_produit);
    END IF;
    RETURN NEW;
END;
$$ language plpgsql;

Le principe est donc simple, cette fonction commence par regarder sur base1 via un dblink si l'id_produit existe déjà ... si le SELECT ne retourne pas de résultat nous procédons à un INSERT en base qui prendra la valeur 1 par defaut de la définition de la table compteur_base2. Sinon nous mettons à jour la valeur actuelle du compteur pour cet id_produit. Vous noterez que pour excuter une commande d'INSERT ou d'UPDATE via dblink il est impératif d'utiliser la commande PERFORM. L'inconvénient de la commande PERFORM c'est que dans le cas d'un dblink elle n'est pas capable de prendre en compte la valeur retournée par dblink pour nous indiquer si l'UPDATE a bien fonctionné. Ce qui implique de faire un SELECT précédemment pour tester l'existance de l'identifiant produit sur base1.
Il suffit donc maintenant de brancher la fonction add_product sur un trigger sur la table produits de base2, de la façon suivante :

create trigger produits_ins AFTER INSERT ON produits FOR EACH ROW EXECUTE PROCEDURE add_product();

A chaque insertion en base la fonction add_product sera donc exécutée, elle mettra à jour notre compteur déporté sur base1 automatiquement.
Il va de soit qu'il faudra faire un autre trigger pour mettre à jour le compteur lors d'un effacement d'un produit dans base2 ... mais j'espère que vous parviendrez à le faire par vous-même ... sinon contactez-moi.

vendredi, 28 juillet 2006

Oracle à la conquête de ses concurrents OpenSource

Je viens de lire un article sur LinuxWorld Australie, dans lequel Michael Olson, ancien patron de la société Sleepycat Software, et maintenant VP chez Oracle, en relation avec la stratégie OpenSource d'Oracle.

A la question : "Oracle achètera-t-il plus de compagnies OpenSource ?"
Il répond : Oui. Il y a une équipe chez Oracle dédiée aux acquisitions. Elle en a fait 24 dans les 18 derniers mois. Je ne pense pas que ce sera PostGres ou MySQL. Il serait incroyablement difficile d'acheter PostGres en raison des termes de sa licence. MartenMickos, Président de MySQL, a indiqué, pour sa part, qu'il voulait que MySQL reste indépendant.

Je pense franchement que ce monsieur oublie un point crucial en dehors de la nature même de la licence de PostgreSQL, c'est le fait que PostgreSQL est avant tout basé sur le principe d'une communauté, et distribué par cette dernière. PostgreSQL n'appartient pas à une société. Comme pour Linux il est distribué par différentes sociétés qui apportent du service autour du produit. Mais en aucun cas Oracle n'aura la possibilité d'acheter quoi que ce soit. Dans le meilleur des cas Oracle pourra peut être s'offrir les compétences avérées de certains développeurs de la communauté, mais cela n'empêchera jamais la continuité des travaux autour du projet OpenSource qu'est PostgreSQL.

lundi, 27 mars 2006

Sony migre Sony Online d'Oracle vers PostgreSQL

Cette annonce bien officielle pourra en faire réfléchir plus d'un, je l'espère ... Sony Online Shifts From Oracle to Open-source Database - Computerworld

Le coût des licences Oracle et le peu travail nécessaire à la migration des applications sont les principales motivations de ce choix.

Une très bonne nouvelle et une reconnaissance publique de plus pour PostgreSQL.

dimanche, 26 mars 2006

PostgreSQL et XML

Pour ceux qui ne connaissent pas encore les nouvelles fonctionnalités XML existantes depuis le version 8 de PostgreSQL, je vous conseil de lire cet article PostgreSQL and XML updated.

Il complète les éléments parus dans l'article du même auteur PostgreSQL and XML qui évoquait le XML avec les version 7.x.

mardi, 10 janvier 2006

PostgreSQL v8.1.2

Comme le souligne PostgreSQLFr, site que vous vous devez de visiter si vous vous intéressez à PostgreSQL, et qui plus est en langue Française, une mise à jour de sécurité pour les utilisateurs de la version Windows, mais aussi quelques corrections qui peuvent pas faire de mal pour les autres :

  • correction d'une erreur dans ReadBuffer qui peut entraîner une perte de données par écrasement des pages récentes. Ce correctif s'applique aux branches 8.0 et 8.1 sur toutes les plateformes.
  • un bogue de comparaison de chaines de caractères locale. Cela peut nécessiter un REINDEX pour certaines locales, notamment le hongrois ;
  • la prévention d'un changement accidentel de locale par plperl ;
  • deux correctifs pour l'encodage japonais ;
  • deux correctifs pour COPY CSV ;
  • des correctifs pour les fonctions retournant un RECORD ;
  • des correctifs pour autovacuum, dblink et pgcrypto.

- page 3 de 4 -