Existe t'il une facon intelligente de gérer une table de log

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

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.

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

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)

Hello

Voici ce que tu me demandais :

Code:

---

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:

---

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.

Code:

---

CREATE TABLE `event`

---

Code:

---

SELECT count(*) FROM avl_event a;

---

Ce n'est pas la même table ! :?

Citation:

---

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 ?

Citation:

---

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.

Citation:

---

Envoyé par CinePhil

Code:

---

CREATE TABLE `event`

---

Code:

---

SELECT count(*) FROM avl_event a;

---

Ce n'est pas la même table ! :?

---

Oups, flagrant délit de changement de nom pour simplifier les requêtes... c'est la même table en fait, j'ai juste enlevé les préfixe qui traine de partout.

Citation:

---

Envoyé par CinePhil

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

---

En fait, elles sont nombreuses à être concernées par le problème et ont déjà été pas mal optimisées.

Je cherche plus une solution de conception qu'une solution ou je changerait les requêtes. Si vous pensez que la table est non optimisable, je me débrouillerais avec les requêtes.(j'arrive a les optimiser les une après les autres, mais bon, ca prend un max de temps)

Citation:

---

Envoyé par CinePhil

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.

---

Effectivement, il est possible d'utiliser plusieurs système pour accéder à la base de donnée. Nous essayons de conserver au maximum l'utilisation de propel (criteria).
Certaines requêtes sont déjà écrite en dur, mais si l'on devait le faire pour toutes les requêtes contenant cette table, nous aurions presque toutes les requêtes du logiciel. C'est vraiment LA table centrale pour nous. Et malheureusement la plus grosse...

exemple de requete qui pose problème :

Code:

---

1 2 3 4 5 6 7 8 9

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 WHERE event.VEHICLE_ID=7 AND event.EVENT_TYPE_ID=2 AND (event.CREATED_AT>='2010-01-24 05:00:00' AND event.CREATED_AT<='2010-01-25 04:59:00') ORDER BY event.CREATED_AT ASC LIMIT 1

---

sachant bien évidement que nous répétons cette requête 2 fois par objet, soit 40 fois sur la page actuellement, et surement 10 fois plus dans quelques mois. La requête prend entre 1 et 4 secondes.

Essaie déjà de remplacer ceci :

Code:

---

1 2	AND (event.CREATED_AT>='2010-01-24 05:00:00' AND event.CREATED_AT<='2010-01-25 04:59:00')

---

par cela :

Code:

---

1 2	AND event.CREATED_AT BETWEEN '2010-01-24 05:00:00' AND '2010-01-25 04:59:00'

---

Citation:

---

Envoyé par pmithrandir

exemple de requete qui pose problème :
sachant bien évidement que nous répétons cette requête 2 fois par objet

---

Pourquoi donc ? 8O
A cause des restrictions sur VEHICLE_ID et EVENT_TYPE_ID ?

Citation:

---

Envoyé par CinePhil

Essaie déjà de remplacer ceci :

Code:

---

1 2	AND (event.CREATED_AT>='2010-01-24 05:00:00' AND event.CREATED_AT<='2010-01-25 04:59:00')

---

par cela :

Code:

---

1 2	AND event.CREATED_AT BETWEEN '2010-01-24 05:00:00' AND '2010-01-25 04:59:00'

---

---

Aucun changement, c'est presque même plus long en fait.

Citation:

---

Envoyé par CinePhil

Pourquoi donc ? 8O
A cause des restrictions sur VEHICLE_ID et EVENT_TYPE_ID ?

---

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)

PS : avec le bon index ca me donne... 0.5 sec...

Code:

---

1 2 3 4 5 6 7 8

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 USE INDEX (event_FI_created_at) WHERE event.VEHICLE_ID=7 AND event.EVENT_TYPE_ID=2 AND event.CREATED_AT BETWEEN '2010-01-24 05:00:00' AND '2010-01-25 04:59:00' ORDER BY event.CREATED_AT ASC LIMIT 1

---

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

Code:

---

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

---

Cela améliore-t-il les performances?

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.

Citation:

---

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 ? 8O

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:

---

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.

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

Code:

---

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.

Tu dois même pouvoir faire plus simple :

Code:

---

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;

---