Website shutdown because of 40% CPU load

[tut2tut]

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

[toxicious]

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

[inoserv]

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.

[gibalah]

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]

Why are you using SELECT * if you're not using all of the columns? Seems like your queries are the culprit.

[BlaZe]

Just get a better server and optimize it.

[futureawesome]

Why are you using SELECT * if you're not using all of the columns? Seems like your queries are the culprit.

Looks about right, select * usage could be tweaked. But then again the first part may be important to return the result with all columns.

    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

SELECT count(*) FROM addon_mirrors/addons WHERE id = "$addonID" 

or some not null column variable.

Just get a better server and optimize it.

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.

[toxicious]

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

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.

Just get a better server and optimize it.

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...

[Th3KiNG]

remove all that taks too much ram and you will be fine

[xentos]

Looks about right, select * usage could be tweaked. But then again the first part may be important to return the result with all columns.

    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

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.

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:

$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.