SelectEtoile : Base de connaissance SGBD

Posts Tagged 'handler'

Administration et optimisation générales Mysql

Administration  / Optimisation MySQL

 

Liens des docs de référence (en français)

 

Orientation de l'optimiseur

● Possibilité de le contraindre

– USE INDEX
– FORCE INDEX
– IGNORE INDEX

● STRAIGHT_JOIN

 

Le cache de requête

● Mise en cache des requêtes SELECT et de leur résultat
● Les requêtes doivent être strictement identiques (case,espace,...)
● Le cache est toujours à jour, car lorsqu'une table est modifiée, les requêtes qui lui font références sont invalidées
● Certaines fonctions empêchent la mise en cache(NOW(), RAND()...)

 

Initialisation du cache de requêtes : SHOW VARIABLES LIKE 'query_cache%';

Visualisation des compteurs de ce cache : SHOW STATUS LIKE 'qcache%';

 

● FLUSH QUERY CACHE : Défragmente le cache de requête
● RESET QUERY CACHE : Réinitialise le cache de requêtes
● FLUSH TABLES : Réinitialise le cache de requêtes

 

Les commandes flush :

● flush privileges : Recharge les privilèges des tables de droits dans la base mysql
● flush tables : Ferme toutes les tables ouvertes, et force les tables utilisées à se refermer. Cela vide aussi le cache de requêtes.
● flush table tablename : Vide du cache uniquement les tables nommées.
● flush logs : Ferme et réouvre tous les fichiers de log

● flush query cache : Dé-fragmente le cache des requêtes pour mieux en utiliser la mémoire. Cette commande n'effacera aucune requête du cache, à la différence de RESET QUERY CACHE.

 

Description de quelques paramétrages MySQL :

● Key_buffer_size

- Tampon qui stock les index des tables MyISAM
- 25 à 30% de la RAM, pour un serveur dédié MySQL, en full MyISAM
- Pas trop grand car risque de swap !
- Ratio: key_reads / key_read_requests < 0.03 (0.01 encore mieux), sinon l'augmenter
- La fraction utilisée du buffer est : (Key_blocks_used * key_cache_block_size) / key_buffer_size

 

● myisam_sort_buffer_size

– Tampon pour la création d'index pour les requêtes de maintenance: ALTER TABLE, REPAIR TABLE, LOAD DATA INFILE
– SET SESSION myisam_sort_buffer_size = 800*1024*1024;
ALTER TABLE ma_table ADD INDEX ...;
 

● bulk_insert_buffer_size

– Tampon pour les insertions massives
• INSERT ...SELECT
• INSERT VALUES(),(),(),....
• LOAD DATA INFILE

 

● innodb_buffer_pool_size

– Tampon pour stocker les index et des données des tables InnoDB
– Jusqu'à 80% de la RAM, pour un serveur dédié MySQL, en full InnoDB
 

● innodb_flush_logs_at_trx_commit (1 par défaut)

– 0: Risque de pertes de transactions validées en cas de crash d 'InnoDB
– 1: transactions flushées après chaque commit. Pas de pertes de transactions validées (ACID)
– 2: Risque de pertes de transactions validées uniquement en cas de crash de l'O.S.

 

● innodb_log_buffer_size

– Taille du tampon des logs d'InnoDB
– Vidé environ toutes les secondes (checkpoint)
– En général entre 8Mo & 16Mo
 

● innodb_log_file_size

– Taille des fichiers de log d'InnoDB (2 par défaut)
– Une grande valeur améliore les performances. Mais augmente le temps de restauration
– Valeurs courantes : 64Mo à 512Mo

 

● table_cache

– Cache des descripteurs de fichier
– Chaque table ouverte nécessite un descripteur de fichiers (par connection), plus un pour le .MYI (MyISAM)
– Augmenter votre table_cache si opened_tables croit rapidement
 

● thread_cache

– Cache des threads
– Chaque session prend un thread à la connexion et le rend à la déconnexion
– Augmenter si threads_created croit rapidement
– Taux de succès du cache de thread: threads_created/connections

 

● 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
- Tampon d'enregistrements pour les full table scans
 

 ● open_files_limit

- Il est courant dce l'augmenter assez largement (ex : 65000)

 

● table_open_cache

- Si l'indicateur Opened_tables augmente, il est nécessaire d'accroitre la variable 'table_open_cache'
- L'augmentation de cette variable augmente le nombre de descripteurs

 

● sort_buffer_size

  • Tampon pour GROUP BY / ORDER BY
  • Indicateurs :
    • Sort_scan  : indique en général les tablescan
    • Sort_merge_passes : pas assez d'espace dans le sort buffer pour faire un tri. Il faut peut-être augmenter le sort_buffer_size.
 

