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
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_logSi 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.logInstaller 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.plExécutez MySQLTuner :
cd /opt/bitnami
sudo ./use_lampstack
perl /home/ubuntu/mysqltuner.plVous 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.cnfDans la section [mysqld], ajoutez ou modifiez les lignes suivantes :
innodb_buffer_pool_size=256M
innodb_log_file_size=32MEnregistrez le fichier et n’oubliez pas ensuite de redémarrer le serveur MySQL en exécutant la commande :
sudo /opt/bitnami/ctlscript.sh restart mysqlPar 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/
