I am new here.
Over a month ago I installed a VPS running Ubuntu 22.04. I also installed Virtualmin.
The purpose of this VPS is to migrate my Prestashop site to it.
I finally succeeded in migrating my site yesterday. Everything was fine.
This morning I realized that my site was displaying “504 gateway time out” errors. I go to the Virtualmin panel and see that the CPU is running at 100%.
I investigate and, using the “top” command, I realize that it’s mariadb/ mysql that’s taking up to 397% CPU!
When I look at my resource usage over the month, I was averaging 0.70% CPU.
Everything went haywire this morning and I can’t figure out why.
Does anyone have any ideas?
This seems to be a reoccurring theme. Did you get a OOM error? (Out of Memory) You have 16G of memory? This should be plenty. How many CPU cores? Pressing 1 will display the usage of all cores.
Could be a bad optimized website/script/plugin that queries the DB way too much. Do you got phpmyadmin installed? Goto the Status tab and then click Processes. There should not be too much (long running) queries there to see. Also click Monitor, you can see some real time stats.
Fixing your script could help, adding indexed on columns on heavy queried tables if needed could do wonders too.
Also, it could be all is fine but you are ‘under attack’ and some bot is doing a ridiculous amount of requests at the moment of your 400% peak.
If it is a bad optimized script/plugin/website, why do I never get the error before on my real website or on my vps ? I don’t get it.
Yes I have phpmyadmin in my vps but when the CPU is overrunning and I can’t enter in. I will reboot mariadb server in the virtualmin dashboard and try it, thx
Hi back,
I have just checked on phpmyadmin and a query seems to be problematic. It never finishes and restarts approximately every 30 seconds.I have checked on the phpmyadmin of my original website, and it only contains one of these queries, and it finishes. Do you have any ideas on how I could resolve this?
If the service provider offered you a virtual machine that shares CPUs with other machines and the latter have issues, heavy loads, then you will also be affected too.
In theory, HOSTINGER sold me their solution by saying “The server’s resources, including the CPU, RAM, and disk space, are all yours. No need to worry about other people’s site traffic affecting your website performance.”
But I don’t know if it is real.
When I restart mariadb service, I can use my website and phpmyadmin for like 4/5minutes ^^.
Just copy the full query and paste it into the sql tab for phpmyadmin and run it note the response time after the query is run if it’s in milliseconds what ever software that is executing that query has some issues, not getting the right data back perhaps ?, if the query is into seconds and the query has joins check that the join fields are of the same type.
The amount of failed queries you get may relate to the amount of traffic your site has, each user maybe running the same query and failing at the same point hence mysql being overloaded
If you leave the service stopped for 5 minutes what is the resources situation in this time range? Just open two Terminals, in one run free or htop, in the other one run commands to start/stop the service.
What is the status of external server connections? These days I faced abuse on a website from Amazon, there were permanently around 60 established connections simulating a flurry of all kinds of human actions. I blocked around 3000 addresses. You can only allow access to your address and drop the others. If you no longer have problems visiting the website, then you have partially identified the source.
It can also be a bug in the Prestashop code, in the MariaDB package or a wrong configuration in my.cnf. If it worked before migration I suspect MariaDB configuration.
jimr1 is correct, normal queries like selecting some products should take milliseconds, maybe a tent of a second max. When above this these are almost always things to optimize.
I would also try to copy and run that query in the SQL tab. After that, you can also add EXPLAIN before that query and run it again, you will get some suggestions from the DB. (If the query does not give away private/sensitive information, you could post it too, maybe we can give some more suggestions based on that.)
I normally add indexes for allmost all key columns that are in use.
Another thing possible is that one of the tables has become very large. Hence why the issue has not come up before. Long time ago I had a website that logged every visitor. And on every page it did some select on IP address. The table with IP addresses grow to something like 500k records and made everything very slow. Adding an index on the IP column was the solution. I never work with such selections these days, but it is a good example of something that is the result of a growing table resulting in performance issues.
