Aide SQL - CPU élevé avec timestamp

Bonjour à tous, j’ai besoin d’avis SVP.

Dans un projet, Cette req SQL me "sature" le CPU de mon VPS (c’est un petit VPS 1 cœurs et 2 Go de RAM).

select
    `orders_super_parents`.`id`,
    `orders`.`id` as `order_lastUpdatedAt_id`,
    `orders_dates`.`date_for_stats_filter` as `orderDate__date_for_stats_filter`
from `orders_super_parents`
inner join `orders` 
    on `orders_super_parents`.`order_id_last_updated_at` = `orders`.`id`
inner join `orders_dates` on `orders`.`id` = `orders_dates`.`order_id`
where `orders_super_parents`.`deleted_at` is null
order by `orders_super_parents`.`row_number` desc
limit 15 offset 0

Technos que j’utilise : PHP 8.0 / Laravel 8 / MariaDB 10.6.

Par contre quand je remplace la ligne orders_dates.date_for_stats_filter as orderDate__date_for_stats_filter (qui est une colonne de type timestamp) par orders_dates.id as orderDate__date_for_stats_filter (qui est un simple id integer), tout fonctionne rapidement nickel (ma req SQL se traite en 0.0024 secondes au lieu de 0.5950 secondes, je le vois quand je débogue PhpmMyAdmin).

C’est la première fois que je vois une req SQL "saturer" un de mes VPS car je select un champs de type timestamp.

Un avis SVP ?

Merci.

Il manquerait pas un index à tout hasard ?

Ma colonne timestamp date_for_stats_filter a bien un index

Salut,

Souvent pour investiguer des requêtes SQL qui ne performent pas correctement, c’est utile de jeter un oeil à l’execution plan. Normalement, il suffit de mettre le mot-clef EXPLAIN devant ta requête et voir ce que ça donne (ça marche comme ça dans Postgresql et une rapide recherche Google semble confirme que c’est aussi le cas pour Maria DB). Ca serait utile de voir l’execution plan de la requête avec orders_dates.date_for_stats_filter et celui de la requête avec orders_dates.id pour voir quelles sont les différences.

Tu pourrais aussi partager les index que tu as sur tes tables ? Souvent, les problèmes de performance sont dûs à des indexs manquants.

À moins que quelque chose m’échappe, cet index n’est pas utilisé dans la requête en question puisqu’il n’y a aucun filtre ou ordre sur cette colonne dans ta requête.

Ici, à vue de de nez, je dirais que :

• where orders_super_parents.deleted_at is null implique un scan complet si deleted_at n’est pas indexé ;
• order by orders_super_parents.row_number desc implique un scan complet suivi d’un tri si row_number n’est pas indexé.

Mais c’est assez difficile à dire en l’état, il manque des informations. Je suggère, comme @Migwel, que tu nous montres la définition des tables avec leurs index, ainsi que le résultat d’un EXPLAIN de la requête présentant les symptômes.

En soi, rien ne nous garantit ici que c’est le manque d’index qui est responsable des performances désastreuses (le nombre de rows est peut-être encore très bas). Les join sont aussi à vérifier. Et seul un EXPLAIN pourra nous montrer le cheminement de la requête.

Par contre quand je remplace la ligne orders_dates.date_for_stats_filter as orderDatedate_for_stats_filter (qui est une colonne de type timestamp) par orders_dates.id as orderDatedate_for_stats_filter (qui est un simple id integer), tout fonctionne rapidement nickel (ma req SQL se traite en 0.0024 secondes au lieu de 0.5950 secondes, je le vois quand je débogue PhpmMyAdmin).

Je n’ai pas assez d’expérience avec MariaDB pour bien deviner comment ça marche, mais je spécule que les champs demandés ont une influence sur le chemin d’exécution et que celui-ci s’avère être plus performant « par hasard » : peut-être est-ce dû à une corrélation entre le l’accroissement de tes ID avec ta colonne row_number, ce qui évite à MariaDB de perdre du temps lors du tri pour le order by ?

