Discussion :

[jlennon]

Bonjour,

avant toutes choses, je tiens à préciser que j'ai déjà chercher, sur le forum, mais aussi sur Google. (On se sent obliger de le signifier! c'est fou : )) Bref,

J'ai installé Wamp 64 bits sur une machine local. Jusque là, pas de soucis. J'ai créé mes tables, créé ma plateforme web qui me permet de manipuler des fichiers csv puis d'insérer les données en base de données, et fait tourné l'ensemble pendant au moins une année. Jusqu'à aujourd'hui, ça m'allait bien. Mais voila, plus le temps passe et plus les données à traiter son nombreuses.
J'ai commencé par optimiser mon code PHP en prenant en compte toutes les bonnes techniques qu'on trouve ici et là sur le web. J'ai ensuite revu l'ensemble des requêtes SQL et mes tables aussi (surtout pour revoir les types de mes attributs!). Je n'étais pas trop habitué avec ce langage et comme tout débutant j'ai foncé tête baissée et appris sur le tas! J'ai donc travaillé et re-travaillé l'ensemble (code Php + base de données! et j'y ai passé du temps!)

Avec tout ça, j'ai gagné en performance; Mais pas assez! : /
J'ai donc revu la configuration apache (Mais là est un autre sujet); puis me suit amener à comprendre la configuration mysql.
Voici une partie du fichier my.ini sous sa forme originelle.

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
 
# The MySQL server
[wampmysqld]
port		= 3306
socket		= /tmp/mysql.sock
key_buffer_size = 16M
max_allowed_packet = 1M
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
basedir=c:/wamp64/bin/mysql/mysql5.6.17
log-error=c:/wamp64/logs/mysql.log
datadir=c:/wamp64/bin/mysql/mysql5.6.17/data

en parcourant les différents sites web, j'ai pu lire qu'en modifiant les paramètres key_buffer_size, max_allowed_packet, sort_buffer_size ... on pouvait atteindre de meilleurs performances! Mais voilà j'ai beau modifier toutes ces valeurs, rien n'y fait! La durée de mes traitements est toujours la même!

Première chose, j'ai vérifié le service mysql pour voir comment il était lancé:

Code :

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

c:\wamp64\bin\mysql\mysql5.6.17\bin\mysqld.exe wampmysqld64

là j'ai vite compris qu'un truc n'allait pas; le wampmysqld64 est censé indiquer la conf à sélectionner sous le my.ini. J'ai donc modifié: [wampmysqld] en [wampmysqld64]

j'ai prié pour que mes paramétrages changent quelque chose mais toujours rien!!
Pourquoi la modification de ces paramétrages ne modifie en rien le temps d'exécution de mes scripts.
En gros les scripts qui prennent du temps, sont des scripts qui parcourent des fichiers de plusieurs milliers de lignes et qui insèrent en base de données! globalement je passe 10min, pour un fichier de 12000 lignes sur lequel j'ai 10 informations à insérer ou à updater! (1 à 2 requêtes par ligne).

Mon serveur tourne sur une machine Windows 7 64 bits, j'ai 16 G0 de mémoires. 15 clients max utilisent l'application en même temps (application associative). La base de données fait actuellement 60 Mo!! (c'est rien!! en plus : ( )
Quels sont à votre avis les meilleurs valeurs pour les paramétrages suivants:

Code :

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

1
2
3
4
5
6
7
8
 
key_buffer_size = 16M
max_allowed_packet = 1M
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M

avez-vous d'autres pistes à me conseiller pour optimiser ma config mysql? Un article aussi?

Merci pour tout! et excusez-moi, si les réponses à mes questions existent quelques part. trouver ce que l'on cherche s'avère parfois compliquer quand on n'use pas des bons mots clés! (surtout quand on n'est pas expert SGBD!) : [

Merci encore & a+!

[Artemus24]

Salut jscritte.

avant toutes choses, je tiens à préciser que j'ai déjà chercher, sur le forum, mais aussi sur Google. (On se sent obliger de le signifier! c'est fou : )) Bref,

