MariaDB mysql high cpu usage

Kuro · Jul 4, 2019

Hi, today suddenly my website is loading slowly. I found out the cause is Mysqld
Of course now I can't access to my website except via SSH.

Check the log of today 20190704 up to 3GB and I deleted it.
Is it possible to upgrade MariaDB from 10.3 to 10.4? (I upgraded from 10.3.16 to 10.4.6 few days ago)

now, my site

Help me please >__<

CentOS Version: CentOS 7 64bit

Centmin Mod Version Installed: 123.09beta01

Nginx Version Installed: 1.17.1

PHP Version Installed: 7.3.6

MariaDB MySQL Version Installed: 10.4.6 (upgraded from 10.3.16 few days ago)

Kuro · Jul 4, 2019

Code:

[08:14][root@emi log]# journalctl -u mariadb --no-pager
-- Logs begin at Sat 2019-06-08 02:50:46 UTC, end at Thu 2019-07-04 08:14:50 UTC. --
Jun 22 16:08:05 emi systemd[1]: Stopping MariaDB 10.3.16 database server...
Jun 22 16:08:06 emi systemd[1]: Stopped MariaDB 10.3.16 database server.
Jun 22 16:08:06 emi systemd[1]: Starting MariaDB 10.3.16 database server...
Jun 22 16:08:07 emi mysqld[742]: 2019-06-22 16:08:07 0 [Note] /usr/sbin/mysqld (mysqld 10.3.16-MariaDB) starting as process 742 ...
Jun 22 16:08:07 emi mysqld[742]: 2019-06-22 16:08:07 0 [Note] InnoDB: Using Linux native AIO
Jun 22 16:08:07 emi mysqld[742]: 2019-06-22 16:08:07 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
Jun 22 16:08:07 emi mysqld[742]: 2019-06-22 16:08:07 0 [Note] InnoDB: Uses event mutexes
Jun 22 16:08:07 emi mysqld[742]: 2019-06-22 16:08:07 0 [Note] InnoDB: Compressed tables use zlib 1.2.7
Jun 22 16:08:07 emi mysqld[742]: 2019-06-22 16:08:07 0 [Note] InnoDB: Number of pools: 1
Jun 22 16:08:07 emi mysqld[742]: 2019-06-22 16:08:07 0 [Note] InnoDB: Using SSE2 crc32 instructions
Jun 22 16:08:07 emi mysqld[742]: 2019-06-22 16:08:07 0 [Note] InnoDB: Initializing buffer pool, total size = 512M, instances = 1, chunk size = 128M
Jun 22 16:08:07 emi mysqld[742]: 2019-06-22 16:08:07 0 [Note] InnoDB: Completed initialization of buffer pool
Jun 22 16:08:07 emi mysqld[742]: 2019-06-22 16:08:07 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
Jun 22 16:08:07 emi mysqld[742]: 2019-06-22 16:08:07 0 [Note] InnoDB: 128 out of 128 rollback segments are active.
Jun 22 16:08:07 emi mysqld[742]: 2019-06-22 16:08:07 0 [Note] InnoDB: Creating shared tablespace for temporary tables
Jun 22 16:08:07 emi mysqld[742]: 2019-06-22 16:08:07 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
Jun 22 16:08:07 emi mysqld[742]: 2019-06-22 16:08:07 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
Jun 22 16:08:07 emi mysqld[742]: 2019-06-22 16:08:07 0 [Note] InnoDB: Waiting for purge to start
Jun 22 16:08:08 emi mysqld[742]: 2019-06-22 16:08:08 0 [Note] InnoDB: 10.3.16 started; log sequence number 574450672468; transaction id 476118811
Jun 22 16:08:08 emi mysqld[742]: 2019-06-22 16:08:08 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
Jun 22 16:08:08 emi mysqld[742]: 2019-06-22 16:08:08 0 [Note] Plugin 'FEEDBACK' is disabled.
Jun 22 16:08:08 emi mysqld[742]: 2019-06-22 16:08:08 0 [Note] Server socket created on IP: '::'.
Jun 22 16:08:08 emi mysqld[742]: 2019-06-22 16:08:08 0 [Note] /usr/sbin/mysqld: ready for connections.
Jun 22 16:08:08 emi mysqld[742]: Version: '10.3.16-MariaDB'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MariaDB Server
Jun 22 16:08:08 emi systemd[1]: Started MariaDB 10.3.16 database server.
Jun 22 16:08:18 emi mysqld[742]: 2019-06-22 16:08:18 0 [Note] InnoDB: Buffer pool(s) load completed at 190622 16:08:18
Jun 28 03:28:02 emi systemd[1]: Stopping MariaDB 10.3.16 database server...
Jun 28 03:28:02 emi mysqld[742]: 2019-06-28  3:28:02 0 [Note] /usr/sbin/mysqld (initiated by: unknown): Normal shutdown
Jun 28 03:28:02 emi mysqld[742]: 2019-06-28  3:28:02 0 [Note] Event Scheduler: Purging the queue. 0 events
Jun 28 03:28:02 emi mysqld[742]: 2019-06-28  3:28:02 0 [Note] InnoDB: FTS optimize thread exiting.
Jun 28 03:28:02 emi mysqld[742]: 2019-06-28  3:28:02 0 [Note] InnoDB: Starting shutdown...
Jun 28 03:28:04 emi mysqld[742]: 2019-06-28  3:28:04 0 [Note] InnoDB: Shutdown completed; log sequence number 789697208756; transaction id 656152885
Jun 28 03:28:04 emi mysqld[742]: 2019-06-28  3:28:04 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
Jun 28 03:28:04 emi mysqld[742]: 2019-06-28  3:28:04 0 [Note] /usr/sbin/mysqld: Shutdown complete
Jun 28 03:28:04 emi systemd[1]: Stopped MariaDB 10.3.16 database server.
Jun 28 03:32:04 emi systemd[1]: Starting MariaDB 10.4.6 database server...
Jun 28 03:32:05 emi mysqld[1002]: 2019-06-28  3:32:05 0 [Note] /usr/sbin/mysqld (mysqld 10.4.6-MariaDB) starting as process 1002 ...
Jun 28 03:32:05 emi mysqld[1002]: 2019-06-28  3:32:05 0 [Note] InnoDB: Using Linux native AIO
Jun 28 03:32:05 emi mysqld[1002]: 2019-06-28  3:32:05 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
Jun 28 03:32:05 emi mysqld[1002]: 2019-06-28  3:32:05 0 [Note] InnoDB: Uses event mutexes
Jun 28 03:32:05 emi mysqld[1002]: 2019-06-28  3:32:05 0 [Note] InnoDB: Compressed tables use zlib 1.2.7
Jun 28 03:32:05 emi mysqld[1002]: 2019-06-28  3:32:05 0 [Note] InnoDB: Number of pools: 1
Jun 28 03:32:05 emi mysqld[1002]: 2019-06-28  3:32:05 0 [Note] InnoDB: Using SSE2 crc32 instructions
Jun 28 03:32:05 emi mysqld[1002]: 2019-06-28  3:32:05 0 [Note] InnoDB: Initializing buffer pool, total size = 512M, instances = 1, chunk size = 128M
Jun 28 03:32:05 emi mysqld[1002]: 2019-06-28  3:32:05 0 [Note] InnoDB: Completed initialization of buffer pool
Jun 28 03:32:05 emi mysqld[1002]: 2019-06-28  3:32:05 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
Jun 28 03:32:05 emi mysqld[1002]: 2019-06-28  3:32:05 0 [Note] InnoDB: Setting log file ./ib_logfile101 size to 268435456 bytes
Jun 28 03:32:05 emi mysqld[1002]: 2019-06-28  3:32:05 0 [Note] InnoDB: Setting log file ./ib_logfile1 size to 268435456 bytes
Jun 28 03:32:05 emi mysqld[1002]: 2019-06-28  3:32:05 0 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
Jun 28 03:32:05 emi mysqld[1002]: 2019-06-28  3:32:05 0 [Note] InnoDB: New log files created, LSN=789697208756
Jun 28 03:32:05 emi mysqld[1002]: 2019-06-28  3:32:05 0 [Note] InnoDB: 128 out of 128 rollback segments are active.
Jun 28 03:32:05 emi mysqld[1002]: 2019-06-28  3:32:05 0 [Note] InnoDB: Creating shared tablespace for temporary tables
Jun 28 03:32:05 emi mysqld[1002]: 2019-06-28  3:32:05 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
Jun 28 03:32:05 emi mysqld[1002]: 2019-06-28  3:32:05 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
Jun 28 03:32:05 emi mysqld[1002]: 2019-06-28  3:32:05 0 [Note] InnoDB: Waiting for purge to start
Jun 28 03:32:05 emi mysqld[1002]: 2019-06-28  3:32:05 0 [Note] InnoDB: 10.4.6 started; log sequence number 789697208844; transaction id 656152887
Jun 28 03:32:05 emi mysqld[1002]: 2019-06-28  3:32:05 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
Jun 28 03:32:05 emi mysqld[1002]: 2019-06-28  3:32:05 0 [Note] Plugin 'FEEDBACK' is disabled.
Jun 28 03:32:05 emi mysqld[1002]: 2019-06-28  3:32:05 0 [Note] Server socket created on IP: '::'.
Jun 28 03:32:05 emi mysqld[1002]: 2019-06-28  3:32:05 0 [Note] /usr/sbin/mysqld: ready for connections.
Jun 28 03:32:05 emi mysqld[1002]: Version: '10.4.6-MariaDB'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MariaDB Server
Jun 28 03:32:05 emi systemd[1]: Started MariaDB 10.4.6 database server.
Jun 28 03:32:14 emi mysqld[1002]: 2019-06-28  3:32:14 0 [Note] InnoDB: Buffer pool(s) load completed at 190628  3:32:14
Jul 04 06:29:45 emi systemd[1]: Stopping MariaDB 10.4.6 database server...
Jul 04 06:29:45 emi mysqld[1002]: 2019-07-04  6:29:45 0 [Note] /usr/sbin/mysqld (initiated by: unknown): Normal shutdown
Jul 04 06:29:45 emi mysqld[1002]: 2019-07-04  6:29:45 0 [Note] Event Scheduler: Purging the queue. 0 events
Jul 04 06:29:45 emi mysqld[1002]: 2019-07-04  6:29:45 0 [Note] InnoDB: FTS optimize thread exiting.
Jul 04 06:29:45 emi mysqld[1002]: 2019-07-04  6:29:45 0 [Note] InnoDB: Starting shutdown...
Jul 04 06:29:45 emi mysqld[1002]: 2019-07-04  6:29:45 0 [Note] InnoDB: Dumping buffer pool(s) to /var/lib/mysql/ib_buffer_pool
Jul 04 06:29:45 emi mysqld[1002]: 2019-07-04  6:29:45 0 [Note] InnoDB: Instance 0, restricted to 8012 pages due to innodb_buf_pool_dump_pct=25
Jul 04 06:29:45 emi mysqld[1002]: 2019-07-04  6:29:45 0 [Note] InnoDB: Buffer pool(s) dump completed at 190704  6:29:45
Jul 04 06:29:47 emi mysqld[1002]: 2019-07-04  6:29:47 0 [Note] InnoDB: Shutdown completed; log sequence number 1035475536094; transaction id 860583621
Jul 04 06:29:47 emi mysqld[1002]: 2019-07-04  6:29:47 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
Jul 04 06:29:47 emi mysqld[1002]: 2019-07-04  6:29:47 0 [Note] /usr/sbin/mysqld: Shutdown complete
Jul 04 06:29:47 emi systemd[1]: Stopped MariaDB 10.4.6 database server.
Jul 04 06:29:47 emi systemd[1]: Starting MariaDB 10.4.6 database server...
Jul 04 06:29:48 emi mysqld[12313]: 2019-07-04  6:29:48 0 [Note] /usr/sbin/mysqld (mysqld 10.4.6-MariaDB) starting as process 12313 ...
Jul 04 06:29:48 emi mysqld[12313]: 2019-07-04  6:29:48 0 [Note] InnoDB: Using Linux native AIO
Jul 04 06:29:48 emi mysqld[12313]: 2019-07-04  6:29:48 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
Jul 04 06:29:48 emi mysqld[12313]: 2019-07-04  6:29:48 0 [Note] InnoDB: Uses event mutexes
Jul 04 06:29:48 emi mysqld[12313]: 2019-07-04  6:29:48 0 [Note] InnoDB: Compressed tables use zlib 1.2.7
Jul 04 06:29:48 emi mysqld[12313]: 2019-07-04  6:29:48 0 [Note] InnoDB: Number of pools: 1
Jul 04 06:29:48 emi mysqld[12313]: 2019-07-04  6:29:48 0 [Note] InnoDB: Using SSE2 crc32 instructions
Jul 04 06:29:48 emi mysqld[12313]: 2019-07-04  6:29:48 0 [Note] InnoDB: Initializing buffer pool, total size = 512M, instances = 1, chunk size = 128M
Jul 04 06:29:48 emi mysqld[12313]: 2019-07-04  6:29:48 0 [Note] InnoDB: Completed initialization of buffer pool
Jul 04 06:29:48 emi mysqld[12313]: 2019-07-04  6:29:48 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
Jul 04 06:29:48 emi mysqld[12313]: 2019-07-04  6:29:48 0 [Note] InnoDB: 128 out of 128 rollback segments are active.
Jul 04 06:29:48 emi mysqld[12313]: 2019-07-04  6:29:48 0 [Note] InnoDB: Creating shared tablespace for temporary tables
Jul 04 06:29:48 emi mysqld[12313]: 2019-07-04  6:29:48 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
Jul 04 06:29:48 emi mysqld[12313]: 2019-07-04  6:29:48 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
Jul 04 06:29:48 emi mysqld[12313]: 2019-07-04  6:29:48 0 [Note] InnoDB: Waiting for purge to start
Jul 04 06:29:48 emi mysqld[12313]: 2019-07-04  6:29:48 0 [Note] InnoDB: 10.4.6 started; log sequence number 1035475536094; transaction id 860583620
Jul 04 06:29:48 emi mysqld[12313]: 2019-07-04  6:29:48 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
Jul 04 06:29:48 emi mysqld[12313]: 2019-07-04  6:29:48 0 [Note] Plugin 'FEEDBACK' is disabled.
Jul 04 06:29:48 emi mysqld[12313]: 2019-07-04  6:29:48 0 [Note] Server socket created on IP: '::'.
Jul 04 06:29:48 emi mysqld[12313]: 2019-07-04  6:29:48 0 [Note] /usr/sbin/mysqld: ready for connections.
Jul 04 06:29:48 emi mysqld[12313]: Version: '10.4.6-MariaDB'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MariaDB Server
Jul 04 06:29:48 emi systemd[1]: Started MariaDB 10.4.6 database server.
Jul 04 06:29:56 emi mysqld[12313]: 2019-07-04  6:29:56 0 [Note] InnoDB: Buffer pool(s) load completed at 190704  6:29:56
Jul 04 07:37:52 emi systemd[1]: Stopping MariaDB 10.4.6 database server...
Jul 04 07:37:52 emi mysqld[12313]: 2019-07-04  7:37:52 0 [Note] /usr/sbin/mysqld (initiated by: unknown): Normal shutdown
Jul 04 07:37:52 emi mysqld[12313]: 2019-07-04  7:37:52 0 [Note] Event Scheduler: Purging the queue. 0 events
Jul 04 07:37:52 emi mysqld[12313]: 2019-07-04  7:37:52 0 [Note] InnoDB: FTS optimize thread exiting.
Jul 04 07:37:52 emi mysqld[12313]: 2019-07-04  7:37:52 0 [Note] InnoDB: Starting shutdown...
Jul 04 07:37:52 emi mysqld[12313]: 2019-07-04  7:37:52 0 [Note] InnoDB: Dumping buffer pool(s) to /var/lib/mysql/ib_buffer_pool
Jul 04 07:37:52 emi mysqld[12313]: 2019-07-04  7:37:52 0 [Note] InnoDB: Instance 0, restricted to 8012 pages due to innodb_buf_pool_dump_pct=25
Jul 04 07:37:52 emi mysqld[12313]: 2019-07-04  7:37:52 0 [Note] InnoDB: Buffer pool(s) dump completed at 190704  7:37:52
Jul 04 07:37:54 emi mysqld[12313]: 2019-07-04  7:37:54 0 [Note] InnoDB: Shutdown completed; log sequence number 1036972021512; transaction id 861305572
Jul 04 07:37:54 emi mysqld[12313]: 2019-07-04  7:37:54 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
Jul 04 07:37:54 emi mysqld[12313]: 2019-07-04  7:37:54 0 [Note] /usr/sbin/mysqld: Shutdown complete
Jul 04 07:37:55 emi systemd[1]: Stopped MariaDB 10.4.6 database server.
Jul 04 07:37:55 emi systemd[1]: Starting MariaDB 10.4.6 database server...
Jul 04 07:38:15 emi mysqld[19236]: 2019-07-04  7:38:15 0 [Note] /usr/sbin/mysqld (mysqld 10.4.6-MariaDB) starting as process 19236 ...
Jul 04 07:38:15 emi mysqld[19236]: 2019-07-04  7:38:15 0 [Note] InnoDB: Using Linux native AIO
Jul 04 07:38:15 emi mysqld[19236]: 2019-07-04  7:38:15 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
Jul 04 07:38:15 emi mysqld[19236]: 2019-07-04  7:38:15 0 [Note] InnoDB: Uses event mutexes
Jul 04 07:38:15 emi mysqld[19236]: 2019-07-04  7:38:15 0 [Note] InnoDB: Compressed tables use zlib 1.2.7
Jul 04 07:38:15 emi mysqld[19236]: 2019-07-04  7:38:15 0 [Note] InnoDB: Number of pools: 1
Jul 04 07:38:15 emi mysqld[19236]: 2019-07-04  7:38:15 0 [Note] InnoDB: Using SSE2 crc32 instructions
Jul 04 07:38:15 emi mysqld[19236]: 2019-07-04  7:38:15 0 [Note] InnoDB: Initializing buffer pool, total size = 512M, instances = 1, chunk size = 128M
Jul 04 07:38:16 emi mysqld[19236]: 2019-07-04  7:38:16 0 [Note] InnoDB: Completed initialization of buffer pool
Jul 04 07:38:16 emi mysqld[19236]: 2019-07-04  7:38:16 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
Jul 04 07:38:17 emi mysqld[19236]: 2019-07-04  7:38:17 0 [Note] InnoDB: 128 out of 128 rollback segments are active.
Jul 04 07:38:17 emi mysqld[19236]: 2019-07-04  7:38:17 0 [Note] InnoDB: Creating shared tablespace for temporary tables
Jul 04 07:38:17 emi mysqld[19236]: 2019-07-04  7:38:17 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
Jul 04 07:38:17 emi mysqld[19236]: 2019-07-04  7:38:17 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
Jul 04 07:38:17 emi mysqld[19236]: 2019-07-04  7:38:17 0 [Note] InnoDB: Waiting for purge to start
Jul 04 07:38:17 emi mysqld[19236]: 2019-07-04  7:38:17 0 [Note] InnoDB: Waiting for purge to start
Jul 04 07:38:17 emi mysqld[19236]: 2019-07-04  7:38:17 0 [Note] InnoDB: Waiting for purge to start
Jul 04 07:38:17 emi mysqld[19236]: 2019-07-04  7:38:17 0 [Note] InnoDB: 10.4.6 started; log sequence number 1036972021512; transaction id 861305574
Jul 04 07:38:17 emi mysqld[19236]: 2019-07-04  7:38:17 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
Jul 04 07:38:17 emi mysqld[19236]: 2019-07-04  7:38:17 0 [Note] Plugin 'FEEDBACK' is disabled.
Jul 04 07:38:17 emi mysqld[19236]: 2019-07-04  7:38:17 0 [Note] Server socket created on IP: '::'.
Jul 04 07:38:17 emi mysqld[19236]: 2019-07-04  7:38:17 0 [Note] /usr/sbin/mysqld: ready for connections.
Jul 04 07:38:17 emi mysqld[19236]: Version: '10.4.6-MariaDB'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MariaDB Server
Jul 04 07:38:17 emi systemd[1]: Started MariaDB 10.4.6 database server.
Jul 04 07:38:25 emi mysqld[19236]: 2019-07-04  7:38:25 0 [Note] InnoDB: Buffer pool(s) load completed at 190704  7:38:25
[08:14][root@emi log]#

Kuro · Jul 4, 2019

(After upgrading to latest MariaDB etc, we had a issue where it used 100% CPU of the server and had to reinstall MySQL Completely, and go back to older version)
Click to expand...

MariaDB - Optimizing MySQL (MariaDB)
He was like me @@
I wonder how to go back to the old version?

eva2000 · Jul 4, 2019

As per MariaDB - MariaDB 10.1 upgrades to MariaDB 10.2, 10.3 and 10.4., major MariaDB version upgrades are one way usually especially for MariaDB 10.4. Use cminfo top command to see your cpu load stats as well as per below.

If running Centmin Mod 123.09beta01 or higher, you will have access to a cminfo top command which can also provide a summary of statistics - some of which are explained in info and linked articles below.

FYI, CPU load optimal is 1.0 per cpu thread so 8 cpu thread normal = 8x1 = 8.0. 16 cpu server normal is 16x1 = 16.0

so cpu load of 8 for

2 cpu server = 8/2 = 4.0 load average

4 cpu server = 8/4 = 2.0 load average

8 cpu server = 8/8 = 1.0 load average

When you type top command, hit number 1 to break down per cpu stats

Centmin Mod is provided as is, so troubleshooting load issues is left to end user to do. However, there's many linux tools and scripts that can help you figure out what was causing the load issues and when.

If you're providing info on this forum, more info might be helpful

What version of Centmin Mod ? .08 stable or .09 beta ? If .09 beta when was it installed and when was last time you updated ?

What's your VPS/Server hardware specifications ? cpu type ? memory available ? disk space ?

Who's your web host ? Different hosts have different limits for server resource usage and some are more restrictive that others so it could just be their resource usage policy you tripped which may or may not be restrictive. If restrictive, then real solution would be finding a better web host. If not restrictive, then it's finding out what caused high cpu load.

If running Centmin Mod 123.09beta01 or higher, you will have access to a cminfo top command which can also provide a summary of statistics - some of which are explained in info and linked articles below.

Tools and commands you will want to read up on and learn for basic system admin tasks and troubleshooting.

20 Command Line Tools to Monitor Linux Performance

13 Linux Performance Monitoring Tools - Part 2

80 Linux Monitoring Tools for SysAdmins - Server Density Blog

Amazing ! 25 Linux Performance Monitoring Tools

20 Linux System Monitoring Tools Every SysAdmin Should Know

My mysqlmymonlite.sh script can help with that mysqlmymonlite.sh Addon | Centmin Mod Community README text file has instructions for setting up a cronjob to email you reports i.e. every 10/15min or every hour.

Notes:

Centmin Mod already installed Glances tool. It can be invoked from SSH command glances or top2. Official Glances site at nicolargo.github.io/glances/ and documentation here.

