Forum Programmation.SQL Optimisations Serveur

Posté par .
Tags : aucun
0
13
nov.
2006
Bonjour,

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 . Évalué à 2.

    avant de vouloir optimiser il faut peut-etre se poser les bonnes questions sur ton systeme.

    à 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 . Évalué à 1.

      Ouep ça je sais bien.

      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 . Évalué à 1.


        [mysqld]
        datadir=/var/lib/mysql
        socket=/var/lib/mysql/mysql.sock
        max_connections = 300 doit pouvoir etre reduit si tu n'as que 40 utilisateurs simultanés
        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


        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 . Évalué à 1.

          En continuant mes recherches je suis tombé sur ces scripts bien sympathiques:

          http://www.day32.com/MySQL/
          • [^] # Re: Une aide

            Posté par . Évalué à 1.

            sinon si tu as mis phpmyadmin pour gerer tes bases mysql, phpmyadmin propose aussi d'optimiser les tables et/ou de les reparer.
      • [^] # Re: reflexion ...

        Posté par (page perso) . Évalué à 2.

        max_connections = 300
        # 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 . Évalué à 1.

          Merci pour cette réponse complète.

          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 . Évalué à 2.

    Je crois que si tu choisis des tailles de cluster plus gros, cela marche mieux pour des fichiers énormes (tu peux aussi rajouter les noatime et autre).

    "La première sécurité est la liberté"

Suivre le flux des commentaires

Note : les commentaires appartiennent à ceux qui les ont postés. Nous n'en sommes pas responsables.