Je sais que l'on ne fait pas confiance, comme si Google avait forcément la réponse que l'on cherche.

comme tout débutant j'ai foncé tête baissée et appris sur le tas!

Ce qui fonctionne pour un petit volume de données, devient vite problématique quand tu dépasses un seuil donnée.

J'ai donc revu la configuration apache (Mais là est un autre sujet); puis me suit amener à comprendre la configuration mysql.

Apache n'agit pas sur la performance de tes accès à tes bases de données.
Tu dois intervenir uniquement dans le fichier "my.ini" pour modifier le paramétrage.

Là j'ai vite compris qu'un truc n'allait pas; le wampmysqld64 est censé indiquer la conf à sélectionner sous le my.ini. J'ai donc modifié: [wampmysqld] en [wampmysqld64]

En effet, Otomatic du forum WampServer a modifié le nom du service mysql en faisant la distinction entre la version 32 bits et la version 64 bits.
Si tu n'as qu'un seul service MySql, tu peux mettre [wampmysqld]. Inversement si tu utilises les deux version à la fois, il ne faut pas mettre les mêmes noms, d'où [wampmysqld] ou [wampmysqld32] pour le 32 bits et mettre [wampmysqld64] pour le 64 bits.

De plus, comme dirait Otomatic, la version 64 bits est expérimentale et n'apporte rien de plus que la version 32 bits. Otomatic te conseillerais d'utiliser la version 32 bits même sur un windows 64 bits.

Pourquoi la modification de ces paramétrages ne modifie en rien le temps d'exécution de mes scripts.

Parce que cela dépend de tes requêtes !
Si tu fais un balayage de A à Z de toutes tes lignes dans toutes tes tables, cela ne changera rien au niveau paramétrage.
Pourquoi ? A la première lecture d'une ligne, celle-ci est mis dans un buffer mémoire.
De plus, la taille de ce buffer est limité en capacité, c'est-à-dire en nombre de lignes stockées.
Lors d'une seconde lecture, si la ligne n'est pas présente dans le buffer, elle sera lu.
Mais si elle est déjà présente dans le buffer, MySql ne fera pas de lecture et tu vas gagner en temps d'accès.

Donc si tu fais un balayage de A à Z de tes lignes, tu peux comprendre que cette technique ne fonctionnera pas car chaque ligne sera lu une et une seule fois.

En gros les scripts qui prennent du temps, sont des scripts qui parcourent des fichiers de plusieurs milliers de lignes ...

C'est ce que je viens d'expliquer car tu balayes toute la table.

... et qui insèrent en base de données! globalement je passe 10min, pour un fichier de 12000 lignes sur lequel j'ai 10 informations à insérer ou à updater! (1 à 2 requêtes par ligne).

Ca, c'est un autre problème.
Il y a deux problèmes lors de l'insertion.

1) où MySql va insérer la nouvelle ligne ?
Pour trouver le lieu où cette ligne sera insérer, il faut une clef (ce qui est bien) ou un index.
Si tu as ni l'un ni l'autre, normalement, l'insertion se fait en fin de table.

2) est-ce que j'ai la place nécessaire pour le faire ?
En DB2, on réserve de la place lorsqu'on va créer une table. C'est le tablespacequi est une structure de stockage, que l'on nomme dans le jargon des informations l'enveloppe.

Maintenant, il se peut aussi que ton insertion se fasse normalement, mais comme tu as surcharger en index tes lignes, cela va prendre bien plus de temps.

Donc quand tu fais, par exemple : "update matable set col1 = 1111 where col2 = 9999;"

Il vaut mieux mettre un index sur "col2" pour des questions de performances, enfin c'est vivement conseillé sur de très grosses volumétries. Est-ce le cas ?

Mon serveur tourne sur une machine Windows 7 64 bits, j'ai 16 G0 de mémoires.

C'est bien. Mais je le répète, cela dépend plus de tes requêtes que de la configuration de ton matériel.

