Mysql – /usr/sbin/thesqld taking 800% CPU and can’t find the solution i read several topics, centos 7 cpanel

mariadbMySQL

First of all i don't have very advanced experience with linux, only for what i needed for now, i am really annoyed and don't know what to do, i read about this in other several topics but it dosent help at all

mysqld taking 800% CPU since traffic increased on my site, how to fix

So the problem is this:

HIGH CPU

As you can see the my mysql process takes 800% CPU after some time, and there i don't know if it's an new attack method or something but i am using cpanel with cpnginx and i had normal site working for few months, it started to happen from a week, i didin't see anything from my error log that can prevent this happening…

The only thing i can do to put the mysql down from eating my 800% CPU is restarting the dedicated server, when i kill the process and comes back again, its still 800% CPU, sometimes 600%, jumping from 500% to 850% CPU, so all my databases are down and my 2 domains are not accessable because they need SQL to run.

There is my configuration file from /etc/my.cnf

[mysqld]
long_query_time=0.1
performance_schema=on

# ERROR LOGI
log-error=/var/lib/mysql/h2k.how2kill.pl.err

# GENERAL
wait_timeout=1000
max_connections=200
interactive_timeout=30
tmp_disk_table_size=1G
max_heap_table_size = 32M
tmp_table_size=200M
join_buffer_size=200M
# INNODB
innodb_buffer_pool_size=3GB
innodb_log_file_size=384M
innodb_buffer_pool_instances=3
innodb_lru_scan_depth=100
innodb_log_buffer_size=1G
innodb_log_file_size=4G
innodb_io_capacity=1000
innodb_change_buffer_max_size=10
innodb_file_per_table=1
# CACHE
table_open_cache=128
query_cache_type=0
query_cache_limit=128M
query_cache_min_res_unit = 2K
query_cache_size=0
thread_cache_size=100
key_buffer_size=256M
sort_buffer_size=2M

Normally my site should work like this:

NORMAL CPU

It always worked like this but after few hours it jumps up to fecking 800% and i can't do anything…

I runned mysqltuner, and do some improvments in mysql but still doesent help at all..

Please help me, i appreciate all help because i try to resolve this problem for like 40 hours.

Best Answer

ALTER TABLE `core_sys_lang_words` ADD INDEX(lang_id, word_key);

ALTER TABLE `bimchatbox_chat` ADD INDEX(user, `time`);

This will be mis-parsed:

WHERE  'member_group_id' != `member_group_id` NOT IN(94,96)

What do you want it to mean?