Discussion :

[pmithrandir]

Bonjour

Je travaille sur un projet qui est basé en grande partie sur le reporting d'objets en mouvements. Toutes les 5 secondes, les objets nous envoient leurs nouvelles coordonnées et nous ajoutons ces nouveaux évènements dans une table.

Actuellement, la table fait environ entre 220Mo de donnée et autant d'index pour 2 millions de tuples.

Il n'est pas possible d'utiliser le cache parce que les objets bougent tout le temps, donc la table a constamment des ajouts.

C'est une table toute bête du type :
ID
Type
latitude
longitude
data

Est ce que vous verriez une autre façon de stocker ces évènements, peut être avec un moteur de base de données spécifique ou je ne sais quelle idée pour que nous puissions optimiser ces traitements ? C'est le gros point noir de notre site au niveau performance pour l'instant.

Merci

Pierre

[mactwist69]

Bonjour,

La ou je travail, les bases de données qu'on manipule possède plusieurs million de ligne.

Nous utilisons donc le moteur MyIsam de MySQL pour optimiser la rapidité en lecture/écriture.

Le moteur MyIsam est non-transactionnel, ne gère pas les clés étrangère, donc il n'a pas à vérifier la validité des enregistrement. Donc l'optimisation est maximum.

Donc toute nos tables ont été créer sur ce moteur.

En espérant que cela puisse aider.

[pmithrandir]

Hello

Effectivement, rétrograder de moteur de base de donnée pour supprimer la notion de transaction et de clef étrangère nous ferait gagner du temps. Malgré tout, je pense que ce n'est pas une solution envisageable pour mon cas. Le projet est déjà en production et c'est quand même une modification majeure qui peut impacter la base de donnée. En particulier en enlevant les tests d'intégrité ce qui ne me plait pas du tout.

En fait, je pensait plus a une solution de conception différente, ou a un autre système de log. (création d'une autre table, organisation des données, etc...)

[CinePhil]

Qu'est-ce qui coince au niveau performances ?
Des SELECT ? Si oui lesquels ?
Des INSERT ? Ce serait plus compréhensible vu le volume de données.

On peut avoir le descriptif complet de la table ? (SHOW CREATE TABLE)

[pmithrandir]

Hello

Voici ce que tu me demandais :

Code :

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

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
 
CREATE TABLE `event` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `event_type_id` int(11) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `message` varchar(255) DEFAULT NULL,
  `received` datetime DEFAULT NULL,
  `driver_id` int(11) DEFAULT NULL,
  `vehicle_id` int(11) DEFAULT NULL,
  `longitude` double DEFAULT NULL,
  `quadrant_long` char(1) DEFAULT NULL,
  `latitude` double DEFAULT NULL,
  `quadrant_lat` char(1) DEFAULT NULL,
  `heading` float DEFAULT NULL,
  `speed` float DEFAULT NULL,
  `location_name` varchar(250) DEFAULT NULL,
  `validity` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `event_FI_2` (`driver_id`),
  KEY `event_FI_3` (`event_type_id`),
  KEY `event_FI_received` (`received`),
  KEY `event_FI_created_at` (`created_at`),
  KEY `event_FI_1` (`vehicle_id`),
  CONSTRAINT `event_FK_1` FOREIGN KEY (`vehicle_id`) REFERENCES `vehicle` (`id`),
  CONSTRAINT `event_FK_2` FOREIGN KEY (`driver_id`) REFERENCES `driver` (`id`) ON DELETE SET NULL,
  CONSTRAINT `event_FK_3` FOREIGN KEY (`event_type_id`) REFERENCES `event_type` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1999499 DEFAULT CHARSET=latin1'

Je ne crois pas que les INSERT posent vraiment de problème, mais je ne maitrise pas cette partie du logiciel.(je peux me renseigner par contre, ca peut nous éviter de mauvaises surprises)

Le problème principal est que l'on ne peut jamais utiliser de cache du fait de l'enregistrement en continue sur la table).
En général, on fait des stat a partir de cette table pour des valeurs de "created_at" comprise entre 2 dates. C'est la que c'est le plus problématique, parce que ce n'est pas très rapide. Et comme on a pas de cache, toutes les requêtes et sous requêtes sont lentes...