For example what's output for these commands
Code (Text):
top -bn1
Code (Text):
ps aufxw
Code (Text):
sar -q
Code (Text):
sar -r
Code (Text):
sar -q -f /var/log/sa/sa$(date +%d -d yesterday)
Code (Text):
sar -r -f /var/log/sa/sa$(date +%d -d yesterday)
If you want to look at per process usage stats use pidstat run pidstat every 1 sec for 5 runs and sed is just to replace your main hostname with word hostname so mask your domain for posting on public places
Code (Text):
pidstat -durh 1 5 | sed -e "s|$(hostname)|hostname|g"
for posting code you might want to use CODE tags for code How to use forum BBCODE code tags

Kuro · Jul 8, 2019

I still have not resolved this problem. My CPU is often overloaded.
Ironically, I deleted the old database before upgrading to Mariadb 10.4.6, so when I rollback to MaraDB 10.3.16 it was still overloaded.
my cminfo top (wordpress site)
cminfo-top-080719-105515.log

eva2000 · Jul 8, 2019

load seems to happen at 9-10:30AM so what backup processes you have happening ?

it's definitely loaded but not necessary just mysql related could be php-fpm process usage so you'd need to tune php-fpm

see PHP-FPM - How to troubleshoot & optimize PHP-FPM server?

