Aller au contenu | Aller au menu | Aller à la recherche


Optimiser son serveur MySQL

Par PlaceOweb le mercredi, octobre 4 2006, 00:31 - SQL - Lien permanent

Votre serveur MySQL commence à ne plus supporter la charge de vos nombreuses requêtes... terminons l'installation initiale par une configuration du daemon mysql via les variables mysql. (Document orienté pour mysql 4.0.1x, mysql 5.0, mysql 5.1 et mysql 5.5 )

Valeurs de paramétrage agissant sur le comportement des requêtes MySQL

MySQL 5.5

MyISAM ou InnoDB

  • Storage Engines (avec un table de comparaison des fonctionnalités offertes par les différents moteurs de table InnoDB, MyISAM et les autres...)
    • InnoDB: A transaction-safe (ACID compliant) storage engine for MySQL that has commit, rollback, and crash-recovery capabilities to protect user data. InnoDB row-level locking (without escalation to coarser granularity locks) and Oracle-style consistent nonlocking reads increase multi-user concurrency and performance. InnoDB stores user data in clustered indexes to reduce I/O for common queries based on primary keys. To maintain data integrity, InnoDB also supports FOREIGN KEY referential-integrity constraints. InnoDB is the default storage engine as of MySQL 5.5.5.
    • MyISAM: The MySQL storage engine that is used the most in Web, data warehousing, and other application environments. MyISAM is supported in all MySQL configurations, and is the default storage engine prior to MySQL 5.5.5.

MySQL 5.1

/etc/my.cnf

[mysqld]
# Ajout de la gestion des jeux de caractères et collation supportées par MySQL.
#character-set-server    = latin1
#collation-server        = latin1_general_ci
character-set-server    = utf8
collation-server        = utf8_general_ci

MyISAM ou InnoDB

  • Storage Engines (avec un table de comparaison des fonctionnalités offertes par les différents moteurs de table MyISAM, InnoDB et les autres...)
    • MyISAM: The default MySQL storage engine and the one that is used the most in Web, data warehousing, and other application environments. MyISAM is supported in all MySQL configurations, and is the default storage engine unless you have configured MySQL to use a different one by default.
    • InnoDB: A transaction-safe (ACID compliant) storage engine for MySQL that has commit, rollback, and crash-recovery capabilities to protect user data. InnoDB row-level locking (without escalation to coarser granularity locks) and Oracle-style consistent nonlocking reads increase multi-user concurrency and performance. InnoDB stores user data in clustered indexes to reduce I/O for common queries based on primary keys. To maintain data integrity, InnoDB also supports FOREIGN KEY referential-integrity constraints.

MySQL 5.0

MyISAM ou InnoDB ?

Moteurs de tables MySQL et types de table

MySQL supporte plusieurs moteurs de stockage, qui gère différents types de tables. Les moteurs de tables MySQL peuvent être transactionnels ou non-transactionnels.

  • MyISAM : non transactionnel
  • InnoDB : transactionnel

Les avantages des tables transactionnelles (TST) sont :

  • Plus sûr. Même si MySQL crashe ou que vous avez un problème matériel, vous pouvez récupérer vos données, soit par un recouvrement automatique, soit à partir d'une sauvegarde combinée avec le log des transactions.
  • Vous pouvez combiner plusieurs commandes et les accepter toutes d'un seul coup avec la commande COMMIT.
  • Vous pouvez utiliser ROLLBACK pour ignorer vos modifications (si vous n'êtes pas en mode auto-commit).
  • Si une mise à jour échoue, tout vos changements seront annulés. (Avec les tables NTST tous les changements opérés sont permanents)
  • Gère mieux les accès concurrents si la table reçoit simultanément plusieurs lectures.

Avantages des tables non-transactionnelles (NTST) :

  • Plus rapides
  • Utilisent moins d'espace disque
  • Utilisent moins de mémoire pour exécuter les mises à jour.

Vous pouvez combiner les tables TST et NTST dans la même requête pour obtenir le meilleur des deux types. Cependant, dans une transaction sans auto-validation, les modifications à une table non-transactionnelles seront toujours immédiatement enregistrés, et ne pourront pas être annulé.

InnoDB
Paramétrage et optimisation MySQL

/etc/my.cnf

[mysqld]
# Pour loguer l'intégralité des requêtes (SELECT et autres requêtes de mise a jour. A désactiver en production !)
#log

# Ajout de la gestion des jeux de caractères et collation supportées par MySQL.
default_character_set   = latin1
default_collation       = latin1_general_ci
#character-set-server   = utf8
#collation-server       = utf8_general_ci

# Ajout pour détecter les requêtes sans index
# Enregistre des informations supplémentaires dans les fichiers de log (log de modifications, log binaire de modifications, log de requêtes lentes, n'importe quel log en fait). Par exemple, le nom d'utilisateur et un timestamp sont enregistrés avec la requête. Si vous utilisez --log-slow-queries et log-long-format, alors les requêtes qui n'utilisent pas d'index seront aussi enregistrées. Notez que log-long-format est obsolète depuis la version 4.1, où --log-short-format a été introduite (le format de log long est la configuration par défaut en version 4.1).
log-long-format

# Ajout pour détecter les requêtes lentes
log-slow-queries
long_query_time = 3

# Ajout pour les requêtes sans index (à partir de MySQL 5.0.23)
log-queries-not-using-indexes

La configuration de vos logs

SHOW VARIABLES LIKE 'LOG%'
Variable_name Value
log OFF
log_bin OFF
log_bin_trust_function_creators OFF
log_error  
log_queries_not_using_indexes ON
log_slave_updates OFF
log_slow_queries ON
log_warnings 1

Vous pouvez modifier certaines valeurs des variables système avec :

-- Activation
SET GLOBAL log_queries_not_using_indexes = 'ON';
-- ou
SET GLOBAL log_queries_not_using_indexes = 1;
 
-- Désactiver
SET GLOBAL log_queries_not_using_indexes = 'OFF';
-- ou
SET GLOBAL log_queries_not_using_indexes = 0;

Variables modifiables à chaud avec un SET

Variables non modifiables à chaud via SET

MySQL 4.0.1

Les variables du serveur mysql sont stockés dans /etc/my.cnf

[mysqld]
port            = 3306
socket          = /var/lib/mysql/mysql.sock
skip-locking
set-variable    = max_allowed_packet=1M
set-variable    = table_cache=512
set-variable    = record_buffer=2M
set-variable    = thread_cache=8
#set-variable   = thread_concurrency=4  # Try number of CPU's*2
set-variable    = myisam_sort_buffer_size=64M

set-variable    = query_cache_size=0 # Désactive l'utilisation du cache
set-variable    = sort_buffer_size=10M
set-variable    = read_buffer_size=4M
set-variable    = key_buffer_size=1G
set-variable    = join_buffer_size=2M

Ou modifiables à chaud en utilisant les requêtes suivantes

SET GLOBAL join_buffer_size=2097152;	# 2M
SET GLOBAL key_buffer_size=1073741824;	# 1G
SET GLOBAL read_buffer_size=4194304;	# 4M
SET GLOBAL sort_buffer_size=10485760;	#10M

join_buffer_size

La taille du buffer qui est utilisée pour les jointures complètes (les jointures qui n'utilisent pas d'index). Ce buffer est alloué une fois pour chaque jointure entre deux tables. Augmentez cette valeur si vous voulez obtenir des jointures plus rapides, lorsque l'ajout d'index n'est pas possible. Normalement, le mieux est d'ajouter de bons index.

key_buffer_size

Les blocs d'index des tables MyISAM et ISAM sont mis en buffer et partagés par tous les threads. key_buffer_size est la taille du buffer utilisé. Le buffer de clé est aussi appelé le cache de clé.

Augmentez cette valeur pour obtenir une meilleure gestion des index (pour les lectures et écritures multiples), autant que vous le pouvez : 64 Mo sur une machine de 256 Mo est une valeur répandue. Toutefois, si vous utilisez une valeur trop grande (par exemple, plus de 50% de votre mémoire totale), votre système risque de commencer à utiliser sa mémoire swap, et devenir très lent. N'oubliez pas que MySQL ne met pas en cache les données lues, et il faut laisser le système d'exploitation respirer.

Pour obtenir encore plus de vitesse lors de l'écriture de plusieurs lignes en même temps, utilisez LOCK TABLES.

read_buffer_size

Chaque thread qui fait une recherche séquentielle alloue un buffer de cette taille pour son scan. Si vous faites de nombreux scan séquentiels, vous pourriez avoir besoin d'augmenter cette valeur. Cette variable a été ajoutée en MySQL 4.0.3. Auparavant, cette variable s'appelait record_buffer.

sort_buffer_size

Chaque thread qui doit faire un tri alloue un buffer de cette taille. Augmentez cette taille pour accélérer les clauses ORDER BY ou GROUP BY.

Optimisation automatique et quotidienne

Dans un petit script bash et avec un CRON, vous pouvez forcer l'optimisation quotidienne des vos bases :

#!/bin/bash
 
#echo "Serveur en mode SLAVE - EXIT"
#exit 0
 
MYSQL_HOST=localhost
MYSQL_USER=root
MYSQL_PASS="lePassowrd"
 
# En cas de crash, reparation
#mysqlcheck --all-databases --repair --host=$MYSQL_HOST --user=$MYSQL_USER --password=$MYSQL_PASS >mysqlcheck.repair.log
#exit 0
 
# Optimisation des tables (seulement le dimanche et si l'espace dique est suffisant)
# %u     quantième du jour dans la semaine (1..7) ; « 1 » représente le lundi
jour_dela_semaine=`date +%u`
if [ $jour_dela_semaine -eq 7 ];
then
#       echo "On traite, car on est dimanche"
 
#       Vérification espace disque
        value=`echo "SHOW VARIABLES LIKE 'datadir';" | mysql --host=$MYSQL_HOST --user=$MYSQL_USER --password=$MYSQL_PASS --skip-column-names`
        if [ -z "$value" ]
        then
                echo "Impossible de lire la variable 'datadir' depuis MySQL, vérifier les accès à MySQL !" && exit 1
        else
#               echo "Le répertoire contenant les données MySQL : $value"
                DATADIR=`echo $value | cut -d " " -f2`
                SPACE_LEFT_ON_PART=`df "$DATADIR" | awk '{print $4}' | tail -n 1`
#               echo "Espace libre sur la partition $DATADIR : $SPACE_LEFT_ON_PART"
        fi
 
        value=`echo "SELECT table_schema, table_name, ROUND( SUM( data_length + index_length ) /1024 ) FROM information_schema.tables GROUP BY table_schema, table_name ORDER BY 3 DESC LIMIT 1;" | mysql --host=$MYSQL_HOST --user=$MYSQL_USER --password=$MYSQL_PASS --skip-column-names`
        if [ -z "$value" ]
        then
                echo "Impossible de lire le resultat de la requete SQL depuis MySQL, vérifier les accès à MySQL !" && exit 1
        else
#               echo "La base table taille la plus grosse sous MySQL : $value"
                BIGGEST_BASE_TABLE=`echo $value | awk '{print $1"."$2}'`
                BIGGEST_SPACE_TABLE=`echo $value | awk '{print $3}'`
#               echo "La plus grande base.table : $BIGGEST_BASE_TABLE : $BIGGEST_SPACE_TABLE"
        fi
 
        if [ $SPACE_LEFT_ON_PART -gt $BIGGEST_SPACE_TABLE ]
        then
#               echo "OK la place disque est suffisante";
                mysqlcheck --all-databases --analyze --check-only-changed --optimize --host=$MYSQL_HOST --user=$MYSQL_USER --password=$MYSQL_PASS >mysqlcheck.log
        else
                SPACE_REQUIRED=$(( $BIGGEST_SPACE_TABLE - $SPACE_LEFT_ON_PART ))
                SPACE_REQUIRED_MO=$(( $SPACE_REQUIRED / 1024 ))
                SPACE_REQUIRED_GO=$(( $SPACE_REQUIRED_MO / 1024 ))
                BIGGEST_SPACE_TABLE_MO=$(( $BIGGEST_SPACE_TABLE / 1024 ))
                BIGGEST_SPACE_TABLE_GO=$(( $BIGGEST_SPACE_TABLE_MO / 1024 ))
#               echo "ERREUR l'espace disque est insuffisant, il manque $SPACE_REQUIRED octets ($SPACE_REQUIRED_MO Mo) ($SPACE_REQUIRED_GO Go)"
                echo "ERREUR l'espace disque est insuffisant sur `hostname`, il manque $SPACE_REQUIRED octets ($SPACE_REQUIRED_MO Mo) ($SPACE_REQUIRED_GO Go) pour optimiser la table $BIGGEST_BASE_TABLE $BIGGEST_SPACE_TABLE octets ($BIGGEST_SPACE_TABLE_MO Mo) ($BIGGEST_SPACE_TABLE_GO Go)" |  mail -s "MySQL, espace disque insuffisant ($SPACE_REQUIRED_MO Mo manquant pour $BIGGEST_BASE_TABLE)" dba@placeoweb.com;
        fi
 
#else
#        echo "On ne traite pas car on est pas dimanche : $jour_dela_semaine"
fi

Ressources

Ajouter un commentaire

Le code HTML est affiché comme du texte et les adresses web sont automatiquement transformées.

Fil des commentaires de ce billet