Différence Saturation CPU et Ram de la Base de données

[JulienK18]

Bonjour à toutes et à tous,

Mon nom de domaine est : www.runnerbreizh.fr

Et j'utilise l'offre : pro2014 et bdd 2 Go

Je rencontre la problématique suivante / Je souhaiterais mettre en place :
Tous les dimanches soir et lundi je rencontre une saturation cpu de plus en plus grosse. Ce que j'ai du mal à comprendre est que parfois le dimanche après midi j'ai 70 utilisateurs en ligne et tout roule, mais dès le dimanche soir même avec 35 personnes le site rame comme pas possible ....
Je constate là pour hier 37% de saturation CPU ainsi que plusieurs dépassement allant jusque 3 Go pour ma bdd de 2 Go ... Y'a-t-il une solution matérielle pour rester rapide malgré tout ? dois-je passer à 4 Go, mais est-ce que ça réglerait aussi la Saturation cpu, car je ne sais pas à quoi ça correspond, avant ça ne me parlait que de dépassement de ram sporadiques ... merci d'avance!
PS mes requêtes ne sont pas optimales mais je suis incapables d'indexer la bdd telle qu'elle est maintenant . Le fait que parfois tout roule avec 70 utliisateurs me laisse à penser que ce n'est pas un réel souci de programmation .
Merci

[Gaston_Phone]

Je constate là pour hier 37% de saturation CPU ainsi que plusieurs dépassement allant jusque 3 Go pour ma bdd de 2 Go ...

Bonjour @JulienK18

Tout d'abord :
- Faire un nettoyage de la base de données,
- Puis virer tous les plugins inutiles.

Voir dans mon guide les paragraphes :
* O4 - Réduire/Nettoyer une Base de Données pleine avec le plugin WP-Sweep
* L2 - N’installer que le strict minimum de plugins

Profitez-en pour lire attentivement les paragraphes A à J de ce guide.

__________________________________________________________________________________

Voici un petit guide que j'ai écrit et qui pourrait vous apporter des éclaircissements pour une Installation complète et propre de votre Site.

---

• Guide - Comprendre la Relation Domaine > Zone DNS > Hébergement > Dossier du site *

---

Voir --> CMS - WordPress - Guide Installation chez OVH
Contrôler votre situation en suivant attentivement les paragraphes : A à J

N'hésitez pas à me faire un retour : positif ou négatif.
C'est comme cela que je peaufine mon Guide.

Si ce guide vous a bien aidé, n'hésitez pas à cliquer sur le bouton « j'aime »

[JulienK18]

Merci
je ne sais pas trop si c'est adapté à mon souci . La BDD ne nécessite pas de nettoyage car je suis le seul à la remplir, de résultats de courses à pied, toutes nécessaires. son principal souci est que quasiment tout est dans une seul table . avec 5 millions de ligne et pas mal d'infos par ligne . DE plus je ne sais même pas ce que sont les plug ins j'utilise juste ce qu'ovh m'a configuré à la base, et je n'utilise pas wordpress . Merci .
Je voulais savoir si des solutions matérielles, comme payer un serveur plus cher, pourraient améliorer la rapidité du site ou si tout est question de requêtes dans la base .

[Gaston_Phone]

La BDD ne nécessite pas de nettoyage car je suis le seul à la remplir, de résultats de courses à pied, toutes nécessaires. son principal souci est que quasiment tout est dans une seul table . avec 5 millions de ligne et pas mal d'infos par ligne

Quel est la taille de la base de données ?

Vous avez écrit 5 millions de lignes ?

[TTY]

Bonjour,

Je voulais savoir si des solutions matérielles, comme payer un serveur plus cher, pourraient améliorer la rapidité du site ou si tout est question de requêtes dans la base .

Ca va aider mais ce n'est pas vraiment ce qu'il faudrait faire.
Si vous avec une table avec 5 Millions de lignes, il faut impérativement l'indexer correctement par rapport aux requêtes qui y sont passées.

Vous utiliser un framework de site (Wordpress, Joomla ...) et si oui lequel ?
On dirait un site from scratch (fait à la main).
EDIT : Oui, c'est un site fait à la main.

[Fritz2cat]

www.runnerbreizh.fr

Le délai très très long pour obtenir la page d'accueil n'est pas reproductible lorsqu'on rafraîchit la page.

Je mettrais bien ma main au feu qu'il s'agit en effet d'un problème d'optimisation de la base de données.

En général ceci est dû à un index qui manque, ce qui implique qu'il faut parcourir toute la grosse table pour exécuter le query. En anglais on appelle ça un "full table scan".

[TTY]

Si les requêtes se font sur une seule table, ce n'est vraiment pas compliqué d'ajouter un index sur les bonnes colonnes.
Si vous avez accès à la configuration du serveur MySql et à ses log, vous pouvez loguer les requêtes trop longue ou sans index. Si ce n'est pas le cas, mettez le site en local sur un poste de travail et faite le.

[JulienK18]

Merci pour vos réponses .
Alors j'aurais dû commencer par là mais je suis complètement nul en mysql, et php aussi d'ailleurs . J'ai eu l'idée de ce site il y a 7 ans, j'ai réussi à créer une requête comme dans mes rêves grâce à plusieurs jours à potasser openclassroom, et ensuite un ami plus pro m'a aidé à tout mettre en place, trouvant l'idée intéressante .
L'idée simple est de mettre plein de résultats de courses à pied sous un même format dans la même table, et quand chaque coureur cherche son nom et prenom (2 colonnes distinctes) , il trouve le compte rendu de ses courses qui sont toutes dans la seule et même table . En gros la table ce sont 14000 résultats mis l'un en dessous de l'autre . Au départ on ne pensait pas qu'il y aurait un succès tel donc c'était plutôt"prévu" pour rester à 3 ou 4000 courses maxi . Une table suffisait.
Quand vous me dites d'indexer, c'est un peu chinois pour moi . J'ai bien un champs qui est un identifiant de chaque course, le reliant donc à sa ville, son nom, son département, etc, mais comme je suis très mauvais aussi en php, j'ai énormément de mal si je sépare en 2 tables de bien configurer les "inner join", je n'y arrive même carrément pas, le site php est une vraie usine à gaz pour moi et je dois oublier de modifier des choses pour que ça fonctionne . L'ami en question n'est plus là pour m'aider, je me suis débrouillé en bricolant depuis 7 ans les requêtes, mais pour vous donner mon niveau, je reste dans ovh sous php 5.6 car dès que je modifie ça, le site ne fonctionne tout simplement plus et je n'ai aucune idée de quoi faire alors !
De plus je ne sais compléter cette table que par des imports de fichiers excel csv, et si finalement j'arrivais à séparer en 2 tables, j'ai peur de bien galérer pour les mises à jour déjà longues chaque week-end . Car cette indexation de toute façon entrainera une reprogrammation je suppose ? quand un coureur se cherche, il faut en effet que ça scanne les 5 millions de ligne .... merci

[TTY]

Ok merci pour les détails.
Séparer en plusieurs tables est une bonne pratique (une pour les coureurs, une pour les villes, une pour les courses etc...) mais au final pour vous éviter les jointures, il faudrait s'assurer déjà qu'il y a un index sur les noms et les prénoms (sur 1 seul index)....

Envoyez moi en message privé un export de la structure de la table. Attention, juste la structure et pas les données hein. je verrai si je peux vous envoyer la/les requêtes adequate pour créer les index si ce n'est pas déjà fait.

[JulienK18]

Non il n'y a pas d'index sur les nom prenoms, sauf lorsqu'ils sont inscrits au site, le champ membre se remplit alors d'une valeur unique . Mais la recherche par nom prenom n'en tient pas compte sinon ne ne trouverait que les inscrits et les non inscrits ne sauraient pas qu'ils peuvent s'inscrire ...
Comme je ne trouve pas comment envoyer un message privé ( je suis nul je vous dis :D )
voici mes champs de cette table de 850 Mo
INDEX CoureurNom CoureurPrenom CoureurClub ClassementScratch NbCoureur Chrono VitesseMoyenne Categorie ClassementCategorie Sexe ClassF Nbrfemmes IndiceF Indice incomplet CourseDate CourseNom CourseVille CourseDepartement CourseType CourseDistance CourseFichierGpsNom CourseParticipants CourseDateEn membre indicekm

Sachant qu'index est donc une valeur unique de chaque ligne ( sert juste à savoir quelles sont les lignes les plus récentes et donc les courses les plus récemment ajoutées) , que CourseFichierGpsNom est plus ou moins un index des courses regroupant pas mal des autres champs (CourseDate CourseNom CourseVille CourseDepartement CourseType CourseDistance CourseParticipants CourseDateEn qui sont des valeurs fixes par course) mais je n'arrive pas à faire les jointures . et que donc membre peut être considéré comme un index de coureurnom prenom pour certains .
voilà voilà .... merci

