Créer une partition pour une table

Version imprimable

18/02/2005, 22h39
Erakis

Créer une partition pour une table

Bonjour à tous ;)

Est-ce que c'est possible de créer des sortes de paritions (Par date) sur une table à l'aide de MySQL. Ou quelques chose de similaire... J'ai 16 millions d'entrées par mois dans une table et j'aimerais la partionner.

Merci.
18/02/2005, 23h57
mathieu

Je n'ai pas très bien compris ta question

Si tu veux seulement récupèrer une partie des enregistrements il suffit d'utiliser "LIMIT" ou bien de poser tes conditions dans la clause "WHERE"
19/02/2005, 09h20
Erakis

Pas tout à fait...

C'est comme séparer l'information d'un gigantesque table. À peu près comme un disque dur. Donc on créer une partition afin de séparer les données selon un critère comme par exemple des interval de date.

Exemple sous SQL 2005: http://www.sqlskills.com/resources/W...0Beta%20II.htm

Je sais que cela se fait aussi sous Oracle.

MySQL a-t-il un système similaire ?
21/02/2005, 19h03
Erakis

Re-bonjour.

Pour donner un peu plus d'informations sur la table en question.
La table est consitué de trois champs comme clé primaire

Primary key [ Date_Entry, Station_ID, SensorID, SensorValue )

Donc, pour les types c'est (DateTime, Int, Int, Float ).

Est-ce que ce serait une solution de créer une table pour chaque mois de l'année ? (Emmerdant mais quand même cela résoudrait le problème relié aux performances décevant, quand je récupère un bloc de données situé entre deux mois d'une année données ou simplement lorsque je veux connaître le MIN et MAX des mois situé entre deux années).

J'attend vos commentaires.
Merci
22/02/2005, 10h30
qi130

Ce système de partitionnement existe aussi sous DB2 (tablespace), mais je n'ai pas vu d'équivalent sous MySQL: cette notion de tablespace renvoie à la définition (allocation) de ressources physiques (HD) pour le stockage des tables (=storage group sous DB2).

Cependant, cette notion de partitionnement de tables est valable pour des segments prédéfinis de valeurs de clé:
- clés de 1 à 100 => partition 1
- clés de 101 à 200 => partition 2
- etc....

dans ton cas, ces segments sont "mouvants", à cause de la date qui croit sans cesse...
22/02/2005, 16h59
Erakis

