MYSQL 8 running in WHM cPanel high CPU usage/crashing requiring mysql reset after 2 days

Question

I have about 30 websites running Magento/Wordpress. Every 1-2 days I would need to reset mysql and then the CPU usage would be around 27% but then it'll slowly climbing up slowly over 1-2 days until it can't process any query. I've been trying to fine tune mysql with mysqltuner but I'm not getting anywhere. Any one have any idea to what's wrong with my mysql?

Server SPECS. 8CPU and 32GB of RAM. MYSQL 8 upgraded from 5. I'm thinking of increase these two settings to 20 and 20GB, will it help?

[mysqld]
skip-name-resolve
max_connections = 300
performance-schema = 1
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
symbolic-links = 0
log-error = /var/log/mysqld.log
pid-file = /var/run/mysqld/mysqld.pid
default-authentication-plugin = mysql_native_password
sql-mode=""
event_scheduler = off

#https://blog.hostseo.com/optimized-my-cnf-configuration-for-mysql-8-on-cpanel-whm-servers/
max_connect_errors = 1000000
max_execution_time = 30000

default_storage_engine = InnoDB
innodb_file_per_table = 1
innodb_flush_log_at_trx_commit = 0
innodb_flush_method = O_DIRECT
innodb_log_buffer_size = 16M
innodb_log_file_size = 2G
innodb_stats_on_metadata = 0

innodb_sort_buffer_size = 2M
join_buffer_size = 10M
read_buffer_size = 3M
read_rnd_buffer_size = 4M
sort_buffer_size = 4M
innodb_buffer_pool_instances = 20 # Use 1 instance per 1GB of InnoDB pool size
innodb_buffer_pool_size = 20G # Use up to 70-80% of RAM
table_definition_cache = 10000
max_allowed_packet=268435456

[root@sydney MySQLTuner-perl]# perl mysqltuner.pl --host localhost
 >>  MySQLTuner 1.9.4
         * Jean-Marie Renouard <[email protected]>
         * Major Hayden <[email protected]>
 >>  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
[--] Performing tests on localhost:3306
[OK] Currently running supported MySQL version 8.0.28-cll-lve
[OK] Operating on 64-bit architecture

-------- Log file Recommendations ------------------------------------------------------------------
[OK] Log file /var/log/mysqld.log exists
[--] Log file: /var/log/mysqld.log(654K)
[OK] Log file /var/log/mysqld.log is not empty
[OK] Log file /var/log/mysqld.log is smaller than 32 Mb
[OK] Log file /var/log/mysqld.log is readable.
[!!] /var/log/mysqld.log contains 3999 warning(s).
[!!] /var/log/mysqld.log contains 1 error(s).
[--] 28 start(s) detected in /var/log/mysqld.log
[--] 1) 2022-03-11T11:33:30.385618Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.28-cll-lve'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server - GPL.
[--] 2) 2022-03-11T11:33:30.385552Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /var/run/mysqld/mysqlx.sock
[--] 3) 2022-03-11T11:25:14.529114Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.28-cll-lve'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server - GPL.
[--] 4) 2022-03-11T11:25:14.529040Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /var/run/mysqld/mysqlx.sock
[--] 5) 2022-03-11T11:19:18.190344Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.28-cll-lve'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server - GPL.
[--] 6) 2022-03-11T11:19:18.190255Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /var/run/mysqld/mysqlx.sock
[--] 7) 2022-03-11T11:18:13.822038Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.28-cll-lve'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server - GPL.
[--] 8) 2022-03-11T11:18:13.821993Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /var/run/mysqld/mysqlx.sock
[--] 9) 2022-03-11T11:08:18.751619Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.28-cll-lve'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server - GPL.
[--] 10) 2022-03-11T11:08:18.751563Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /var/run/mysqld/mysqlx.sock
[--] 13 shutdown(s) detected in /var/log/mysqld.log
[--] 1) 2022-03-11T11:33:24.769107Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.28-cll-lve)MySQL Community Server - GPL.
[--] 2) 2022-03-11T11:25:07.265191Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.28-cll-lve)MySQL Community Server - GPL.
[--] 3) 2022-03-11T11:19:11.826431Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.28-cll-lve)MySQL Community Server - GPL.
[--] 4) 2022-03-11T11:18:07.247423Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.28-cll-lve)MySQL Community Server - GPL.
[--] 5) 2022-03-11T11:08:15.163952Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.28-cll-lve)MySQL Community Server - GPL.
[--] 6) 2022-03-11T11:04:38.012145Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.28-cll-lve)MySQL Community Server - GPL.
[--] 7) 2022-03-11T05:25:06.991803Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.28-cll-lve)MySQL Community Server - GPL.
[--] 8) 2022-03-11T04:42:44.397640Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.28-cll-lve)MySQL Community Server - GPL.
[--] 9) 2022-03-10T11:28:04.502497Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.28-cll-lve)MySQL Community Server - GPL.
[--] 10) 2022-03-10T11:22:48.305672Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.28-cll-lve) MySQL Community Server - GPL.

-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA
[--] Data in MyISAM tables: 7.3G (Tables: 2270)
[--] Data in InnoDB tables: 2.9G (Tables: 3824)
[--] Data in MEMORY tables: 0B (Tables: 47)
[OK] Total fragmented tables: 0

-------- Analysis Performance Metrics --------------------------------------------------------------
[--] innodb_stats_on_metadata: OFF
[OK] No stat updates during querying INFORMATION_SCHEMA.

-------- Views Metrics -----------------------------------------------------------------------------

-------- Triggers Metrics --------------------------------------------------------------------------

-------- Routines Metrics --------------------------------------------------------------------------

