Cette article fait suite au précédent article comment persister mes données.

Il y a un peu plus de 3 ans je me rendais à une conférence organisée par l’AFUP Lyon : Présentation de PostgreSQL. Vous pouvez retrouver les slides et le retour des conférences. (C’est la première que j’ai entendu parler de cette superbe association qu’est l’AFUP).

Je n’avais pas besoin qu’on me présente PostgreSQL car je connaissait déjà cette solution. C’est d’ailleurs pour moi la meilleure solution, Open Source (voir même toutes solutions confondues), de Base de Données. Lors de cette conférence, Dimitri Fontaine nous a présenté comment travailler avec PostgreSQL lorsque l’on est développeur. C’est là que la révélation est apparue plus évidente en moi.

Le serveur de base de données n’est pas là que pour stocker des données. Il est là pour les manipuler.

Imaginons un application de gestion de contact, où l’on veut pouvoir avoir son age. Une première idée pourrait être d’enregistrer l’age du contact directement dans la table. Le problème est que l’age, je ne vous apprend rien, change tous les ans. Il faudrait donc faire un mise à jour quotidienne pour vérifier que l’age est toujours à jour. On comprend bien que cette solution n’est pas viable. Du coup on comprend rapidement que nous devons stocker la date de naissance et calculer l’age à chaque fois en fonction de la date courante.

On pourrait être tenté de récupérer la date de naissance de la DB et de faire le calcul dans notre application. Et c’est là qu’on se trompe. Pourquoi faire un calcul dans l’application alors que la DB peux le faire toute seule. D’autant plus qu’une fonction AGE existe déjà dans PostgreSQL. (Il existe aussi un moyen d’avoir le même résultat avec MySQL mais pas de manières native.). De plus comment peut-on récupérer tous les contacts qui ont 18 ans avec un requête SQL ?

Retour d’expérience

Présentation

Sur un précédent projet d’e-commerce, en PHP et avec MySQL, nous avions un système de mise en cache d’informations. Explication : plusieurs informations permettaient de savoir si un produit devait être mis en ligne ou non : produit actif ou non, produit affichable même si la quantité est négative, fournisseur actif, famille du produit active, … Toutes ces informations étaient donc calculées et stocké dans un booléen final pour savoir si le produit était affichable ou non.

Une autre information importante était de savoir si un article était disponible ou non. L’information est simple à trouver mais nous avions plusieurs zones de stockage (entrepôts et magasins). Les stocks magasins étaient bien pris en compte dans la disponibilité car il était possible de faire remonter le produit depuis un magasin vers un entrepôt pour ensuite être expédié chez le client.

Pour cette mise en cache, un script PHP tournait toutes les heures pour mettre à jour les informations. Le script était construit d’une manière très discutable :

