Grosse base de données Mysql et performances PHP

[Dev@lone]

Bonjour à tous,

Étant novice dans la gestion de grosses bases de données, je m'en remets à vos conseils.

J'ai actuellement une table par "centrale", une centrale fournit de multiples informations par jours, et remplit donc progressivement sa table. La problématique est qu'une de nos "centrales" remplit sa bdd à une vitesse très rapide, nous sommes à 40Go de données en 3 mois. Ce qui pose évidement des soucis de performance lors des requêtes php (qui sont déjà optimisées).

Ces tables "centrale" vont donc grossir de plus en plus, et j'ai besoin d'une solution afin de trouver de meilleures performances.

J'ai pensé découper les tables "centrale" en différentes tables "centraleX_2011-09", "centraleX_2011-08", etc...

Le problème c'est que je sens bien le 10/15Go de données par mois, et ça me semble encore trop faible comme découpage pour avoir de bonnes performances.
Et faire une table par jour deviendrait un gros bordel dans phpmyadmin...

Auriez vous des solutions alternatives ?

Merci d'avance.

[CinePhil]

Que sont ces "centrales" ?
Que sont ces données qui arrivent à vitesse rapide ?
Pourquoi une des centrales alimente t-elle sa table plus vite que les autres ?
Quelle est la structure type d'une de ces tables "centrale" ?

Est-il nécessaire de conserver toute les données à brève, moyenne et longue échéance ?

[Dev@lone]

Les centrales sont des FTP indépendant qui sont analysés, ils contiennent des csv qui sont eux aussi analysés afin d'être retranscris dans la table de la centrale.
La table d'une centrale est toujours la même, 20 champs, un champ id, deux champs date (datetime et timestamp), le reste sont des varchar(255).

Le fait qu'une table soit plus alimentée qu'une autre vient du fait de la production de donnée plus importante sur une centrale que sur une autre, et nous n'avons pas la main là dessus.

Les lignes en bdd ne peuvent pas être "compressées", car nous avons besoin d'accéder aux données historique de manière précise (ex : aujourd'hui nous demandons toutes les données du 20 décembre 2002 pour une centrale donnée).

J'ai pensé au partitionnement par jour... ?

[CinePhil]

Si je comprends bien, une table de centrale est alimentée par les fichiers csv ?

Il y a donc 17 colonnes varchar(255) dans la table, ce qui fait bien lourd avec un important volume de données.
Les données issues d'un fichier CSV sont rarement correctement modélisées. Ces données devraient être réparties dans les tables d'une BDD normalisée, ce qui devrait réduire considérablement le volume de données à traiter et accélérer les traitements sur les données.

Peut-on voir en exemple quelques lignes d'une table centrale avec, bien sûr, la structure de la table et la signification des colonnes ?

[Dev@lone]

Merci de ton aide.

Oui en effet tables alimentées par des csv que j'analyse en amont.

Voici un exemple d'une table avec 6 lignes :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
 
