Mysql High CPU , all my Websites are down

hi , i have a contabo vps which have DDOS attacks protection and cloudflare with the main domain, VPS specs:
CPU Model: Intel(R) Xeon(R) CPU E5-2630 v4 @ 2.20GHz
CPU Details: 4 Core (2200 MHz)
Ram : 8GB
Distro Name: CentOS Linux release 7.6.1810 (Core)
Kernel Version: 3.10.0-957.21.3.el7.x86_64
i run CWP Pro.
i have installed 3 wordpress sites , there is one with about 35000 posts.

Now, the CPU usage is 100% and all my websites are down because of mysqld service

there are some screenshots of the CWP processes and mysql queries.

https://imgur.com/Q8yK9A7



https://imgur.com/uzFLNm3


https://imgur.com/577yZ5V

please help me , how can i optimize mysql for better performance?

you need to optimize my.cnf

post your my.cnf  here, I can help you.

thank you for your reply ,

that is my my.cnf file :

Code: [Select]

#
# This group is read both both by the client and the server
# use it for options that affect everything
#
[client-server]

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d


it just the normal one , i didn't change anything to it.

post your /etc/my.cnf.d/server.conf

/etc/my.cnf.d/server.cnf :

Code: [Select]

#
# These groups are read by MariaDB server.
# Use it for options that only the server (but not clients) should see
#
# See the examples of server my.cnf files in /usr/share/mysql/
#

# this is read by the standalone daemon and embedded servers
[server]

# this is only for the mysqld standalone daemon
[mysqld]

#
# * Galera-related settings
#
[galera]
# Mandatory settings
#wsrep_on=ON
#wsrep_provider=
#wsrep_cluster_address=
#binlog_format=row
#default_storage_engine=InnoDB
#innodb_autoinc_lock_mode=2
#
# Allow server to accept connections on all interfaces.
#
#bind-address=0.0.0.0
#
# Optional setting
#wsrep_slave_threads=1
#innodb_flush_log_at_trx_commit=0

# this is only for embedded server
[embedded]

# This group is only read by MariaDB servers, not by MySQL.
# If you use the same .cnf file for MySQL and MariaDB,
# you can put MariaDB-only options here
[mariadb]

# This group is only read by MariaDB-10.1 servers.
# If you use the same .cnf file for MariaDB of different versions,
# use this group for options that older servers don't understand
[mariadb-10.1]



Security & Optimization Check should give you a good option on what you should change. You find it in
Mysql Manager

that is the process or the queries when it run my sites stopped and CPU load became full :\

https://imgur.com/NGPmKxy



Any one can help me please !

You must talk to a PHP/SQL programmist to optimize your SQL queries

 :if he us running a WordPress sites (or 3 of them), how is he supposed to do that?

Isn't there better advice on this? Is the problem that server simply isn't big enough now to handle so many posts?
 Perhaps he needs to archive some of the old posts?

Can someone provide a more in depth explanation of why this is happening to the O.P

I tried to help him. However on analysis of his SQL queries I found the site was doing a search on 30K+ posts to display related posts which IMO were the most inefficient queries for that big a WordPress site. These queries can bring down even a dedicated server as these are compute intensive and need very high disk i/o. Such queries need specialized search servers like sphinx or equivalent. Currently his vps is incapable of handling such SQL queries.

thank you very much ,
recently, i have found that :
when i am running the server on php5 nothing happen , no problem at all except the high CPU usage of phpfm.
And when i change it to php7 CPU load became very low and every thing is very good for about 8 hours and suddenly the query run and my websites go down.

So, the problem is between my website and php7 .
The Query : https://imgur.com/a/7lZXjc2
this is my active plugins : https://imgur.com/u7hshya
Theme : Publisher Version 7.6.0

i am using the same theme and plugins on both websites , but only the one with 30000 posts is making the problem.

WP Automatic runs a cron periodically which fires some queries which is not liked by your mysql server due to the sheer number of posts on the site. When these queries run your server goes down.

thank you for your help
i think your answer is the most right answer . i will disable it for two days and see what will happen.

But what if it was the problem, how can i fix this, i am totally depending on it in my website !!

thanks advanced

You must talk to a PHP/SQL programmist to optimize your SQL queries

Another useless response from Igor.  Why do you continue to tell people to take their problems to other people.  It is not helpful.  You are not telling the OP anything he didnt already know.

It will be great if some experts would suggest some Setups for my.cnf files. Eg for small WP sites, for big eshops, etc.