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

• mustaccio: Does this answer your question? MySQL not releasing memory
• Wilson Hauck: The TOP report shows Tasks 15 running and 248 sleeping raises 2 questions to consider. Please post TEXT results from OS Command Prompt of iostat -xm 5 3 for IOPS by device and core/cpu count to see if 15 running tasks are reasonable for your core count. The 248 sleeping tasks indicates missing CLOSE within your Connect, Process, Close code to manage releasing resources when finished with a user's unit of work. Posting your code providing these 3 functions might allow someone an opportunity to suggest CLOSE placement..
• ashkan: hi, thanks for help, Device: rrqm/s wrqm/s r/s w/s rMB/s wMB/s avgrq-sz avgqu-sz await r_await w_await svctm %util fd0 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 sda 0.00 0.00 6.80 87.20 0.06 1.81 40.93 0.24 2.59 1.97 2.64 1.40 13.12 dm-0 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 this is what i have got from the iostate
• Wilson Hauck: Any chance you can post the code used to Connect, Process, Close your client connections? the IOSTAT posted is not readable. Put 4 spaces at beginning of each line and we should be able to read the results. There should be ate least 12 lines of output - for 5 second delav between each of 3 cycles.
• Wilson Hauck: Would you post a new TOP report, 1st page to compare since changing the 4 Global Variables, please.

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.

• ashkan: Hello, first of all , thanks for your help , i have made the changes but didnt really change anything :) still having the same load and memory usage
• Rick James: Then we need to see some slow queries. More: mysql.rjweb.org/doc.php/mysql_analysis#slow_queries_and_slowlog
• ashkan: Would you please give me more details, I'm not that pro in this kinda cases

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.

• ashkan: Hi, well actually didnt change that much ... stilll using 30GB or memory but i guess the cpu load is little bit decreased
• Wilson Hauck: Additional information request, please. Post on pastebin.com and share the links. From your SSH login root, Text results of: A) SELECT COUNT(*) FROM information_schema.tables; B) SHOW GLOBAL STATUS; after minimum 24 hours UPTIME C) SHOW GLOBAL VARIABLES; D) SHOW FULL PROCESSLIST; E) STATUS; not SHOW STATUS, just STATUS; G) SHOW ENGINE INNODB STATUS; for server workload tuning analysis to provide suggestions.