mercredi , 22 septembre 2021

Le processus MYSQL utilise 150% de CPU !

Si vous constatez des ralentissements en visitant votre site web, la première chose à faire est de vérifier 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 :

top
Le processus MySQL utilise plus de 100% de CPU

Pour votre information, un processus peut utiliser plus de 100% de CPU lorsque votre serveur est multi-coeur.

Dans le cas présent, on peut voir que le processus MySQL utilise 195.4% de CPU !! Il y a donc un problème avec le serveur MySQL.

Vérifiez les logs de votre serveur Apache et Mysql

Avant toute chose, vérifiez les logs de votre serveur Apache en exécutant la commande ci-dessous.

sudo tail -100 /opt/bitnami/apache2/logs/error_log

Si vous ne voyez rien de particulier, vous pouvez aussi vérifier les logs de votre serveur MySQL :

sudo tail -100 /opt/bitnami/mysql/data/mysqld.log

Installer et Exécuter MySQLTuner.

Pour débugger les problèmes de configuration MySQL, vous pouvez utiliser l’outil MySQLTuner.

Téléchargez et modifiez les permissions du fichier :

cd /home/ubuntu
wget https://raw.github.com/rackerhacker/MySQLTuner-perl/master/mysqltuner.pl
chmod 775 mysqltuner.pl

Exécutez MySQLTuner :

cd /opt/bitnami
sudo ./use_lampstack
perl /home/ubuntu/mysqltuner.pl

Vous devrez ensuite introduire le login et le mot de passe de l’administrateur du serveur (login = root).

Voici un exemple de résultat de l’outil MySQLTuner.

 >>  MySQLTuner 1.8.1 - Major Hayden <major@mhtx.net>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.pl/
 >>  Run with '--help' for additional options and output filtering

[--] Skipped version check for MySQLTuner script
Please enter your MySQL administrative login: root
Please enter your MySQL administrative password: [!!] failed to execute: SHOW REPLICA STATUS\G
[!!] FAIL Execute SQL / return code: 256
[OK] Currently running supported MySQL version 8.0.20
[OK] Operating on 64-bit architecture

-------- Log file Recommendations ------------------------------------------------------------------
[OK] Log file /opt/bitnami/mysql/data/mysqld.log exists
[--] Log file: /opt/bitnami/mysql/data/mysqld.log(36K)
[OK] Log file /opt/bitnami/mysql/data/mysqld.log is not empty
[OK] Log file /opt/bitnami/mysql/data/mysqld.log is smaller than 32 Mb
[OK] Log file /opt/bitnami/mysql/data/mysqld.log is readable.
[!!] /opt/bitnami/mysql/data/mysqld.log contains 39 warning(s).
[OK] /opt/bitnami/mysql/data/mysqld.log doesn't contain any error.
[--] 78 start(s) detected in /opt/bitnami/mysql/data/mysqld.log
[--] 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.
[--] 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
[--] 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.
[--] 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
[--] 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.
[--] 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
[--] 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.
[--] 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
[--] 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.
[--] 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
[--] 25 shutdown(s) detected in /opt/bitnami/mysql/data/mysqld.log
[--] 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.
[--] 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.
[--] 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.
[--] 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.
[--] 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.
[--] 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.
[--] 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.
[--] 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.
[--] 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.
[--] 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.

-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA
[--] Data in InnoDB tables: 239.6M (Tables: 378)
[OK] Total fragmented tables: 0

-------- Analysis Performance Metrics --------------------------------------------------------------
[--] innodb_stats_on_metadata: OFF
[OK] No stat updates during querying INFORMATION_SCHEMA.

-------- Security Recommendations ------------------------------------------------------------------
[--] Skipped due to unsupported feature for MySQL 8

-------- CVE Security Recommendations --------------------------------------------------------------
[--] Skipped due to --cvefile option undefined