[TTY]

Ok mais 1 an sur openclassroom quoi :)

Connectez vous dans votre PHPmyAdmin,
- cliquez sur la table en question
- Cliquez sur exporter
- dans la section "Méthode d'exportation" sectionnez " Personnalisée, afficher toutes les options possibles"
- dans la section "Sortie :" sélectionnez "Voir la sortie en texte"
- dans la section "Options spécifiques au format " sélectionnez : structure (pour éviter d'avoir les 800Mo)
- Validez en cliquant sur le bouton "exécuter"

Postez le résultat affiché dans le textarea (en texte et pas en photo)

Voilà à quoi doit ressembler l'écran de l'export :

[JulienK18]

ah non openclassrooms 3 semaines à tout casser :D

|------
|Column|Type|Null|Default
|------
|//**INDEX**//|mediumint(7)|No|
|CoureurNom|varchar(32)|Yes|NULL
|CoureurPrenom|varchar(21)|Yes|NULL
|CoureurClub|varchar(70)|Yes|NULL
|ClassementScratch|mediumint(5)|Yes|NULL
|NbCoureur|mediumint(5)|Yes|NULL
|Chrono|varchar(12)|Yes|NULL
|VitesseMoyenne|time|Yes|NULL
|Categorie|varchar(4)|Yes|NULL
|ClassementCategorie|mediumint(5)|Yes|NULL
|Sexe|text|Yes|NULL
|ClassF|mediumint(5)|Yes|NULL
|Nbrfemmes|int(11)|Yes|NULL
|IndiceF|float|Yes|NULL
|Indice|float|Yes|NULL
|incomplet|int(1)|Yes|NULL
|CourseDate|varchar(11)|Yes|NULL
|CourseNom|varchar(70)|Yes|NULL
|CourseVille|varchar(30)|Yes|NULL
|CourseDepartement|smallint(2)|Yes|NULL
|CourseType|varchar(40)|Yes|NULL
|CourseDistance|decimal(7,3)|Yes|NULL
|CourseFichierGpsNom|varchar(25)|Yes|NULL
|CourseParticipants|mediumint(5)|Yes|NULL
|CourseDateEn|date|Yes|NULL
|membre|varchar(10)|Yes|NULL
|indicekm|float|Yes|NULL

merci

[TTY]

Ce n'est pas ça :(
Vous devriez avoir qq chose du type

-- phpMyAdmin SQL Dump
-- version 5.1.1deb5ubuntu1
-- https://www.phpmyadmin.net/
--
-- Hôte : localhost:3306

--
-- Structure de la table `dcms`
--

CREATE TABLE `dcms` (
  `idDcms` mediumint(6) UNSIGNED NOT NULL,
  `isFocus` tinyint(1) UNSIGNED NOT NULL,
  `isPublish` tinyint(1) NOT NULL COMMENT 'Etat de publication. 1 : Brouillons - 2 : En relecture - 3 : publié',
  `isDelete` tinyint(1) NOT NULL,
  `isInternal` tinyint(1) NOT NULL COMMENT 'Pages n apparaisants pas dans les menus automatiques',
  `datePublish` datetime NOT NULL,
  `dateUnpublish` datetime NOT NULL,
  `position` mediumint(6) UNSIGNED NOT NULL COMMENT 'Ordre dapparition des pages',
  `idUpdater` mediumint(6) UNSIGNED NOT NULL,
  `dateUpdate` datetime NOT NULL,
  `idCreator` mediumint(6) UNSIGNED NOT NULL,
  `dateAdd` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

--
-- Index pour les tables déchargées
--

--
-- Index pour la table `dcms`
--
ALTER TABLE `dcms`
  ADD PRIMARY KEY (`idDcms`),
  ADD KEY `isFocus` (`isFocus`),
  ADD KEY `isInternal` (`isInternal`);

--
-- AUTO_INCREMENT pour les tables déchargées
--

--
-- AUTO_INCREMENT pour la table `dcms`
--
ALTER TABLE `dcms`
  MODIFY `idDcms` mediumint(6) UNSIGNED NOT NULL AUTO_INCREMENT;
COMMIT;

Cela permettrai de voir la table et ses index.

[JulienK18]

désolé
ça doit être ça alors

-- phpMyAdmin SQL Dump
-- version OVH
-- https://www.phpmyadmin.net/
--
-- Host: y
-- Generation Time: Sep 22, 2022 at 06:43 PM
-- Server version: 5.7.38-log
-- PHP Version: 7.4.29

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";

--
-- Database: BDDRunners56
--

---

--
-- Table structure for table runners56table
--

CREATE TABLE runners56table (
INDEX mediumint(7) NOT NULL,
CoureurNom varchar(32) DEFAULT NULL,
CoureurPrenom varchar(21) DEFAULT NULL,
CoureurClub varchar(70) DEFAULT NULL,
ClassementScratch mediumint(5) DEFAULT NULL,
NbCoureur mediumint(5) DEFAULT NULL,
Chrono varchar(12) DEFAULT NULL,
VitesseMoyenne time DEFAULT NULL,
Categorie varchar(4) DEFAULT NULL,
ClassementCategorie mediumint(5) DEFAULT NULL,
Sexe text,
ClassF mediumint(5) DEFAULT NULL,
Nbrfemmes int(11) DEFAULT NULL,
IndiceF float DEFAULT NULL,
Indice float DEFAULT NULL,
incomplet int(1) DEFAULT NULL,
CourseDate varchar(11) DEFAULT NULL,
CourseNom varchar(70) DEFAULT NULL,
CourseVille varchar(30) DEFAULT NULL,
CourseDepartement smallint(2) DEFAULT NULL,
CourseType varchar(40) DEFAULT NULL,
CourseDistance decimal(7,3) DEFAULT NULL,
CourseFichierGpsNom varchar(25) DEFAULT NULL,
CourseParticipants mediumint(5) DEFAULT NULL,
CourseDateEn date DEFAULT NULL,
membre varchar(10) DEFAULT NULL,
indicekm float DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

--
-- Indexes for dumped tables
--

--
-- Indexes for table runners56table
--
ALTER TABLE runners56table
ADD PRIMARY KEY (INDEX),
ADD KEY CourseFichierGpsNom (CourseFichierGpsNom),
ADD KEY ClassementScratch (ClassementScratch),
ADD KEY CourseDateEn (CourseDateEn),
ADD KEY CourseNom (CourseNom),
ADD KEY Nom, Prenom (CoureurNom,CoureurPrenom),
ADD KEY Complet (incomplet),
ADD KEY NbCoureur (NbCoureur);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table runners56table
--
ALTER TABLE runners56table
MODIFY INDEX mediumint(7) NOT NULL AUTO_INCREMENT;
COMMIT;

[TTY]

ADD KEY Nom, Prenom (CoureurNom,CoureurPrenom),

Vous avez bien un index sur le couple CoureurNom/CoureurPrenom
Il faut analyser plus en profondeur le problème de performances, tan au niveau de la BDD (sa structure) que du code source PHP. Crée un cache qui se construit régulièrement via une tâche cron pour éviter les requêtes SQL à chaque chargement de page.

Bien sur vous pourriez prendre un hébergement plus rapide pour quelques gains mais ce serai reculer pour mieux sauter à mon avis.

[JulienK18]

ok ...
bon ben je vais tenter des jointures je ne sais faire que ça, si toutefois j'arrive à adapter toutes les pages à ça . merci .

[TTY]

Vous aurez certainement de l'aide sur https://www.developpez.net
Il ne feront pas le boulot à votre place mais avec un peu de courage et de temps vous devriez pouvoir améliorer les choses.
Si vous avez un peu de budget, vous pouvez aussi faire appel à un pro. Ou alors avec un stage étudiant, une relation, pourquoi pas un des coureurs inscrits qui serait du métier ?
Fait le développement sur un poste de travail ou un autre hébergement est une bonne pratique.

Bon courage !

[JulienK18]

mes 1ers tests sur des jointures n'accélère pas du tout les choses c'est ça qui est flippant :D pourtant la taille de la table principale passe directement de 850 Mo à 400. mais le délai pour voir un palmarès est le même . A voir si ça joue sur la quantité de gens qui font cette requête en même temps . merci

[TTY]

Pouvez vous donner le requête SQL ?

[JulienK18]

c'est un peu plus compliqué que ça c'est la recherche du nom prénom + l'affichage de beaucoup d'infos et calcul à son propos . Beaucoup de requêtes sur la même page . Une idée éventuelle que j'ai serait d'afficher seulement ses courses les + récentes, et toutes ses courses uniquement s'il reclique sur cette demande . mais dans tous les cas le fait d'afficher son nombre de courses total et d'autres stats entraine la recherche dans la table complète .