{"id":24975,"date":"2021-09-22T12:00:31","date_gmt":"2021-09-22T10:00:31","guid":{"rendered":"https:\/\/exoconsult.com\/?p=24975"},"modified":"2021-09-22T15:23:32","modified_gmt":"2021-09-22T13:23:32","slug":"le-processus-mysql-utilise-150-de-cpu","status":"publish","type":"post","link":"https:\/\/exoconsult.com\/fr\/serveur\/le-processus-mysql-utilise-150-de-cpu\/","title":{"rendered":"Le processus MYSQL utilise 150% de CPU !"},"content":{"rendered":"\n

Si vous constatez des ralentissements en visitant votre site web, la premi\u00e8re chose \u00e0 faire est de v\u00e9rifier le nombre et l’utilisation CPU de chaque processus qui tourne sur votre serveur. <\/p>\n\n\n\n

Pour voir les processus, il faut se connecter sur le serveur en SSH et entrer la commande suivante : <\/p>\n\n\n\n

top<\/code><\/pre>\n\n\n\n
\"\"<\/a>
Le processus MySQL utilise plus de 100% de CPU<\/figcaption><\/figure>\n\n\n\n
Pour votre information, un processus peut utiliser plus de 100% de CPU lorsque votre serveur est multi-coeur. <\/p>\n\n\n\n
Dans le cas pr\u00e9sent, on peut voir que le processus MySQL utilise 195.4% de CPU !! Il y a donc un probl\u00e8me avec le serveur MySQL.<\/p>\n\n\n\n
V\u00e9rifiez les logs de votre serveur Apache et Mysql<\/h2>\n\n\n\n
Avant toute chose, v\u00e9rifiez les logs de votre serveur Apache en ex\u00e9cutant la commande ci-dessous. <\/p>\n\n\n\n
sudo tail -100 \/opt\/bitnami\/apache2\/logs\/error_log<\/code><\/pre>\n\n\n\n
Si vous ne voyez rien de particulier, vous pouvez aussi v\u00e9rifier les logs de votre serveur MySQL :<\/p>\n\n\n\n
sudo tail -100 \/opt\/bitnami\/mysql\/data\/mysqld.log<\/code><\/pre>\n\n\n\n
Installer et Ex\u00e9cuter MySQLTuner.<\/h2>\n\n\n\n
Pour d\u00e9bugger les probl\u00e8mes de configuration MySQL, vous pouvez utiliser l’outil MySQLTuner. <\/p>\n\n\n\n
T\u00e9l\u00e9chargez et modifiez les permissions du fichier :<\/p>\n\n\n\n
cd \/home\/ubuntu\nwget https:\/\/raw.github.com\/rackerhacker\/MySQLTuner-perl\/master\/mysqltuner.pl\nchmod 775 mysqltuner.pl<\/code><\/pre>\n\n\n\n
Ex\u00e9cutez MySQLTuner :<\/p>\n\n\n\n
cd \/opt\/bitnami\nsudo .\/use_lampstack\nperl \/home\/ubuntu\/mysqltuner.pl<\/code><\/pre>\n\n\n\n
Vous devrez ensuite introduire le login et le mot de passe de l’administrateur du serveur (login = root).<\/p>\n\n\n\n
Voici un exemple de r\u00e9sultat de l’outil MySQLTuner.<\/p>\n\n\n\n
 >>  MySQLTuner 1.8.1 - Major Hayden <major@mhtx.net>\n >>  Bug reports, feature requests, and downloads at http:\/\/mysqltuner.pl\/\n >>  Run with '--help' for additional options and output filtering\n\n[--] Skipped version check for MySQLTuner script\nPlease enter your MySQL administrative login: root\nPlease enter your MySQL administrative password: [!!] failed to execute: SHOW REPLICA STATUS\\G\n[!!] FAIL Execute SQL \/ return code: 256\n[OK] Currently running supported MySQL version 8.0.20\n[OK] Operating on 64-bit architecture\n\n-------- Log file Recommendations ------------------------------------------------------------------\n[OK] Log file \/opt\/bitnami\/mysql\/data\/mysqld.log exists\n[--] Log file: \/opt\/bitnami\/mysql\/data\/mysqld.log(36K)\n[OK] Log file \/opt\/bitnami\/mysql\/data\/mysqld.log is not empty\n[OK] Log file \/opt\/bitnami\/mysql\/data\/mysqld.log is smaller than 32 Mb\n[OK] Log file \/opt\/bitnami\/mysql\/data\/mysqld.log is readable.\n[!!] \/opt\/bitnami\/mysql\/data\/mysqld.log contains 39 warning(s).\n[OK] \/opt\/bitnami\/mysql\/data\/mysqld.log doesn't contain any error.\n[--] 78 start(s) detected in \/opt\/bitnami\/mysql\/data\/mysqld.log\n[--] 1) 2021-09-22T06:22:03.911797Z 0 [System] [MY-010931] [Server] \/opt\/bitnami\/mysql\/bin\/mysqld.bin: ready for connections. Version: '8.0.20'  socket: '\/opt\/bitnami\/mysql\/tmp\/mysql.sock'  port: 3306  MySQL Community Server - GPL.\n[--] 2) 2021-09-22T06:22:02.054677Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: '\/tmp\/mysqlx.sock' bind-address: '::' port: 33060\n[--] 3) 2021-09-21T13:15:16.103948Z 0 [System] [MY-010931] [Server] \/opt\/bitnami\/mysql\/bin\/mysqld.bin: ready for connections. Version: '8.0.20'  socket: '\/opt\/bitnami\/mysql\/tmp\/mysql.sock'  port: 3306  MySQL Community Server - GPL.\n[--] 4) 2021-09-21T13:15:15.832644Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: '\/tmp\/mysqlx.sock' bind-address: '::' port: 33060\n[--] 5) 2021-09-21T10:58:10.004938Z 0 [System] [MY-010931] [Server] \/opt\/bitnami\/mysql\/bin\/mysqld.bin: ready for connections. Version: '8.0.20'  socket: '\/opt\/bitnami\/mysql\/tmp\/mysql.sock'  port: 3306  MySQL Community Server - GPL.\n[--] 6) 2021-09-21T10:58:09.649705Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: '\/tmp\/mysqlx.sock' bind-address: '::' port: 33060\n[--] 7) 2021-09-21T09:39:55.741147Z 0 [System] [MY-010931] [Server] \/opt\/bitnami\/mysql\/bin\/mysqld.bin: ready for connections. Version: '8.0.20'  socket: '\/opt\/bitnami\/mysql\/tmp\/mysql.sock'  port: 3306  MySQL Community Server - GPL.\n[--] 8) 2021-09-21T09:39:55.608634Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: '\/tmp\/mysqlx.sock' bind-address: '::' port: 33060\n[--] 9) 2021-09-21T09:14:02.850020Z 0 [System] [MY-010931] [Server] \/opt\/bitnami\/mysql\/bin\/mysqld.bin: ready for connections. Version: '8.0.20'  socket: '\/opt\/bitnami\/mysql\/tmp\/mysql.sock'  port: 3306  MySQL Community Server - GPL.\n[--] 10) 2021-09-21T09:14:02.543948Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: '\/tmp\/mysqlx.sock' bind-address: '::' port: 33060\n[--] 25 shutdown(s) detected in \/opt\/bitnami\/mysql\/data\/mysqld.log\n[--] 1) 2021-09-21T13:15:10.183510Z 0 [System] [MY-010910] [Server] \/opt\/bitnami\/mysql\/bin\/mysqld.bin: Shutdown complete (mysqld 8.0.20)  MySQL Community Server - GPL.\n[--] 2) 2021-09-21T10:57:46.927608Z 0 [System] [MY-010910] [Server] \/opt\/bitnami\/mysql\/bin\/mysqld.bin: Shutdown complete (mysqld 8.0.20)  MySQL Community Server - GPL.\n[--] 3) 2021-09-21T09:39:49.988335Z 0 [System] [MY-010910] [Server] \/opt\/bitnami\/mysql\/bin\/mysqld.bin: Shutdown complete (mysqld 8.0.20)  MySQL Community Server - GPL.\n[--] 4) 2021-08-23T15:25:52.339721Z 0 [System] [MY-010910] [Server] \/opt\/bitnami\/mysql\/bin\/mysqld.bin: Shutdown complete (mysqld 8.0.20)  MySQL Community Server - GPL.\n[--] 5) 2021-07-01T17:07:25.592884Z 0 [System] [MY-010910] [Server] \/opt\/bitnami\/mysql\/bin\/mysqld.bin: Shutdown complete (mysqld 8.0.20)  MySQL Community Server - GPL.\n[--] 6) 2021-06-14T11:10:30.670885Z 0 [System] [MY-010910] [Server] \/opt\/bitnami\/mysql\/bin\/mysqld.bin: Shutdown complete (mysqld 8.0.20)  MySQL Community Server - GPL.\n[--] 7) 2021-06-14T11:09:23.648129Z 0 [System] [MY-010910] [Server] \/opt\/bitnami\/mysql\/bin\/mysqld.bin: Shutdown complete (mysqld 8.0.20)  MySQL Community Server - GPL.\n[--] 8) 2021-05-17T08:21:26.821282Z 0 [System] [MY-010910] [Server] \/opt\/bitnami\/mysql\/bin\/mysqld.bin: Shutdown complete (mysqld 8.0.20)  MySQL Community Server - GPL.\n[--] 9) 2021-04-22T09:05:22.879450Z 0 [System] [MY-010910] [Server] \/opt\/bitnami\/mysql\/bin\/mysqld.bin: Shutdown complete (mysqld 8.0.20)  MySQL Community Server - GPL.\n[--] 10) 2020-12-01T16:17:41.508768Z 0 [System] [MY-010910] [Server] \/opt\/bitnami\/mysql\/bin\/mysqld.bin: Shutdown complete (mysqld 8.0.20)  MySQL Community Server - GPL.\n\n-------- Storage Engine Statistics -----------------------------------------------------------------\n[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA\n[--] Data in InnoDB tables: 239.6M (Tables: 378)\n[OK] Total fragmented tables: 0\n\n-------- Analysis Performance Metrics --------------------------------------------------------------\n[--] innodb_stats_on_metadata: OFF\n[OK] No stat updates during querying INFORMATION_SCHEMA.\n\n-------- Security Recommendations ------------------------------------------------------------------\n[--] Skipped due to unsupported feature for MySQL 8\n\n-------- CVE Security Recommendations --------------------------------------------------------------\n[--] Skipped due to --cvefile option undefined\n\n-------- Performance Metrics -----------------------------------------------------------------------\n[--] Up for: 2h 58m 48s (68M q [6K qps], 600 conn, TX: 13G, RX: 7G)\n[--] Reads \/ Writes: 96% \/ 4%\n[--] Binary logging is enabled (GTID MODE: OFF)\n[--] Physical Memory     : 14.3G\n[--] Max MySQL memory    : 5.1G\n[--] Other process memory: 0B\n[--] Total buffers: 168.0M global + 33.1M per thread (151 max threads)\n[--] P_S Max memory usage: 72B\n[--] Galera GCache Max memory usage: 0B\n[OK] Maximum reached memory usage: 565.8M (3.86% of installed RAM)\n[OK] Maximum possible memory usage: 5.1G (35.31% of installed RAM)\n[OK] Overall possible memory usage with other process is compatible with memory available\n[OK] Slow queries: 0% (0\/68M)\n[OK] Highest usage of available connections: 7% (12\/151)\n[OK] Aborted connections: 0.17%  (1\/600)\n[--] Query cache have been removed in MySQL 8\n[OK] Sorts requiring temporary tables: 0% (0 temp sorts \/ 9K sorts)\n[OK] No joins without indexes\n[OK] Temporary tables created on disk: 0% (0 on disk \/ 8K total)\n[OK] Thread cache hit rate: 98% (12 created \/ 600 connections)\n[OK] Table cache hit rate: 99% (68M hits \/ 68M requests)\n[OK] table_definition_cache(2000) is upper than number of tables(688)\n[OK] Open file limit used: 0% (2\/8K)\n[OK] Table locks acquired immediately: 100% (4 immediate \/ 4 locks)\n[OK] Binlog cache memory access: 99.66% (5849 Memory \/ 5869 Total)\n\n-------- Performance schema ------------------------------------------------------------------------\n[--] Memory used by P_S: 72B\n[--] Sys schema is installed.\n\n-------- ThreadPool Metrics ------------------------------------------------------------------------\n[--] ThreadPool stat is disabled.\n\n-------- MyISAM Metrics ----------------------------------------------------------------------------\n[--] MyISAM Metrics are disabled on last MySQL versions.\n\n-------- InnoDB Metrics ----------------------------------------------------------------------------\n[--] InnoDB is enabled.\n[--] InnoDB Thread Concurrency: 0\n[OK] InnoDB File per table is activated\n[!!] InnoDB buffer pool \/ data size: 128.0M\/239.6M\n[!!] Ratio InnoDB log file size \/ InnoDB Buffer pool size (75 %): 48.0M * 2\/128.0M should be equal to 25%\n[OK] InnoDB buffer pool instances: 1\n[--] Number of InnoDB Buffer Pool Chunk : 1 for 1 Buffer Pool Instance(s)\n[OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances\n[OK] InnoDB Read buffer efficiency: 100.00% (895199263 hits\/ 895202764 total)\n[!!] InnoDB Write Log efficiency: 13.78% (22605 hits\/ 164015 total)\n[OK] InnoDB log waits: 0.00% (0 waits \/ 141410 writes)\n\n-------- Aria Metrics ------------------------------------------------------------------------------\n[--] Aria Storage Engine not available.\n\n-------- TokuDB Metrics ----------------------------------------------------------------------------\n[--] TokuDB is disabled.\n\n-------- XtraDB Metrics ----------------------------------------------------------------------------\n[--] XtraDB is disabled.\n\n-------- Galera Metrics ----------------------------------------------------------------------------\n[--] Galera is disabled.\n\n-------- Replication Metrics -----------------------------------------------------------------------\n[--] Galera Synchronous replication: NO\n[--] No replication slave(s) for this server.\n[--] Binlog format: ROW\n[--] XA support enabled: ON\n[--] Semi synchronous replication Master: Not Activated\n[--] Semi synchronous replication Slave: Not Activated\n[--] This is a standalone server\n\n-------- Recommendations ---------------------------------------------------------------------------\nGeneral recommendations:\n    Check warning line(s) in \/opt\/bitnami\/mysql\/data\/mysqld.log file\n    MySQL was started within the last 24 hours - recommendations may be inaccurate\n    Before changing innodb_log_file_size and\/or innodb_log_files_in_group read this: https:\/\/bit.ly\/2TcGgtU\nVariables to adjust:\n    innodb_buffer_pool_size (>= 239.6M) if possible.\n    innodb_log_file_size should be (=16M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.<\/code><\/pre>\n\n\n\n
A la fin de ce fichier, on peut voir que MySQLTuner nous conseille d’augmenter la taille de innodb_buffer_pool_size <\/strong>et innodb_log_file_size<\/strong>.<\/p>\n\n\n\n
Modifier les param\u00e8tres du serveur MySQL<\/h2>\n\n\n\n
Si vous \u00eates toujours connect\u00e9 en tant que \u00ab\u00a0use_lampstack\u00a0\u00bb, vous pouvez ex\u00e9cuter la commande \u00ab\u00a0exit\u00a0\u00bb pour revenir sur votre utilisateur ubuntu.<\/p>\n\n\n\n
Ex\u00e9cutez la commande suivante :<\/p>\n\n\n\n
sudo nano \/opt\/bitnami\/mysql\/my.cnf<\/code><\/pre>\n\n\n\n
Dans la section [mysqld]<\/strong>, ajoutez ou modifiez les lignes suivantes :<\/p>\n\n\n\n
innodb_buffer_pool_size=256M\ninnodb_log_file_size=32M<\/code><\/pre>\n\n\n\n
Enregistrez le fichier et n’oubliez pas ensuite de red\u00e9marrer le serveur MySQL en ex\u00e9cutant la commande : <\/p>\n\n\n\n
sudo \/opt\/bitnami\/ctlscript.sh restart mysql<\/code><\/pre>\n\n\n\n
Par acquis de conscience, vous pouvez de nouveau ex\u00e9cuter l’outil MySQLTuner pour v\u00e9rifier que tout est ok. Vous pouvez \u00e9galement v\u00e9rifier avec la commande \u00ab\u00a0top\u00a0\u00bb que la charge serveur a bien diminu\u00e9e.<\/p>\n\n\n\n
Troubleshooting<\/h2>\n\n\n\n
Pour aller plus loin : https:\/\/docs.bitnami.com\/bch\/faq\/troubleshooting\/troubleshoot-server-performance\/<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
Si vous constatez des ralentissements en visitant votre site web, la premi\u00e8re chose \u00e0 faire est de v\u00e9rifier le nombre et l’utilisation CPU de chaque processus qui tourne sur votre serveur. Pour voir les processus, il faut se connecter sur le serveur en SSH et entrer la commande suivante : Pour votre information, un processus […]<\/p>\n","protected":false},"author":1,"featured_media":24976,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"page_builder":"","wds_primary_category":0,"footnotes":""},"categories":[15,21,14],"tags":[45,43,42,44],"class_list":["post-24975","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-apache","category-bitnami","category-serveur","tag-apache","tag-cpu","tag-mysql","tag-performance"],"_links":{"self":[{"href":"https:\/\/exoconsult.com\/fr\/wp-json\/wp\/v2\/posts\/24975","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/exoconsult.com\/fr\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/exoconsult.com\/fr\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/exoconsult.com\/fr\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/exoconsult.com\/fr\/wp-json\/wp\/v2\/comments?post=24975"}],"version-history":[{"count":3,"href":"https:\/\/exoconsult.com\/fr\/wp-json\/wp\/v2\/posts\/24975\/revisions"}],"predecessor-version":[{"id":24981,"href":"https:\/\/exoconsult.com\/fr\/wp-json\/wp\/v2\/posts\/24975\/revisions\/24981"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/exoconsult.com\/fr\/wp-json\/wp\/v2\/media\/24976"}],"wp:attachment":[{"href":"https:\/\/exoconsult.com\/fr\/wp-json\/wp\/v2\/media?parent=24975"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/exoconsult.com\/fr\/wp-json\/wp\/v2\/categories?post=24975"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/exoconsult.com\/fr\/wp-json\/wp\/v2\/tags?post=24975"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}