-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 2h 58m 48s (68M q [6K qps], 600 conn, TX: 13G, RX: 7G)
[--] Reads / Writes: 96% / 4%
[--] Binary logging is enabled (GTID MODE: OFF)
[--] Physical Memory     : 14.3G
[--] Max MySQL memory    : 5.1G
[--] Other process memory: 0B
[--] Total buffers: 168.0M global + 33.1M per thread (151 max threads)
[--] P_S Max memory usage: 72B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 565.8M (3.86% of installed RAM)
[OK] Maximum possible memory usage: 5.1G (35.31% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (0/68M)
[OK] Highest usage of available connections: 7% (12/151)
[OK] Aborted connections: 0.17%  (1/600)
[--] Query cache have been removed in MySQL 8
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 9K sorts)
[OK] No joins without indexes
[OK] Temporary tables created on disk: 0% (0 on disk / 8K total)
[OK] Thread cache hit rate: 98% (12 created / 600 connections)
[OK] Table cache hit rate: 99% (68M hits / 68M requests)
[OK] table_definition_cache(2000) is upper than number of tables(688)
[OK] Open file limit used: 0% (2/8K)
[OK] Table locks acquired immediately: 100% (4 immediate / 4 locks)
[OK] Binlog cache memory access: 99.66% (5849 Memory / 5869 Total)

-------- Performance schema ------------------------------------------------------------------------
[--] Memory used by P_S: 72B
[--] Sys schema is installed.

-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.

-------- MyISAM Metrics ----------------------------------------------------------------------------
[--] MyISAM Metrics are disabled on last MySQL versions.

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[!!] InnoDB buffer pool / data size: 128.0M/239.6M
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (75 %): 48.0M * 2/128.0M should be equal to 25%
[OK] InnoDB buffer pool instances: 1
[--] Number of InnoDB Buffer Pool Chunk : 1 for 1 Buffer Pool Instance(s)
[OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances
[OK] InnoDB Read buffer efficiency: 100.00% (895199263 hits/ 895202764 total)
[!!] InnoDB Write Log efficiency: 13.78% (22605 hits/ 164015 total)
[OK] InnoDB log waits: 0.00% (0 waits / 141410 writes)

-------- Aria Metrics ------------------------------------------------------------------------------
[--] Aria Storage Engine not available.

-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.

-------- XtraDB Metrics ----------------------------------------------------------------------------
[--] XtraDB is disabled.

-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.

-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] Binlog format: ROW
[--] XA support enabled: ON
[--] Semi synchronous replication Master: Not Activated
[--] Semi synchronous replication Slave: Not Activated
[--] This is a standalone server

-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
    Check warning line(s) in /opt/bitnami/mysql/data/mysqld.log file
    MySQL was started within the last 24 hours - recommendations may be inaccurate
    Before changing innodb_log_file_size and/or innodb_log_files_in_group read this: https://bit.ly/2TcGgtU
Variables to adjust:
    innodb_buffer_pool_size (>= 239.6M) if possible.
    innodb_log_file_size should be (=16M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.

A la fin de ce fichier, on peut voir que MySQLTuner nous conseille d’augmenter la taille de innodb_buffer_pool_size et innodb_log_file_size.

Modifier les paramètres du serveur MySQL

Si vous êtes toujours connecté en tant que « use_lampstack », vous pouvez exécuter la commande « exit » pour revenir sur votre utilisateur ubuntu.

Exécutez la commande suivante :

sudo nano /opt/bitnami/mysql/my.cnf

Dans la section [mysqld], ajoutez ou modifiez les lignes suivantes :

innodb_buffer_pool_size=256M
innodb_log_file_size=32M

Enregistrez le fichier et n’oubliez pas ensuite de redémarrer le serveur MySQL en exécutant la commande :

sudo /opt/bitnami/ctlscript.sh restart mysql

Par acquis de conscience, vous pouvez de nouveau exécuter l’outil MySQLTuner pour vérifier que tout est ok. Vous pouvez également vérifier avec la commande « top » que la charge serveur a bien diminuée.

Troubleshooting

Pour aller plus loin : https://docs.bitnami.com/bch/faq/troubleshooting/troubleshoot-server-performance/

Ledger Wallet

Check Also

Installer Redis pour optimiser WordPress

Redis est un système Open-Source permettant de stocker en mémoire RAM de l’information sous la …

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *