Mysql Server high CPU load and high memory usage

Question

Greeting everyone, I have a Cpanel VPS server with below details : 8Core CPU | 32GB RAM | 450GB SSDNVME Cpanel V100.0.5 on CentOS v7.9.2009 Mysql V8.0 Litespeed server Web Host Professional (2-Worker) License (with LSCache Standard) I'm using this server as shared hosting and have some websites with high traffic. I have tried many ways and read a lot of articles for having the best performance but unfortunately the server is always having a high average load and MYSQL server cpu load is over 100% and uses 30GB of ram!!!!! this is my.cnf file :

[mysqld]
user                    = mysql
pid_file                = /var/run/mysqld/mysqld.pid
socket                  = /var/run/mysqld/mysqld.sock
port                    = 3306
basedir                 = /usr
datadir                 = /var/lib/mysql
tmpdir                  = /tmp
lc_messages_dir         = /usr/share/mysql
skip_external_locking
bind_address            = 127.0.0.1
max_connections         = 100
max_user_connections    = 80
wait_timeout            = 300
interactive_timeout     = 300
disable-log-bin=1
default-authentication-plugin=mysql_native_password
performance-schema=0
key-buffer-size=2G
join_buffer_size = 32M
sort_buffer_size = 16M
read_buffer_size = 16M
read_rnd_buffer_size = 128M
thread_stack        = 8M
thread_cache_size       = 8
myisam_sort_buffer_size = 512M
thread_stack            = 5M
thread_cache_size       = 32
max_binlog_size         = 100M
max_heap_table_size = 2048M
tmp_table_size      = 2048M
table_open_cache    = 3072
table_definition_cache  = 8200
skip-name-resolve   =1
performance_schema  = ON
concurrent_insert   = ALWAYS
low_priority_updates    = 1
default-storage-engine=InnoDB
innodb_buffer_pool_size=24G
innodb_buffer_pool_instances = 24
innodb_file_per_table       = 1
innodb_log_file_size            = 3072M
innodb_log_files_in_group       = 2
innodb_log_buffer_size      = 128M
innodb_log_file_size = 64M
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
max_allowed_packet=268435456
open_files_limit=40000
default-storage-engine=innodb
event_scheduler = OFF
mysqlx=0
slow-query-log=1
slow-query-log-file= /var/lib/mysql/mysql-slow-query.log
long-query-time=1

This is what top shows :

top - 18:07:10 up  4:51,  1 user,  load average: 23.36, 21.19, 15.64
Tasks: 266 total,  15 running, 248 sleeping,   3 stopped,   0 zombie
%Cpu(s): 30.1 us, 21.3 sy, 47.7 ni,  0.0 id,  0.0 wa,  0.0 hi,  0.8 si,  0.0 st
KiB Mem : 32780000 total,  5730248 free, 10833944 used, 16215808 buff/cache
KiB Swap: 15626236 total, 15626236 free,        0 used. 20516092 avail Mem
PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND
22844 mysql     20   0   32.6g   4.5g  18028 S 269.9 14.4 252:39.28 mysqld
 6908 liliome   30  10 1036500 101008  46684 R  32.8  0.3   0:29.10 lsphp
 7536 liliome   30  10 1038428 101556  45348 R  26.2  0.3   0:23.27 lsphp
 8911 parfuma   30  10 1141864 125180  39784 S  26.2  0.4   0:01.63 lsphp
 7556 liliome   30  10 1036164  99384  45528 S  24.2  0.3   0:23.72 lsphp
 8850 parfuma   30  10 1048088 105052  39616 R  24.2  0.3   0:02.70 lsphp
 6523 liliome   30  10 1038872 103252  46596 R  23.8  0.3   0:33.37 lsphp
 8948 parfuma   30  10 1068108 124128  38948 S  23.5  0.4   0:01.45 lsphp
 8937 liliome   30  10 1033132  92788  41868 R  21.5  0.3   0:01.23 lsphp
    1 root      20   0   50352  10916   4192 R  21.2  0.0  11:31.33 systemd
 7278 liliome   30  10 1038464 100944  45908 R  21.2  0.3   0:28.64 lsphp
 7434 liliome   30  10 1036336  99804  45672 R  20.9  0.3   0:24.50 lsphp
 8522 liliome   30  10 1040252 103176  45544 R  16.9  0.3   0:07.71 lsphp
 8708 parfuma   30  10 1058200 115912  39700 S  15.6  0.4   0:04.85 lsphp
 8936 parfuma   30  10 1070156 124580  39068 S  15.6  0.4   0:01.40 lsphp
 8484 liliome   30  10 1038604 101160  45756 R  14.2  0.3   0:07.59 lsphp
 9008 negroupi  30  10 1008676  62644  37076 S  12.3  0.2   0:00.37 lsphp

this is process manaer report please let me know what else do you guys need to help me optimize the server for best performance:)

With thanks in advance

Answer 1

Lower these to no more than 1% of RAM:

max_heap_table_size = 2048M  --> 200M
tmp_table_size      = 2048M  --> 200M

That will help prevent swapping, which could be terrible for performance.

Then let's discuss your slowest query; be sure to include EXPLAIN and SHOW CREATE TABLE.

Answer 2

Rate Per Second = RPS

Consider disabling these 4 values in your config by starting the lines with (pound sign) # and space character.

join_buffer_size = 32M
sort_buffer_size = 16M
read_buffer_size = 16M
read_rnd_buffer_size = 128M

You will likely find Handler_read_next and Handler_read_rnd_next RPS will be significantly reduced. It is rare the defaults for these 4 global variables need to be increased.