CREATE TABLE `MaCentrale` (
  `releve_id` int(11) NOT NULL AUTO_INCREMENT,
  `onduleur_id` varchar(255) NOT NULL,
  `pac` varchar(255) NOT NULL,
  `iac` varchar(255) NOT NULL,
  `vac` varchar(255) NOT NULL,
  `pcc1` varchar(255) NOT NULL,
  `pcc2` varchar(255) NOT NULL,
  `ucc1` varchar(255) NOT NULL,
  `icc1` varchar(255) NOT NULL,
  `ucc2` varchar(255) NOT NULL,
  `icc2` varchar(255) NOT NULL,
  `edaily` varchar(255) NOT NULL,
  `eweekly` varchar(255) NOT NULL,
  `emonth` varchar(255) NOT NULL,
  `eyear` varchar(255) NOT NULL,
  `etotal` varchar(255) NOT NULL,
  `epartial` varchar(255) NOT NULL,
  `date` datetime NOT NULL,
  `date_insert` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `file` varchar(255) NOT NULL,
  PRIMARY KEY (`releve_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=20249 ;
 
--
-- Contenu de la table `MaCentrale`
--
 
INSERT INTO `MaCentrale` VALUES(10236, '225587', '5935.91', '7.867', '239.466', '3949.215', '2103.288', '533.96', '7.245', '514.954', '4.062', '17759', '0', '0', '0', '0', '0', '2011-07-28 12:10:00', '2011-08-07 05:19:42', 'MaCentrale_INV_1_1_110728_103020.csv.gz');
INSERT INTO `MaCentrale` VALUES(10237, '225587', '4761.839', '6.215', '240.096', '3167.993', '1675.513', '548.267', '5.668', '534.857', '3.128', '18490', '0', '0', '0', '0', '0', '2011-07-28 12:20:00', '2011-08-07 05:19:42', 'MaCentrale_INV_1_1_110728_103020.csv.gz');
INSERT INTO `MaCentrale` VALUES(10238, '225587', '4895.292', '6.422', '239.789', '3293.91', '1686.46', '542.218', '6.04', '532.292', '3.17', '19356', '0', '0', '0', '0', '0', '2011-07-28 12:30:00', '2011-08-07 05:19:42', 'MaCentrale_INV_1_1_110728_103020.csv.gz');
INSERT INTO `MaCentrale` VALUES(10239, '240977', '6187.081', '8.083', '239.407', '4104.207', '2207.123', '556.7', '7.032', '538.528', '4.043', '18468', '0', '0', '0', '0', '0', '2011-07-28 12:10:00', '2011-08-07 05:19:42', 'MaCentrale_INV_1_1_110728_103020.csv.gz');
INSERT INTO `MaCentrale` VALUES(10240, '240977', '5086.869', '6.542', '239.938', '3390.576', '1780.113', '563.993', '5.868', '556.544', '3.171', '19293', '0', '0', '0', '0', '0', '2011-07-28 12:20:00', '2011-08-07 05:19:42', 'MaCentrale_INV_1_1_110728_103020.csv.gz');

[Dev@lone]

Les différents champs varchar sont ceux sur lesquels je fais mes calculs en front.
Ils sont en varchar et non en float car j'ai eu un problème avec une requête qui ne voulait passer que si les champs était en varchar... J'arriverais pas à te retrouver exactement la requête je pense

[CinePhil]

Que veux-tu dire par "que j'analyse en amont." ?
Cela veut-il dire qu'un CSV est en fait le résultat d'une analyse de données ?

À ce que je vois, la plupart des colonnes sont des nombres. Elles ne devraient pas être du type VARCHAR.
Seule la dernière colonne est vraiment un VARCHAR, qui plus est très répétitif. Cette dernière devrait être externalisée dans une table des fichiers et il ne devrait y avoir ici que l'identifiant du fichier.
Je vois aussi des colonnes, pcc1 et 2, icc1 et 2, ucc 1 et 2. N'y aura t-il toujours que 2 colonnes de chaque sorte ? Peut-il y en avoir zéro ou une seule ou plus de deux ?

Ces données sont-elles des relevés de mesures sur des onduleurs ? Intensité, tension et puissance actives et de court-circuit ?

Décris-nous un peu le processus qui conduit à l'obtention de ces données et à quoi elles sont destinées, pourquoi on les stocke en BDD alors qu'elles existent en fichiers CSV, pour en faire quoi ?

[Dev@lone]

En effet tu as compris le processus, il s'agit de données d'onduleurs. Une centrale comporte plusieurs onduleurs. Cette centrale envoi un csv toutes les heures environ, ce csv est ensuite traité par un process php afin de convertir les données reçu de la même manière pour toutes les centrales (car certaines les envoient sous des formes différentes, et nous devons faire des calculs en amont).

La dernière colonne est en effet répétitive, mais nous avons besoin de savoir pour telle donnée d'où elle vient, donc en effet c'est répétitif mais pas trop le choix ici.

Les formats Varchar regarde mon message plus haut, à cause d'une requête j'étais obligé de passer par ça pour que les calculs soient bons... Incompréhensible d'ailleurs.

[CinePhil]

Plutôt que FLOAT qui peut effectivement causer des problèmes dans les calculs, utilise DECIMAL.

D'une manière générale, je t'encourage à ne pas travailler sur les données brutes mais à les incorporer dans une structure de données normalisée.

Bon courage.

[Dev@lone]

Ok no problèmes, merci pour ça.

Par contre au niveau de l'optimisation bdd tu aurais une idée ? Car sur une table j'ai plus de 300 millions d'enregistrements, et c'est là qu'on a des problèmes (40Go la table)

[CinePhil]

Ta table fait 40 Go en grande partie à cause des varchar(255) et notamment de la dernière colonne qui est la plus coûteuse en espace.
Si tu externalises le nom du fichier et que tu mets à la place dans la table centrale un entier référençant l'identifiant du fichier, cela n'occupera plus que 4 octets par ligne au lieu des 40 octets de ton exemple de données, soit 1,2 Go pour 300 millions de lignes au lieu de 12 Go.

Les décimaux par contre sont apparentés aux chaînes de caractères donc tu ne gagneras pas beaucoup de place avec eux.

[StringBuilder]

Pourquoi ne pas stocker :
- Les CSV dans un CLOB (s'il s'agit que de restituer les données, pas besoin de les structurer)
- Ou de stocker uniquement le chemin d'accès au fichier CSV, qui sera éventuellement stocké sur un NAS compressé, ou dans des ZIP mensualisés.

[Dev@lone]

@CinePhil :
Sur une table de test (3millions d'enregistrements) j'ai supprimer la colonne "file" et passé toutes les autres varchar en decimal(10,0), je passe de 430Mo à 330Mo

@StringBuilder :
CLOB je connais pas.
Mais si je comprends ton idée c'est un stockage des csv ?
On a besoin d'avoir les données dispo de suite pour un affichage immédiat, c'est la base de notre appli, elle a besoin de toutes ces infos de manière permanente pour les afficher en front, donc je pense pas que le stockage soit une bonne soluce car on devra extraire et analyser les zip à chaque demande en front, donc assez lourd à mon avis...

[CinePhil]

Envoyé par Dev@lone

@CinePhil :
Sur une table de test (3millions d'enregistrements) j'ai supprimer la colonne "file" et passé toutes les autres varchar en decimal(10,0), je passe de 430Mo à 330Mo

Euh... DECIMAL(10,0), c'est un entier ! Le zéro représente le nombre de chiffres après la virgule et le premier nombre représente le nombre de chiffres au total.

Dans tes données exemples, tu as une précision de 3 décimales donc un DECIMAL(10, 3) serait plus approprié. Voire même moins de 10 car tes données ne donnent que des nombres à maximum 7 chiffres.

D'ailleurs, tu pourrais aussi étudier la possibilité de ne stocker que des entiers, ce qui prendrait beaucoup moins de place (un entier occupe 4 octets), et de diviser par 1000 lors de la présentation des données.

[Waldar]

J'ajouterai qu'il ne faut pas supprimer vos deux dernières colonnes, il faut les changer par un identifiant de traitement, c'est la proposition initiale de Cinephil : il parle du nom du fichier, mais la date qui est attachée c'est exactement pareil, un timestamp c'est une quinzaine d'octets je ne sais plus exactement.

Imaginez une table comme ceci (je ne sais pas si vous avez des règles de nommage, vous pouvez toujours adapter) :

Code :

1
2
3
4
5
6
7
CREATE TABLE `Traitement`
(
    trt_id           int          NOT NULL AUTO_INCREMENT,
    trt_date_insert  timestamp    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    trt_nom_fichier  varchar(255) NOT NULL,
    PRIMARY KEY (`trt_id`)
)

Et dans votre table MaCentrale vous remplacez vos deux dernières colonnes par ce trt_id.

[StringBuilder]

Envoyé par Dev@lone

@StringBuilder :
CLOB je connais pas.
Mais si je comprends ton idée c'est un stockage des csv ?
On a besoin d'avoir les données dispo de suite pour un affichage immédiat, c'est la base de notre appli, elle a besoin de toutes ces infos de manière permanente pour les afficher en front, donc je pense pas que le stockage soit une bonne soluce car on devra extraire et analyser les zip à chaque demande en front, donc assez lourd à mon avis...

CLOB, c'est un type de donnée Oracle (mais il y a le même avec MySQL, sous un autre nom peut-être, BLOB par exemple, ou TEXT).

Ce type de donnée permet de stocker jusqu'à 2 Go de données dans un enregistrement. En revanche, la place occupée dans la table n'est que celle d'un pointeur vers la donnée, stockée physiquement ailleurs dans le tablespace.

Ceci permet d'avoir des temps de réponse très intéressants sur la table elle-même, tout en conservant le fichier dans la base de données.

En ce qui concerne le ZIP, le temps de compression/décompression d'un fichier ZIP est généralement plus court que le temps d'accès sur le disque du fichier non compressé. D'ailleurs, c'est tellement rapide que la plupart des sites web compressent à la volée les pages avant de les envoyer au navigateur, qui les décompresse alors (gain notable de bande passante, et donc de vitesse). Il n'y a donc rien de surprenant à stocker les données (dabns un CLOB ou non) dans un format compressé.
En revanche, il est évidement plutôt préférable dans ton cas d'opter pour un ZIP par fichier. Ceci n'est intéressant que si les CSV sont un minimum volumineux (plusieurs centaines de Ko, sinon en effet ça sert à rien).

Le seul point négatif du CLOB combiné ou non au ZIP (ou d'un stockage externe à la base, en ZIP ou non) c'est qu'il sera impossible de faire des recherches sur les données contenues dans les CSV. Rien ne t'empêche cependant de mélanger les deux (stocker dans la table ou un deux valeurs clés du CSV, et stocker le reste du fichier dans un CLOB ou sur disque).

[Dev@lone]

@CinePhil :
En mettant des INT partout je descend à 281Mo sur la table de test, donc la différence n'est pas flagrante et à mon avis je ne réduirais pas la table de 40Go avec cette méthode

@Waldar :
Si je comprends bien le but serait d'externaliser les données file et timestamp selon vous ?

Afin de regrouper vos propals j'ai supprimer les champs qui posent souci, et mis en INT les autres, voici le resultat :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
 
 
 
CREATE TABLE `MaCentrale` (
  `releve_id` int(11) NOT NULL AUTO_INCREMENT,
  `onduleur_id` int(11) NOT NULL,
  `pac` int(11) NOT NULL,
  `iac` int(11) NOT NULL,
  `vac` int(11) NOT NULL,
  `pcc1` int(11) NOT NULL,
  `pcc2` int(11) NOT NULL,
  `ucc1` int(11) NOT NULL,
  `icc1` int(11) NOT NULL,
  `ucc2` int(11) NOT NULL,
  `icc2` int(11) NOT NULL,
  `edaily` int(11) NOT NULL,
  `eweekly` int(11) NOT NULL,
  `emonth` int(11) NOT NULL,
  `eyear` int(11) NOT NULL,
  `etotal` int(11) NOT NULL,
  `epartial` int(11) NOT NULL,
  `date` datetime NOT NULL,
  PRIMARY KEY (`releve_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=22561852 ;
 
--
-- Contenu de la table `MaCentrale`
--
 
INSERT INTO `MaCentrale` VALUES(20729799, 227578, 2255, 3, 225, 1494, 830, 427, 3, 472, 2, 53605, 0, 0, 0, 0, 0, '2011-05-30 18:00:00');
INSERT INTO `MaCentrale` VALUES(20729800, 227576, 2364, 3, 225, 1597, 788, 471, 3, 465, 2, 67981, 0, 0, 0, 0, 0, '2011-05-30 18:00:00');
INSERT INTO `MaCentrale` VALUES(20729801, 227619, 3243, 4, 225, 1668, 1676, 474, 3, 475, 3, 78110, 0, 0, 0, 0, 0, '2011-05-30 18:00:00');
INSERT INTO `MaCentrale` VALUES(20729802, 227608, 2223, 3, 225, 1247, 990, 399, 3, 313, 3, 53835, 0, 0, 0, 0, 0, '2011-05-30 18:00:00');

Nous passons de 430Mo à 270Mo sur la table de test (3 millions d'enregistrement), donc un gain de moins de 50%, ce qui n'est donc pas gérable pour moi.

Que pensez vous du partitionnement ? Je pensais faire une partition par jour ?

[SQLpro]

Dans votre cas les problèmes de dégradation des perfs sont liés à deux défaut majeur de MySQL :
1) MySQL ne possède pas d'ETL dédié pour absorber de gros fichier à intégrer...
Regardez en comparaison les performances d'un ETL comme SSIS dédié à SQL Server...
http://msdn.microsoft.com/en-us/libr...ql.100%29.aspx
En effet l'ETL travaille en parallèle ses fichiers en activant tous les CPU disponible pour insérer les lignes, ce que ne fait pas MySQL qui travaille toutes ses requête en mono thread !
2) MySQL est très loin de valoir du Oracle, du SQL Server, voir même du postGreSQL en matière d'indexation (a lire http://blog.developpez.com/sqlpro/p9...t-sur-l-index/) comme en matière d'optimisation des requêtes (certaines requêtes sont infaisables sous MySQL... http://blog.developpez.com/sqlpro/p9...udre-aux-yeux/)

Bref vous aurez du mal à optimiser la chose...

A +