● join_buffer_size

- Zone de jointure des requêtes n'utilisant pas d'index. Elle peut être augmenté mais le mieux est de trouver les indexes pertinents pour cette requête.
- Ce buffer est alloué une fois pour chaque jointure entre deux tables.
 

● tmp_table_size

- Tampon pour les tables temporaires stockées en mémoire (memory). Au delà, elles sont copiées sur disque (MyISAM)

 

● query_cache_size

- La mémoire allouée pour stocker les résultats des vieilles requêtes
- Pour désactiver, il suffit de positionner à 0

 

● max_allowed_packet

- Le buffer de message est initialisé avec net_buffer_length octets, mais peut grandir jusqu'à max_allowed_packet octets lorsque nécessaire.
- Cette valeur est par défaut petit, pour intercepter les gros paquets, probablement erronés. Vous devez augmenter cette valeur si vous utilisez de grandes colonnes BLOB. Cette valeur doit être aussi grande que le plus grand BLOB que vous utiliserez.

 

● thread_cache_size

- nombre threads
- Dans beaucoup de cas, on met cette valeur à 0. 

 

 

 

 

Indicateurs de performance :

Ces indicateurs sont interrogeable via 'show status like "%expression%";'

● Indicateur de hautes charges

– max_used_connections
– threads_created
– opened_tables
 

● Indicateur de réponses lentes

– slow_queries
– Slow_launch_threads (Nombre de threads qui ont pris plus de slow_launch_time secondes pour être créés)

 

● Indicateurs de comportements

– Table_locks_immediate
– Table_locks_waited
 

● Les handler

– Handler_read_first=>lecture d'index
– Handler_read_key=>lecture d'index
– Handler_read_next=>lecture d'index
– Handler_read_prev=>lecture d'index
– Handler_read_rnd=>lecture des données
– Handler_read_rnd_next=>lecture des données

 

● Indicateurs de requêtes:

- Tables temporaires
• Created_tmp_disk_tables => augmenter tmp_table_size
• Created_tmp_tables => order by, group by
- Requêtes pas optimisées
• Select_full_join, Select_full_range_join: problèmes d'index dans la jointure
• Select_scan: full scan sur la première table de la jointure
- Requêtes coûteuse
• sort_merge_passes => augmenter le sort_buffer_size

 

Supervision 

● Supervision des requêtes lentes

- log-slow-queries[=file_name] : génère un fichier contenant les requêtes SQL qui vont mettre plus de long_query_time secondes à s'exécuter.

- Le temps d'acquisition d'un verrou n'est pas compté. 

 

 

Paramètres généraux :

lower_case_table_names = 1 : Rend insensitive le nom des tables

ex : même résultat pour ces 2 commande: 'select * from matable' et 'select * from MaTable'

transaction-isolation = {READ-UNCOMMITTED | READ-COMMITTED
| REPEATABLE-READ | SERIALIZABLE}
pour vérification : SELECT @@global.tx_isolation; ou SELECT @@tx_isolation;

 

Paramètres InnoDB :

innodb_data_home_dir = /MYSQL/dbdata/InnoDB_data
innodb_data_file_path = ibdata1:500M;ibdata2:5000M

Cette conf permet de fixer la taille de l'enveloppe InnoDB (ici , 5.5 Go)

Pour superviser, le taux de remplissage de cette enveloppe, il suffit de lancer la commande suivante :

select date_format(now(),'%m/%d/%Y %H:%i:%s') 'mysdate',

engine,

round((sum(data_length)+sum(index_length))/1024/1024) as used,

ceiling(substring( table_comment, instr( table_comment, ':' ) +2, instr( table_comment, ' kB' ) - instr( table_comment, ':' ) -2 )/1024)+round(data_free/(1024*1024)) as free

from information_schema.tables

where engine='InnoDB'

group by engine;

 

Gestion de la log tournante sur 3 fichiers de 512 Mo. En cas de changement de taille de la log, il suffit stopper l'instance MySQL, modifier dans le fichier cnf la taille des log, renommer les anciens fichiers de log au cas ou et de redémarrer. MySQL recrée automatiquement les nouveaux fichiers de log avec leur nouvelle taille. Il peut y avoir des problèmes de performance si ces fichiers de log sont trop petit. 

 innodb_log_file_size=512M
innodb_log_files_in_group=3
innodb_log_group_home_dir=/MYSQL/dbdata/InnoDB_log

 

InnoDB essaie d'écrire sur le disque au moins une fois par seconde, mais cette écriture n'est plus garantie. Pour garantir cette écriture, il faut passer ce paramètre à 1.

innodb_flush_log_at_trx_commit=0