MySQL : index efficace ? Vos conseils

[michel.leonard]

Salut à tous, chers WRInautes,

Je vous expose mon problème, plutôt ma question...
Je suis en train de créer un site internet (qui à pour but d'être un système d'annonces entre particuliers).
Comme la loi nous impose de conserver des logs du serveur à des fins de lutte "anti-terroriste" j'ai créé un système qui logue chaque requète dans une base de données de type "ARCHIVE" mySql. Voici le fonctionnement que j'ai prévu :

{
Si une IP n'est pas répertoriée dans la table logs_ip, l'insérer dans cette table avec un id auto incrémenté.
Faire une pause usleep(25);
}
Ensuite effectuer un select de l'id de cette ip.
Insérer ce nombre, une emprunte de $REQUEST_URI, et la date courante dans la base de log_archive de type ARCHIVE

La question que je me pose est la suivante:
Dans le passage surligné en rouge, au moment du select 'id' Where ip='$REMOTE_ADDR', est-il préférable de créer un hash de l'IP , et d'en déduire un entier modulo 100 (par exemple) . Ainsi SQL n'ai pas a rechercher parmi toutes les IP si celle du client existe, mais seulement parmi celles qui ont un hash modulo 100 correspondant.

Est-ce une bonne solution ? Avez vous un avis, un conseil ? Merci pour vos réponses

 

[jcaron]

Plein de commentaires:
- tu devrais probablement vérifier exactement ce que tu dois conserver, tu vas probablement te rendre compte que c'est beaucoup moins que tu ne penses, en gros, stocker l'IP et la date de création dans l'annonce devrait largement suffire, non?
- pour le reste, conserver les logs Apache (découpés par jour et compressés) devrait faire l'affaire
- une adresse IP (IPv4 en tous cas) c'est un entier de 32 bits. Pas la peine de créer une table pour convertir une IP en un ID numérique, c'est déjà un nombre (que tu peux obtenir avec la fonction php ip2long() par exemple).
- pas besoin de faire un sleep entre un insert et un select

Jacques.

 

[michel.leonard]

Salut Jacques, le problème est qu'une ipv6 n'est pas facilement convertible en un entier... Et également que dans la table de log, je n'ai pas vérifié mais il me semble normal de conserver aussi une emprunte (ou la valeur) de $REQUEST_URI, sinon je ne suis pas en mesure de dire qui et allé ou à quel moment...
Pensez vous que chaque annonce devrait avoir son propre champ de logs plutôt qu'un seul fichier de log de type ARCHIVE ?

 

[jcaron]

Encore une fois, je ne pense pas que tu aies l'obligation de pouvoir dire qui a consulté quoi sur ton site. Ce qui est important, c'est de savoir qui a créé quelle annonce. Donc oui, un champ pour stocker l'IP + la date de création de l'annonce + les autres informations que tu peux avoir (adresse e-mail, détails du paiement s'il y en a un, etc.) ça devrait largement suffire, non?

Jacques.

 

[michel.leonard]

re, cher Jcaron, merci pour tes petits conseils.
Petite question si tu es toujours sur ce thread,
Une base avec environ 100 updates par seconde en écriture et 100 en lecture est-elle plus optimisée en inodb ou myisam ?
La taille du update est inférieure à 5 kilobits.

D'ores et déjà merci. Mike

 

[zeb]

Tu est en train de te compliquer la vie de façon mortelle .... En premier ton Ip ne sera pas forcement utilisée par une personne unique donc "Si une IP n'est pas répertoriée dans la table logs_ip" n'a pas lieu d'être soit tu la conserve soit tu fait rien.
Ensuite une base de données n'est absolument pas faite pour faire du log ou de la statistique. Je sais que c'est tentant car pratique mais là tu va très vite saturer la base avec des donnes qui ne te seront qu’hypothétiquement utiles. Un fichier sur le filesystem est beaucoup plus adapté. Et comme dit par JCaron tes logs apaches (qui sont assez finement paramétrables) te donneront la totalité de ce que tu peut avoir besoin en évitant les doublons que tu est en train de mettre en place au détriment du métier de ton site qui lui aussi aura des besoins en ressources SGBD.

 

[michel.leonard]

http://dev.mysql.com/doc/refman/5.0/fr/archive-storage-engine.html

ce moteur SQL est particulierement adapté aux logs, il ne crée pas d'index et compresse les données.
Dans ma base il y à forcément des doublons comme dans tout système de logs, sauf qu'en sql tu peux les indexer pour éviter de stocker plusieurs fois certaines valeurs.

ARCHIVE est un moteur spécialisé dans le stockage de grosses quantités de données de manière très économique : les données sont compressées à leur insertion et aucun index n'est généré, ce qui améliore la rapidité en écriture.
Il ne gère ni les transactions, ni les relations ni les index, et ne permet de faire que des requete SELECT et INSERT. Les ordres de suppression ou de modifications seront refusés.

On peut ainsi conserver d'énormes quantités de données sans craindre qu'elles soient supprimées ou modifiées.

CITATION DE : http://www.siteduzero.com/tutoriel

 

[zeb]

Tu fait comme tu le sent ... mais une base écrit au final dans un fichier et ajouter une éventuelle connexion réseau pour aller écrire dans ce fichier plus lentement que le fait un ordre direct (notion évident de couche de code), c'est forcement moins performant.
Ensuite un log par principe comporte forcement des doublons et les supprimer via SQL ne correspond pas du tout a la finalité cherchée car même si une IP existe déjà rien ne montre que c'est la même personne pour la même action bref ça ne correspond pas a la demande que tu formule qui elles est présente déjà dans le log apache.

En resumé :
1/ tu ne répond pas au cahier des charges de la fonction
2/ tu alourdi le système
3/ au nom d'un dédoublonnage tu génère un très gros doublon de log (c'est énorme les logs serveurs)

Ce qui en revanche ne serait pas idiot (a mon avis), serait de récupérer automatiquement tes logs serveurs et de mettre au point deux trois petits scripts qui extraient les données que tu souhaite pour ensuite les stocker là où il faut de façon allégée, ou les consulter le moment venus

 

[michel.leonard]

Salut zeb, et merci pour ta réponse,

1/ si une adresse ip a déja été utilisé dans le passé, et qu'elle est déjà loguée, c'est le paramètre DATE qui définit à qui elle appartenais réellement (dans le cadre de la lutte anti terroriste)
2/ si on est amené à me demander un log un jour, c'est mieux de faire un select que de parcourir des gros fichiers textes en boucle...
3/ justement, le cahier des charges dit : "être capable de déterminer quelle IP visite quelle PAGE du site à quelle HEURE".

Et en plus écrire des centaines de fois par seconde dans un fichier texte est un peu plus lent que faire quelques centaines de requètes SQL...

Qu'en penses tu ? Tu penses réellement qu'il est mieux d'avoir des logs de plusieurs Gigaogtets au format txt ?

 

[zeb]

Et en plus écrire des centaines de fois par seconde dans un fichier texte est un peu plus lent que faire quelques centaines de requètes SQL...

Et tu pense que MySql il fait quoi ? Il ne garde pas ton contenu log en mémoire il procède aussi a un accès disque ... donc soit tes données traversent tes couches logicielles plus les éventuelles couches réseau (très lent soit dit au passage en comparaison du reste) pour ensuite traverser les couches SQL qui elle feront obligatoirement un accès disque. Soit elle y vont directement (tu ne peut pas faire plus court qu'un unique accès disque).

Ensuite les données que tu donne sont déjà sur le log apache que tu ne désactivera pas de toute façon.

Pour finir la probabilité qu'on te demande qui fait quoi a telle date est super faible et trouver le log d'une date se fait facilement avec la date du fichier qui lui peut être parsé en moins de temps qu'il faut pour le dire avec un éditeur de texte (et encore c'est pas a toi de le faire dans le cas précité)

 

[michel.leonard]

OK, comme tu le sens, chacun fait son choix, moi je préfère opter pour le select, et en plus les donnes ARCHIVE sont compressées automatiquement, ça prend très peu de place... Merci pour ton avis

 

[zeb]

c'est vrai que la compression c'est compliqué

 

[michel.leonard]

 

[zeb]

et ?