J'ai rajouté récemment 1Go de Ram dans mon serveur MySQL qui n'en contenait que 512. Après avoir retravaillé un peu mon fichier my.cnf, je trouve que le gain de performance n'est pas flagrant. Comme je n'ai pas la possibilité de redémarrer MySQL 36000 fois je m'adresse à vous pour avoir des conseils d'optimisation correspondant à ma config.
Pour info:
- Serveur Fuji Primergy TX 150 Serie 3 sous FC4
- P4 2,8 Ghz, 1,5 Go de Ram ECC, 2 disques SCSI 36 Go en Mirroring
- Ce serveur abrite principalement un CRM Apache/PHP/MySQL et des partages SAMBA, et quelques scripts PERL
- Le CRM est utilisé par une quarantaine d'utilisateurs en simultané
Voici mon fichier my.cnf, que je pense avoir surdimensionné:
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
max_connections = 300
key_buffer = 16M
myisam_sort_buffer_size = 48M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
table_cache = 1024
thread_cache_size = 64
wait_timeout = 1800
connect_timeout = 10
max_allowed_packet = 16M
max_connect_errors = 10
query_cache_limit = 1M
query_cache_size = 24M
query_cache_type = 1
tmp_table_size=64M
Merci d'avance pour vos conseils
# reflexion ...
Posté par NeoX . Évalué à 2.
à savoir :
quelles sont les requetes majoritaires (grosses requetes, petites requetes) ?
y a-t-il des requetes qui se repetent souvent ?
et plein de questions comme ca afin de determiner ce qu'il faut optimiser.
grosse ou petite requete, tu ne mettras pas les memes valeurs pour la taille de la memoire de sortie de la requete.
repetition frequente de la meme requete, tu previligieras la mise en cache de cette requete...
[^] # Re: reflexion ...
Posté par Flavien . Évalué à 1.
Il se trouve que CRM oblige, j'ai des requêtes de tous types et de toutes tailles.
Pour préciser un peu:
- 2/3 de mes requêtes sont des SELECT (dont 1/4 de grosses requêtes (3-4 sec d'exécution quand le serveur est full) et 3/4 des requêtes sont petites (éxécution quasi simultanée))
- 1/3 d' INSERT/UPDATE qui ne posent pas de soucis d'éxécution.
- 90% des requêtes proviennent de ce CRM qui travaille avec 14 tables en innoDB.
- Les 10% proviennent des scripts PERL qui utilisent des tables Myisam avec des petites requêtes.
Alors je demendais pas qu'on me ponde mon my.cnf aux petits oignons, juste si il n'y as pas des trucs aberrants par rapport à ma config... Voilà peut être pourras tu m'orienter un peu plus...
Merci
[^] # Re: reflexion ...
Posté par NeoX . Évalué à 1.
apres il faut vraiment regarder dans les logs de mysql pour voir s'il n'y a pas des erreurs ou des timeouts...
de plus man mysql ou my.cf devrait t'aider à comprendre les parametres pour tuner ceux qui te concerne vraiment.
[^] # Une aide
Posté par Flavien . Évalué à 1.
http://www.day32.com/MySQL/
[^] # Re: Une aide
Posté par NeoX . Évalué à 1.
[^] # Re: reflexion ...
Posté par -=[ silmaril ]=- (site web personnel) . Évalué à 2.
# comme dit plus haut trop grand amha
key_buffer = 16M
# trop petit, c'est justement la et sur le query_cache que tu peut gagner.
# pour savoir si tu est bon au niveau taille key_buffer utilise la commande
# show status like '%key%':
# mon key_buffer est a 200Mo:
#...
#| Key_read_requests | 1270448781 |
#| Key_reads | 6213190 |
#| Key_write_requests | 184507520 |
#| Key_writes | 28508089 |
#....
# "Key_reads/Key_read_request" devrait normalement être inférieur à 0.01
# "Key_write/Key_write_requests" est habituellement proche de 1
myisam_sort_buffer_size = 48M
join_buffer_size = 1M
# utile dans le cadre de jointure n'utilisant pas des index
# il est plus interessant dans ce cadre de verifier qu'il n'existe pas
# de telles requetes
read_buffer_size = 1M
sort_buffer_size = 2M
# peut etre augmenter si utilisation intensive de group by/order by sur
# de grosses quantitée de données.
table_cache = 1024
# depend du nombre de table hebergées par ton serveur db
# idealement egal au nombre de tables.
# show status like 'Open%tables'; peut te permettre de gerer au mieux
# cette valeur. (Opened_tables => nombre d'ouverture de tables depuis
# le demarrage).
thread_cache_size = 64
wait_timeout = 1800
connect_timeout = 10
max_allowed_packet = 16M
max_connect_errors = 10
query_cache_limit = 1M
query_cache_size = 24M
query_cache_type = 1
# cache trop petit amha
#show status like 'Qcache%';
# _insert => nombre d'ajout dans le cache
# _hits => résultat repris direct depuis le cache
# _lowmem_prunes => nombre de requetes supprimées par manque d'espace.
# _not_cached => requete non mises en cache soit trop grandes
# (> query_cache_limit) soit explicitement demandé (sql_no_cache).
# insert et lowmem_prunes doivent etre faible par rapport a hits.
tmp_table_size=64M
# ce n'est pas une valeur que je boosterais, a moins d'avoir bcp de
# mauvaises requetes
# show status like '%tmp%';
# => si tu a bcp de created_tmp_tables => voir a optimiser les requetes sql
# => si created_tmp_disk_tables est proche de created_tmp_tables
# alors oui il faut augmenter tmp_table_size.
Sinon pour info tu peut modifier les variables de mysql 'online':
http://www.mysql.org/doc/refman/5.0/fr/system-variables.html
[^] # Re: reflexion ...
Posté par Flavien . Évalué à 1.
Je suis relativement proche des valeurs que tu recommandes.
D'ailleurs en suivant les conseils du script que j'ai posté plus haut j'ai baissé le key_buffer de 16M à 4 Mo et le calcul de Key_reads/Key_read_request me donne un ratio de 0.0151 ce qui n'est pas très loin de ce que tu préconise.
Mon lowmem_prunes est à 0 pour un total de 723190 requêtes sur une journée de production.
Seul mes valeures de temp me semblent élevées:
mysql> show status like '%tmp%';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 19383 |
| Created_tmp_files | 381 |
| Created_tmp_tables | 46155 |
+-------------------------+-------+
Created_tmp_disk_tables/Created_tmp_tables me donne un ratio de 42%. Pourtant j'ai passé le tmp_table_size à 256 Mo. Et je n'ai que 2 requêtes qui sont vraiment lourdes, les autres étant je pense, relativement bien optimisées. D'ailleurs sur mes 723190 requêtes je n'en ai que 79 qui ont dépassé un temps d'éxécution de 5 secondes.
Je commence à me demander si ce n'est pas mon processeur qui est complètement à l'ouest .
En tout cas encore merci pour tes éclaircissements.
# tu peux aussi optimiser le FS
Posté par Nicolas Boulay (site web personnel) . Évalué à 2.
"La première sécurité est la liberté"
Suivre le flux des commentaires
Note : les commentaires appartiennent à celles et ceux qui les ont postés. Nous n’en sommes pas responsables.