Là encore, on restera dans la spéculation tant qu’on regarde pas le EXPLAIN

Ok merci.

Pour les résultats avec EXPLAIN, j’ai fais des impr écran, ça sera + simple :

Avec la colonne timestamp orders_dates.date_for_stats_filter (la req SQL qui rame) :

http://www.cms-v2-preprod.daw-dev.fr/medias/app/upload/avec-date_for_stats_filter.png

Avec la colonne integer orders_dates.id :

http://www.cms-v2-preprod.daw-dev.fr/medias/app/upload/avec-id.png

Et pour les index de mes tables (ici je note que les indexs des colonnes utilisées dans ma req SQL) :

Table orders_super_parents :

• id (int) : Index primary key

• order_id_last_updated_at (int) : Index foreign key, Index unique key.

• deleted_at (timestamp) : Index key.

• row_number (int) : Index unique key.

Table orders :

• id (int) : Index primary key

Table orders_dates :

• date_for_stats_filter (timestamp) : Index key.

• order_id (int) : Index foreign key.

Dans ta 2ème version, order by orders_dates.id ça revient très exactement à order by orders.id Jackpot !

Du coup, le moteur lit la table orders, séquentiellement, selon orders.id. Quand il a trouvé 15 lignes, il s’arrête et affiche le résultat.

Dans la requête plus longue, s’il lit orders, il lit toute la table, il va systématiquement chercher dans orders_date la ligne correspondante, il récupère la colonne orders_dates.date_for_stats_filter et il doit scanner toute la table pour trouver les 15 lignes voulues.

Il faudrait le forcer à lire la table orders_date en premier, et aller chercher les enregistrements correspondants dans les autres tables, mais les index ne sont peut-être pas adaptés.

Merci mais non ça ne reviens pas au même. orders_dates.id c’est l’id de la table orders_dates et pas de la table de orders. Et ce n’est pas le order by que je modifie entre mes 2 versions, mais un select.

Et comment je peux faire ceci stp ? (normalement j’ai déjà tous les bons indexs, au pire je les ajouterai).

J’ai essayé cet req SQL, mais ça ne change rien à mon problème (et avec le EXPLAIN je vois que la table orders_date est toujours lu en dernier) :

select
    `orders_super_parents`.`id`,
    `orders`.`id` as `order_lastUpdatedAt_id`,
    `orders_dates`.`date_for_stats_filter` as `orderDate__date_for_stats_filter`

from `orders_dates`

inner join `orders` 
    on `orders`.`id` = `orders_dates`.`order_id`

inner join `orders_super_parents`
    on `orders_super_parents`.`order_id_last_updated_at` = `orders`.`id`

where `orders_super_parents`.`deleted_at` is null
order by `orders_super_parents`.`row_number` desc
limit 15 offset 0

Merci

Le mot clé pour chercher sur Google, c’est HINT. Les HINTS, ce sont les instructions complémentaires que tu ajoutes dans ta requête pour aider le moteur SGBDR, par exemple en utilisant spécifiquement certains index.

Si tu es sur MySQL, tu as une présentation ici : Doc

Edit.

Mais, j’imagine que ta base de données sert à autre chose que pour cette requête. Et modifier des index ou ajouter des index, ça n’est pas forcément une bonne idée. Tu vas résoudre le problème de cette requête, mais tu vas peut-être générer plein d’autres problèmes.

Peut-être que d’autres requêtes qui fonctionnaient très bien vont se mettre à utiliser tes nouveaux index, bêtement. Peut-être que les écritures vont-être très ralenties, parce qu’il y aura plus d’index à mettre à jour.

1000 mercis, tu m’apprend vraiment une chose de très important.

Effectivement, pour cette req SQL, quand je rajoute FORCE INDEX (orders_dates_order_id_foreign) ça résout mon problème.

Voici ma req SQL :

select
    `orders_super_parents`.`id`,
    `orders`.`id` as `order_lastUpdatedAt_id`,
    `orders_dates`.`date_for_stats_filter` as `orderDate__date_for_stats_filter`

from `orders_super_parents`