Alors existe-t-il une solution à mon problème ? Car 5 millions d'enregistrements par mois, au bout de 6 mois ce sera catastrophique d'extraire un mois de données de cette table :(
22/02/2005, 20h45
qi130

1 table par Station_id, si le nombre est raisonnable ?
22/02/2005, 22h47
Erakis

Bonjour, merci pour votre aide, c'est apprécié :)

Des Applications qui possède 1 ou plusieurs stations. Donc des stations il peut y en avoir facilement 300 et + qui projettent chacune 200 entrées environ par jour. Une station possède un ID qui est linké (relation) avec un application ApplicationsStation = (Station_ID, Application_ID), donc la solution proposé (1 table par station n'est pas réalisable/possible.)

Merci.
23/02/2005, 09h56
qi130

Il y aurait peut-être une solution basée sur une ou plusieurs procédures stockées:
:idea: [mode conception on-line] :lol:

tu sollicites 1 PS pour faire les inserts.
cette PS, en fonction des données qui lui arrivent, décide de la table où ces données doivent atterir...
L'algo est à trouver, mais l'idée est que: si la table existe, on y insère les données, si elle n'existe pas, une nouvelle table est créée.

L'algo en question doit donc "peser" la future clé afin de définir le meilleur endroit de stockage. On implémente ici une sorte de partitionnement automatique...

Qu'en penses-tu ?
23/02/2005, 15h07
Erakis

Bonjour qi130 ;)

Donc, ta solution ressesmble de très près à celle que je propose qui est de créer une table pour chaque mois... (5 millions d'enregistrement par mois)

Mais j'ai lu quelque part que les procédures stockées sous MySQL ne serons pas exécutée plus rapidement comparé aux autres SGBD comme MSSQL. Les procédures stockés ne sont pas sauvegardées dans le SGBD comme étant une procédure, mais seulement reçues comme, via une requête...

Petite question comme ça, comment fait-on pour récupérer la liste des tables existantes sous MySQL ?

Merci.
23/02/2005, 15h43
qi130

Pour la liste, c'est par là : http://dev.mysql.com/doc/mysql/fr/show-tables.html
23/02/2005, 17h10
Maximil ian

Citation:

Envoyé par Erakis

Mais j'ai lu quelque part que les procédures stockées sous MySQL ne serons pas exécutée plus rapidement comparé aux autres SGBD comme MSSQL. Les procédures stockés ne sont pas sauvegardées dans le SGBD comme étant une procédure, mais seulement reçues comme, via une requête...

J'ai du mal à saisir 8O
Une procédure stockée est par définition stockée sur le serveur. Les SP de MySQL ne dérogent pas à la règle...

Citation:

Envoyé par Erakis

Alors existe-t-il une solution à mon problème ? Car 5 millions d'enregistrements par mois, au bout de 6 mois ce sera catastrophique d'extraire un mois de données de cette table :(

Je ne connais pas la criticité de ta base, mais MySQL est-il le choix le plus judicieux pour une base qui croît de 5 millions d'enregistrements par mois (à moins que ça ne soit 16 millions comme tu le disais au début) ?

La question mérite d'être posée et des tests d'être réalisés :roll:

Citation:

Envoyé par Maximilian

Citation:

Envoyé par Erakis

Mais j'ai lu quelque part que les procédures stockées sous MySQL ne serons pas exécutée plus rapidement comparé aux autres SGBD comme MSSQL. Les procédures stockés ne sont pas sauvegardées dans le SGBD comme étant une procédure, mais seulement reçues comme, via une requête...

J'ai du mal à saisir 8O
Une procédure stockée est par définition stockée sur le serveur. Les SP de MySQL ne dérogent pas à la règle...

Citation:

Envoyé par Erakis

Alors existe-t-il une solution à mon problème ? Car 5 millions d'enregistrements par mois, au bout de 6 mois ce sera catastrophique d'extraire un mois de données de cette table :(

Je ne connais pas la criticité de ta base, mais MySQL est-il le choix le plus judicieux pour une base qui croît de 5 millions d'enregistrements par mois (à moins que ça ne soit 16 millions comme tu le disais au début) ?

La question mérite d'être posée et des tests d'être réalisés :roll:

Ma criticité est basé sur le fait que présentement j'ai que 3 millions d'enregistrement et que nous nous attendons à en avoir 5 millions et plus par mois. Pour le moment, je dois parcourir ma table à la recherche de l'année la plus élevé et la plus basse pour une station, et je dois te dire que ça prends facilement 4-5 secondes pour la recheche sur un P4 2.8Ghz ! Voilà la simple requête :
Code:

1 2 3 4 SELECT DATE_FORMAT( MIN(Date_Entry), '%Y' ) FROM DataLoggersSensorsValues WHERE DataLogger_ID = 2; SELECT DATE_FORMAT( MAX(Date_Entry), '%Y' ) FROM DataLoggersSensorsValues WHERE DataLogger_ID = 2;
J'ai un index sur la date et une sur le DataLogger_ID. Je ne peux faire plus ? Pourquoi est-ce aussi long si MySQL est supposé être rapide pour ce genre de traitement ?

Pour les procédures stocké, je suis désolé si je me suis trompé, j'avais lu ça d'un post sur Expert Exchange's, Je n'ai pas trouvé comment et où sont stocké les procédures stockés sous MySQL :(

Merci pour votre aide.

23/02/2005, 23h21
Hubert Roksor
En théorie, il est possible de distribuer une table sur plusieurs emplacements en utilisant l'option RAID TYPE. Pour ma part, je ne l'ai jamais utilisée, ni ne connais de personne l'utilisant. Dans le même genre, il est également possible de stocker les index et/ou les données de chaque table dans des dossiers séparés. (option "DATA DIRECTORY" de CREATE TABLE)

Mais avant toute chose, pourquoi souhaites-tu stocker la table dans différentes partitions ? est-ce pour des questions de taille (table plus grande que le disque dur) ou de rapidité ? Dans le second cas je ne suis pas sûr qu'il y ait grand-chose à y gagner... Autre chose, de quelle(s) manière(s) tes requêtes utilisent-elles le champ Date_Entry ? selon l'utilisation que tu en fais, tu pourrais remplacer ce type de champ par un champ INT tout simple, contenant un timestamp UNIX pour représenter la date. La solution est valable si tu accèdes aux données de façon linéaire et que les dates sont comprises entre 1970 et 2037 (à peu près), par exemple:
- tous les enregistrements par ordre chronologique
- tous les enregistrements de mars 2003
- les enregistrements du 2 février 2004 à 7h09 au 4 janvier 2005 à 0h00
En revanche cela ne marche pas avec des requêtes portant sur une caractéristique propre à la date, comme le nom (ou numéro) du jour ou du mois:
- tous les enregistrements classés par jour de la semaine
- les enregistrements du mois de mars de chaque année
L'avantage d'un timestamp face à un champs DATETIME est qu'il nécessite deux fois mois de place (4 octets contre 8 pour le DATETIME) et que les index sont bien plus efficaces.

Autre chose, y a-t'il d'autres champs que les quatre que tu as cité ?
23/02/2005, 23h37
Hubert Roksor

Je viens de relire le dernier post, qu'est-ce que le champ DataLogger_ID ? J'ai également oublié de demander quels index étaient défini pour la table, il y a de fortes chances qu'ils soit mal définis ou mal utilisés (cas le moins probable) par MySQL. Vérifie avec EXPLAIN que les bons indices sont utilisés.
24/02/2005, 15h06
Maximil ian

Citation:

Envoyé par Erakis

Ma criticité est basé sur le fait que présentement j'ai que 3 millions d'enregistrement et que nous nous attendons à en avoir 5 millions et plus par mois. Pour le moment, je dois parcourir ma table à la recherche de l'année la plus élevé et la plus basse pour une station, et je dois te dire que ça prends facilement 4-5 secondes pour la recheche sur un P4 2.8Ghz

Ce que je voulais dire, c'est que la base de données servant pour le forum du site internet de ta boîte n'est peut-être pas d'une importance aussi critique que celle qui sert à gérer les ventes et les commandes clients, avec des dizaines d'utilisateurs connectés, plusieurs centaines de milliers de requêtes par heure et des millions d'enregistrements.
Dans le premier cas MySQL est un excellent choix, dans le deuxième il vaut mieux y réfléchir.

Citation:

Envoyé par Erakis

J'ai un index sur la date et une sur le DataLogger_ID. Je ne peux faire plus ? Pourquoi est-ce aussi long si MySQL est supposé être rapide pour ce genre de traitement ?

Normalement l'index stocke les valeurs dans l'ordre, donc il devrait trouver instantanément le max et le min. Comme Hubert Roksor le conseille, utilise EXPLAIN pour voir les index qui sont réellement utilisés et si le moteur n'est pas obligé de faire un sort complet de la table.

Merci à vous deux.
Voilà pour la création de la table.

Code:

1
2
3
4
5
6
7
8
9
10
11
12
 
CREATE TABLE `dataloggerssensorsvalues` (
  `Date_Entry` datetime NOT NULL default '0000-00-00 00:00:00',
  `DataLogger_ID` int(11) NOT NULL default '0',
  `Sensor_ID` int(11) NOT NULL default '0',
  `Value_Entry` float NOT NULL default '0',
  `Validation_Date` datetime default NULL,
  `Validation_User` int(11) default NULL,
  PRIMARY KEY  (`Date_Entry`,`DataLogger_ID`,`Sensor_ID`),
  KEY `Date_Entry_Index` (`Date_Entry`),
  KEY `Data_Logger_ID_Index` (`DataLogger_ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED;

Le champs DataLogger_ID est une Foreign key vers une table de stations (DataLogger).

Code:

1
2
3
4
5
6
7
8
9
10
11
12
 
CREATE TABLE `dataloggers` (
  `ID` int(11) NOT NULL auto_increment,
  `Name` varchar(50) NOT NULL default '',
  `Location` varchar(50) NOT NULL default '',
  `Version` varchar(10) NOT NULL default '''Unknow''',
  `SerialNumber` varchar(50) NOT NULL default '''Unknow''',
  PRIMARY KEY  (`ID`),
  UNIQUE KEY `ID` (`ID`),
  UNIQUE KEY `Name` (`Name`),
  KEY `ID_Index` (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Comme je suis habitué à travailler avec MSSql Server, je ne connais pas vraiment la commande EXPLAIN alors je vais tenter de trouver de l'aide avec la référence en ligne de MySQL. Malgré ce problème je tiens à dire tout de même que MySQL est un EXCELLENT outil et que j'y trouve des fonctionnalités très intéressantes comparé à MSSQL 2000.

En même temps j'en profite pour vous demander si c'est possible de regrouper les valeurs (AVG) en BLOC de 4h ? Ex:

Voilà la requête qui me sort mes enregistrement pour 1 mois donné

Code:

1
2
 
SELECT * FROM DataLoggersSensorsValues WHERE (DataLogger_ID = 4) AND (Date_Entry BETWEEN '2004-02-01 00:00:00' AND '2004-03-01 00:00:00')

Pour le moment (à titre de tests) la table me retourne 53 000 enregistrements. Ex :

Code:

1
2
3
4
5
6
7
8
9
10
11
 
+---------------------+---------------+-----------+-------------+-----------------+-----------------+
| Date_Entry          | DataLogger_ID | Sensor_ID | Value_Entry | Validation_Date | Validation_User |
+---------------------+---------------+-----------+-------------+-----------------+-----------------+
| 2004-02-01 00:00:00 |             4 |         2 |    0.155244 | [NULL]          |          [NULL] |
| 2004-02-01 00:00:00 |             4 |         1 |      8.5435 | [NULL]          |          [NULL] |
| 2004-02-01 00:00:00 |             4 |         4 |    0.146998 | [NULL]          |          [NULL] |
| 2004-02-01 00:00:00 |             4 |         5 |     65.4648 | [NULL]          |          [NULL] |
| 2004-02-01 00:00:00 |             4 |         6 |     4.85574 | [NULL]          |          [NULL] |
| 2004-02-01 00:05:00 |             4 |         3 |     7.32606 | [NULL]          |          [NULL] |
+---------------------+---------------+-----------+-------------+-----------------+-----------------+

Je dois regrouper ces valeurs sous bloc de 4h afin de les afficher dans une grille du genre :

Code:

1
2
3
4
5
6
7
 
Heure -->    00 à 04 | 04 à 08 | 08 à 12 | 12 à 16 | 16 à 20  | 20 à 24
Jour 1 -->
Jour 2 -->
Jour 3 -->
Jour 4 -->
...

Merci encore pour votre soutient.

Quelques remarques, dans l'ordre:

Citation:

Envoyé par Erakis

ROW_FORMAT=COMPRESSED

Est-ce que tu as compressé la table avec myisampack ? Auquel cas, les performances seront moindres. Si mes souvenirs sont bons tu peux la décompresser avec

Code:

ALTER TABLE dataloggerssensorsvalue. ROW_FORMAT=FIXED

Citation:

demander si c'est possible de regrouper les valeurs (AVG) en BLOC de 4h

Cela doit être possible avec quelque chose comme

Code:

GROUP BY FLOOR(DATE_FORMAT(Date_Entry, '%H') / 4)

...mais les performances doivent être assez mauvaises.

Code:

SELECT * FROM DataLoggersSensorsValues WHERE (DataLogger_ID = 4) AND (Date_Entry BETWEEN '2004-02-01 00:00:00' AND '2004-03-01 00:00:00')

En ajoutant "EXPLAIN" avant le SELECT tu peux connaitre quels index ont été envisagés et lequel a été utilisé pour la requête. Dans le cas présent, c'est certainement Data_Logger_ID_Index qui a été préféré à Date_Entry_Index parce qu'il s'agit d'une recherche fixe (une seule valeur possible: 4) alors que la date couvre une intervalle. Remplace Date_Entry_Index par un index sur (Data_Logger_ID, Date_Entry) et réessaie.

Citation:

Je dois regrouper ces valeurs sous bloc de 4h afin de les afficher dans une grille [...]

Il faut donc grouper les résultats par tranche de 4 heures et par journée... je pense que dans un tel cas, un timestamp UNIX est plus pratique car on peut lui appliquer des maths directment. Pour le vérifie, je t'invite à créer une nouvelle colonne "Entry_TS" de type INT UNSIGNED, qui représentera le timestamp UNIX associé à la date. Ensuite nous le mettons à jour grâce à cette requête: (évidemment, en temps normal il aurait été calculé au moment de l'insertion)
Code:

1 2 UPDATE dataloggerssensorsvalues SET Entry_TS = UNIX_TIMESTAMP(Date_Entry)
Pour finir, nous créons un index sur (DataLogger_ID, Entry_TS)

Code:

ALTER TABLE dataloggerssensorvalues ADD INDEX logger_by_date (Sensor_ID, Entry_TS)

Ensuite, pour calculer les groupes de 4 heures il suffit d'un calcul relativement simple. Qu'est-ce que l'on sait ?

le timestamp est représenté par le nombre de secondes écoulées depuis le 1er janvier 1970 0h00
4 heures = 14400 secondes
toutes les 86400 secondes un nouveau jour commence

Donc:

FLOOR(Entry_TS / 86400) nous donne le numéro du jour. 01/01/1970 = jour 0, 02/01/1970 = jour 1, etc...
Entry_TS % 86400 nous donne le nombre de secondes écoulées depuis le début du jour en cours
FLOOR((Entry_TS % 86400) / 14400) nous donne le nombre de tranches de 4 heures écoulées depuis le début du jour. Alternativement on peut utiliser FLOOR(Entry_TS / 14400) % 6 qui donnera le même résultat.

Pour récupérer les moyennes de chaque jour du mois de février 2004 par tranche de 4 heures, on peut donc utiliser:
Code:

1 2 3 4 5 SELECT Entry_TS, (FLOOR(Entry_TS / 14400) % 6) AS tranche, AVG(Value_Entry) AS moyenne FROM DataLoggersSensorsValues WHERE Entry_TS BETWEEN UNIX_TIMESTAMP('2004-02-01 00:00:00') AND UNIX_TIMESTAMP('2004-02-29 23:59:59') GROUP BY FLOOR(Entry_TS / 86400), (FLOOR(Entry_TS / 14400) % 6) ORDER BY Entry_TS
Pour avoir le grand jeu:
Code:

1 2 3 4 5 6 7 8 9 10 11 12 SELECT DATE_FORMAT(Entry_TS, '%d-%m-%Y') AS date, CASE (FLOOR(Entry_TS / 14400) % 6) WHEN 0 THEN '00h-04h' WHEN 1 THEN '04h-08h' WHEN 2 THEN '08h-12h' WHEN 3 THEN '12h-16h' WHEN 4 THEN '16h-20h' WHEN 5 THEN '20h-00h' END AS tranche, AVG(Value_Entry) AS moyenne FROM DataLoggersSensorsValues WHERE Entry_TS BETWEEN UNIX_TIMESTAMP('2004-02-01 00:00:00') AND UNIX_TIMESTAMP('2004-02-29 23:59:59') GROUP BY FLOOR(Entry_TS / 86400), (FLOOR(Entry_TS / 14400) % 6) ORDER BY Entry_TS
...mais la requête va certainement générée une grosse table temporaire du fait des champs texte et sera plus lente. L'idéal est de ne demander que le strict minimum, et sous forme numérique pour être plus compact. Résoudre les timestamp de début/fin peut aider un peu je pense, à vérifier:
Code:

1 2 3 4 5 SELECT Entry_TS, FLOOR(Entry_TS / 14400) % 6 AS tranche, AVG(Value_Entry) AS moyenne FROM DataLoggersSensorsValues WHERE Entry_TS BETWEEN 1075590000 AND 1078095599 GROUP BY FLOOR(Entry_TS / 86400), (FLOOR(Entry_TS / 14400) % 6) ORDER BY Entry_TS
Ensuite à toi de formater la date et afficher la tranche horaire de façon lisible pour un humain.

Une dernière note: tout n'a pas été testé donc quelques typos peuvent subsister.

25/02/2005, 23h49
Hubert Roksor

Au fait, si tu as toujours des problèmes de performances, poste les requêtes problématiques avec le tableau EXPLAIN qui va avec.
26/02/2005, 00h29
qi130

Citation:

Envoyé par Hubert Roksor

...tout plein de choses très pointues

:hola:

Chapeau bas !

euh tu maitrises d'autres trucs que MySQL ?

Incroyable :o !
Je ne pouvais pas avoir mieux comme explication, je suis vraiment content. Merci pour tout ces petits trucs, tu m'as certainement mis sur la bonne piste. Merci à tout le monde aussi ;)

Cependant j'ai une dernière question mise en rapport avec ce traitement. J'aimerais bien l'effectuer à l'aide d'une fonction stockée. Comme je suis un habitué de MSSQL Server j'en fais souvent usage. Sont-elles aussi bénéfiques sous MySQL ? Et mon problème principal avec les procédures/fonctions stockées c'est que je suis incapable d'en créer une sous MySQL. Pourtant je suis exactement la doc mysql... J'ouvre la console [MySQL Command Line Client] (Reférence sur la doc : http://dev.mysql.com/doc/mysql/en/create-procedure.html) et j'y entre exactement cela :
Code:

1 2 3 4 5 mysql> delimiter // mysql> CREATE FUNCTION hello (s CHAR(20)) RETURNS CHAR(50) -> RETURN CONCAT('Hello, ',s,'!'); -> //
J'obtiens ceci :
Code:

1 2 3 4 5 ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(s CH AR(20)) RETURNS CHAR(50) RETURN CONCAT('Hello, ', s,'!')' at line 1
Bref, chaque fois que j'essais de créer une fonction ou une procédure j'obtiens ce genre d'erreur, même dans les cas les plus simples.
Cette simple procédure ne passe pas :
Code:

1 2 3 4 5 6 7 mysql> delimiter // mysql> CREATE PROCEDURE simpleproc (OUT param1 INT) -> BEGIN -> SELECT COUNT(*) INTO param1 FROM t; -> END -> //
Voilà ce que j'obtiens :
Code:

1 2 3 4 5 6 ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'PROCE DURE simpleproc (OUT param1 INT) BEGIN SELECT COUNT(*) INTO param1 FROM dat' at line 1
Merci encore.

27/02/2005, 12h35
Hubert Roksor

Citation:

Envoyé par qi130

tu maitrises d'autres trucs que MySQL ?

Euh... le PHP et la chaise à roulette principalement. :D

Citation:

Envoyé par Erakis

Je ne pouvais pas avoir mieux comme explication

C'est cool, j'avais peur d'en faire trop.

Citation:

Envoyé par Erakis

J'aimerais bien l'effectuer à l'aide d'une fonction stockée

Chez MySQL les "fonctions" sont en fait des appels à des bibliothèques (donc du C, généralement). Quant aux procédures stockées... elles sont malheureusement encore très jeunes chez MySQL et disponible uniquement à partir des dernières versions alpha (5.0.x). Si ton serveur est sous Linux tu peux essayer la 5.0.2 (trop buggée sous Windows) ou compiler la 5.0.3-dev pour tester, mais il va encore falloir un peu de temps pour que la lignée soit déclarée stable, désolé.

Au fait, j'ai retesté la requête que j'ai posté plus haut et effectivement indiquer le timestamp explicitement (l'indique sous forme numérique) améliore réellement les performances.
28/02/2005, 18h39
Erakis

Hubert Roksor vous êtes un PRO ;)
Ce post à été des plus avantageux pour moi et pour tout le monde à vrai dire ;).

Milles merci.