-------- Security Recommendations ------------------------------------------------------------------
[--] Skipped due to unsupported feature for MySQL 8

-------- CVE Security Recommendations --------------------------------------------------------------
[OK] NO SECURITY CVE FOUND FOR YOUR VERSION

-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 18h 7m 56s (30M q [462.304 qps], 95K conn, TX: 93G, RX: 5G)
[--] Reads / Writes: 95% / 5%
[--] Binary logging is enabled (GTID MODE: OFF)
[--] Physical Memory     : 30.5G
[--] Max MySQL memory    : 101.5G
[--] Other process memory: 0B
[--] Total buffers: 20.0G global + 278.0M per thread (300 max threads)
[--] P_S Max memory usage: 72B
[--] Galera GCache Max memory usage: 0B
[!!] Maximum reached memory usage: 29.3G (95.89% of installed RAM)
[!!] Maximum possible memory usage: 101.5G (332.49% of installed RAM)
[!!] Overall possible memory usage with other process exceeded memory
[OK] Slow queries: 0% (0/30M)
[OK] Highest usage of available connections: 11% (34/300)
[OK] Aborted connections: 0.02%  (20/95581)
[--] Query cache have been removed in MySQL 8
[OK] Sorts requiring temporary tables: 0% (14 temp sorts / 9M sorts)
[!!] Joins performed without indexes: 73107
[OK] Temporary tables created on disk: 0% (161 on disk / 1M total)
[OK] Thread cache hit rate: 98% (1K created / 95K connections)
[OK] Table cache hit rate: 99% (36M hits / 36M requests)
[OK] table_definition_cache(10000) is upper than number of tables(6471)
[OK] Open file limit used: 4% (1K/40K)
[OK] Table locks acquired immediately: 99% (15M immediate / 15M locks)
[OK] Binlog cache memory access: 99.97% (123404 Memory / 123446 Total)

-------- Performance schema ------------------------------------------------------------------------
[--] Performance_schema is activated.
[--] 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
[OK] InnoDB buffer pool / data size: 20.0G/2.9G
[OK] Ratio InnoDB log file size / InnoDB Buffer pool size: 2.0G * 2/20.0G should be equal to 25%
[OK] InnoDB buffer pool instances: 20
[--] Number of InnoDB Buffer Pool Chunk : 160 for 20 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% (1267999296 hits/ 1268033436 total)
[!!] InnoDB Write Log efficiency: 79.63% (1431596 hits/ 1797854 total)
[OK] InnoDB log waits: 0.00% (0 waits / 366258 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 /var/log/mysqld.log file
    Check error line(s) in /var/log/mysqld.log file
    MySQL was started within the last 24 hours - recommendations may be inaccurate
    Reduce your overall MySQL memory footprint for system stability
    Dedicate this server to your database for highest performance.
    We will suggest raising the 'join_buffer_size' until JOINs not using indexes are found.
             See https://dev.mysql.com/doc/internals/en/join-buffer-size.html
             (specially the conclusions at the bottom of the page).
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    join_buffer_size (> 10.0M, or always use indexes with JOINs)

Answer 1

I didn't fix the problem but refreshing the server and then upgrade to mariaDB helps. These are the steps:

1. Refresh the server, example reinstall linux
2. Reinstall WHM/cPanel
3. Upgrade to MariaDB before migrating websites over

Answer 2

InnoDB buffer pool / data size: 7.0G/2.9G
InnoDB Read buffer efficiency: 99.99%

Those says that the buffer_pool is probably not full. Raising to 20G will neither help nor hurt. Well, if the data grows a lot, raising may be important.

These are excessive; for starters, cut them down by 10x.

max_allowed_packet = 550M
table_open_cache = 40000
innodb_open_files = 40000
table_definition_cache = 40000
open_files_limit = 60000

For deeper dive into the settings: Analysis

For analyzing the "slow" queries: SlowLog -- this is likely to lead to an improvement.

Answer 3

Your server is running out of physical RAM after being up for a while and thrashing. See this:

[OK] Maximum reached memory usage: 23.1G (75.58% of installed RAM)
[!!] Maximum possible memory usage: 117.6G (385.24% of installed RAM)
[!!] Overall possible memory usage with other process exceeded memory

What can you do about this? Allocate less memory in your MySQL server.

Some opportunities:

• You have ludicrously high open file and table cache limits, exceeding your OS limits. You only have 6,471 tables.

  [OK] table_definition_cache(40000) is upper than number of tables(6471)
  [OK] Open file limit used: 15% (6K/40K)
  
  [MY-010140] [Server] Could not increase number of max_open_files to more than 40000 (request: 80210)
  [MY-010142] [Server] Changed limits: table_open_cache: 19895 (requested 40000)
  
  

  Remove these config lines and take the defaults, which are good.

  table_open_cache = 40000
  innodb_open_files = 40000
  table_definition_cache = 40000
  open_files_limit = 60000
  

• You reported a high-water-mark of 29/200 active connections. Try reducing max_connections.

  [OK] Highest usage of available connections: 14% (29/200)
  

• Do your tables contain large BLOBs or CLOBs (LONGBLOB, LONGTEXT)? You can reduce max_allowed_packet (presently about 0.5GiB) to match the length of your largest LOB.

• Is your server machine dedicated to the database server software? If not, don't forget to leave room for other processes, like php. Reduce innodb_buffer_pool_size in this case.

From what I see, your server's workload doesn't need a bigger machine. But, you should investigate the unindexed JOIN operations in the queries coming from your applications. Maybe some indexes will help.

[!!] Joins performed without indexes: 40853