Kuro · Jul 8, 2019

I checked that MySQL was over 100 percent. Like this (124.4%)
So I think the cause is mysql

Kuro · Jul 8, 2019

here, above is only a new database less than 1 day. This will be more detailed.
cminfo-top-080719-123939.log

eva2000 · Jul 8, 2019

you have 4 cpu threads so optimal load is 4x 1.0 = 4.0 or 4x 100 = 400% so mysql is only using 124/400 = 0.31 load average or 31% of overall cpu utilisation well within reason

but overall cpu load from sar report from cminfo top shows cpu load reports >4.0 so cumulative cpu load is high which is most likely php-fpm processes pushing you over cumulative >4.0 cpu load optimal levels

eva2000 · Jul 8, 2019

cminfo top stats output example of cumulative high cpu load averages > 4x1.0 = 4.0 which is optimal threshold for 4 cpu thread server

sar filter on 5 minute interval cpu load averages greater than >4.0 reported by cminfo top command will list timestamps when 5 min cpu load intervals report >4.0

Code (Text):

------------------------------------------------------------------
Filter sar -q for times cpu load avg (5min) hit/exceeded cpu threads max

01:55:01 AM         1       276      5.12      4.02      3.57         0
02:10:01 AM         0       267      4.85      4.14      3.80         0
02:15:01 AM         0       268      3.46      4.02      3.84         0
02:20:01 AM         3       264      3.50      4.32      4.09         0
02:25:01 AM         2       263      4.78      4.22      4.11         0
02:45:01 AM         0       263      4.51      4.31      4.03         0
02:50:02 AM         7       267      5.91      4.73      4.25         0
02:55:01 AM         2       272      4.12      4.55      4.31         0
03:00:01 AM         5       265      3.89      4.22      4.26         0
03:15:01 AM         3       276      4.90      4.25      4.06         0
03:25:01 AM         5       268      5.04      4.68      4.35         0
04:20:01 AM         7       268      5.71      4.28      3.87         0
04:40:01 AM         2       268      4.64      4.05      3.90         0
05:05:01 AM        10       267      4.67      4.19      3.92         0
05:10:01 AM         5       271      5.46      5.00      4.37         0
05:15:01 AM         5       271      5.01      5.39      4.77         0
05:20:01 AM         3       268      5.46      5.37      4.91         0
05:25:01 AM         5       267      6.14      5.75      5.19         0
05:30:02 AM         5       271      6.63      6.27      5.55         0
05:35:01 AM         6       283      8.00      7.96      6.50         0
05:40:01 AM         6       269      8.09      7.76      6.77         0
05:45:01 AM         2       267      8.76      8.13      7.12         0
05:50:01 AM         5       271     11.30     10.27      8.35         0
05:55:01 AM        24       274      9.61     10.55      9.06         0
06:00:02 AM         9       269     12.28     11.90     10.07         0
06:05:02 AM         5       269     11.03     12.79     11.05         0
06:10:01 AM        14       271     11.66     11.99     11.13         0
06:15:01 AM        18       277     14.54     14.64     12.59         0
06:20:02 AM         8       270     15.57     15.34     13.47         0
06:25:01 AM        25       267     17.60     17.11     14.78         0
06:30:01 AM        13       272     19.09     17.75     15.66         0
06:35:02 AM         9       281     17.86     17.74     16.29         0
06:40:01 AM        18       268     14.25     16.31     16.16         0
06:45:01 AM        12       269     17.60     17.34     16.65         0
06:50:02 AM         5       272     15.75     17.08     16.80         0
06:55:01 AM        13       280     17.26     16.88     16.75         0
07:00:02 AM        17       268     18.49     17.52     17.05         0
07:05:01 AM        16       268     17.04     17.03     16.94         0
07:10:01 AM        15       271     17.71     17.51     17.16         0
07:15:02 AM        13       283     18.99     18.00     17.48         0
07:20:01 AM         3       268     15.61     17.28     17.41         0
07:25:01 AM        11       267     15.47     17.02     17.31         0
07:30:02 AM        15       272     16.76     17.00     17.26         0
07:35:01 AM        20       271     18.16     17.42     17.38         0
07:40:02 AM        15       269     17.71     17.63     17.49         0
07:45:01 AM        19       267     16.60     17.08     17.30         0
07:50:01 AM        11       271     17.24     17.08     17.20         0
07:55:01 AM         3       271     15.70     16.44     16.90         0
08:00:01 AM         9       279     15.00     15.42     16.33         0
08:05:02 AM        17       272     14.96     15.01     15.90         1
08:10:01 AM        14       274     15.37     15.04     15.66         0
08:15:01 AM        14       291     17.67     15.92     15.86         0
08:20:02 AM        10       270     14.47     15.13     15.58         0
08:25:01 AM        15       267     15.52     15.19     15.39         0
08:30:01 AM        16       272     15.28     15.47     15.47         0
08:35:02 AM        13       274     13.78     15.18     15.42         0
08:40:01 AM        11       266     17.32     15.52     15.41         0
08:45:01 AM         8       266     16.27     15.55     15.39         0
08:50:02 AM        16       269     15.85     16.20     15.73         0
08:55:01 AM         0       243      0.35      7.48     12.29         0

