Discussion :

[Juseba]

Bonjour,

Je réalise actuellement un système de gestion de données émis pas un grand nombre de capteur.
Je souhaiterai pouvoir faire « rapidement » des statistiques sur la fréquence d’utilisation des capteurs.
Par Statistiques j’entends : Être capable de générer un tableau qui classerait par fréquence d’utilisation des adresses (champs addr) émise lors de la dernière heure, puis lors du dernier jour. Pour voir quelles adresses sont le plus souvent sollicité. Le problème pour moi, c’est que c’est un jour, ou une heure « glissante » pour coller au mieux à la réalité en temps réel.
Voilà le schéma de ma table principale :

Code SQL :

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

1
2
3
4
5
6
7
8
CREATE TABLE IF NOT EXISTS `cap_value` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `addr` varchar(32) NOT NULL,
  `date` int(10) unsigned NOT NULL,
  `target_id` int(10) unsigned NOT NULL,
  `from_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Je reçois en moyenne 2 valeurs par secondes, donc en un jour la table grandi de 170 000 lignes, 10 jours après j’ai donc environs 1.7 millions de lignes, d’où le besoin de trouver un système fiable, rapide et indépendant du nombre de ligne.
Pour le moment le meilleur moyen que j’ai trouvé est une requête de ce style :

Code SQL :

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

1
2
3
4
5
SELECT COUNT(*) AS total, addr
FROM `cap_value`
WHERE `date` >=1330270773 /*<----- H-1 ou J-1*/
GROUP BY addr
ORDER BY total DESC

Sur une table de 8 Millions de lignes cette requête dure 4 secondes, sachant que l’application est en php et destiné au "web", le temps est trop long, même avec un petit système de cache j’ai peur que la charge du serveur (qui est plutôt modeste au passage) ne cesse de monter.
Dans un premier temps, est-ce que vous pensez que mon schéma est correct, et ensuite comment est-ce que vous procéderiez ? Créer une seconde table pour garder que les enregistrements des dernières 24h serait-elle une meilleure approche ?

Est-ce que la création d'une vue serait une autre alternative ?

Cordialement,

Ps : "addr" est composé au maximum de 32 caractères (^[a-z0-9]{2,32}$ exactement) et peux prendre des milliers de valeurs.

[CinePhil]

La première chose à examiner quand on a un problème de performance : les index.

La colonne addr est-elle indexée ?

[Maljuna Kris]

Saluton,
En toute première analyse je dirais que j'espère que la colonne addr est indexée.

Créer une seconde table pour garder que les enregistrements des dernières 24h serait-elle une meilleure approche ?

Le problème consisterait alors à choisir la procédure et/ou la fréquence pour purger cette table vers la table définitive. Un trigger horaire ?

[Juseba]

Bonjour, merci pour vos réponses.

Coté index j'ai fait ceci :

Code :

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

1
2
3
Key_name 	Seq_in_index 	Column_name	Index_type
PRIMARY		1		id		BTREE
addr		1		addr		BTREE

Le problème consisterait alors à choisir la procédure et/ou la fréquence pour purger cette table vers la table définitive. Un trigger horaire ?

Avant chaque requête pour générer les stats je ferais un petit "DELETE .. WHERE date <= time()-1j" ? le problème étant de conserver "l'heure ou le jour glissant" donc impossible de fonctionner avec un cron qui irait toutes les heures supprimer les vieux enregistrements. Au mieux je peux me permettre un décalage de 10 minutes grand maximum.

[ericd69]

ou une procédure stockée appelée en cron...

en tout cas, comme on doit atteindre les limites de ce que peut mysql, un partitionnement vertical...

tu peux le faire 1 fois par jours ça suffit

par contre un index sur addr ne partitionnant pas de par ce que fait sa requête... sur 8 millions de tuples et plus, ça n'apportera rien...

à moins que d'autres requêtes ne soit prévue et cherchant une adresse précise...

de toute façon un explain sur la requête te confirmera son utilisation

[skuatamad]

Il faudrait un index sur la date.

Avant chaque requête pour générer les stats je ferais un petit "DELETE .. WHERE date <= time()-1j" ?

Ben avant chaque requête ça ne sera pas intéressant, mais tu peux historiser la table chaque nuit par exemple.

[Jean.Cri1]

Je vais peut etre me faire taper dessus :

a quoi sert ton id ?

[ericd69]

c'est l'index automatique sur les clés primaires de toutes les table

tu as jamais fait attention avant?

[Jean.Cri1]

Je ne connais pas les specificités de mysql.

Est ce obligatoire d'avoir une cle primaire ?

et si non -c'etait le sens de ma question - a quoi sert elle ici ?

[ericd69]

la clé primaire est une valeur unique associée à chaque ligne d'une table (l'identifiant de la ligne)

elle va donc servir dans les recherches ou les fonctions pour déterminer le nombre de lignes dans la table

c'est généralement un compteur auto incrémenté mais on peut utiliser des types non numériques (hyper déconseillé)

elle est auto indexée pour optimiser un certain nombre d'opérations

[Juseba]

Bonjour,

L'id ici me sert a faire des suppressions ciblé de telle ou telle ligne, voir certaines plages, ou dans certains cas plus rare la lecture de certaines lignes.

Merci pour vos réponses, je vais donc partir sur un cron journalier + l'indexation de "date", c'est la seule solution qui me permet de faire des stats sur une heure glissante à priori :/

Bonne journée