process mysql 100 %

axil_76 · 2010-04-12 11:45:58

j'ai un petit problème avec mysql ! dès que je fais une recherche le process mysql utilise 100 % de la CPU !
j'ai télécharger l'utilitaire mysqltuner.pl il me dit qu'il faut ajuster les variables suivantes:
Variables to adjust:
query_cache_limit (> 2M, or use smaller result sets)
table_cache (> 256)

donc il faut mettre pour query_cache_limit plus de 2 M ??? et pour table_cache plus de 256 ????
en parallèle j'ai installé Mysql Administrator pour observer la base et on voit que le key buffer monte à 100 % lorsque je lance une recherche !
Une idée ???
Merci d'avance !

version de glpi 0.72.21
Mysql 5.0.67

axil_76 · 2010-04-12 12:55:03

j'ai augmenté le key_buffer dans la rubrique [isamchk]
[isamchk]
key_buffer = 512M

c'est mieux !

Last edited by axil_76 (2010-04-12 12:55:13)

axil_76 · 2010-04-27 14:53:05

ca recommence !
lorsque je clique sur ordinateur ca mouline ....!
j'ai installé Mysql Administrator et on observe que le key buffer est 100 % et du coup le process mysql est a 100 % !
mysql

version de glpi 0.72.4

axil_76 · 2010-04-27 14:54:52

par contre j'ai pas de problème sur la base ocs !
y pas quelqu'un qui connait un peu mysql ???

Last edited by axil_76 (2010-04-27 14:55:35)

remi · 2010-04-27 15:37:25

> donc il faut mettre pour query_cache_limit plus de 2 M ??? et pour table_cache plus de 256 ????

Ben oui.... c'est bien ce que conseille mysqltunner

+

axil_76 · 2010-04-27 16:06:23

Ca me donne ca !

>> MySQLTuner 1.1.1 - Major Hayden <major@mhtx.net>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering
Please enter your MySQL administrative login: root
Please enter your MySQL administrative password:

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.0.67-0ubuntu6-log
[OK] Operating on 32-bit architecture with less than 2GB RAM

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 5M (Tables: 616)
[--] Data in InnoDB tables: 65M (Tables: 81)
[--] Data in MEMORY tables: 0B (Tables: 9)
[..] Total fragmented tables: 6

-------- Security Recommendations -------------------------------------------
[..] User '' has no password set.
[..] User '' has no password set.

-------- Performance Metrics -------------------------------------------------
[--] Up for: 11s (1K q [94.455 qps], 346 conn, TX: 207K, RX: 65K)
[--] Reads / Writes: 100% / 0%
[--] Total buffers: 1.7G global + 197.1M per thread (500 max threads)
[..] Allocating > 2GB RAM on 32-bit systems can cause system instability
[..] Maximum possible memory usage: 98.0G (4956% of installed RAM)
[OK] Slow queries: 0% (0/1K)
[OK] Highest usage of available connections: 0% (3/500)
[OK] Key buffer size / total MyISAM indexes: 512.0M/7.3M
[..] Query cache efficiency: 0.0% (0 cached / 675 selects)
[OK] Query cache prunes per day: 0
[OK] Temporary tables created on disk: 18% (20 on disk / 111 total)
[OK] Thread cache hit rate: 99% (3 created / 346 connections)
[..] Table cache hit rate: 2% (80 open / 3K opened)
[OK] Open file limit used: 15% (162/1K)
[OK] Table locks acquired immediately: 100% (343 immediate / 343 locks)
[OK] InnoDB data size / buffer pool: 65.3M/700.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
Enable the slow query log to troubleshoot bad queries
Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
*** MySQL's maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
query_cache_limit (> 2M, or use smaller result sets)
table_cache (> 80)

remi · 2010-04-27 16:09:13

> [--] Up for: 11s (1K q [94.455 qps], 346 conn, TX: 207K, RX: 65K)
> MySQL started within last 24 hours - recommendations may be inaccurate

11s c'est totalement insuffisant pour collecter des données statistiques.

+

axil_76 · 2010-05-06 09:34:12

Ci-dessous le résultat du mysqltuner.pl après quelques jours .....

>> MySQLTuner 1.1.1 - Major Hayden <major@mhtx.net>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering
Please enter your MySQL administrative login: root
Please enter your MySQL administrative password:

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.0.67-0ubuntu6-log
[..] Switch to 64-bit OS - MySQL cannot currently use all of your RAM

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 6M (Tables: 616)
[--] Data in InnoDB tables: 66M (Tables: 81)
[--] Data in MEMORY tables: 7M (Tables: 9)
[..] Total fragmented tables: 12

-------- Security Recommendations -------------------------------------------
[..] User '' has no password set.
[..] User '' has no password set.

-------- Performance Metrics -------------------------------------------------
[--] Up for: 2d 0h 8m 56s (522K q [3.015 qps], 6K conn, TX: 1B, RX: 26M)
[--] Reads / Writes: 75% / 25%
[--] Total buffers: 1.7G global + 77.1M per thread (500 max threads)
[..] Allocating > 2GB RAM on 32-bit systems can cause system instability
[..] Maximum possible memory usage: 39.4G (1327% of installed RAM)
[OK] Slow queries: 0% (12/522K)
[OK] Highest usage of available connections: 2% (14/500)
[OK] Key buffer size / total MyISAM indexes: 512.0M/7.5M
[OK] Key buffer hit rate: 100.0% (41M cached / 16K reads)
[..] Query cache efficiency: 0.0% (0 cached / 124K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 4K sorts)
[OK] Temporary tables created on disk: 1% (2K on disk / 171K total)
[OK] Thread cache hit rate: 99% (14 created / 6K connections)
[..] Table cache hit rate: 4% (255 open / 5K opened)
[OK] Open file limit used: 42% (432/1K)
[OK] Table locks acquired immediately: 100% (175K immediate / 175K locks)
[..] Connections aborted: 45%
[OK] InnoDB data size / buffer pool: 66.4M/700.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Enable the slow query log to troubleshoot bad queries
Increase table_cache gradually to avoid file descriptor limits
Your applications are not closing MySQL connections properly
Variables to adjust:
*** MySQL's maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
query_cache_limit (> 5M, or use smaller result sets)
table_cache (> 257)

j'ai modifié plusieurs fois en augmentant les paramètres query_cache_limit et table_cache mais rien ne change j'ai toujours mysql qui monte à 100 %

c'est lorsque je lance une requête pour afficher l'ensemble des serveurs , la requête prend donc toute la Key buffer et mysql monte à 100 %

mysql

Forum GLPI-Project

Announcement

#1 2010-04-12 11:45:58

process mysql 100 %

#2 2010-04-12 12:55:03

Re: process mysql 100 %

#3 2010-04-27 14:53:05

Re: process mysql 100 %

#4 2010-04-27 14:54:52

Re: process mysql 100 %

#5 2010-04-27 15:37:25

Re: process mysql 100 %

#6 2010-04-27 16:06:23

Re: process mysql 100 %

#7 2010-04-27 16:09:13

Re: process mysql 100 %

#8 2010-05-06 09:34:12

Re: process mysql 100 %

Board footer