I have a small cPanel server which I use for my clients' projects and my own personal projects. This is also shared among a few of my friends who chime in to keep the disks spinning. Recently I've noticed a huge increase in CPU usage for mysql and few of my friends reported that their DB's were crashed and fixing it from the cPanel repair tool helped.
However, this kept happening and I'm trying to find a solution for this. My server has 16GB of RAM with RAID 1 Disks. The processor is an old one called W3520.
When I restart mysql using systemctl restart mysql
following log appears.
2020-11-24 19:04:12 0 [Note] InnoDB: Using Linux native AIO
2020-11-24 19:04:12 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2020-11-24 19:04:12 0 [Note] InnoDB: Uses event mutexes
2020-11-24 19:04:12 0 [Note] InnoDB: Compressed tables use zlib 1.2.7
2020-11-24 19:04:12 0 [Note] InnoDB: Number of pools: 1
2020-11-24 19:04:12 0 [Note] InnoDB: Using SSE2 crc32 instructions
2020-11-24 19:04:12 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2020-11-24 19:04:12 0 [Note] InnoDB: Completed initialization of buffer pool
2020-11-24 19:04:12 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2020-11-24 19:04:13 0 [Note] InnoDB: 128 out of 128 rollback segments are active.
2020-11-24 19:04:13 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2020-11-24 19:04:13 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2020-11-24 19:04:13 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2020-11-24 19:04:13 0 [Note] InnoDB: Waiting for purge to start
2020-11-24 19:04:13 0 [Note] InnoDB: 10.3.27 started; log sequence number 43130164885; transaction id 86603248
2020-11-24 19:04:13 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
2020-11-24 19:04:13 0 [Note] Plugin 'FEEDBACK' is disabled.
2020-11-24 19:04:13 0 [Note] Server socket created on IP: '::'.
2020-11-24 19:04:13 0 [Note] InnoDB: Buffer pool(s) load completed at 201124 19:04:13
2020-11-24 19:04:13 0 [Note] Reading of all Master_info entries succeeded
2020-11-24 19:04:13 0 [Note] Added new Master_info '' to hash table
2020-11-24 19:04:13 0 [Note] /usr/sbin/mysqld: ready for connections.
Version: '10.3.27-MariaDB' socket: '/var/lib/mysql/mysql.sock' port: 3306 MariaDB Server
2020-11-24 19:05:38 52 [ERROR] Got error 127 when reading table './[database]/[table]'
2020-11-24 19:05:38 52 [ERROR] mysqld: Table '[table]' is marked as crashed and should be repaired
2020-11-24 19:05:38 52 [ERROR] mysqld: Table '[table]' is marked as crashed and should be repaired
This also shows a few other tables to be repaired. Which I have now repaired using PHPMyAdmin interface of WHM. Then reloaded mysql using systemctl restart mysql
However, the problem still seems to linger around as my CPU usage from mysql is ±100%
I also tried repairing it with mysql_upgrade -u root --force -p
but didn't yield any result. I also ran mysqlcheck --repair --all-databases
to try to fix the crashed databases but it didn't yield any result either.
Following is my my.cnf file content.
#
# This group is read both both by the client and the server
# use it for options that affect everything
#
[client-server]
#
# include all files from the config directory
#
!includedir /etc/my.cnf.d
[mysqld]
log-error=/var/lib/mysql/domain.com.err
innodb_file_per_table=1
default-storage-engine=MyISAM
max_allowed_packet=268435456
open_files_limit=10000
performance_schema = off
sql_mode="NO_AUTO_CREATE_USER"
I'm running following versions,
MariaDB 10.3.27
cPanel/WHM v90.0.17
Linux domain.com 3.10.0-1160.6.1.el7.x86_64 #1 SMP Tue Nov 17 13:59:11 UTC 2020 x86_64 x86_64 x86_64 GNU/Linux
I just found the following errors in the log too.
https://paste.ubuntu.com/p/ZH66kBd23y/
Thank you.