0

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

Improve this question
6
  • 1
    Does this answer your question? MySQL not releasing memory
    – mustaccio
    Commented Jan 19, 2022 at 13:10
  • 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..
    – Wilson Hauck
    Commented Jan 21, 2022 at 1:44
  • 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
    – ashkan
    Commented Jan 23, 2022 at 8:30
  • 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
    Commented Jan 23, 2022 at 18:48
  • Would you post a new TOP report, 1st page to compare since changing the 4 Global Variables, please.
    – Wilson Hauck
    Commented Jan 23, 2022 at 19:02

2 Answers 2

Reset to default
0

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.

Improve this answer
3
  • 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
    – ashkan
    Commented Jan 19, 2022 at 7:50
  • Then we need to see some slow queries. More: mysql.rjweb.org/doc.php/mysql_analysis#slow_queries_and_slowlog
    – Rick James
    Commented Jan 19, 2022 at 7:52
  • Would you please give me more details, I'm not that pro in this kinda cases
    – ashkan
    Commented Jan 19, 2022 at 8:02
0

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.

Improve this answer
2
  • 1
    Hi, well actually didnt change that much ... stilll using 30GB or memory but i guess the cpu load is little bit decreased
    – ashkan
    Commented Jan 23, 2022 at 8:36
  • 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.
    – Wilson Hauck
    Commented Jan 23, 2022 at 18:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.