Apache connections stuck on "sending reply" and high mysql l

Apache connections stuck on "sending reply" and high mysql load
Apache on my dedicated server constantly hangs due to connections being stuck in a state of "sending reply":
++++++++++++++
list of top cheapest host http://Listfreetop.pw

Top 200 best traffic exchange sites http://Listfreetop.pw

free link exchange sites list http://Listfreetop.pw
list of top ptc sites
list of top ptp sites
Listfreetop.pw
Listfreetop.pw
+++++++++++++++

https://i.imgur.com/9AV3U9o.png

This happens at least several times a day - sometimes it won't happen for 2 or 3 hours, sometimes it'll happen constantly to the point where all connections will be occupied and stuck in sending reply within 5 minutes of restarting apache. This results in sites having constant downtime. The IP's in these requests are usually Amazon AWS or Cloudflare (I use Cloudflare for many sites, and Amazon AWS IP's may be from having the site using Ezoic nameservers) that access a variety of URL's on the site. The 'SS' column in the screenshot above shows how long the connections been stuck - in this example it's hundreds of seconds on each request, so something is very wrong.

These are simple Wordpress sites, usually no images, no unusual plugins, very simple and definitely nothing unusual that would cause a page to take a long time to load. However, they each have 5,000 to 100,000+ posts on each site and may be posting up to several thousand posts a day (using the plugin "WP all import pro") that are scheduled to publish at random times within 24 hours from being imported. There are 10+ sites like this on the server but only 3 or 4 constantly appear in apache status with stuck connections.

Across all sites on the server, monthly pageviews are under 1,000,000 and should be easily handled by my server (from namecheap: XEON E3 1240 V3 8GB DDR3 1TB HDD SATA).

Mysql is often under high load on the server, and sometimes when looking at the processlist in phpmyadmin there will be stuck queries or sometimes NULL queries. Running the 'top' command also often shows very high CPU usage (my server has 4 CPU cores). Example screenshots:

https://i.imgur.com/uT1wmrY.png (queries being stuck)
https://i.imgur.com/pg3qWgl.png (NULL queries)
https://i.imgur.com/lKtRGDr.png (high mysql CPU use)

Of concern is that the mysql error log in /var/lib/mysql is over 3GB in size now, too big for me to even read - renaming it and checking a new one generated shows many 'Got an error reading communication packets' errors. I don't know if that is the issue for stuck connections in apache and if so, what to do about it.

make money c programming
blog.de
url-4ever.com
babybearhits.com
venezici.creadunet.com
lcwsoft.com
make money working from home
bitcoinflood.com

I have tried changing all possible apache settings such as Max Request Workers from the default 150 so more connections can be made (doesn't help as any new connections also get occupied and stuck), disabling all plugins or trying different themes on the wordpress sites (no effect), as well as changing some different mysql settings in my.cnf (per https://serverfault.com/a/989269/548654) with no improvement. I also tried changing all of the database tables for 1 of the problem sites to InnoDB from MyISAM and it still gets stuck connections. What else can I try to resolve this?
Apache on my dedicated server constantly hangs due to connections being stuck in a state of "sending reply":

https://i.imgur.com/9AV3U9o.png

This happens at least several times a day - sometimes it won't happen for 2 or 3 hours, sometimes it'll happen constantly to the point where all connections will be occupied and stuck in sending reply within 5 minutes of restarting apache. This results in sites having constant downtime. The IP's in these requests are usually Amazon AWS or Cloudflare (I use Cloudflare for many sites, and Amazon AWS IP's may be from having the site using Ezoic nameservers) that access a variety of URL's on the site. The 'SS' column in the screenshot above shows how long the connections been stuck - in this example it's hundreds of seconds on each request, so something is very wrong.

These are simple Wordpress sites, usually no images, no unusual plugins, very simple and definitely nothing unusual that would cause a page to take a long time to load. However, they each have 5,000 to 100,000+ posts on each site and may be posting up to several thousand posts a day (using the plugin "WP all import pro") that are scheduled to publish at random times within 24 hours from being imported. There are 10+ sites like this on the server but only 3 or 4 constantly appear in apache status with stuck connections.

