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