par exemple :

Code :

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

1
2
 
SELECT count(*) FROM avl_event a;

prend 5 sec sur ma machine de dev.

Nous travaillons sur symfony, donc nous n'avons pas non plus la possibilité de faire des modifications trop proche de la base de donnée. Par exemple, il nous est impossible d'utiliser USE INDEX alors que ca augmenterait sensiblement nos performances souvent.

[CinePhil]

Code :

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

CREATE TABLE `event`

Code :

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

SELECT count(*) FROM avl_event a;

Ce n'est pas la même table !

En général, on fait des stat a partir de cette table pour des valeurs de "created_at" comprise entre 2 dates.

Deux dates de type datetime ou de type date ?
On peut voir la requête ?

Nous travaillons sur symfony, donc nous n'avons pas non plus la possibilité de faire des modifications trop proche de la base de donnée. Par exemple, il nous est impossible d'utiliser USE INDEX alors que ca augmenterait sensiblement nos performances souvent.

Pour autant que je sache, Symfony offre des outils pour fabriquer lui même les requêtes mais rien n'interdit de construire la requête à la main et de lui demander de la soumettre sans modification ? Enfin j'espère que c'est possible sinon c'est une lacune de Symfony.

[danidan75]

Perso, je te conseillerais une clef à 3 entrées, type:

Code :

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

KEY `whatever` (`vehicle_id`, `event_type_id`, `created_at`)

Cela améliore-t-il les performances?

[pmithrandir]

J'ai changé l'index event_type en ajoutant le champ created_at en second paramètre.

Mes requêtes sont incroyablement plus rapides... j'essaye de voir si cela n'introduit pas des lenteurs ailleurs.(insertion peut être). Merci en tout cas, j'avais essayé cette piste il y a quelques temps, mais en me replongeant dedans j'ai pu faire mieux.

[CinePhil]

En gros, pour chaque véhicule, nous devons avoir le premier évènement de type 2 de la période, ainsi que le dernier évènement de type 3 sur cette même période.

