Centos 7 , WHM High Mysql&MariaDB CPU Usage %600

Question

I have a problem. My web site very slowly I think it's mysql. Max user connection instant 100 users. 30-40 people in most cases.

My server :

• Intel Xeon E3-1230v3

• 16 GB DDR3 Ram

• 240 GB OCZ SSD Disk

• Centos 7 WHM-cPanel MariaDB

...

PID USER      PR  NI    VIRT    RES    SHR S  %CPU  %MEM     TIME+ COMMAND
12496 mysql   20   0   14.1g   1.3g  12228 S 576.1  8.2 116:38.62 

/usr/sbin/mysqld

/etc/my.cnf :

port        = 1905
socket      = /var/lib/mysql/mysql.sock

# The MySQL Safe server
[mysqld_safe]
open_files_limit = 58000
# The MySQL server
[mysqld]
max_connections = 300
local-infile    = 0
socket      = /var/lib/mysql/mysql.sock
skip-external-locking
key_buffer_size = 1024M
table_open_cache = 4096
open_files_limit = 40960
max_allowed_packet=1073741824
sort_buffer_size = 4M
read_buffer_size = 4M
join_buffer_size = 4M
sort_buffer_size = 4M
read_rnd_buffer_size = 128M
myisam_sort_buffer_size = 128M
thread_cache_size = 16
#query_cache_size= 32M
tmp_table_size = 64M
max_heap_table_size = 64M

#table_cache    = 1024

#Slowly log
slow-query-log = 1
slow-query-log-file = /var/lib/mysql/mysql-slow.log
long_query_time = 1

# Uncomment the following if you are using InnoDB tables
innodb_file_per_table= 1
#innodb_data_home_dir = /var/lib/mysql
#innodb_data_file_path = ibdata1:50M:autoextend
#innodb_log_group_home_dir = /var/lib/mysql
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 8096M
#innodb_additional_mem_pool_size = 64M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 64M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50

# Connection Settings
wait_timeout = 60
skip-external-locking



[mysqldump]
quick
max_allowed_packet = 64M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[myisamchk]
key_buffer_size = 512M
sort_buffer_size = 256M
read_buffer = 8M
write_buffer = 8M
# Query Cache Settings
query_cache_type = 1
query_cache_limit = 256K
query_cache_min_res_unit = 2k
query_cache_size = 80M
skip-external-locking

[mysqlhotcopy]
interactive-timeout

How Can I do this problem?

Thanks.

Answer 1

Rate Per Second=RPS Suggestions to consider for your my.cnf [mysqld] section for performance improvement,

read_rnd_buffer_size=256K  # from 128M to reduce handler_read_rnd_next RPS from 6 million +
innodb_io_capacity=1900  # from 200 to enable more of your SSD io capacity
thread_cache_size=100  # from 16 to reduce threads_created count of 24,136 in 8 hrs
aria_pagecache_buffer_size=64M  # from 128M to conserve RAM, only 2 blocks used in 8 hrs
key_buffer_size=128M  # from 1G to conserve RAM, 15M used was the High Water Mark

Aras and Furkan, If you still do not have your indexes created, suggested by Rick James, please post a comment indicating you need help with this request to improve performance by index management.

Answer 2

Login to the WHM with root user , From Multi PHP manager , switch the website to PHP 7.1 enable php-fpm for that particular website.

Secondly , download the mysql tuner script which will help you to tweak /etc/my.cnf settings for the best mysql performance.

if the things are not working even after all these changes then you will have to optimize your mysql database.

Answer 3

Bottom line: Adding a few indexes will help a lot:

INDEX(islemkey)
INDEX(site, durum, islem_baslangic)
INDEX(durum, site, site_durum, id)

Details

UPDATE odemeler SET step='odeme_kontrol' WHERE islemkey = '78TNgyacgjwt'

You need INDEX(islemkey) on odemeler.

`expire` varchar(255) DEFAULT NULL,
AND islem_baslangic LIKE '2018-11-29%'

If expire is a DATE or DATETIME, then it should be declared such. If it is in some non-standard date format, then it needs converting. Ditto for islem_baslangic and any other columns that contain dates.

SELECT * FROM odemeler WHERE site = '71' AND durum = '1'

odemeler needs this composite INDEX(site, durum); optionally they could be listed in the opposite order.

SELECT id,tcno, miktar,durum,islem_baslangic,site,site_durum
    FROM odemeler
    WHERE site = '74' AND durum = '1' AND site_durum = '0'
    ORDER BY id DESC LIMIT 20

Needs

INDEX(site, durum, site_durum,  -- in any order
      id)   -- last

Reason

Without a usable index, most of the queries showing in the PROCESSLIST must scan the entire table. This takes a lot of CPU. And it slows down each query. And then the queries stack up. (That was a lot of queries to have in the PROCESSLIST at once.)

With usable indexes, each query will come and go much faster and use much less CPU. That's the purpose of indexes!