SELECT/UPDATE and SELECT/INSERT kills MySQL server

Question

I run a server that is getting killed by the number of requests it is getting from one of our iPhone games for storing scores. By this I mean the server becomes unresponsive.

I only really know enough MySQL/PHP to get by, so I am floundering in my attempts to fix this. I am sure its a problem that can be optimised because we have a dedicated server. We handle only 300 requests a minute.

Basically we check the score someone is posting from their iPhone game (using a SELECT) to see if they have an existing score. If they do, and their new score is better, we do an UPDATE, otherwise we do an INSERT. The statements look like this:

$sql = "SELECT id,score,level FROM $table WHERE board='$board' AND name='$name' AND udid = '$udid' AND fbuid = '$fbuid'"

The UPDATE and INSERT statements look like this:

$sql = "UPDATE $table SET score='$score', level='$level', country='$country', date=CURRENT_TIMESTAMP WHERE board='$board' AND name='$name' AND udid = '$udid' AND fbuid = '$fbuid'"

$sql = "INSERT INTO $table(board, udid, fbuid, name, score, level, country) VALUES ('$board', '$udid', '$fbuid', '$name', '$score', '$level', $country')"

And for completeness, here is the definition for the table:

CREATE TABLE $table (
id int(11) NOT NULL auto_increment,
board tinyint(4) NOT NULL,
udid varchar(45) default NULL,
fbuid bigint(20) unsigned default NULL,
name varchar(25) default NULL,
country tinyint(4) default NULL,
level tinyint(4) default NULL,
score decimal(10,0) default NULL,
date timestamp NOT NULL default CURRENT_TIMESTAMP,
PRIMARY KEY (id),
KEY scoreidx (score),
KEY udididx (udid),
KEY fbuididx (fbuid),
KEY boardidx (board),
KEY levelidx (level),
KEY countryidx (country)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

I currently connect to the MySQL server from PHP using:

$conn = mysql_pconnect(DB_HOST,DB_USER,DB_PASSWORD,MYSQL_CLIENT_INTERACTIVE);

But used to use msql_connect previously, but did not notice an improvement with that.

Any pointers on how I can optimise this, or links to any web pages that describe what I should be doing would be HUGELY appreciated.

Answer 1

"MySQL uses table-level locking for MyISAM, MEMORY and MERGE tables, page-level locking for BDB tables, and row-level locking for InnoDB tables."

http://dev.mysql.com/doc/refman/5.0/en/internal-locking.html

So, each time you update, the full table gets a lock. You might want to switch to InnoDB.

Answer 2

MyISAM has table-locks instead of the InnoDB row-locks. You could create a copy of your table, alter the engine to InnoDB, and (on a testserver) test the load with mysqlslap or similar stress testing tools.

Also, very important:

ALTER TABLE tablename ADD INDEX(board,name,udid,fbuid)

MySQL can only use 1 index at a time, so your several 'loose' ones don't do that much good when always querying for this specific combination.

Answer 3

you could try optimizing by not doing the select and updating on duplicate primary key.. You would need a primary key like email address or something though.

$sql = "INSERT INTO $table(board, udid, fbuid, name, score, level, country) VALUES
('$board', '$udid', '$fbuid', '$name', '$score', '$level', $country') ON DUPLICATE KEY
UPDATE score = '$score'";