• Optimiser Mysql sur serveur dédié Linux

    image de l'aticle Optimiser Mysql sur serveur dédié Linux

    Tout d'abord nous allons faire des tests sur votre serveur pour voir ce qui ne va pas et y remédier. Connectez vous en SSH via putty à votre serveur.

    Ensuite téléchargez le script mysqltuner.pl qui va nous aider à tester nos variables MySQL et changer leur valeur.

    wget http://mysqltuner.com/mysqltuner.pl 
    chmod +x mysqltuner.pl
    ./mysqltuner.pl

    Un fois que vous avez lancé la dernière ligne de commande (./mysqltuner.pl) vous allez voir des informations s'afficher sur votre console

     >>  MySQLTuner 1.2.0 - Major Hayden <major@mhtx.net>
     >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
     >>  Run with '--help' for additional options and output filtering

    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.1.56-log
    [OK] Operating on 64-bit architecture

    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 26M (Tables: 92)
    [--] Data in InnoDB tables: 144K (Tables: 9)
    [!!] Total fragmented tables: 16

    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned

    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 1d 23h 17m 16s (21M q [128.130 qps], 323K conn, TX: 31B, RX: 1B)
    [--] Reads / Writes: 93% / 7%
    [--] Total buffers: 34.0M global + 2.7M per thread (500 max threads)
    [OK] Maximum possible memory usage: 1.4G (35% of installed RAM)
    [OK] Slow queries: 0% (1/21M)
    [OK] Highest usage of available connections: 9% (49/500)
    [OK] Key buffer size / total MyISAM indexes: 8.0M/11.4M
    [OK] Key buffer hit rate: 99.3% (10M cached / 72K reads)
    [!!] Query cache is disabled
    [OK] Sorts requiring temporary tables: 0% (4 temp sorts / 10K sorts)
    [OK] Temporary tables created on disk: 16% (170 on disk / 1K total)
    [!!] Thread cache is disabled
    [!!] Table cache hit rate: 2% (64 open / 2K opened)
    [OK] Open file limit used: 4% (112/2K)
    [OK] Table locks acquired immediately: 99% (21M immediate / 21M locks)
    [!!] InnoDB data size / buffer pool: 171M/128M

    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        Set thread_cache_size to 4 as a starting value
        Increase table_cache gradually to avoid file descriptor limits
    Variables to adjust:
        query_cache_size (>= 8M)
        thread_cache_size (start at 4)
        table_cache (> 64)

    Nous n'allons pas voir en détails toutes ces informations mais seulement celles qui nous intéressent. En l'occurrence
    - Query cache is disabled
    - Thread cache is disabled
    - InnoDB data size / buffer pool: 171M/128M

    On va activer le cache pour Mysql, connectez-vous à MySql avec les droits de super utilisateur avec tous les privilèges pour pouvoir passer les commandes que nous allons effectuer :

    mysql -u root -p

    Regardons quelques résultats de vos variables MySql en faisant :

    show variables like 'query%';
    +------------------------------+---------+
    | Variable_name                | Value   |
    +------------------------------+---------+
    | query_alloc_block_size       | 8192    |
    | query_cache_limit            | 1048576 |
    | query_cache_min_res_unit     | 4096    |
    | query_cache_size             | 0 |
    | query_cache_type             | ON      |
    | query_cache_wlock_invalidate | OFF     |
    | query_prealloc_size          | 8192    |
    +------------------------------+---------+

    Vous voyez que query_cache_size est à 0, ce qui désactive d'emblé le cache MySql. Passez la commande suivante :

    SET GLOBAL query_cache_size = 8388608;

    query_cache_size attend une valeur en bytes, si vous voulez allouer 8MB au cache vous devez faire 1024 * 1024 * 8 = 8388608. Ensuite tapez exit pour terminer la session MySql. Editez le fichier my.cnf :

    vi /etc/my.cnf

    Et ajoutez sous [mysqld] ceci :

    query_cache_size = 16M
    query_cache_type=1
    query_cache_limit=4M

    Maintenant le cache devrait être activé, vous pouvez faire un /etc/init.d/mysqld restart et relancer les test (./mysqltuner.pl) pour vérifier cela.

    Ensuite attaquons-nous aux connexions persistantes (Thread cache is disabled), toujours sous [mysqld] dans my.cnf ajoutez :

    thread_cache_size=4

    Et pour InnoDB data size / buffer pool: 171M/128M) il faudra ajouter une variable toujours dans my.cnf sous [mysqld]. La variable est innodb_buffer_pool_size. Cette variable définie la quantité de mémoire que MySql peut utiliser pour les indexes de cache et les données des tables InnoDB. Ajouter sous [mysqld] dans /etc/my.cnf ceci :

    innodb_buffer_pool_size=4

    Détruire les connexions inactives

    Un des problèmes de MySQL est qu'il garde perfois des connexions actives à cause de connexions lentes entre le client et le serveur et cela va ralentir vos processus. Pour détruire les connexions inactives rajoutez ces lignes :

    wait_timeout=300
    connect_timeout=300
    max_connect_errors=10

    Augmenter la taille de la table "cache"

    Sur un site aux tables volumineuses ou même sur un CMS e-commerce ou blog il faut augmenter la taille des tables créées par Mysql, rajoutez ces lignes :

    max_heap_table_size = 300M
    tmp_table_size = 300M
    table_open_cache = 15000

    Les 2 premières lignes servent à s'assurer que MySQL ne créé pas de base de données temporaire sur le disque mais qu'il utilise la mémoire à la place. La dernière ligne table_open_cache, la plus importante, sert à augmenter la taille de la table cache (précédemment appelée sous MySQL inférieur à 5.3 table_cache). Attention, si vous mettez de trop grandes valeurs pour les 2 premières lignes votre serveur va vite saturer et provoquer une erreur 500 lors de l'appel de vos pages. Essayez donc plusieurs valeurs afin de tester la capacité de votre serveur.

    Redémarrez mysql (/etc/init.d/mysqld restart). Vous pouvez refaire un test pour voir les améliorations et surtout tester vos site web afin de afin de voir si les résultats de retour des pages sont meilleurs.

    Attendez 24H. et refaite tourner le script mysqltuner.pl afin de voir si vous devez changer quelques variables Mysql comme table_open_cache.

    Voir aussi : Compression - Optimiser son site Internet mod_deflate.

1 commentaire

Toublanc  31-01-2014

Merci pour ces astuces ;)

Laissez un commentaire

* Votre e-mail ne sera jamais utilisé ou donné à un tiers

Recherche

Catégories

Newsletters

Archives