SelectEtoile : Base de connaissance SGBD

Posts Tagged 'optimisation'

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

 

 

 

 

 

 

 

Optimisation des caches


  • bdb_cache_size : cache de données
  • bulk_insert_buffer_size : favorise les insert massif sur les tables myisam
  • join_buffer_size : cache servant au jointure n’utilisant pas d’indexes
  • key_buffer_size : cache pour les indexes (25% de la mémoire totale)
  • Verification de perfs de ce cache avec show status(Variables "Key_read_requests", "Key_reads", "Key_write_requests", et "Key_writes". Le ratio "Key_reads/Key_read_request")
  • read_buffer_size : buffer utilisateur affecté pour chaque lecture de table
  • record_rnd_buffer_size : Quand des données sont lus après un tri, les données sont lus à travers ce tampon pour éviter des accès disques
  • query_cache_limit : Ne pas mettre en cache les résultats supérieur à cette valeur.
  • query_cache_size : Cache de requetes stockant les les ordres ainsi que les resultats
  • query_cache_type : Peut prendre les valeurs suivantes (numériques seulement):

Valeur

Alias

Commentaire

0

OFF

Ne pas mettre en cache ni récupérer des valeurs

1

ON

Mettre en cache tous les résultats sauf les requêtes ayant précisé "SELECT SQL_NO_CACHE"

2

DEMAND

Mettre en cache seulement les requêtes ayant précisé " SELECT SQL_CACHE"

 

  • sort_buffer : Chaque processus qui a besoin de faire un tri alloue un tampon de cette taille. (group by et order by)

 

 

Requêtes sur le cache de requête :

  • show variables like 'query_cache_%';
  • show status like 'Qcache_%';
  • FLUSH QUERY CACHE;                  -- dé-fragmenter le cache
  • RESET QUERY CACHE;                  -- vide le cache de requête


Articles tagged

Analyser une requete sql SYBASE ASE

Les outils présentés ci-dessous permettent de tracer et d'analyser une requête sql sur un serveur SYBASE ASE.

 

  • set showplan on : affiche le plan d'exécution de la requête
  • set statistics io,time on : affiche le nombre d'io/ table/index et le temps des différentes étapes
  • set noexec on : combiner avec showplan, permet d'afficher le plan sans executer la requête
  • set tracefile dirname/filename [for spid] : redirige les résultats des ordres SQL d'un session vers le fichier spécifié (New ASE15)
Toujours faire un 'set tracefile off' en fin de session
Sinon, il est possible que toutes les autres sessions soient polluées par cette trace
  • set show_sqltext {on | off} : affiche le SQL text d'un requête, procédure stockée, curseur ...
  • dbcc traceon(3604) : affiche la sortie à l'écran. Il faut positionner cette option pour les commandes suivantes
  • dbcc traceon(302) : affiche le cout des jointures et des SARG sur chaque table
  • dbcc traceon(310) : affiche les plans de moins en moins couteux (NEW PLAN). Par contre, il n’affiche pas les plans de plus fort cout.
  • dbcc traceon(317) :  affiche les plans plus couteux que les NEW PLANS. Ce sont les WORK PLANS.
  • dbcc sqltext(spid) : affiche la requête lancée par le spid

 


Différents paramètres de 310 et 317:

  • total cost : estimation du cout total en millisecondes d’une jointure (lp*2ms+pp*18ms)

  • varno : position de la table dans la clause from (NB : max 16 tables / jointure, si varno > 16 worktable)

  • indexid : indid de l’index utilisé (0 si la table)

  • pathtype : type d’opération (sclause, join ou orstruct)

  • method : NESTED ITERATION (en majorité), REFORMATTING ou OR OPTIMIZATION

  • outerrows : nombre de lignes de la table externe pour chaque ligne de la table courante

  • joinsel : estimation de la selectivité de la table ou de l’indexe de la table courante

  • cpages : nombres de pages (donnees ou indexes) que l’optimiseur estime dans la table courante

  • prefetch : prefetch strategy (N : pas de prefetch, S : prefetch strategy). Important quand le server est configuré en Large IO.

  • Iosize : taille des blocks io choisi par l’optimiseur

  • Replace : strategie de replacement dans le cache (LRU ou MRU)

  • Lp : Nombre de logical reads = outerrows * cpages

  • Pp : nombre de physical reads

  • Corder : principale (premiere) colonne de l’indexe (colid de syscolumns)

  • Jnvar, Refcost, Refpages, reftotpages, ordercol[0] sont peu utilisables et peu importantes

 

 

Si vous avez un doute sur une requête, il est possible de forcer le plan d'exécution ou de forcer un index précis.

Cela est très utile si on pense qu'un index ou un plan est meilleur que ce que l'optimiseur SYBASE a choisi

 

Pour forcer le plan, il suffit de lancer la commande suivante :

set forceplan on -- avant de lancer la requête

Pour forcer l'index, il suffit de préciser entre parenthèses "index nom_index" comme indiqué dans l'exemple suivant :

select * from ma_table (index mon_index) where col1 ='00'

 

Le forceplan force l'optimiseur a choisir son plan dans l'ordre des tables de la clause FROM. L'optimiseur choisit toutefois les index qu'il peut prendre sauf si vous les avez forcer aussi.

 

Informations pratiques :

Si une valeur d’une colonne est plus importante que les autres (ex : 90 %), les jointures sur cette colonne (ex : valeur1 représentant 0.001 % de la table) ne prendront pas forcément le bon index.

En utilisant la commande sp_modifytats comme suit, cela permet de modifier l’indice de densité de la colonne et à l’optimiseur de prendre un meilleur indexe.

 

Sp_modifystats tablename,colonnename,’REMOVE_SKREW_FROM_DENSITY’

 

Lien très intéressant sur le sujet : http://www.sybase.com/detail?id=2602#539757