<?php
...
$sql = 'SELECT art_id FROM articles WHERE _cache_last_update < ' . $nowSubOneHour;
$result = mysql_query($sql);
while($row = mysql_fetch_row($result) {
    update_cache($row['art_id']);
}

function update_cache($artId)
{
    $fournisseurActif = fournisseurIsActif($artId);
    $familleActif = familleIsActif($artId);
    $articleViewIfNegativeQuantity = articleIsActifIfNegativeQuantity($artId);
    $quantity = articleGlobalQuantity($artId);
    $active = $fournisseurActif
                && $familleActif
                && ($quantity > 0 || $articleViewIfNegativeQuantity);
    updateArticleCache($artId, array(
        'actif' => $active,
        'available' => $quantity > 0
    ));
}

Bon alors il faut imaginer que le code n’était pas aussi allégé et surtout loin d’être aussi simple. Au total environ 300 lignes de codes étaient utilisées pour cette opération de mise en cache.

Maintenant parlons bench. Avec cette solution et 30 000 articles en base, le script mettait une dizaine de minutes à s’exécuter sur la totalité des articles. Suite à l’évolution de l’entreprise nous avons ajouté 15 000 articles. À partir de cet ajout de lignes le script mettait plus d’une heure à s’exécuter. Le script étant appelé toutes les heures les appels s’accumulaient et rendaient le serveur inopérant aux alentours de midi. Il était prévu que je refasse ce script mais là ça devenait une priorité. Je tiens à préciser, pour ma défense, que j’avais récupéré le projet en l’état.

Correction

J’aurais pu garder le principe de faire tous les calculs avec PHP mais avec mes yeux nouveaux, suite à la conférence, je suis parti dans l’idée d’utiliser la base de données pour, plus, manipuler les données.

Première amélioration que l’on peux faire est pour la récupération de tous les articles dont la date de dernière mise à jour est plus vielle qu’il y à une heure. On peut utiliser directement la base de données pour le calcul de la date.

$sql = 'SELECT art_id FROM articles WHERE _cache_last_update < DATE_SUB(NOW(), INTERVAL 1 HOUR)';

Voici un exemple de la correction finale que l'on va expliquer juste après :


// update.sql
CREATE VIEW v_cache_article_vca AS
SELECT  art_id AS vca_art_id,
        art_actif
            AND frn_actif
            AND fam_actif
            AND (
                vaq_quantite > 0
                OR art_show_when_negative
            )
            AS vca_front_actif,
        art_actif AS vca_art_actif,
        frn_actif AS vca_frn_actif,
        fam_actif AS vca_fam_actif,
        art_show_when_negative AS vca_art_show_when_negative,
        vaq_quantite > 0 AS vca_disponibilite,
        vaq_quantite AS vca_stock_total,
        ...
FROM t_article_art
JOIN v_article_quantite_vaq
    ON art_id = vaq_art_id
LEFT JOIN t_fournisseur_frn
    ON art_frn_id = frn_id
LEFT JOIN t_famille_fam
    ON art_fam_id = fam_id
...;

CREATE TABLE tmp_cache_article_vca SELECT * FROM v_cache_article_vca;

// cron.php
<?php

$sql = 'TRUNCATE tmp_cache_article_vca;';
mysql_query($sql);

$sql = 'INSERT tmp_cache_article_vca SELECT * FROM v_cache_article_vca;';
mysql_query($sql);

$sql = 'UPDATE t_article_art
        JOIN tmp_cache_article_vca
            ON art_id = vca_art_id
        SET _cache_disponibilite = vca_disponibilite,
            _cache_date_last_vente = vca_date_last_vente,
            _cache_stock_total = vca_stock_total,
            _cache_front_actif = vca_front_actif,
            _cache_id_famille_princ = vca_id_famille,
            _cache_id_categorie_princ = vca_id_categorie,
            _cache_id_ss_categorie_princ = vca_id_ss_categorie,
            _cache_qte_vendu = vca_qte_vendue,
            _cache_date_dermaj = NOW();';
mysql_query($sql);

Le script est donc passé d’environ 300 lignes à une vingtaine de ligne avec une performance extrêmement plus importante. Nous sommes passé de plus d’une heure d’exécution à quelques secondes pour le même résultat.

Explication

Comme nous pouvons le voir dans la troisième requête un « simple » update est fait. La première chose à savoir est que l’on peut faire un update de la manière présentée : faire un join avec un update et utiliser les données depuis d’autres tables.

Ensuite une chose importante est l’utilisation des vues. Les vues est une table fictive dans laquelle nous pouvons faire des sélections (SELECT) avec des filtres (WHERE) et des tri (ORDER). Une meilleure définition des vues est faites sur Wikipedia.

Pour cette solution, et pour d’autres, j’ai usé, et abusé, des vues. J’ai donc, ici, créé la vue v_cache_article_vca qui me renvoie toutes les informations nécessaires pour la mise à jour des données de la table t_article_art. Cette vue utilise également d’autres vues, comme v_article_quantite_vaq qui permet de sommer les quantités des articles depuis les différents emplacements utilisable.

Enfin j’utilise une table temporaire, tmp_cache_article_vca, qui est construite à partir de la vue v_cache_article_vca. Cette table est vidée et re-remplie depuis la vue avant la mise à jour des données en cache. J’utilise une table car elle permet une meilleur performance. Elle permet aussi de pouvoir créer des index sur certaines colonnes, chose que l’on ne peux pas faire sur une vue. Cette utilisation est mise en place pour palier au manque des vues matérialisées dans MySQL, que l’on peut entre autre utiliser avec PostgreSQL (j’ai pas mis le petit logo placement produit ? (P) maintenant c’est fait 😛 ).

Une chose importante lorsque l’on travail avec les bases de données c’est de créer les bons index sur les tables. L’utilisation des index prend plus d’espace disque mais permet des performances beaucoup plus importante. Un très bon site permet de mieux s’y connaître dans les index de base de données.

Bonus

Une petit bonus avec l’utilisation de la vue est arrivé assez rapidement. Il arrivait souvent qu’un chef produit me demande : « pourquoi ce produit n’apparait pas sur le site marchand ? ». Et là, lorsque l’on utilisait l’ancienne méthode ma réponse était : « Attends je regarde. » Et je devais regarder dans les différentes tables si le produit était bien marqué actif. Idem pour le fournisseur et la famille. Et puis, vérifier que la quantité soit positive. Du coup il me fallait plusieurs minutes pour trouver la réponse.

Une fois la vue mise en place, ma réponse était toujours la même mais c’est le temps de réponse qui était beaucoup plus court. J’avais dans ma vue toutes les informations qui étaient utilisée pour, au final dire si oui ou non le produit pouvait être affiché sur le site marchand. Du coup, une simple requête sur ma vue me permettait d’avoir en un clin d’œil toutes les informations requise pour répondre : « Ton fournisseur est inactif. » ou « Ton produit n’est pas en stock et la coche Afficher le produit même si le stock est nul est inactive ».

 

Nous avons vu que la base de données permettait de faire beaucoup plus de chose que de simplement stocker nos données. Une bonne connaissance de sa base de données et une bonne configuration peux permettre une hausse des performances.

 

Merci de votre lecture et espère vous avoir aidé (et pas trop perdu 😛 ) et peut-être même inspiré. J’espère pouvoir, assez rapidement, vous expliquer comment, sur le même projet, j’ai pu mettre en place un système de statistique très rapide, de la même manière, avec des vues et des tables temporaires.

N’hésitez pas à laisser un commentaire 😉