Je présente clairement mon prob, ca risque d'etre assez long, mais c'est pas tres compliqué.
Je suis en train de réaliser une série de script pour pouvoir analyser en temps réel les logs d'un firewall.
Problem: les logs sont de l'ordre de la dizainepar seconde, et la machine qu'on me donne, c'est pas une bombe.
Apres moults deboire et optimisation, j'ai reussi a faire un parser de logs en perl qui stock tous les logs dans une DB au fur et a mesure, sans trop bouffer de ressources.
Problem: exploitation des logs. La, ca bouffe de la ressource a mort.
contrainte: le script d'insertion tourne continuellement
Je présente ma base simplement :
Une table principale, 'log' dont chaque entrée représente une ligne du log
qqs autres tables qui sont en fait la description des interfaces et hotes source/destination (clés étrangeres quoi)
Pour l'instant, je veux juste obtenir les 30 dernieres entrées.
Si je fais  

• select * from log order by id_log desc limit 0,30

c'est lent, mais ca va encore

• select * from log,autre where log.id=autre.id order by id_log desc limit 0,30

c'est affreusement lent, pas jouable pour de l'analyse temps réel.
 
Ma question, on y vient: j'ai l'impression qu'il fait la jointure et ensuite seulement sélectionne les trente derniers. J'aimerai etre sur qu'il selectionne, et ensuite fait la jointure. Ensuite, Est-ce que mettre des index sur toutes les clés peut augmenter les perfs ?
Parce que la je vois plus trop ...
Merci.
PS: a terme, le passage en libre est envisageable si l'outil me parait sufisement intéressant, et que sufisemment de personnes sont interessées ...

1° Bien sûr qu'il fait la jointure avant, autrement il ne serait pas sûr que les 30 derniers seraient suffisant
2° Une clé est déjà indexée
 
Maintenant pour ton problème, je crois que tu ne prends pas la bonne direction. Utiliser une DB simplement pour regarder les X dernier résultat n'est pas une bonne solution. Commence déjà par instaurer un logrotate sur tes log, ainsi tu pourras sauvgarder tes logs dans des fichiers ou dans un DB si tu veux faire des stats à postériori tranquillement, et il ne te restera plus qu'un fichier de log de taille restrainte que tu pourrais devoir facilement lire, soit à l'oeil nu, soit avec ton script perl légèrement modifié.

Et on ne le répétera jamais assez, pour optimiser une requête MySQL, utilisez explain et aidez-vous de la doc.
http://dev.mysql.com/doc/mysql/fr/EXPLAIN.html

bon, bah je vais voir ce que donne ce explain
Maintenant, pour le fait que je prenne pas la bonne direction, j'y pensais aussi, mais je voyais pas trop quelle direction prendre.
Pour l'instant, je veux juste les trente dernier, mais en exploit', je trierai sur ladresse source, le fait qu'un trafic soit rejeté etc... , et j'ai pas envie de réinventé le SQL
gizmo: et il y a moyen de lui dire de joindre APRES le tri ? ou alors je dois créer a la mano une table temporaire que je rempli avec les log correspondant a mes conditions, sur laquelle je fais ma jointure et que je vide ensuite ?
PS: j'espere etre clair.

non, impossible de demander la jointure après. tout au plus peux-tu mettre ta preière requète dans un subselect, si ta version de MySQL le permet, mais cela reste lourd comme manipulation.

je viens de regarder le EXPLAIN .. et il me fait un truc bizarre:

 
table a : 500000 enregistrements clé primaire ida
table b : 7 enregistrements clé prim idb
table c : 6 enregistrements clé prim idc
 
Les id sont autoincrémenté, en int(11) tous.
la structure de b et c est identique ou presque
 
Le explain me donne le type ALL pour la table b et eq_ref sur la table c; Je ne vois pas la diff entre les deux
Une clé primaire est indexée et unique bien sur? pas besoin de le spécifié en dur ?
Merci
 
PS: tout est effectué avec phpmyadmin si ca peut aider.

pour l'instant, j'ai trouvé une solution paliative, pas propre:
j'ai crée un table log_tmp IDENTIQUE en tout point a log, et pour chauqe requete, je fais:
truncate table log_tmp;
insert into log_tmp select 'ma requete qui fait le trie dans log'
select * from log, ... pour faire les jointures.
 
C'est beaucoup plus rapide, mais j'espere toujours qu'il y a moyen de faire ca de maniere plus 'propre' ...

Bah... Modifie ton perl en fait :
 
-> Tu insèrres dans LOG
-> Tu shootes les "vieilles lignes" de LOG_TMP
-> Tu insèrres dans LOG_TMP
 
=> Comme ça, pour tes lignes récentes (100 par exemple), tu peux utiliser LOG_TMP qui est en permanance à jour, et plus jamais besoin de taper dans LOG qui va être monstrueuse.
 
Ensuite, de la même façon, je te conseille de créer des tables de stats rafraîchies tous les jours avec les données de la veille pour les infos style le traffic, les ports utilisés, les attaques, etc.
 
Parceque si tu tapes à chaque fois dans toute la table "log" pour retrouver ce que tu veux, tu vas pleurer

Effectivement, c'est pas con ca ...
Concretement, je pensais purger la base des entrée vieilles de plus de 1 semaine.
mais je ne peux/veux pas inserer en perl dans logtemp, car pour l'instant, je filtre pas, donc ca marcherai, mais a terme, je compte pouvoir filtrer par IP etc ... et récupérer les 30 derniers.
Pour l'instant c'est assez rapide.
Si ca ralenti trop qd la table grossit, je verrai pour crée r une table log plus petite, effectivement.
Merci a tous, je vous tiendrais de toute facon au courant.

Un conseil, garde tes logs sur 31 jours minimum et pas une semaine.

Moi je pense que le mieu, c'est de conserver l'intégralité des logs (après-tout, en plus des analyses de sécurité, qui demandent généralement un historique assez court, ce type de logs peuvent permettre de faire d'autres analyses, style l'évolution du traffic dans le temps, et autres, qui peuvent servir à tuner un proxy, ou planifier l'achat de nouveaux points d'accès, etc.)
 
En fait, ce que je verrais bien, c'est des traîtements du même style que ceux des analyseurs de traffic sur les sites :
-> Une table poubelle qui permet de conserver l'historique, ou de reconstruire les stats en cas de problème
-> Une table "de travail" où seules les infos de la connection (ou de la semaine) sont stockées
-> Une chiée de tables dédiées aux stats (nombre de hit par IP et par jour, nombre de chaque type d'attaque par jour, etc.)
-> Une ribembelle de batchs qui s'occupent d'allimenter les tables de stats à partir de la table de travail, toutes les heures ou toutes les nuits, selon si la charge du serveur permet de le faire en journée ou non.

Mais pourquoi personne ne veut de mon logrotate pour faire ses traitements de stat en background tranquillement

c'est quoi "logrotate" exactement ?
 
c'est bien le fait de supprimer les x plus anciennes lignes à chaque ajout de x nouvelles lignes non ?
 
si c'est le cas, ça revient au fonctionnement de la table "log_tmp" que je prévonnise non ?

non, c'est pas ça. Ca permet simplement d'effectuer une rotation dans les fichiers de stockage des logs, c'est un outil standard sous environnement unix ( http://packages.debian.org/unstable/admin/logrotate ). Vu ce qu'il a a faire (examiner les derniers logs en live et éventuellement des stats pèpères sur les anciens) ca me semble clairement plus indiqué que de jouer avec une DB pour l'utilisation live. Par la suite, s'il veut s'amuser à faire des stat sur les hits ou autres conneries, rien ne l'empèche de récupérer les anciens journaux et de les traiter à l'aise en background ou sur une autre machine pour les mettre dans une DB par exemple.

ok d'accord
 
disons que selon les infos qu'il veut tirer des logs, une db peut être plus appropriée. mais c'est vrai que pour récupérer les 30 dernières lignes selon un filtre, pas besoin

Pour la partie DB, ça ne marcherait pas de créer une vue pour les 30 derniers logs ?
 
CREATE VIEW AS select les 30 derniers FROM Tablelog
 
Et ensuite, tu fais ton inner join entre la vue et la table IP?
 
Je dis ça, je dis rien, hein

j'utilise sysklogd sous debian. J'ai une rotation des logs sur 7 jours.
Mais ca n'a rien a voir avec ce que je veux faire.
Moi, je veux pouvoir debugguer mon firewall lors de la mise en place d'un nouveau service, ce qui passe, ce qui ne passe pas etc ...
chaque entré log est du style :
regle - interface source/ipsourcce/portsource intfdest/ipdest/portdest.
Je parse chaque entrée du log, et je la met dans une base qui ne sert que pour le debuggage. Je ne touche pas au syslog. En gros, je duplique les logs, dans un format bien plus pratique pour l'analyse (le SQL est qd meme pas ml pour filtrer sur de grandes quantités de données.
Je dois pour voir dire : tien, j'arrive pas a me connecter sur cette machine en DMZ. Est ce que le firewall rejette mon paquet, ou le prob vient d'ailleur ?
Actuellement, j'ai rien trouvé qui fasse ca pour mon Firewall, alors je le fais moi meme ...

une vue ce n'est rien d'autre qu'une facilité d'écriture, la requète est quand même effectuée.

On est sous MySQL là.  
Remarque, c'est peut être la v5 ?

Pour l'instant, on joue avec des grep et des regexp, mais c'est lourd, mes fichiers de log font facile 50 meg (en milieu de journée).
 
Et pour la vue .. euh ..oué, ca remonte a mes cours d'IUT ca, faudra que je jette un oeil ... a l'occaz.
Pour l'instant, ca marche comme ca, alors j'y touche plus trop. On verra quand j'aurai fini d'implémenter les fonctionnalités de base.
(j'ai appris perl, les regexp et les optimisation mySQL cette dernieresemaine, alors chaque chose en son temps

50Meg de log c'est rien du tout, je m'attendais à une base beaucoup plus importante (on a un soft qui nous a fait 32Go de log en 1WE ). Ca ne devrait poser aucun problème à un DBMS, même avec des insertions constantes, c'est que tu as merdé quelque part.

ah? j'ai merdé .. euh .. j'ose pas y croire p
Je sais bien, mais le truc, c'est que je sais pas ou. J'ai pas la machine la (un vénérable p!!! 450 avec 128 dedram ).
Mais les optimisation de requete mysql sont trop compliquées pour l'instant, alors je me concentre sur le coté fonctionnel pour commencer, histoire que ca soit opérationel assez vite, ensuite on optimisera et on fera un truc plus propre (je ne peux pas me concentrer la dessus toute la journée).

 
Mettre plutôt un LEFT JOIN ? Pour bien dire à MySQL que les 30 derniers, ils ont pas besoin d'avoir des addresses IP...
 
P'tain j'ai trop la pêche en SQL moi ce soir. Je vais arreter le C++ et devenir DBA si ça continue

left join ?
Bon, je me met ce topic de coté (je l'ai posté, ca devrait pas etre dur a trouver ) et j'y reviendrait qd le projet sera un peu plus avancé, et que je pourrais prendre le temps de fignoler un peu ...
En tout cas, je suis toujours open pour d'éventuelles remarques et/ou conseils ...

Euh... non, reste bien loin du SQL et garde ton C++... La tu es en train de lui proposer un outter join, ce qui est plus gourmand qu'un inner join.

Tu peux expliquer stp.
Comme Lam's je penserais que c'est plus rapide.

Simple, dans le cas d'un INNER JOIN tu ne retournes que les couples qui match, les autres sont jetés. Dans le cas d'un OUTTER JOIN, non seulement tu retourne ces couples mais également tous les éléments de gauche (et/ou droite selon la requète) qui ne matchent pas, accompagnés de colonnes NULL, ce qui fait que tu te retrouve avec une génération de ligne et donc de consomation ressource qui peut être bien plus importante.

 
Je suis d'accord avec toi sur le fait qu'en principer, une outer join est gourmande, mais là, je crierais au scandale si ça n'était pas optimisé.    
 
Dans mon esprit, une BDD décente, elle devrait être capable de rétro-propager ses contraintes, à savoir : il me faut les 30 derniers éléments, donc, je me contente de prendre les 30 derniers de ma table de gauche, et je les lie à ma table de droite. C'est pas comme ça que ça se passer sous Oracle et compagnie ?    
 
Et sinon, j'ai pas fait de SQL depuis des lustres, et Dev-cpp a fini de compiler mon projer , donc j'y retourne.    

En aucun cas cela ne se passe comme ça pour la simple et bonne raison que les X dernier éléments, cela ne signifie rien tant que toutes les contraintes n'ont pas été validées, ce qui implique non seulement les matching entre colonnes mais également les ORDER BY. Il est donc totalement impossible de savoir à l'avance pour un DBMS quels seront les x derniers éléments.

Avec cette dernière réponse, je viens de comprendre.  
Mais sans ORDER BY et table temporaire, ça serait plus efficace ? Ou je me trompe encore ?

d'ou l'interet de faire deux requete : la premiere, j'effectue mais filtres, et je selectionne les trentes derniers qui correspondent aux critères.
La seconde selectionne tout ce petit monde et 'résout' les clés étrangères.
En fait, conceptuellement, je voudrait pouvoir faire un SELECT ... dans le champs FROM. (oui, effectivement, ca correspond a une vue )

 
Ah bah très bien. Je m'endormirai moins ignare ce soir.  
 
Pis je viens de voir que mySQL supporte les procédures stockées maintenant. Ca ressemble de plus en plus à PostgreSql...

non

developpe stp ..
c'est vrai que j'ai pris MySQL parce que je connaissais deja, sans trop me soucier du reste ...
Je ne conais pas les différences entre les deux, si ce n'est les on-dit : postrGRE est du niveau Acces, plein de bases et autre truc hyper compliqués ...

Y a les TEMPORARY TABLE qui marchent très bien.
 
Dans son cas, il crée une TEMPORARY TABLE avec les 30 derniers et il fait ses jointures sur cette table.

donc, temporary table effectuée: l'algo reste le mem .. et ca m'a pris 30 secondes a mettre en place :
 
drop table tmp
create temporary table tmp select * from .. where criteres
jointure sur tmp
 
Merci a tous, pour l'instant, je suis satisfait de mes 'optimisations' ..

T'as beaucoup gagné en perfs ?

non, en tout cas rien de visible.
Par contre, c'est plus propre (si la question parler juste du passage en temporary table)
En revanche, globalement, en ce qui concerne le fait de faire une premiere requete de tri dans une table temporaire, puis les jointure, la le gain en perf est enorme ...