Need Help Optimizing mysql / code - Can Pay

[vexious]

I'm having huge problems.... I just purchased a high powered VPS 1gb memory ect ect however my CPU usage is extremely high and fluctuating... from 65-100% usage.... And I'm also getting timeouts(Assuming timeouts are from such high cpu usage...?)..

 

I need help on how to optimize the sql database and or code... The database is getting like 300+ quries a second or so i believe...

 

Anyways heres my code: - Let me know if you can help..

 

Table:
CREATE TABLE `invites` (
  `user` int(11) NOT NULL default '0',
  `name` VARCHAR(100) NOT NULL default '0',
  `count` int(11) NOT NULL default '0',
  `time` int(11) NOT NULL default '0'
);


<?php
require_once 'include.php'; 

if ( checkInvites($user,"zip",$_POST['ids'],$mysql) == TRUE ) { 
include "need.php";
} else {
include "main.php";
}

    function checkInvites($uid, $appName, $ids, $mysql)
    {
	$conn = mysql_connect($mysql['host'],$mysql['user'],$mysql['pass']);
		@mysql_select_db($mysql['db']) or die( "Unable to select database");
	$time = time();
        if ( $ids == NULL ) {
            $count = 0;
        } else {
            $count = count($ids);
        }

   		$query = "SELECT `count` FROM invites WHERE `user` = $uid AND `appname` = '$appName'";
	$result = mysql_query($query,$conn);
	if ( !( $result ) ) {
		echo "There was a problem with the Database!<br/>";
            echo mysql_error()."<br/>";
	}
	if( ! ( $row = mysql_fetch_row( $result ) ) ) {
		$query = "INSERT INTO invites VALUES($uid,'$appName',$count,$time);";
		$result = mysql_query($query,$conn);
		if ( !( $result ) ) {
                echo "There was a problem with the Database!<br/>";
                echo mysql_error()."<br/>";
		}
            if ( $count == 20 ) {
                $equation = FALSE;
            } else {
                $equation = TRUE;
            }
	} else {
            if ( $row[0] == 20 ) {
                $equation = FALSE;
            } else {
                $query = "UPDATE invites SET `count` = count + $count WHERE `user` = $uid AND `appname` = '$appName';";
                $result = mysql_query($query,$conn);
                if ( !( $result ) ) {
                    echo "There was a problem with the Database!<br/>";
                    echo mysql_error()."<br/>";
                }
                if ( $count + $row[0] > 19 ) {
                    $equation = FALSE;
                } else {
                    $equation = TRUE;
                }
            }
        }
        $_count = -($count + $row[0] - 20);
        if ( $_count > 0 ) {
            echo "<div style='text-align: center; padding: 15px; font-weight: bold; font-size: 10pt;'>Add<span style='font-size: 13pt; color: red; font-weight:bold;'>$_count</span>Before Proceeding.</div>";
        }

        mysql_close($conn);
        return $equation;
    }

?>

[roopurt18]

A few questions:

 

Are you sure that is the code that is causing the problem?

 

How many records are in your `invites` table?

 

Do you have any indexes in the `invites` table?

 

How often is that code run?  I know you said the DB is receiving ~300 queries per second, but is that because that code is executed on every page load? Every couple page loads?

 

How many users are hitting your site?

[vexious]

It loads every time a user loads to page to make sure they have invited x users... and if not they cant access the main page.  So I guess then it also loads / sends data every time a user invites someone to post their id, and count.

[roopurt18]

What about the other questions?

[vexious]

Ah sorry..

 

about 80,000 impressions  a day

 

I have about 90,000 records

 

Not sure what you mean by do i have indexes in the invites table... im new to mysql

[vexious]

If you could even simply recommend config changes that might help...

 

I have 700mb of memory free on my server.. The problem is my CPU usage is at 80-100%

 

=\

[roopurt18]

I notice in your CREATE TABLE you have a `name` column but in your code you refer to `appname`.

 

Since your SELECT and UPDATE queries are searching on the `user` and `name` columns, try adding an appropriate INDEX to your table:

   ALTER TABLE `invites` ADD INDEX ( `user` , `name` )  

 

It's probably not advisable to create an INDEX on the entire `name` column as it's 100 characters long, you can limit the size of the INDEX with:

ALTER TABLE `invites` ADD INDEX ( `user` , `name` (12) )  

Someone with more MySQL experience than I have can tell you if that's a better practice than using the entire field.

 

If you have access to phpMyAdmin you should try running the queries directly from there and use EXPLAIN to get more information about how MySQL is running them.

 

P.S.  Use `name` or `appname` in your SQL statements depending on what the column is actually called in the table.