Across all sites on the server, monthly pageviews are under 1,000,000 and should be easily handled by my server (from namecheap: XEON E3 1240 V3 8GB DDR3 1TB HDD SATA).

Mysql is often under high load on the server, and sometimes when looking at the processlist in phpmyadmin there will be stuck queries or sometimes NULL queries. Running the 'top' command also often shows very high CPU usage (my server has 4 CPU cores). Example screenshots:

https://i.imgur.com/uT1wmrY.png (queries being stuck)
https://i.imgur.com/pg3qWgl.png (NULL queries)
https://i.imgur.com/lKtRGDr.png (high mysql CPU use)

Of concern is that the mysql error log in /var/lib/mysql is over 3GB in size now, too big for me to even read - renaming it and checking a new one generated shows many 'Got an error reading communication packets' errors. I don't know if that is the issue for stuck connections in apache and if so, what to do about it.

I have tried changing all possible apache settings such as Max Request Workers from the default 150 so more connections can be made (doesn't help as any new connections also get occupied and stuck), disabling all plugins or trying different themes on the wordpress sites (no effect), as well as changing some different mysql settings in my.cnf (per https://serverfault.com/a/989269/548654) with no improvement. I also tried changing all of the database tables for 1 of the problem sites to InnoDB from MyISAM and it still gets stuck connections. What else can I try to resolve this?
Try converting your database table engines to InnoDB as you have too many table level locks.

Your processor is doing ~75 work on user level stuff and rest of the %cpu looks normal

The bottleneck is somehow the database
∞A U T O M 8 N . C O M
High Available webstack for cPanel * 10 minute deployment
Fully rebrandable Solution for Service providers and SME
I tried that with one of the problem sites and it still frequently gets connections stuck in sending reply.
Since it does seem to be a bottleneck at the database, try running mysqltuner to see if it will give you some items to change in the mysql configuration.

It also looks like you have some IO wait and using some swap which wont help with IO to which mysql uses a good bit . Try increasing RAM for the server and see if that helps.

Also, since you mentioned AWS IPs, it could be some type of attack on one or multiple sites on that server. Seeing cloudflare IPs would be normal if you have sites using cloudflare. Use netstat -ntu to see if there are a lot of connections from a select few IPs or say a lot of SYN connections that could be eating all the open connections.
-Steven | u2-web@Cooini, LLC - Business Shared Hosting | Isolate sites with Webspaces | Site Builder | PHP-FPM | MariaDB
WHMCS Modules: Staff Knowledgebase | Custom Modules and Hooks
"It is the mark of an educated mind to be able to entertain a thought without accepting it" -Aristotle
Since it does seem to be a bottleneck at the database, try running mysqltuner to see if it will give you some items to change in the mysql configuration.

It also looks like you have some IO wait and using some swap which wont help with IO to which mysql uses a good bit . Try increasing RAM for the server and see if that helps.

Also, since you mentioned AWS IPs, it could be some type of attack on one or multiple sites on that server. Seeing cloudflare IPs would be normal if you have sites using cloudflare. Use netstat -ntu to see if there are a lot of connections from a select few IPs or say a lot of SYN connections that could be eating all the open connections.
I tried changing different mysql settings as mentioned (per https://serverfault.com/a/989269/548654) from the default settings and there was no improvement.
After trying everything I believe it is slow SQL queries running on the server causing this issue, rather than a bot attack or anything of that nature. Primarily now I keep seeing SELECT SQL_CALC_FOUND_ROWS queries that get stuck for several minutes at a time. When there are a lot of these for certain databases/websites I look in apache status and those are the sites that have a bunch of stuck connections; when there are none of these queries running apache status is clean. There are other types of queries that get stuck sometimes but it definitely looks like the SELECT SQL_CALC_FOUND_ROWS queries are the main culprits. So I am seeing what I can do to handle those (https://wordpress.stackexchange.com/...dpress-queries).