and forgive me in advance as I somewhat a newbie. Am going to do my best to explain the situation in a way it could be generally understood. Long story short, as the title suggests. I have a website with lots of traffic, but on a heavy duty dedicated server and by all means, should be running smoothly however the website is generally laggy and takes quite a bit of time to load up.
The server host and I have cornered the problem, but beyond finding the generalized problem actually fixing it is outside of what I pay them for. They extensively looked at why the website is having these major spikes and gave me the following information;
[root@xxx-x-x-xxx ~]#| Id | User | Host | db | Command | Time | State | Info |
|---|---|---|---|---|---|---|---|
| 4 | xxx_xxx | localhost:xxxxxx | xxx_xxxx | Sleep | 4 | ||
| 20652 | xxx_xxxx | localhost | xxx_xxxx | Execute | 1 | Sending data | SELECT a.*, sum(b.views) as count FROM videos b LEFT JOIN Wo_Users a on a.user_id=b.user_id WHERE |
| 20656 | root | localhost | Query | 0 | starting | show processlist |
They then went onto say;
On deeply investigating on the issue we could see that mysql is
consuming more CPU usage in the server. As updated in the above
snippet the database "xxx_xxx" is executing multiple connections even
if already one session exists. Please modify the code accordingly so
that the code will check if a session to mysql already exists before
starting a new one.
I am familiar with my website and it’s code and even pretty good on the backend side of things, and I have made a few educated guesses as to what they meant but my guesses did not seem to resolve the website lag/speed issues. So I asked my host for clarification and they responded;
We checked again and these are the results.
> load average: 0.99, 0.90, 0.87
the below logs and server load was increased on that particular time
which also came normal suddenly.
12:10:01 PM runq-sz plist-sz ldavg-1 ldavg-5 ldavg-15 blocked
01:40:01 PM 9 1129 1.17 0.96 0.90 0
01:50:01 PM 14 1129 1.54 1.35 1.14 0
02:00:01 PM 12 1136 0.80 0.98 1.05 0
02:10:01 PM 13 1124 1.23 1.14 1.09 0
02:20:01 PM 12 1138 16.20 7.16 3.29 0
02:30:01 PM 11 1126 1.09 2.33 2.56 0
02:40:01 PM 8 1140 1.37 1.39 1.93 0
Average: 11 1129 1.21 1.10 1.05 0
As per below logs there is an slight variation in CPU usage of your
domain php processes which may be caused an issue on that time.You can refer below logs,
-----------------------------------------------------------
|User |Domain |CPU%|MEM%|MySQL#|
-----------------------------------------------------------
|xxx|xxx |69.58|0.00|0.0 |
| Top Process | 99.4 | /usr/local/cpanel/3rdparty/bin/perl /usr/local/cpanel/3rdparty/bin/awstats.pl -configxxx.xxx -LogFile/etc/apache2/logs/domlogs/xxx.xxx-ssl_log.bkup -update |
| Top Process | 97.0 | php-fpm: pool xxx.xxx |
| Top Process | 47.0 | php-fpm: pool xxx.xxx |
~~~~~~
2022-01-06
Account: xxx Domain: xxx.xxx CPU: 69.58 Mem: 0.00 MySQL: 0.0
%CPU 99.4 - /usr/local/cpanel/3rdparty/bin/perl /usr/local/cpanel/3rdparty/bin/awstats.pl -configcxxx.xxx -LogFile/etc/apache2/logs/domlogs/xxx.xxx-ssl_log.bkup -update
%CPU 97.0 - php-fpm: pool xxx.xxx
%CPU 47.0 - php-fpm: pool xxx.xxx
~~~~~~
So their response only served to confuse my issue more and as you can see, their is a bit of a language gap as well. I’ve done my due diligence in researching the topic but am getting nowhere fast and everything I’ve personally tried have not helped at all either. Any opinions, help or clarification on what I may be able to do would be greatly appreciated and thank you in advance.
2
Answers
In all honesty, the query you’re using looks expensive and as the tables grow, you will significantly slow down searches querying that way. I would advise that you modify the query to perform better, stress it and see if you can increase your numbers while lowering delays.
As a tip for hosting, any cloud provider out there will provide you instances you can scale at any time by applying more cores, memory or disk-space. Known as vertical scaling, in time your setup may need to horizontally scale across servers and with that you will want to follow this next tip closely.
Many by default will host MySQL with their HTTP server and this is fine, MySQL default is locked to localhost so access can’t be granted outside the machine but this being a safe practice is not an efficient one at all and proves to be very costly.
As the cloud will charge you exponentially more for a higher core-count, most should start out spreading their structure by single cores till this is reasonable so splitting the servers up you don’t need a single 8core box, you could get away with maybe a 1-core front, and a 4-core MySQL. Testing would be needed here.
Your mention of sessions is not really the case here, but if you feel sessions are overwhelmed make another server and use Redis/MySQL. When you reach 64-cores you can consider sharding or something complicated to horizontally scale the databases. HTTP will scale no problem as long as it’s stateless any load-balancer will be your savior.
(An example of this would be, if you’re hosting videos, the videos could not be hosted on any of the HTTP servers servicing front-end. They’d be hosted on other servers in the back-end so even if 1 or 20 HTTP servers were online behind balancer they all behave properly and can switch across the servers and still keep a session/etc.)
Any questions ask but this may not be appropriate for SO.
In those speed tests, did you see anything relating to node or sockets failing?