Latest Announcemnent
New thread

Website shutdown because of 40% CPU load

Status
Not open for further replies.
T

toxicious

Member
24
2012
1
0
My website was hosted by a free host for 2 months without complaints, then I moved to a new paid host, and it got shutdown after some hours because it created a 40% load on their Xenon 8 core CPUs?!

Though they say they will take a look at it too see if they can correct anything that is brutally wrong in the scripts, I thought that I wanted to help too (it is my site duh).

I'm not a PHP expert, I'm learning and coding a lot though every week.
My website is very db heavy I think, it is basically a front-end UI for listing the stuff in the DB and allowing the user to interact with it.

I don't have that much stats on it, but I know that I had ~300 daily visits every day when it got shutdown, and probably a lot more outer calls to the API I wrote.

So I am not sure what caused that load since I am inexperienced with web coding. I do have a shoutbox accessible on all of the few pages, which makes a query to the db every 3 seconds to check for new messages when the site is open. But still, I've seen lots and lots of sites with shoutboxes so I doubt that is the problem.

Since the load is so damn big compared to the average user (which they say is 1-2%) I thought that maybe someone here have a clue about the problem even though I don't provide the source code of the page.

So yeah, if anyone feels like throwing out a clue idea, please do!
I really feel I have abandoned my users since the website is down :(
 
36 comments
Shoutbox isn't even hosted on your server so it shouldn't even cause cpu usage issues.
You either had some weird loop or your not telling the whole story.
 
I promise you that I haven't any weird things running, no rapidleech or torrent. Basically just lots of mysql queries. Everything else are by mistake/errors.
 
I'd have to agree with futureawesome somewhat and say that you should optimize both PHP and SQL.

There is plenty of free tools to help you do so, just google :)
 
Yeah I think mysql is the load boss. The host sent me a picture of the load meter when it was at 40%, but on that picture I also saw a tab that said mysql. Since I no longer have a running website no data is generated, but he really should have sent me that data as well too directly see if mysql was the theif.
 
300 visits a day... that's 13 visitors an hour. There's no way MySQL could be jacking things up that high at such a low hit rate. Even if you had a massive database incorrectly indexed and a ridiculous amount of queries per page, it should never go that high.

You're tying everyone's hands behind their backs by not saying what kind of site it is, what kind queries are issued, the quantity of said queries, and what system you're using, unless it's custom.
 
300 visitors are too much 40% load. There should something more. I know a site in my server that getting way more than 5000 visitor p/d and the load is just as normal.
Try Caching your site as I think not only the mysql is the culprit here also non optimized php, js, image, css all can be responsible for 40% Load!

You should provide some more info about your scripts as we cannot give any dime clue on it without knowing anything about ur db query, loop thingy.
 
I had such an issue before, but the hosting support team checked it theirselves and fixed it.
If your website is a wordpress site, then it can be from one of your plugins. That is what my issue was coming from.
 
You are lucky. my vps got suspended due to +10% CPU load. but they returned me again with last warning.

buy a vps with 1gb-2gb ram and 4+ cores
 
Okay the site is a index site, indexing mirrors for files. It's nothing with torrents, just files. For example the home page grabs 50 of the files in the db and lists them, then when the user clicks one of them, the scripts grabs all the mirrors that are related to that file (normally around 3).

Before it got shutdown the mirror table had around 20k mirrors, and the files table around 4k entries.

It's just basic queries as INSERT, SELECT and maybe one or two UPDATE when I add new mirrors (only I could do it).

EDIT: I could provide you with some snippets that are used the most, wait a sec and I'll paste them somewhere.

---------- Post added at 11:10 AM ---------- Previous post was at 10:49 AM ----------

Before I start, the file "dblogin.php" just opens the MySQL connection and selects the correct DB.

Here is the code for retrieving addons (as you can see it outputs in JSON), this page is fetched with jQuery and parsed and shown to the user:
http://pastebin.com/Gb1ZVicb

This function is called a lot by the API, every time a user loads a kind of page, a greasemonkey script calls this function to see what to do:
http://pastebin.com/qLMfy3uR

I hope this can help you a little. I am really glad you guys a trying to help me :)

EDIT: this was the pic the host sent me: http://slickimg.com/images/2012/07/26/Z8rNc.png
 
Last edited:
Basically its grabbing the mirrors and updating your database. If thats a bit frequent, then mysql seems to be the issue here.

I feel mysql is not properly optimized which is also leading to the high resource usage.
 
Find a Xen PV or a XenHVM VPS provider and you will not be banned due to this reason.
I installed Gentoo Linux & FreeBSD on those VPS and compile the kernel, world, and all packages (which means use 100% CPU resources more than 48 hours) without any issues.
However, these VPS have some I/O issues due to its virtualization technology.
 
RodneyTrot said:
Why are you using SELECT * if you're not using all of the columns? Seems like your queries are the culprit.
Click to expand...
Looks about right, select * usage could be tweaked. But then again the first part may be important to return the result with all columns.

PHP: 
	else
	{
		//Has no mirrors, though it still may exist
		$result = mysql_query("SELECT * FROM addons WHERE id = $addonID");
		if (mysql_num_rows($result))
		{
			//It has been requested
			return -1;
		}
		else
		{
			//It hasn't been requested
			return 0;
		}
Based on the description of his method though getMirrorCount he should be using
PHP: 
SELECT count(*) FROM addon_mirrors/addons WHERE id = "$addonID"
or some not null column variable.

BlaZe said:
Just get a better server and optimize it.
Click to expand...
QFT, it sounds like he may have just transferred to PAID shared hosting.


A suggestion if this getting mirror count is an issue. You could just create another small table with id and # of mirrors. You update the table whenever you add a new mirror. Can't really suggest anything else without looking at your whole site.
 
tut2tut said:
You are lucky. my vps got suspended due to +10% CPU load. but they returned me again with last warning.

buy a vps with 1gb-2gb ram and 4+ cores
Click to expand...

gibalah said:
Find a Xen PV or a XenHVM VPS provider and you will not be banned due to this reason.
I installed Gentoo Linux & FreeBSD on those VPS and compile the kernel, world, and all packages (which means use 100% CPU resources more than 48 hours) without any issues.
However, these VPS have some I/O issues due to its virtualization technology.
Click to expand...

BlaZe said:
Just get a better server and optimize it.
Click to expand...

Well the reason for me not using a Google like cluster to drive my website is the same as for probably the most of you: I don't have the money.
If I had 20$ each month that in the end just would end up in a drawer, I would have a VPS. But I don't. I recently upgraded to paid, shared hosting. If it is impossible to host my site without a VPS, I will have to shut it down permanently.

Anyway, I really appreciate all the tips you guys have been giving me! :)
Unfortunately, I'm going on vacation tomorrow, so I can't really test anything yet. (I still have no host to test it at atm). But my new hosts "PHP & MySQL specialist" is looking into my scripts, I'll leave it to him and I'll hope he will fix it and get my website up before I come back!

Still, it feels like "just" optimizing my mysql won't take the load down to 5% or something like that :/ I'm hoping I'm wrong...
 
futureawesome said:
Looks about right, select * usage could be tweaked. But then again the first part may be important to return the result with all columns.

PHP: 
    else
    {
        //Has no mirrors, though it still may exist
        $result = mysql_query("SELECT * FROM addons WHERE id = $addonID");
        if (mysql_num_rows($result))
        {
            //It has been requested
            return -1;
        }
        else
        {
            //It hasn't been requested
            return 0;
        }
Based on the description of his method though getMirrorCount he should be using
PHP: 
SELECT count(*) FROM addon_mirrors/addons WHERE id = "$addonID"
or some not null column variable.


QFT, it sounds like he may have just transferred to PAID shared hosting.


A suggestion if this getting mirror count is an issue. You could just create another small table with id and # of mirrors. You update the table whenever you add a new mirror. Can't really suggest anything else without looking at your whole site.
Click to expand...


I Do agree, People suggest to optimize and tweak the server is not everything, as far i've read you have low visitor count. So the problem is with your PHP Code. For example what futureawesome has posted, I do not know if it's a orginal used script. But 'limit' is important in most query's

for example:
PHP: 
$result = mysql_query("SELECT * FROM addons WHERE id = $addonID"); 
        if (mysql_num_rows($result))
It loops through all the Lines inside the database to receive the count, However we do not need that!

if (mysql_num_rows($result)) // Can only receive boolean's True | False | 0 | 1

There is no need to receive for example 3222 on num_rows since we only need 0 = none or 1 = Yes

$result = mysql_query("SELECT * FROM addons WHERE id = $addonID LIMIT 1");

Also Please do not forget about security.
 
Status
Not open for further replies.
Back
Top