-1

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.

4
  • cPanel is off-topic for Server Fault.
    – tater
    Nov 24, 2020 at 22:54
  • @tater Apologies. Will remove the question. Nov 25, 2020 at 13:13
  • @tater, bit harsh, the crash and effects weren't as a result of cPanel.
    – danblack
    Nov 25, 2020 at 20:25
  • @CodingSomething MariaDB 10.3.27 became Stable GA on Nov 20, 2020. Unless you have time to discover, report 'bugs' and wait on a fix - in the future avoid the bleeding edge by only installing a version more than 90 days old to conserve your time and sanity. Also review the changelogs following the version you 'think' you want to install to see what was discovered after the version went GA, before you take the leap to update. Nov 28, 2020 at 11:13

1 Answer 1

2

MyISAM has no crash safety. Stop using it for your friends (and enemies) tables.

Remove the setting to revert the default to innodb and use some of the 16G by setting innodb_buffer_pool_size to some reasonable portion of this. Change existing tables with ALTER TABLE {tablename} ENGINE=Innodb and gain some crash safety.

I assume you've let mysqlcheck --repair finish.

On a shared hosting server, its possible your table_open_cache is exhausted, however recommend looking at SHOW GLOBAL STATUS information to identify potential tuning points. With the right information dba stackexchange user can help with tuning and general MariaDB performance.

Not the answer you're looking for? Browse other questions tagged .