inner join `orders` 
    on `orders_super_parents`.`order_id_last_updated_at` = `orders`.`id`

inner join `orders_dates`
    FORCE INDEX (`orders_dates_order_id_foreign`)
    on `orders`.`id` = `orders_dates`.`order_id`

where `orders_super_parents`.`deleted_at` is null
order by `orders_super_parents`.`row_number` desc
limit 15 offset 0

Mais comment ça se fait que je suis obligé d’ajouter FORCE INDEX (orders_dates_order_id_foreign) ? Et que Maria Db ne gère pas ça automatiquement sur toutes les req SQL ?

Merci.

Je ne connais pas Maria Db, mais de manière générale, les SGBD fonctionnent ainsi :

• Pour chaque table, il y a un fichier annexe avec quelques statistiques, très très grossièrement : nombre de lignes, valeur-moyenne et Ecart-type pour toutes les colonnes de type numérique, des indicateurs un peu similaires pour les colonnes de type texte.

• Au moment d’exécuter une requête, le moteur consulte ces statistiques pour tous les fichiers qui interviennent dans la requête, et il réfléchit, et il choisit un plan d’action : quel fichier lire en premier, quel index utiliser pour accéder directement aux lignes correspondant dans les autres fichiers etc. Tu peux contrôler le plan d’execution d’une requete, il y a généralement des outils pour ça ( cf : explain Select ) ; là, tu peux consulter le plan d’exécution avec ou sans le Hint, pour voir comment il procède dans les 2 cas.

Si pour une raison ou une autre, les statistiques ne sont plus à jour, le moteur risque d’être très mal inspiré. Si on lui dit qu’il y a 100 lignes dans le fichier A, alors qu’il y en a 1000000, ça va bien l’induire en erreur !

Et dans beaucoup de systèmes, les statistiques ne se mettent jamais à jour, sauf quand l’utilisateur (ou l’administrateur de la base) lance les commandes adéquates.

Tu dois pouvoir consulter quand les statistiques ont été mises à jour pour la dernière fois sur chacun des fichiers … pour vérifier si c’est effectivement là le problème, puis lancer la commande pour rafraîchir ces statistiques (GATHER_STATS ?) On dit que quand 10% des lignes ont été modifiées (ou insérées ou supprimées), il est temps de mettre à jour les stats de la table.

Regarde ici :Statistics Maria-Db

Si ta base de données est administrée par quelqu’un d’autre … c’est un peu son job. C’est celui qui écrit dans la table qui doit s’assurer que la table reste exploitable, pas celui qui lit.

Peut-être qu’en mettant à jour les stats, et en revenant à la requête d’origine, sans le HINT, tu auras de très bonnes performances.

Merci pour toutes ces précieuses infos.

Là c’est un projet où je gère tout (Web App + Serveur Web que j’ai conf). J’ai parlé de tout ce que tu m’a dit à des dév (expérimenté) que je connais, même eux ne connaissais pas trop les HINTS, et encore moins ce que tu viens de m’expliquer dans ton dernier post. Mais c’est très intéressant, je vais creser.

Merci beaucoup.

Il y a 2 écoles autour des Hints. Certains disent que c’est un mauvais remède. Quand on utilise un hint, on se prétend plus compétent que les ingénieurs qui conçoivent Maria-DB. Si demain, on installe une nouvelle version de Maria-Db, les hints seront toujours là dans la requête, et peut-être qu’ils vont empêcher la requête de bien fonctionner.

D’autres les utilisent fréquemment.

Les 2 points de vue peuvent s’entendre. Et la fonctionnalité existe, Donc les ingénieurs qui ont conçu Maria-DB (ou Oracle ou …) considèrent que ça peut être utile pour le développeur.

Dans l’ordre, s’assurer que les statistiques sur les tables sont correctes, qu’elles n’induisent pas le moteur en erreur.

Et si la base est bien propre, si malgré des statistiques à jour, le moteur ne trouve pas un plan d’exécution valable, alors, en dernier recours, mettre les mains dans le cambouis, et regarder si avec les hints, on peut l’aider.