Background
On a heavy loaded WordPress server you might experience the following when looking at iotop. Heavy writes. Additional graphing of the disk’s writes might show constant up to 2M writes taking place.
Fortunatley iotop has the o option to only see active and then you’ll see something like this:
TID PRIO USER DISK READ DISK WRITE SWAPIN IO> COMMAND 338 be/3 root 0.00 B/s 67.31 K/s 0.00 % 68.55 % [jbd2/dm-0-8] 307904 be/4 scott@he 57.70 K/s 0.00 B/s 0.00 % 36.60 % spamd child 5425 be/4 ergopak 105.77 K/s 0.00 B/s 0.00 % 8.76 % php-fpm: pool 15317604343943 81025 be/4 mysql 0.00 B/s 0.00 B/s 0.00 % 4.45 % mysqld [ib_io_wr-3] 81029 be/4 mysql 0.00 B/s 153.85 K/s 0.00 % 0.47 % mysqld [ib_pg_flush-2] 81030 be/4 mysql 0.00 B/s 153.85 K/s 0.00 % 0.45 % mysqld [ib_pg_flush-3] 81033 be/4 mysql 0.00 B/s 0.00 B/s 0.00 % 0.22 % mysqld [ib_log_flush] 81028 be/4 mysql 0.00 B/s 76.93 K/s 0.00 % 0.22 % mysqld [ib_pg_flush-1] 81027 be/4 mysql 0.00 B/s 76.93 K/s 0.00 % 0.21 % mysqld [ib_pg_flush_co] 81024 be/4 mysql 0.00 B/s 0.00 B/s 0.00 % 0.15 % mysqld [ib_io_wr-2] 81023 be/4 mysql 0.00 B/s 0.00 B/s 0.00 % 0.14 % mysqld [ib_io_wr-1] 418 be/3 root 0.00 B/s 153.85 K/s 0.00 % 0.00 % systemd-journald 69577 be/4 www-data 0.00 B/s 9.62 K/s 0.00 % 0.00 % apache2 -k start 81035 be/4 mysql 0.00 B/s 19.23 K/s 0.00 % 0.00 % mysqld [ib_log_writer]
The problem is, what do you do next? Here is one route that could help.
- Start a terminal and connect to the server at MySQL root.
- Start another terminal
- On the first terminal, enter MySQL and do this:
mysql> SET GLOBAL general_log = 'ON'; Query OK, 0 rows affected (0.14 sec) mysql> SET GLOBAL general_log_file = '/var/log/mysql/general.log'; Query OK, 0 rows affected (0.03 sec)
BEWARE: You are working on a busy server. Turning on the above may quickly overwhelm your busy server with even more logging and more business. That’s why we have two terminals, and that’s why you’re only run this for a very short while and then turn it off.
4. In the second terminal, do this:
tail -f /var/log/mysql/general.log | egrep -i "connect|init"
5. Filtering on the massive amounts of information the SQL general log will give you is super hard, but the above two commands will show some database connection information. As a bonus you might even see application information. From here, you can quickly stop the logging:
mysql> SET GLOBAL general_log = 'OFF';
Now use your magnifying glass to see what application is going so crazy.
In many cases, these could be normal workloads, such as WordFence.
In other cases, like hacked websites, you might want to disable the website.
What about excessive logging?
With MySQL 8.x you have another problem. Excessive logging. Here is an example:
[email protected]:/root]> service mysql status ● mysql.service - MySQL Community Server Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled) Active: active (running) since Wed 2024-06-26 04:56:05 SAST; 6min ago Process: 80982 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exited, status=0/SUCCESS) Main PID: 81003 (mysqld) Status: "Server is operational" Tasks: 47 (limit: 28733) Memory: 839.6M CGroup: /system.slice/mysql.service └─81003 /usr/sbin/mysqld Jun 26 05:02:12 host.example.com mysqld[81003]: 2024-06-26T03:02:12.942825Z 213 [Warning] [MY-013360] [Server] Plugin mysql_native_password reported: ''mysql_native_password' is deprecated and will be removed in a future release. Please use caching_sha2_password instead' Jun 26 05:02:15 host.example.com mysqld[81003]: 2024-06-26T03:02:15.148186Z 214 [Warning] [MY-013360] [Server] Plugin mysql_native_password reported: ''mysql_native_password' is deprecated and will be removed in a future release. Please use caching_sha2_password instead' Jun 26 05:02:15 host.example.com mysqld[81003]: 2024-06-26T03:02:15.464124Z 215 [Warning] [MY-013360] [Server] Plugin mysql_native_password reported: ''mysql_native_password' is deprecated and will be removed in a future release. Please use caching_sha2_password instead' Jun 26 05:02:16 host.example.com mysqld[81003]: 2024-06-26T03:02:16.073485Z 216 [Warning] [MY-013360] [Server] Plugin mysql_native_password reported: ''mysql_native_password' is deprecated and will be removed in a future release. Please use caching_sha2_password instead' Jun 26 05:02:17 host.example.com mysqld[81003]: 2024-06-26T03:02:17.280689Z 217 [Warning] [MY-013360] [Server] Plugin mysql_native_password reported: ''mysql_native_password' is deprecated and will be removed in a future release. Please use caching_sha2_password instead' Jun 26 05:02:18 host.example.com mysqld[81003]: 2024-06-26T03:02:18.367809Z 218 [Warning] [MY-013360] [Server] Plugin mysql_native_password reported: ''mysql_native_password' is deprecated and will be removed in a future release. Please use caching_sha2_password instead' Jun 26 05:02:19 host.example.com mysqld[81003]: 2024-06-26T03:02:19.156726Z 219 [Warning] [MY-013360] [Server] Plugin mysql_native_password reported: ''mysql_native_password' is deprecated and will be removed in a future release. Please use caching_sha2_password instead' Jun 26 05:02:25 host.example.com mysqld[81003]: 2024-06-26T03:02:25.361705Z 220 [Warning] [MY-013360] [Server] Plugin mysql_native_password reported: ''mysql_native_password' is deprecated and will be removed in a future release. Please use caching_sha2_password instead' Jun 26 05:02:26 host.example.com mysqld[81003]: 2024-06-26T03:02:26.336884Z 221 [Warning] [MY-013360] [Server] Plugin mysql_native_password reported: ''mysql_native_password' is deprecated and will be removed in a future release. Please use caching_sha2_password instead' Jun 26 05:02:26 host.example.com mysqld[81003]: 2024-06-26T03:02:26.453044Z 222 [Warning] [MY-013360] [Server] Plugin mysql_native_password reported: ''mysql_native_password' is deprecated and will be removed in a future release. Please use caching_sha2_password instead' [[email protected]:/root]>
All these events are also taking up writes. If you don’t see the log, check the journal.
As of 26 June 2024, we don’t have a good solution for turning these warnings off on a heavy server with many MySQL database usernames and passwords.
See here for more information:
https://stackoverflow.com/questions/36301100/how-do-i-turn-off-the-mysql-password-validation