Discussion :

[ionesco]

J'y ai penser au fichier texte, mais j'ai peur que le nombre d'ouverture écriture fermeture fichier par seconde soit énorme et que j'ai des problèmes de concurrence. Le lock sur les fichiers est-il gérer par Apache comme MySQL gère le lock des tables?

[CinePhil]

On sort du cadre de MySQL et je n'ai jamais eu à faire ce genre de chose mais quand on voit comment se remplissent les fichiers de log sur une machine Linux on se dit qu'un truc similaire doit être programmable non ?

[SQLpro]

Il est rare que je poste des conseils sur MySQL, car depuis le temps que je dit que c'est de la m... alors profitez-en !

Le probleme est que quasi toutes les requêtes avec JOINTURE + GROUP BY (ou ORDER BY) font que MySQL écrit un resultat temporaire sur disque. A tel point que 60% des tables temporaires sont sur disque ( et non en mémoire donc...). Ca pose de gros probleme de perf.

Avec le GROUP BY, MySQL écrit sur disque, sans le GROUP BY il n'écrit pas sur disque et c'est beaucoup plus rapide.

C'est dingue quand même, sur 90% des requêtes qui ont une clause GROUP BY, ORDER BY ou un DISCTINCT, ca tape sur le disque.

Les GROUP BY et ORDER BY imposent des opérations de tri qui sont couteuses en terme de ressource si le volume est important des tables de travail qui sont des tables temporaires implicites sont créées pour ce faire :
Solutions :
1) augmenter drastiquement la RAM
2) récrivez les requêtes pour éliminer les GROUP BY et ORDER BY inutiles

Qu'est-ce qui est le moins impactant sur une perf, un full scan ou taper dans des table temporaires sur disque? J'ai réussi à forcer des indexes pour m'éviter la table temporaire, mais l'inconvéniant est que ca fait un full scan.

Full scan, mais c'est pas optimal !

Ma question: connaissez-vous une façon de mettre en tampon ces insertions répétées. Le but est que je ne fasse qu'un seul insert par heure (par exemple). Regrouper 10 000 inserts puis les balancer, au lieu de le faire 10 000 fois pendant l'heure.

Une idée : Faire les insert successifs en alimentant un fichier texte délimité puis en faisant régulièrement un LOAD DATA INFILE.

J'y ai penser au fichier texte, mais j'ai peur que le nombre d'ouverture écriture fermeture fichier par seconde soit énorme et que j'ai des problèmes de concurrence. Le lock sur les fichiers est-il gérer par Apache comme MySQL gère le lock des tables?

Le fichier est une fausse bonne idée. Dans un bon SGBDR les données à écrire sont mise en cache avant d'être écrites pas sessions (toutes les 60 secondes dans SQL Server) et de manières groupées. Seule les transactions sont écrites au fil de l'eau (écriture WAL, virtualisée dans SQL Server). Contrairement à un fichier qui serait écrit physiquement assez rapidement et même s'il existe des possibilité de n'invoquer qu'un seul et même process d'écriture par le biais d'un service...
En conclusion : assurez vous que le journal de transaction soit placé sur un disque dédié uniquement à cela et à haute vitesse !

Enfin sur vos index.... Contrairement à ce que vous as dit cinéphil, le fait d'avoir deux colonnes dans un index n'est pas mauvais, surtout si ces deux données sont manipulées systématiquement ensemble ce qui apparemment semble le cas de longitude/latitude.
En revanche vous avez des index stupide. Par exemple il ne sert à rien d'indexer une colonne ne possédant que 2 valeurs (0 ou 1). La dispersion sera tellement faible que l'index ne sera jamais utilisé....
Même chose pour libelle_lieu de type varchar(120) à priori je dirais qu'indexer quelque chose qui peut faire 120 caractères n'a aucun sens. On est là dans le domaine de l'indexation textuelle plus du tout dans l'indexation de données.... Pour terminer il faudrait faire des index couvrants en auditant les 20% des requêtes qui consomment 80% des ressources...

Bref, y'a pas beaucoup de possibilité d'évolution avec MySQL dès que l'on a affaire à du volume, des requêtes complexe ou du transactionnel massif : ça supporte mal la charge !

A +

[ionesco]

Bonjour SQLPRO,

Solutions :
1) augmenter drastiquement la RAM
2) récrivez les requêtes pour éliminer les GROUP BY et ORDER BY inutiles

1) Je n'ai pas la main mise sur ça. pour le moment c'est 8 GO de RAM
2) J'ai supprimer beaucoup de tri, et pour les tris simples sur un resultset pas trop grand, je fais faire le tri par le langage de programmation. J'ai appliqué le même raisonnement aux mutliples ORDER BY RAND() rencontrés... Par contre j'ai vu des ORDER BY CASE IF THEN et là je sais pas quoi faire. Une horreur. J'ai par ailleur aussi viré toutes les fonctions du style CURDATE, NOW, INTERVAL etc.....

En revanche vous avez des index stupide. Par exemple il ne sert à rien d'indexer une colonne ne possédant que 2 valeurs (0 ou 1). La dispersion sera tellement faible que l'index ne sera jamais utilisé....

Je me suis également rendu compte de la choses ==> je les ai virés

Même chose pour libelle_lieu de type varchar(120) à priori je dirais qu'indexer quelque chose qui peut faire 120 caractères n'a aucun sens. On est là dans le domaine de l'indexation textuelle plus du tout dans l'indexation de données....

Le problème est qu'une requête tri sur le libelle_lieu, et elle est beaucoup utilisée (700 000 fois / journée enviton). La table des lieux fait 300 000 lignes, je pensais que cet index était plutôt utile.

Pour terminer il faudrait faire des index couvrants en auditant les 20% des requêtes qui consomment 80% des ressources...

J'ai audité. Verdict: ce n'est pas 20% des requêtes qui merdent mais plutôt un bon 60%. j'ai très peu de requêtes simple, et beaucoup de requêtes avec 6-7 JOIN + GROUP BY + ORDER BY.....

J'ai retravaillé tous les indexes et les types de données et j'ai déjà vu pas mal de changement. Net amélioration. Mais pas suffisant à mon goût.

Je me suis attaqué au cache des requêtes MySQL qui a un taux d'utilisation de 60% en moyenne sur une semaine: c'est faible. Le problème vient du fait que des updates et insert sont fait à longeur de journée et ainsi les requêtes de type SELECT qui concernent ces tables sont sorties du cache.

MySQL et grosses appli = merde ou = employé de gros moyens (réplication, clustering, load balancing...).

[ionesco]

bonjour,

qu'est ce qui est le mieux: un "using filesrot" dans le explain mais en scannant peu de lignes, ou bien scanner 10 fois plus de ligne mais cette fois ci sans "using filesort" ?

merci!