Bref, sachant qu'on a pas le droit au sous requêtes avec les criteria, c'est assez difficile a imaginer en global. (déjà que c'est pas super facile autrement)

J'ai peur de comprendre...
La requête est exécutée pour chaque véhicule et chaque type d'événement ?

Je ne comprends pas bien cette histoire d'interdiction de sous-requêtes en criteria mais admettons... Tu as essayé par une table temporaire ?

Si j'ai bien compris le besoin, essaie ça, éventuellement en ajoutant le USE INDEX :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
CREATE TEMPORARY TABLE tmp
SELECT event.VEHICLE_ID, event.EVENT_TYPE_ID, MAX(event.CREATED_AT) AS Date_dernier_event
FROM event
WHERE event.CREATED_AT BETWEEN '2010-01-24 05:00:00' AND '2010-01-25 04:59:00'
GROUP BY event.VEHICLE_ID, event.EVENT_TYPE_ID;
 
ALTER TABLE tmp
ADD INDEX(VEHICLE_ID, EVENT_TYPE_ID);
 
SELECT event.ID, event.VEHICLE_ID, event.DRIVER_ID, event.EVENT_TYPE_ID, event.CREATED_AT, event.MESSAGE, event.RECEIVED, event.LONGITUDE, event.QUADRANT_LONG, event.LATITUDE, event.QUADRANT_LAT, event.HEADING, event.SPEED, event.LOCATION_NAME, event.VALIDITY
FROM event
INNER JOIN tmp ON event.VEHICLE_ID = tmp.VEHICLE_ID
  AND event.EVENT_TYPE_ID = tmp.EVENT_TYPE_ID
WHERE event.CREATED_AT = tmp.Date_dernier_event;
 
DROP TABLE tmp;

Ensuite le programme traite le jeu de résultats.

[pmithrandir]

J'ai un peu creusé pour avoir pile poil ce qu'il me faut et ca me donne ca :

Code :

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

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
35
36
37
38
39
40
 
CREATE TEMPORARY TABLE tmpMax
SELECT event.VEHICLE_ID, event.event_TYPE_ID, MAX(event.CREATED_AT) AS Date_last_event
FROM event
WHERE event.CREATED_AT BETWEEN '2010-01-24 05:00:00' AND '2010-01-25 04:59:00'
GROUP BY event.VEHICLE_ID, event.event_TYPE_ID;
 
CREATE TEMPORARY TABLE tmpMin
SELECT event.VEHICLE_ID, event.event_TYPE_ID, MIN(event.CREATED_AT) AS Date_first_event
FROM event
WHERE event.CREATED_AT BETWEEN '2010-01-24 05:00:00' AND '2010-01-25 04:59:00'
GROUP BY event.VEHICLE_ID, event.event_TYPE_ID;
 
ALTER TABLE tmpMax
ADD INDEX(VEHICLE_ID, event_TYPE_ID);
 
ALTER TABLE tmpMin
ADD INDEX(VEHICLE_ID, event_TYPE_ID);
 
(
  SELECT event.ID, event.VEHICLE_ID, event.DRIVER_ID, event.event_TYPE_ID, event.CREATED_AT, event.MESSAGE, event.RECEIVED, event.LONGITUDE, event.QUADRANT_LONG, event.LATITUDE, event.QUADRANT_LAT, event.HEADING, event.SPEED, event.LOCATION_NAME, event.VALIDITY
  FROM event
  INNER JOIN tmpMax ON event.VEHICLE_ID = tmpMax.VEHICLE_ID
    AND event.event_TYPE_ID = tmpMax.event_TYPE_ID
  WHERE event.CREATED_AT = tmpMax.Date_last_event
    AND event.event_type_id = 3
)
UNION
(
  SELECT event.ID, event.VEHICLE_ID, event.DRIVER_ID, event.event_TYPE_ID, event.CREATED_AT, event.MESSAGE, event.RECEIVED, event.LONGITUDE, event.QUADRANT_LONG, event.LATITUDE, event.QUADRANT_LAT, event.HEADING, event.SPEED, event.LOCATION_NAME, event.VALIDITY
  FROM event
  INNER JOIN tmpMin ON event.VEHICLE_ID = tmpMin.VEHICLE_ID
    AND event.event_TYPE_ID = tmpMin.event_TYPE_ID
  WHERE event.CREATED_AT = tmpMin.Date_first_event
    AND event.event_type_id = 2
)
ORDER BY VEHICLE_ID, event_type_id
 
DROP TABLE tmpMax;
DROP TABLE tmpMin;

Je vais donc creuser ca un peu plus. Si c'est trop compliqué je garde ca sous le coude pour le moment ou nous aurons plus de client, et ou le besoin sera plus fort.

Merci à vous en tout cas.

[CinePhil]

Tu dois même pouvoir faire plus simple :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
CREATE TEMPORARY TABLE tmp
SELECT event.VEHICLE_ID, event.EVENT_TYPE_ID, 
  MAX(event.CREATED_AT) AS Date_dernier_event,
  MIN(event.CREATED_AT) AS Date_premier_event
FROM event
WHERE event.CREATED_AT BETWEEN '2010-01-24 05:00:00' AND '2010-01-25 04:59:00'
GROUP BY event.VEHICLE_ID, event.EVENT_TYPE_ID;
 
ALTER TABLE tmp
ADD INDEX(VEHICLE_ID, EVENT_TYPE_ID);
 
SELECT event.ID, event.VEHICLE_ID, event.DRIVER_ID, event.EVENT_TYPE_ID, event.CREATED_AT, event.MESSAGE, event.RECEIVED, event.LONGITUDE, event.QUADRANT_LONG, event.LATITUDE, event.QUADRANT_LAT, event.HEADING, event.SPEED, event.LOCATION_NAME, event.VALIDITY
FROM event
INNER JOIN tmp ON event.VEHICLE_ID = tmp.VEHICLE_ID
  AND event.EVENT_TYPE_ID = tmp.EVENT_TYPE_ID
WHERE event.CREATED_AT = tmp.Date_dernier_event;
 
DROP TABLE tmp;