Discussion :

[tetzispa]

Bonjour,

depuis quelques semaines j'ai mon serveur (4CPU, 8GO de ram, Redhat) qui a un load average très élevé (entre 20 et 30).

Sur ce serveur, je n'ai que nagios et mysql qui tourne. nagios rentre les infos dans mysql via ndo.

par défaut, l'installation de mysql crée un fichier 'my.cnf' vide.

Je l'ai rempli afin de tenter d'optimiser les requêtes etc. mais rien n'y fait, donc je sèche un peu afin d'optimiser mysql et faire en sorte que mon serveur arrête de monter en charge pour quelques éléments qui sont supervisés (153 hosts pour 1823 services).

j'ai installé mysqltunner.pl afin de trouver ce qui pouvait clocher, mais là, j'ai pratiquement rien à faire.

Quand je corrige ce qu'il me dit, tout va bien, et quand je le relance le lendemain (comme ce matin) j'ai encore des choses à modifier, donc je ne sais plus où donner de la tête :

General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries without LIMIT clauses
Increase table_open_cache gradually to avoid file descriptor limits
Read this before increasing table_open_cache over 64:
Variables to adjust:
tmp_table_size (> 64M)
max_heap_table_size (> 64M)
table_open_cache (> 4096)

my.cnf :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
skip-innodb
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
 
 
query_cache_type               = 1
query_cache_limit              = 4M
query_cache_size               = 1G
tmp_table_size                 = 64M
max_heap_table_size            = 64M
max_connections                = 500
thread_cache_size              = 50
open_files_limit               = 65535
table_definition_cache         = 4096
table_open_cache               = 4096
key_buffer_size		   = 500M	
 
 
log_queries_not_using_indexes  = 1
slow_query_log                 = 1
slow_query_log_file            = /var/lib/mysql/mysqld-slow-query.log
 
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[CinePhil]

Avez-vous cherché si vous avez des requêtes lentes ?
http://www.dbnewz.com/2010/05/28/tra...quetes-lentes/

[tetzispa]

le script me donne l'info, apparemment ça va :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-------- Performance Metrics -------------------------------------------------
[--] Up for: 20h 37m 30s (34M q [460.734 qps], 7K conn, TX: 15B, RX: 14B)
[--] Reads / Writes: 18% / 82%
[--] Total buffers: 1.6G global + 2.7M per thread (500 max threads)
[OK] Maximum possible memory usage: 2.9G (37% of installed RAM)
[OK] Slow queries: 2% (755K/34M)
[OK] Highest usage of available connections: 1% (6/500)
[OK] Key buffer size / total MyISAM indexes: 500.0M/300.1M
[OK] Key buffer hit rate: 100.0% (170M cached / 63K reads)
[OK] Query cache efficiency: 94.4% (26M cached / 27M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 13K sorts)
[!!] Temporary tables created on disk: 39% (2K on disk / 5K total)
[OK] Thread cache hit rate: 99% (6 created / 7K connections)
[!!] Table cache hit rate: 1% (307 open / 16K opened)
[OK] Open file limit used: 0% (461/65K)
[OK] Table locks acquired immediately: 99% (9M immediate / 9M locks)

[CinePhil]

Je ne sais pas comment interpréter les résultats mais s'il n'y a que 2% de slow queries mais que celles-ci sont lancées très souvent, c'est peut-être une cause de la lenteur de votre serveur. Il conviendrait donc quand même d'analyser ces slow queries, de voir si elles sont lancées souvent et de chercher pourquoi elles sont lentes.

Je vois aussi qu'il y a beaucoup d'insertions par rapport aux lectures. Peut-être que, si la taille des tables dans lesquelles sont insérées les données devient grande, l'indexation de ces tables à chaque insertion prend du temps. Il conviendrait alors de tester ce phénomène et, peut-être, de revoir le modèle de données et/ou l'indexation.

[tetzispa]

j'essaie de comprendre ce que tu me dis

en effet, si les 2% (au moment T, il peut en avoir plus ou moins) sont des requêtes récurrentes l'impact peut être à ce niveau.

Je confirme qu'il y a beaucoup d'insertions dans la BDD, cependant je ne peux pas revoir le modèle (ça fonctionne comme ça avec nagios et son plugin ndo).

Donc pour tester, je regarde du côté de ton lien ?

Sinon, je ne sais pas comment tourne parfaitement mysql, est-il possible de vider mes tables une fois par mois et de stocker de façon incrémentielles afin d'éviter qu'elle gonfle trop (je pense que ça pourrait venir de là)

[tetzispa]

j'ai tracé mes requêtes lentes et il apparaît que c'est surtout via cacti que j'en ai

mais bon, pour analyser c'est un peu compliqué n'étant pas spécialiste en SQL

[CinePhil]

Pour préciser mon précédent commentaire, lorsque tu insères une ligne dans une table, MySQL réindexe la table pour prendre en compte cette nouvelle insertion. Quand il y a quelques centaines ou milliers de lignes, c'est rapide et quasi transparent pour l'utilisateur. Quand il y a des millions de lignes à ré-indexer pour une ligne insérer, ça devient lourd et ça ralentit les insertions.
S'il y a des insertions en chaîne, il peut alors être intéressant de désactiver les index, d'insérer puis de réactiver les index ; ça peut aller plus vite.

De manière plus générale, MySQL devient beaucoup moins performant quand la BDD grossit. Si on a une BDD avec des tables dépassant le million de lignes, il est prudent d'envisager de changer de SGBD. Postgresql est déjà plus performant, sinon passer à Oracle ou MS SQL Server, DB2... des vrais SGBDR de pro quoi !

[tetzispa]

S'il y a des insertions en chaîne, il peut alors être intéressant de désactiver les index, d'insérer puis de réactiver les index ; ça peut aller plus vite.

comment faire ?


et pour un SGBD de pro je suis d'accord, les autres équipes utilisent oracle, mais pour nagios, c'est niet

[CinePhil]

comment faire ?

De mémoire, on ne peut pas simplement "désactiver" un index sous MySQL ; il faut le supprimer puis le recréer lorsque les insertions en masse sont faites.
Mais il vaut mieux tester avant si ça vaut le coup dans ton cas. Ce n'est probablement sensible qu'à partir de plusieurs millions, voire dizaines de millions de lignes.

[tetzispa]

ok c'est mort donc haha