15 clients max utilisent l'application en même temps (application associative).

Le problème est que MySql n'est pas fait pour faire du transactionnel.
Voire même, il devient contre performant sur de très grosses volumétrie.

La base de données fait actuellement 60 Mo!! (c'est rien!! en plus : ( )

Oui, je confirme, c'est rien du tout.

avez-vous d'autres pistes à me conseiller pour optimiser ma config mysql? Un article aussi?

a) voir si ta bases de données est désorganisée.
Il faut régulièrement réorganiser les tables, juste pour conserver une performance acceptable.
La dégradation survient au fur et à mesure des insertions et des suppressions que l'on fait.

b) les clefs permettent de trier les lignes dans l'ordre de leur insertion.
Si tu désires inserer une ligne à un endroit où il n'y a pas assez de places, MySql va faire un décalage de toutes les autres lignes de ton espace disque.
Ca peut être une cause du problème de la performance de l'insertion avec MySql.

En DB2 sous gros système IBM, dans le tablespace, on utilise "FREEPAGE 20" et "PCTFREE 20" pour définir le taux d'espace libre (20%) dans la page.
Je ne connais pas son équivalent avec MySql. Je ne sais même pas si cela existe aussi.

c) as-tu mis des indexes sur les colonnes où tu fais des "where" dans tes select ?
Normalement, cela améliore grandement tes performances.
Mais encore un fois, cela dépend de ce que tu fais réellement avec tes requêtes.

d) est-ce que tes tables sont bien structurées par rapport à tes requêtes ?
Il se peut que tu fasses un accès à une table, juste pour récupérer une seule colonne, à partir d'un identifiant.
Ne serait-il pas plus judicieux de faire la lecture seul de cette table et de la mettre dans un tableau en mémoire en php ?

e) on peut envisager le découpage d'une table si celle-ci es trop large.
Si tu as des colonnes que tu sélectionnes très fréquemment, et d'autres très peu, il faudrait envisager de les scinder en deux.
Faire deux tables, l'une où les accès seraient très fréquentes et une autres où les accès seraient seraient moins fréquentes.
Ainsi la longueur de la ligne utile serait plus petite. Mais il faut envisager de revoir aussi la taille de la page.

f) est-ce que tes tables sont en engine myisam ou en innodb ?

Les questions de performances sont difficiles à mettre en oeuvre et ne se font pas à partir de conseils basiques.
Il faut faire une étude de cas et envisager de refondre la totalité de la base de données.
Il y a beaucoup de tests à envisager et parfois la déception au final.
Voire ce qui est important, au sens du chemin critique en terme de performance.
Est-ce le batch qui prend du temps, ou le transactionnel ?

Le seul conseil que je pourrais donner est peut-être de changer de SGBDR car MySql n'est vraiment pas fait pour du multi-utilisateur.

P.S.: tu peux jeter un coup d’œil sur mon site en ce qui concerne le paramétrage du fichier "my.ini".
P.S.2: pour ce qui concerne WampServer, as-tu posé ta question sous le forum WampServer à Otomatic ?

@+

[jlennon]

Merci beaucoup pour ta réponse! Je viens de lire rapidement mais Je vais prendre plus le temps pour lire et relire et lire de nouveau! & surtout afin de bien maitriser tout ce qui est dit. (ça peut prendre du temps/surtout parce que je ne maitrise pas! Mais je préfère être sur de bien tout saisir avant de vraiment répondre!)

Merci encore! Vraiment! Je sais que ce n'était pas évident de tout lire!

[CinePhil]

En gros les scripts qui prennent du temps, sont des scripts qui parcourent des fichiers de plusieurs milliers de lignes et qui insèrent en base de données! globalement je passe 10min, pour un fichier de 12000 lignes sur lequel j'ai 10 informations à insérer ou à updater! (1 à 2 requêtes par ligne).

À mon avis, c'est plutôt de ce côté qu'il faut chercher la cause du problème.

Quel est le processus effectué par le programme ?