Créer une partition pour une table

**Erakis** · 18/02/2005, 22h39

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.

**mathieu** · 18/02/2005, 23h57

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"

**Erakis** · 19/02/2005, 09h20

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 ?

**Erakis** · 21/02/2005, 19h03

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

**qi130** · 22/02/2005, 10h30

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...

**Erakis** · 22/02/2005, 16h59

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

**qi130** · 22/02/2005, 20h45

1 table par Station_id, si le nombre est raisonnable ?

**Erakis** · 22/02/2005, 22h47

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.

**qi130** · 23/02/2005, 09h56

Il y aurait peut-être une solution basée sur une ou plusieurs procédures stockées:

[mode conception on-line]

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 ?

**Erakis** · 23/02/2005, 15h07

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.

**qi130** · 23/02/2005, 15h43

Pour la liste, c'est par là : http://dev.mysql.com/doc/mysql/fr/show-tables.html

**Maximil ian** · 23/02/2005, 17h10

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

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...

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

**Erakis** · 23/02/2005, 18h52

Envoyé par Maximilian

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

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...

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

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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.

**Hubert Roksor** · 23/02/2005, 23h21

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é ?

**Hubert Roksor** · 23/02/2005, 23h37

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.

**Maximil ian** · 24/02/2005, 15h06

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.

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.

**Erakis** · 24/02/2005, 18h42

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

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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.

**Hubert Roksor** · 25/02/2005, 23h44

Quelques remarques, dans l'ordre:

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

ALTER TABLE dataloggerssensorsvalue. ROW_FORMAT=FIXED

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

Cela doit être possible avec quelque chose comme

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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

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

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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.

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
UPDATE dataloggerssensorsvalues
SET Entry_TS = UNIX_TIMESTAMP(Date_Entry)

Pour finir, nous créons un index sur (DataLogger_ID, Entry_TS)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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.