Vous n'êtes pas identifié(e).
Pages : 1
Bonjour,
Je trouve ma base de données Mysql un peu lente, alors du coup j'ai voulu regarder un peu les outils existant pour analyser un peu plus en détails le ce qui se passe.
J'ai trouvé un script perl mysqlreport sur ce site : http://hackmysql.com/mysqlreport
J'essaye maintenant de comprendre le rapport générer mais n'étant pas expert en base de données j'ai un peu de mal.
Le rapport en question :
MySQL 5.0.51a-24+lenny4 uptime 78 3:25:29 Wed Dec 8 12:06:02 2010
__ Key _________________________________________________________________
Buffer used 6.00k of 16.00M %Used: 0.04
Current 2.92M %Usage: 18.24
Write hit 100.00%
Read hit -38333%
__ Questions ___________________________________________________________
Total 2.53G 374.5/s
DMS 1.94G 287.5/s %Total: 76.78
Com_ 582.49M 86.3/s 23.04
QC Hits 3.57M 0.5/s 0.14
COM_QUIT 1.01M 0.2/s 0.04
-Unknown 19.07k 0.0/s 0.00
Slow 10 s 3.32k 0.0/s 0.00 %DMS: 0.00 Log: OFF
DMS 1.94G 287.5/s 76.78
SELECT 1.05G 155.9/s 41.64 54.23
UPDATE 592.84M 87.8/s 23.45 30.54
INSERT 292.57M 43.3/s 11.57 15.07
DELETE 3.05M 0.5/s 0.12 0.16
REPLACE 0 0/s 0.00 0.00
Com_ 582.49M 86.3/s 23.04
commit 290.21M 43.0/s 11.48
begin 290.21M 43.0/s 11.48
change_db 1.03M 0.2/s 0.04
__ SELECT and Sort _____________________________________________________
Scan 206.01k 0.0/s %SELECT: 0.02
Range 27.96M 4.1/s 2.66
Full join 7.76k 0.0/s 0.00
Range check 0 0/s 0.00
Full rng join 159 0.0/s 0.00
Sort scan 4.34M 0.6/s
Sort range 60.55k 0.0/s
Sort mrg pass 0 0/s
__ Query Cache _________________________________________________________
Memory usage 6.19M of 16.00M %Used: 38.67
Block Fragmnt 19.00%
Hits 3.57M 0.5/s
Inserts 531.36M 78.7/s
Insrt:Prune 24.71:1 75.5/s
Hit:Insert 0.01:1
__ Table Locks _________________________________________________________
Waited 11 0.0/s %Total: 0.00
Immediate 2.53G 374.8/s
__ Tables ______________________________________________________________
Open 64 of 64 %Cache: 100.00
Opened 40.53k 0.0/s
__ Connections _________________________________________________________
Max used 29 of 100 %Max: 29.00
Total 1.01M 0.2/s
__ Created Temp ________________________________________________________
Disk table 293.79M 43.5/s
Table 583.47M 86.4/s Size: 32.0M
File 5 0.0/s
__ Threads _____________________________________________________________
Running 6 of 25
Cached 3 of 8 %Hit: 99.98
Created 180 0.0/s
Slow 0 0/s
__ Aborted _____________________________________________________________
Clients 610 0.0/s
Connects 2 0.0/s
__ Bytes _______________________________________________________________
Sent 507.37G 75.1k/s
Received 257.49G 38.1k/s
__ InnoDB Buffer Pool __________________________________________________
Usage 6.81M of 8.00M %Used: 85.16
Read hit 97.91%
Pages
Free 76 %Total: 14.84
Data 431 84.18 %Drty: 49.88
Misc 5 0.98
Latched 0 0.00
Reads 70.54G 10.4k/s
From file 1.47G 218.1/s 2.09
Ahead Rnd 6772491 1.0/s
Ahead Sql 4148368 0.6/s
Writes 8.41G 1.2k/s
Flushes 1.10G 162.7/s
Wait Free 1529 0.0/s
__ InnoDB Lock _________________________________________________________
Waits 21966 0.0/s
Current 0
Time acquiring
Total 17212296 ms
Average 783 ms
Max 21508 ms
__ InnoDB Data, Pages, Rows ____________________________________________
Data
Reads 1.51G 224.2/s
Writes 1.28G 189.3/s
fsync 311.69M 46.2/s
Pending
Reads 0
Writes 0
fsync 1
Pages
Created 10.40M 1.5/s
Read 1.58G 234.2/s
Written 1.10G 162.7/s
Rows
Deleted 267.88M 39.7/s
Inserted 289.80M 42.9/s
Read 14.97G 2.2k/s
Updated 589.91M 87.4/sUn truc qui me paraît pas cool c'est les valeurs "Write hit 100.00%" et "Read hit -383333%"
Je capte pas trop malgré la doc à quoi ça correspond et comment l'améliorer, mais il dise que si c'est bas c'est pas bon !.
Extrait doc sur le passage en question : "A lower percentage may indicate a problem. A low key hit percentage is usually caused by the key buffer being too small (indicated in the Key Report section above) which prevents MySQL from loading more indexes into RAM."
Ma variable key_buffer est à 16M dans mon my.cnf.
Merci d'avance de aide 
Dudu
Hors ligne
Salut,
au vu de ce que tu nous dit, je dirais que la solution serait de passer cette variable a 32M...
Le manuel disait « Nécessite Windows XP ou mieux ». J'ai donc installé Linux.
Je ne suis pas asocial, Je ne suis juste pas orienté utilisateur.
Hors ligne
Ca change pas grand chose pour pas dire rien.
D'autres pistes ? des astuces ? Pour info c'est une base utilisé par zabbix pour le monitoring.
Hors ligne
J'ai du mal à lire le rapport mysqlreport, certaines valeurs font penser à un bug : Read hit -383333%
Essaye plutôt le script MySQL Tuning Primer Script, il est disponible ici :
https://launchpad.net/mysql-tuning-primer
Donne nous ensuite le résultat ici.
J'utilise ce script pour le MySQL de Tux-planet, je le trouve bien fait, surtout pour mieux ajuster la configuration du serveur. Il te dit par exemple "augmenter le key_buffer" et là tu sais précisément ce que tu dois faire.
Hors ligne
Ah merci ça à l'air plus lisible que ce que j'avais trouvé.
Le rapport du script tuning primer :
-- MYSQL PERFORMANCE TUNING PRIMER --
- By: Matthew Montgomery -
MySQL Version 5.0.51a-24+lenny4-log x86_64
Uptime = 0 days 1 hrs 24 min 59 sec
Avg. qps = 447
Total Questions = 2282006
Threads Connected = 28
Warning: Server has not been running for at least 48hrs.
It may not be safe to use these recommendations
To find out more information on how each of these
runtime variables effects performance visit:
http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html
Visit http://www.mysql.com/products/enterprise/advisors.html
for info about MySQL's Enterprise Monitoring and Advisory Service
SLOW QUERIES
The slow query log is enabled.
Current long_query_time = 5 sec.
You have 202 out of 2282126 that take longer than 5 sec. to complete
Your long_query_time seems to be fine
BINARY UPDATE LOG
The binary update log is NOT enabled.
You will not be able to do point in time recovery
See http://dev.mysql.com/doc/refman/5.0/en/point-in-time-recovery.html
WORKER THREADS
Current thread_cache_size = 8
Current threads_cached = 1
Current threads_per_sec = 0
Historic threads_per_sec = 0
Your thread_cache_size is fine
MAX CONNECTIONS
Current max_connections = 100
Current threads_connected = 28
Historic max_used_connections = 30
The number of used connections is 30% of the configured maximum.
Your max_connections variable seems to be fine.
INNODB STATUS
Current InnoDB index space = 2.68 G
Current InnoDB data space = 6.14 G
Current InnoDB buffer pool free = 0 %
Current innodb_buffer_pool_size = 8 M
Depending on how much space your innodb indexes take up it may be safe
to increase this value to up to 2 / 3 of total system memory
MEMORY USAGE
Max Memory Ever Allocated : 136 M
Configured Max Per-thread Buffers : 262 M
Configured Max Global Buffers : 58 M
Configured Max Memory Limit : 320 M
Physical Memory : 1.94 G
Max memory limit seem to be within acceptable norms
KEY BUFFER
Current MyISAM index space = 961 K
Current key_buffer_size = 32 M
Key cache miss rate is 1 : 0
Key buffer free ratio = 81 %
Your key_buffer_size seems to be fine
QUERY CACHE
Query cache is enabled
Current query_cache_size = 16 M
Current query_cache_used = 7 M
Current query_cache_limit = 1 M
Current Query cache Memory fill ratio = 48.09 %
Current query_cache_min_res_unit = 4 K
MySQL won't cache query results that are larger than query_cache_limit in size
SORT OPERATIONS
Current sort_buffer_size = 2 M
Current read_rnd_buffer_size = 256 K
Sort buffer seems to be fine
JOINS
Current join_buffer_size = 132.00 K
You have had 18 queries where a join could not use an index properly
You should enable "log-queries-not-using-indexes"
Then look for non indexed joins in the slow query log.
If you are unable to optimize your queries you may want to increase your
join_buffer_size to accommodate larger joins in one pass.
Note! This script will still suggest raising the join_buffer_size when
ANY joins not using indexes are found.
OPEN FILES LIMIT
Current open_files_limit = 1024 files
The open_files_limit should typically be set to at least 2x-3x
that of table_cache if you have heavy MyISAM usage.
Your open_files_limit value seems to be fine
TABLE CACHE
Current table_cache value = 64 tables
You have a total of 344 tables
You have 64 open tables.
Current table_cache hit rate is 2%
, while 100% of your table cache is in use
You should probably increase your table_cache
TEMP TABLES
Current max_heap_table_size = 16 M
Current tmp_table_size = 32 M
Of 531360 temp tables, 33% were created on disk
Effective in-memory tmp_table_size is limited to max_heap_table_size.
Perhaps you should increase your tmp_table_size and/or max_heap_table_size
to reduce the number of disk-based temporary tables
Note! BLOB and TEXT columns are not allow in memory tables.
If you are using these columns raising these values might not impact your
ratio of on disk temp tables.
TABLE SCANS
Current read_buffer_size = 128 K
Current table scan ratio = 1 : 1
read_buffer_size seems to be fine
TABLE LOCKING
Current Lock Wait ratio = 0 : 2289756
Your table locking seems to be fineHors ligne
Bon pour résumer :
tu utilises Debian lenny
MySQL est en version 5.0.51a
tu as 2 Go de Ram sur ton serveur
Pour ce qui est du rapport MySQL Tuning Primer Script :
SLOW QUERIES => ok
BINARY UPDATE LOG => ok
WORKER THREADS => ok
MAX CONNECTIONS => ok
INNODB STATUS => pas ok
MEMORY USAGE => ok
KEY BUFFER => ok
QUERY CACHE => ok
SORT OPERATIONS => ok
JOINS => ok
OPEN FILES LIMIT => ok
TABLE CACHE => pas ok
TEMP TABLES => warning à 33% dû sûrement au problème de table_cache juste au dessus
TABLE SCANS => ok
TABLE LOCKING => ok
Pour la partie InnoDB, il dit "Current InnoDB buffer pool free = 0 %". ce que je comprends, c'est que le buffer_pool d'InnoDB, qui est de 8Mo, est plein à 100%. C'est pas cool. Pour bien comprendre, il s'agit là d'un espace en mémoire dans lequel InnoDB stocke des informations avant des les écrire sur le disque. Si il est trop petit, MySQL passe sont temps à le vider et a écrire les données et donc cela peut se traduire par une baisse de performances.
Ce paramètre ne doit pas dépasser 80% de la RAM il me semble. Dans ton cas, je tenterais un :
innodb_buffer_pool_size = 32MPour le problème de table_cache. Le rapport indique que MySQL utilise ce paramètre à 100%. D'après la conf, il ouvre régulièrement plus 64 tables en simultanées. Je tenterais donc un :
table_cache = 128C'est paramètre sont à mettre dans le my.cnf. Relance ensuite ton serveur, fait le tourner pendant quelques heures (surtout pendant une période de charge, la nuit sa compte pas) et refait un rapport pour voir si c'est mieux ou pas (tu peux le coller ici sans problème).
Hors ligne
Yes niquel ça va beaucoup mieux.
En fouinant un peu dans les entrailles du web j'ai vu que beaucoup augmentait innodb_buffer_pool_size à 75-80 % comme tu le dis plus haut du coup je l'ai passé à 1G, le serveur fais presque que de la base et avec le table_cache à 128 ça va niquel.
La charge serveur est passé de 4 à 0,7 déjà ! Je vais voir ce que ça donne sur quelques jours.
Merci en tout cas.
Hors ligne
Par contre, l'effet pervers, c'est que si il ne s'agit pas d'un serveur dédié MySQL, et bien cela peut occuper beaucoup de RAM. Je prends l'exemple d'un serveur avec Apache + Base MySQL, si tu alloue trop de ressources au MySQL, l'Apache peut se mettre à swaper par manque de RAM.
Bref, un paramètre à ajuster avec précaution.
Hors ligne
Le serveur swap déjà ..., il y a un service apache dessus mais il sert peu donc pas de souci.
Par contre je pense que j'ai toujours un souci avec le table_cache, je l'ai passé à 256 :
TABLE CACHE
Current table_cache value = 256 tables
You have a total of 344 tables
You have 256 open tables.
Current table_cache hit rate is 10%
, while 100% of your table cache is in use
You should probably increase your table_cacheJ'ai l'impression que le serveur ouvre toutes les tables en permanence ...
Le TEMP TABLES n'a pas l'air de bouger :
TEMP TABLES
Current max_heap_table_size = 16 M
Current tmp_table_size = 32 M
Of 439677 temp tables, 33% were created on disk
Effective in-memory tmp_table_size is limited to max_heap_table_size.
Perhaps you should increase your tmp_table_size and/or max_heap_table_size
to reduce the number of disk-based temporary tables
Note! BLOB and TEXT columns are not allow in memory tables.
If you are using these columns raising these values might not impact your
ratio of on disk temp tables.Dernière modification par Dudu (2010-12-09 11:44:13)
Hors ligne
Pour moi, le table_cache est le nombre de tables que MySQL met en mémoire lors d'une requête. Donc si on exécute cette instruction :
select * from table1, table2 ...Le serveur va ouvrir deux tables (= à la variables opened_tables). D'ailleurs, il est possible de connaître le nombre de tables ouvertes à un instant T avec cette instruction :
mysql> SHOW GLOBAL STATUS LIKE 'Opened_tables';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Opened_tables | 2741 |
+---------------+-------+Le truc, c'est que tout cela dépend aussi du nombre de connexions. Dans le rapport que tu as fait, il est indiqué 28 connexions simultanées au moment ou il a été lancé. Donc si les 28 utilisateurs lancent la requête ci-dessus, cela donne :
2 tables x 28 = 56 opened_tablesMySQL va donc tenter de mettre 56 tables en cache, c'est à dire en mémoire, pour aller plus vite. Si il n'y arrive pas, il utilisera le disque (donc c'est plus lent).
Voilà pour ce qui est de la théorie, on comprend donc qu'il est important de pouvoir mettre l'ensemble des table en cache. La limitation du paramètre table_cache est la mémoire physique du système. Dans ton cas, tu as 2Go de RAM, donc tu peux monter sans problème à 512, 1024 ou un peu plus si nécessaire.
Augmente par pallié, relance le script de tuning et ajuste si besoin.
Hors ligne
Ah ok, je comprend mieux.
mysql> show global status like 'Opened_tables';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Opened_tables | 5548 |
+---------------+-------+Du coup si je divise par 28 (le nombre de connexions ouverte) ça fait environ 200 tables ouverte par connexion...
Je vais augmenter au fur et à mesure et vérifié.
Mais là ça va déjà mieux qu'au début
Hors ligne
Je me suis gouré, le nombre de tables en temps réel c'est Open_tables :
mysql> show global status like 'Open_tables';Opened_tables c'est le nombre total de tables ouvetres depuis le démarrage du serveur, ça change tout.
Hors ligne
Ah oui c'est mieux, ça me donne 380 tables ouvertes.
Du coup pour résumer les 2 paramètres dans le fichier my.cnf :
table_cache = 512
innodb_buffer_pool_size=768MPour le moment cela me semble un bon compromis.
Merci
Hors ligne
Pages : 1