High Mysql loads always on wordpress sites (Above 200%)

[farooq]

My server shows more than 200% CPU load on Mysqld ( /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid )

Total server load in peak hours with just a 1000 users online (above 20%), after this sites are slow and unresponsive.

I have hired 2 cpanel management companies to optimize the server and they could not fix it. I even upgraded the server as per their recommendation to :

Xeon E3-1240 with 24 GB Ram >> [Dual] Xeon E5-2640 with 64 GB Ram

I have a same 2nd server with 4 sites with around 500 users online at a time but has never seen mysql loads higher than 90%.

What do i do and where to start these are simple worpress sites and cannot handle 1000 users online at a time with nginx, php-fpm and cloudflare.


[[email protected] ~]# mysqladmin proc status
+---------+---------------------+-----------+--------------------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+---------+---------------------+-----------+--------------------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
| 1727030 | moviesx_usr123x | localhost | moviesx_123mx | Sleep | 0 | | |
| 1727031 | moviesx_usr123x | localhost | moviesx_123mx | Sleep | 0 | | |
| 1727032 | buzz123_redgg567 | localhost | buzz123_rd34fgfgfg | Query | 0 | Sending data | SELECT MAX(p.post_modified_gmt) AS last_modified, MIN(p.post_date_gmt) AS published_at
FROM nmh_p |
| 1727033 | moviesx_usr123x | localhost | moviesx_123mx | Query | 0 | Sending data | SELECT nmh_posts.*
FROM nmh_posts LEFT JOIN nmh_term_relationships ON (nmh_posts.ID = nmh_term |
| 1727034 | putlx45_Redf23ukh89 | localhost | putlx45_eer566hjkj | Query | 0 | statistics | SELECT t.*, tt.* FROM nmh_terms AS t INNER JOIN nmh_term_taxonomy AS tt ON t.term_id = tt.term_id WH |
| 1727035 | moview_YesUSRer3r34 | localhost | moview_YesDBere434 | Sleep | 0 | | |
| 1727036 | buzz123_redgg567 | localhost | buzz123_rd34fgfgfg | Sleep | 0 | | |
| 1727037 | moview_YesUSRer3r34 | localhost | moview_YesDBere434 | Sleep | 0 | | |
| 1727038 | buzz123_redgg567 | localhost | buzz123_rd34fgfgfg | Sleep | 1 | | |
| 1727039 | buzz123_redgg567 | localhost | buzz123_rd34fgfgfg | Sleep | 0 | | |
| 1727040 | root | localhost | | Query | 0 | starting | show processlist |
+---------+---------------------+-----------+--------------------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
Uptime: 252275 Threads: 11 Questions: 169075515 Slow queries: 0 Opens: 62941 Flush tables: 1 Open tables: 2000 Queries per second avg: 670.203

 

[kodeslogic]

I would first start by checking if the server is reaching the max MySQL connection during the peak hours.

 

[cPRex]

1000 is also quite a bit of traffic for one domain. If that's really the amount of traffic each domain is getting, I can see how that would cause load on the system.

 

[farooq]

1000 is also quite a bit of traffic for one domain. If that's really the amount of traffic each domain is getting, I can see how that would cause load on the system.

So i have trippled the sever resources, still i face the same problem. Do i need to increase the resources like CPU and RAM again? What config do you suggest is good.

 

[farooq]

I would first start by checking if the server is reaching the max MySQL connection during the peak hours.

My Max Connections is 500 with Performance schema enabled. In daily process log, i see max 2.0 connections at most.

 

[cPRex]

We don't recommend specific hardware or software configurations, so I don't have much to add on that end. You might want to try working with one of the server administrators at System Administration Services as those are all companies familiar with the cPanel software and how it works with other tools on the system.

 

[farooq]

I guess i will have to hire someone else again. Last time i sued assistanz and other for optimization, they could not help at all, they suggested an upgrade and the new server still shows high cpu loads.