7:15 AM peaked at 5 min cpu load average of 18.0 which is 18/4 = 4.5 cpu load average or 450% cpu utilisation so overloaded

Code (Text):

07:15:02 AM        13       283     18.99     18.00     17.48         0

Kuro · Jul 8, 2019

Maybe I will try downgrading the PHP version (7.3.7). It coincides with the date my website has problems.

Kuro · Jul 9, 2019

The cause is PHP 7.3.7

eva2000 · Jul 9, 2019

interesting what php web apps you using ?

Log in / Register

Forums

Learn about Centmin Mod LEMP Stack today

MariaDB mysql high cpu usage

Kuro Member

Kuro Member

Kuro Member

eva2000 Administrator Staff Member

Kuro Member

eva2000 Administrator Staff Member

Kuro Member

Kuro Member

eva2000 Administrator Staff Member

eva2000 Administrator Staff Member

Kuro Member

Kuro Member

eva2000 Administrator Staff Member

.

Log in / Register

Useful Searches

Learn about Centmin Mod LEMP Stack today

MariaDB mysql high cpu usage

Kuro Member

Kuro Member

Kuro Member

eva2000 Administrator Staff Member

Kuro Member

eva2000 Administrator Staff Member

Kuro Member

Kuro Member

eva2000 Administrator Staff Member

eva2000 Administrator Staff Member

Kuro Member

Kuro Member

eva2000 Administrator Staff Member

.