Please help identify MySQL performance bottlenecks in my app
I have a facebook app that I have written in PHP/MYSQL and FBJS/AJAX. The app is Rails Across Europe, which may be found at http://apps.facebook.com/rails_across_europe (please note that it does require access to your FB data). If you would like to view a screencast of the app's operation, you may do so at http://screenr.com/TZR and http://screenr.com/sZR (each screencast is ~3 minutes)
The problem is that response to user input is very slow. I'm not sure what could be causing this performance bottleneck. I don't have any experience with performance optimization, which is why I'm asking your help. I figure the bottleneck could be any of the following areas:
- MySQL
- AJAX
- Web server
Regarding my web hosting, I am using an A2 Hosting shared-hosting account. I'm not sure whether this type of web host is capable of handling the kind of intensive database and user activity that my app requires, but it's all I can afford. If this could be the source of my performance problem, please let me know.
To help determine the source of the performance problem, I am including some source code. The first is a SQL query, the second is an AJAX call.
Train movement seems to be the slowest operation, so I'll use that as an example. I realize that this is an extremely long code sample, but unless I submit the entire context for your review, I don't know how anyone could help me. Here is the PHP/MYSQL code:
MoveTrain.php
public function moveTrain($destCityId) {
require_once 'Train.php';
$trainModel = new Train();
require_once 'Route.php';
$routeModel = new Route();
$userNamespace = new Zend_Session_Namespace('User');
$gamePlayerId = $userNamespace->gamePlayerId;
$trainData = $trainModel->getTrain($gamePlayerId);
$originCityId = $trainData['origin_city_id'];
$destChanged = false;
if ( $destCityId != $trainData['dest_city_id'] ) {
$originCityId = $trainData['dest_city_id'];
$destChanged = true;
}
$routeResp = $routeModel->getPlayerRouteIdByCityIds($gamePlayerId, $originCityId, $destCityId);
$routeId = $routeResp['route_id'];
$trainRow = array();
// if route is invalid OR if destination city has not changed and train has arrived,
// bypass train movement
if($routeResp['error_msg'] == 'SUCCESS' || ($routeResp['error_msg'] == 'ROUTE_NOT_OWNED' && !$destChanged)) {
if((!$destChanged && $trainData['status'] == 'ENROUTE') ||
($destChanged && $trainData['status'] == 'ARRIVED')) {
$routeData = $routeModel->getRouteByCityIds($originCityId, $destCityId);
$unitsToDestination = 0;
$trainRow['direction'] = $routeModel->getRouteTravelDirection($originCityId, $destCityId); //+
// if traveling to a new city destination and traveling negative then train track unit
// is equal to route track unit count (because train is at the end of a new route)
switch ($trainRow['direction']) {
case '+':
if($destChanged && $trainData['status'] == 'ARRIVED') {
$trainData['track_unit'] = 0;
}
$unitsToDestination = $routeData['track_unit_count'] - $trainData['track_unit'];
break;
case '-':
if($destChanged && $trainData['status'] == 'ARRIVED') {
$trainData['track_unit'] = $routeData['track_unit_count'];
}
$unitsToDestination = $trainData['track_unit'];
break;
default:
break;
}
// 2. Move the train
$unitsToDestination = $unitsToDestination - $trainData['track_units_remaining'];
$trackUnitsRemaining = 0;
$trainArrived = false;
// Note that I changed from < to <=
if ( $unitsToDestination <= 0) {
// Went too far or arrived.
$trackUnitsRemaining = abs($unitsToDestination);
$trackUnit = $routeData['track_unit_count'];
if($trainRow['direction'] == '-') {
$trackUnit = 0;
}
$trainArrived = true;
} else {
// Did not reach destination
$trackUnitsRemaining = 0;
switch ( $trainRow['direction'] ) {
case '+':
$trackUnit = $routeData['track_unit_count'] - $unitsToDestination;
break;
case '-':
$trackUnit = $unitsToDestination;
break;
default:
break;
}
}
// 3. Save changes carefully.
$trainRow['route_id'] = $routeId;
$trainRow['origin_city_id'] = $originCityId;
$trainRow['dest_city_id'] = $destCityId;
$trainRow['track_unit'] = $trackUnit; //5
$trainRow['track_units_remaining'] = $trackUnitsRemaining; //2
// $trainArrived = ($trackUnit == 0 || $trackUnit == $routeData['track_unit_count']);
$trainRow['status'] = ($trainArrived) ? 'ARRIVED' : 'ENROUTE';
$trainRow['date_last_used'] = date('Y-m-d H:i:s');
//$trainId = $trainModel->getTrainId($gamePlayerId);
$where = $trainModel->getAdapter()->quoteInto('id = ?', $trainData['id']);
$trainModel->update($trainRow, $where);
} else {
$trainRow = $trainData;
}
}
return $trainRow;
}
Train.php:
public function getTrain($gamePlayerId) {
$sql = $this->getAdapter()->quoteInto("SELECT gp.player_number, t.* FROM train t, game_player gp WHERE t.`game_player_id` = ?", $gamePlayerId)
. ' AND gp.id = t.game_player_id;';
$stmt = $this->getAdapter()->query($sql);
$train = $stmt->fetchAll();
return $train[0];
}
Route.php:
public function getPlayerRouteIdByCityIds($gamePlayerId, $cityId1, $cityId2) {
$resp = array('route_id' => nu开发者_StackOverflow社区ll, 'error_msg' => 'INVALID_ROUTE');
$where = $this->getAdapter()->quoteInto('(city_id_1 = ?', $cityId1)
. $this->getAdapter()->quoteInto(' AND city_id_2 = ?)', $cityId2)
. $this->getAdapter()->quoteInto(' OR (city_id_1 = ?', $cityId2)
. $this->getAdapter()->quoteInto(' AND city_id_2 = ?)', $cityId1);
$select = $this->select()->where($where);
$row = $this->fetchRow($select);
if($row) {
require_once 'PlayerRoute.php';
$playerRouteModel = new PlayerRoute();
if($playerRouteModel->isRouteOwned($row->id, $gamePlayerId)) {
$resp['route_id'] = $row->id;
$resp['error_msg'] = 'SUCCESS';
return $resp;
}
$resp['error_msg'] = 'ROUTE_NOT_OWNED';
}
return $resp;
}
public function getRouteByCityIds($cityId1, $cityId2) {
$db = $this->getAdapter();
$sql = 'SELECT * FROM route AS r'
. $db->quoteInto(' WHERE (r.city_id_1 = ?', $cityId1)
. $db->quoteInto(' AND r.city_id_2 = ?)', $cityId2)
. $db->quoteInto(' OR (r.city_id_1 = ?', $cityId2)
. $db->quoteInto(' AND r.city_id_2 = ?);', $cityId1);
$stmt = $db->query($sql);
$routeRow = array();
foreach($stmt as $row) {
$routeRow = $row;
}
if(count($routeRow) == 0) { return false; }
$sql = $db->quoteInto('SELECT track_unit_count FROM route_count WHERE id = ?', $routeRow['id']);
$stmt = $db->query($sql);
foreach($stmt as $row) {
$routeRow['track_unit_count'] = $row['track_unit_count'];
}
return $routeRow;
}
public function getRouteTravelDirection($startCityId, $endCityId) {
$routeData = $this->getRouteByCityIds($startCityId, $endCityId);
return ($routeData['city_id_1'] == $startCityId) ? '+' : '-';
}
PlayerRoute.php
public function isRouteOwned($routeId, $gamePlayerId) {
$where = $this->_db->quoteInto('route_id = ?', $routeId)
. $this->_db->quoteInto(' AND game_player_id = ?', $gamePlayerId);
$select = $this->select()->where($where);
$row = $this->fetchRow($select);
return (!$row) ? false : true;
}
I decided against submitting the FBJS/AJAX code sample. Maybe I'll submit that as another question. Thanks to anyone who can help me with this.
Modify my.conf to log long queries and set the time quite high (perhaps 5 seconds). If the performance problem is a mysql query you'll see it in the log.
精彩评论