[MySQL] Répartir des données dans plusieurs tables

Répartir des données dans plusieurs tables [MySQL] - SQL/NoSQL - Programmation

Marsh Posté le 16-05-2011 à 09:26:31    

Hello,
 
j'ai une petite question MySQL:
 
J'ai un script qui récupere des données chaque minutes, et les met dans une table. Jusque là rien de sorcier.
 
le probleme c'est que ça fait un paquet de rows, et j'aimerai bien faire de façon dégréssif au bout d'un certain temps.
Ce que je veux dire c'est qu'en gros je ne garde qu'une row sur 5 une fois passé les 1 semaine, puis une sur 25 une fois passé un mois, etc...
 
en gros j'ai pensé à bouger ces 'vieilles' données dans une autre table pour chaque précision (une 5min, une 25min, etc), et supprimer les rows dont je n'ai plus besoin... seul probleme, je sais pas comment faire pour trier :??:  
 
autre solution: tout garder dans la même table, mais quand meme faire le tri. (à la limite ça m'arrange, mais j'ai peur que ça ralentisse mes requetes sql)
 
la structure:
 

+-------------+-----------------------+------+-----+---------+----------------+
| Field       | Type                  | Null | Key | Default | Extra          |
+-------------+-----------------------+------+-----+---------+----------------+
| id          | int(10) unsigned      | NO   | PRI | NULL    | auto_increment |
| date        | datetime              | NO   |     | NULL    |                |
| value       | mediumint(8) unsigned | NO   |     | NULL    |                |
| region      | varchar(5)            | NO   | MUL | NULL    |                |
| site        | varchar(5)            | NO   |     | NULL    |                |
| description | varchar(30)           | NO   |     | NULL    |                |
+-------------+-----------------------+------+-----+---------+----------------+

Reply

Marsh Posté le 16-05-2011 à 09:26:31   

Reply

Marsh Posté le 16-05-2011 à 10:38:55    

Faire un tri se fait avec "ORDER BY..." à la fin du SELECT.
 
Le tri n'est fait que lors de l'extraction des données. Lors de l'insertion des données, c'est la base de données qui se débrouille et le programmeur n'a pas à connaître sa cuisine interne.
 
La deuxième solution n'est pas à craindre car le ralentissement ne sera pas sensible, si l'index est correctement défini. Donc, dans le cas précis, il sera peut-être intéressant d'ajouter un index secondaire sur la date.


Message édité par olivthill le 16-05-2011 à 10:39:45
Reply

Marsh Posté le 17-05-2011 à 13:07:26    

Y'a la solution des partitions aussi ;)
 
Ou faire comme le soft Piwik : une table par période (le mois, par ex), ça simule en qq sorte les partitions.


Message édité par rufo le 17-05-2011 à 13:18:43

---------------
Astres, outil de help-desk GPL : http://sourceforge.net/projects/astres, ICARE, gestion de conf : http://sourceforge.net/projects/icare, Outil Planeta Calandreta : https://framalibre.org/content/planeta-calandreta
Reply

Marsh Posté le 17-05-2011 à 13:46:24    

Oui mais avant de sortir les chars d'assaut il faudrait en savoir un peu plus ... parce qu'il s'il a 10'000 lignes on va emmerder le système plus qu'autre chose...


---------------
Si la vérité est découverte par quelqu'un d'autre,elle perd toujours un peu d'attrait
Reply

Marsh Posté le 17-05-2011 à 15:42:12    

Effectivement, 10000 enregistrements par semaine (en supposant que ça tourne H24), soit 520000 par an, c'est pas énorme :/
 
mobyfab, chaque nuit, j'ai une BD qui est alimentée avec plus de 45 millions d'enregistrements, répartis dans 3 tables et exploitées durant la journée, sous Mysql, ça tient très bien la charge du moment que c'est bien indexé ;)


---------------
Astres, outil de help-desk GPL : http://sourceforge.net/projects/astres, ICARE, gestion de conf : http://sourceforge.net/projects/icare, Outil Planeta Calandreta : https://framalibre.org/content/planeta-calandreta
Reply

Marsh Posté le 18-05-2011 à 16:36:52    

C'est à coup de 111 INSERT par minute, ça devrais pas trop changer mais ça peut augmenter. 1700000 rows pour l'instant (63m pour 13 mois). ça tourne bien H24. C'est du MyISAM.
Pour aller chercher des données "au milieu" j'ai eu pas mal de soucis niveau vitesse... même avec des indexes.
j'ai un index primary sur id, et un sur region+date (dual)
 
example:


mysql> SELECT id FROM table WHERE region = 'CN' AND date < SUBDATE(NOW(), INTERVAL 10000 MINUTE) ORDER BY id DESC LIMIT 1;                                                    
+---------+
| id      |
+---------+
| 1252100 |
+---------+
1 row in set (0.78 sec)
 
mysql> EXPLAIN SELECT id FROM table WHERE region = 'CN' AND date < SUBDATE(NOW(), INTERVAL 10000 MINUTE) ORDER BY id DESC LIMIT 1;
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | table | index | dual          | PRIMARY | 4       | NULL |    2 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)
 


Sans index c'est 2 fois plus lent.
 
du coup j'ai du utiliser:


mysql> SELECT id FROM table WHERE region = 'CN' AND date BETWEEN SUBDATE(NOW(), INTERVAL 10000+20 MINUTE) AND SUBDATE(NOW(), INTERVAL 10000 MINUTE) ORDER BY id DESC LIMIT 1;
+---------+
| id      |
+---------+
| 1252100 |
+---------+
1 row in set (0.00 sec)
 
mysql> EXPLAIN SELECT id FROM table WHERE region = 'CN' AND date BETWEEN SUBDATE(NOW(), INTERVAL 10000+20 MINUTE) AND SUBDATE(NOW(), INTERVAL 10000 MINUTE) ORDER BY id DESC LIMIT 1;
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                       |
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------------+
|  1 | SIMPLE      | table | range | dual          | dual | 25      | NULL | 1097 | Using where; Using filesort |
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------------+
1 row in set (0.00 sec)
 


 
içi quasi immédiat :)
on vois bien la diff type index (ref si pas d'index) vs type range et filesort.
 
Rufo, mysql stock ou mod genre percona ?
 
Sinon j'était parti pour faire une table par période, une du moment présent jusqu’à 1 mois, qui as un sample par minute, puis une table de 1 mois à 3 mois qui garde un sample toutes les 10 minutes, etc...
 
J'aimerai gagner un poil en espace également, vu que là ça vas faire dans les 6 GO par ans et je compte conserver 13 mois de données.


Message édité par mobyfab le 18-05-2011 à 16:44:59
Reply

Marsh Posté le 18-05-2011 à 17:12:36    

mysql stock ou mod genre percona ? => pas bien compris ta question. Si pour la partition, ta question est, comment fait mysql (natif ou plugin), la réponse est du natif mais à partir de la version 5.1 a priori : http://dev.mysql.com/doc/refman/5. [...] oning.html


---------------
Astres, outil de help-desk GPL : http://sourceforge.net/projects/astres, ICARE, gestion de conf : http://sourceforge.net/projects/icare, Outil Planeta Calandreta : https://framalibre.org/content/planeta-calandreta
Reply

Marsh Posté le 18-05-2011 à 17:24:29    

Je voulais juste savoir quelle version de MySQL tu utilise. :)
 
je vais jeter un oeil à ton lien.

Reply

Sujets relatifs:

Leave a Replay

Make sure you enter the(*)required information where indicate.HTML code is not allowed