Huge Cpu Load/poor Performance Php Zend System

[saar62097]

Hey all

 

I am a self-taught AS3/Flex/AIR developer and I have built a system of touchscreen-kiosks running on AIR with remote content managment from an online Flex app.

 

This is the first time I have done server side stuff, so feel free to correct my question if needed.

 

Both the AIR apps and the Flex app connect to a mysql database for simple CRUD operations using php scripts and the Zend framework. The kiosks call the server every 30 seconds to update.

 

All these simple server side php scripts were auto-generated by Flash Builder's data wizard. I did the simple adjusments (gateway.php, config.ini, mysqli connection parameters) and deployed everything to the client's server. This is a a shared-server type.

 

Now, the system works. But it works slowly. Each create/read/update/delete operation works but I think it should be a lot faster.

Also, these operations put a lot of load on the server's cpu.

According to the hosting guys, this system opens 6 php-cgi proccesses that take up 40% of the server cpu power, and by doing so slows itself down and other sites hosted on that server.

 

My questions are:

 

First of all, is there a problem to begin with or is this performance expexted from the Zend framework?

Are the auto-generated scripts written by Flash Builder poorly written and can be optimized?

Can this kind of system stay on a shared-hosted-server? Should we move to a VPS server for better performance?

Is it posiible that if the tables I created on the mysql database with phpmyadmin weren't optimiatlly built that it would have this kind of impact on performance?

 

I don't know php or mysql.

Any kind of help will be much appriciated.

 

Saar

 

Here is the script written by Flash Builder: I havn't added anything - just changed the connection parameters.

 

 

<?php

 

class KiosksService {

 

var $username = "--------";

var $password = "--------";

var $server = "--------";

var $port = "3306";

var $databasename = "--------";

var $tablename = "kiosks";

 

var $connection;

 

/**

* The constructor initializes the connection to database. Everytime a request is

* received by Zend AMF, an instance of the service class is created and then the

* requested method is invoked.

*/

public function __construct() {

$this->connection = mysqli_connect(

$this->server,

$this->username,

$this->password,

$this->databasename,

$this->port

);

 

$this->throwExceptiononerror($this->connection);

}

 

/**

* Returns all the rows from the table.

*

* Add authroization or any logical checks for secure access to your data

*

* @return array

*/

public function getAllKiosks() {

 

$stmt = mysqli_prepare($this->connection, "SELECT * FROM $this->tablename");

$this->throwExceptiononerror();

mysqli_stmt_execute($stmt);

$this->throwExceptiononerror();

$rows = array();

mysqli_stmt_bind_result($stmt, $row->KioskID, $row->Station, $row->Branch, $row->Chain, $row->Pingdate, $row->Layout, $row->Online, $row->Clips, $row->Extra);

while (mysqli_stmt_fetch($stmt)) {

$row->Pingdate = new DateTime($row->Pingdate);

$rows[] = $row;

$row = new stdClass();

mysqli_stmt_bind_result($stmt, $row->KioskID, $row->Station, $row->Branch, $row->Chain, $row->Pingdate, $row->Layout, $row->Online, $row->Clips, $row->Extra);

}

mysqli_stmt_free_result($stmt);

mysqli_close($this->connection);

return $rows;

}

 

/**

* Returns the item corresponding to the value specified for the primary key.

*

* Add authorization or any logical checks for secure access to your data

*

*

* @return stdClass

*/

public function getKiosksByID($itemID) {

$stmt = mysqli_prepare($this->connection, "SELECT * FROM $this->tablename where KioskID=?");

$this->throwExceptiononerror();

mysqli_stmt_bind_param($stmt, 'i', $itemID);

$this->throwExceptiononerror();

mysqli_stmt_execute($stmt);

$this->throwExceptiononerror();

mysqli_stmt_bind_result($stmt, $row->KioskID, $row->Station, $row->Branch, $row->Chain, $row->Pingdate, $row->Layout, $row->Online, $row->Clips, $row->Extra);

if(mysqli_stmt_fetch($stmt)) {

$row->Pingdate = new DateTime($row->Pingdate);

return $row;

} else {

return null;

}

}

 

/**

* Returns the item corresponding to the value specified for the primary key.

*

* Add authorization or any logical checks for secure access to your data

*

*

* @return stdClass

*/

public function createKiosks($item) {

 

$stmt = mysqli_prepare($this->connection, "INSERT INTO $this->tablename (Station, Branch, Chain, Pingdate, Layout, Online, Clips, Extra) VALUES (?, ?, ?, ?, ?, ?, ?, ?)");

$this->throwExceptiononerror();

 

mysqli_stmt_bind_param($stmt, 'sssssisi', $item->Station, $item->Branch, $item->Chain, $item->Pingdate->toString('YYYY-MM-dd HH:mm:ss'), $item->Layout, $item->Online, $item->Clips, $item->Extra);

$this->throwExceptiononerror();

 

mysqli_stmt_execute($stmt);

$this->throwExceptiononerror();

 

$autoid = mysqli_stmt_insert_id($stmt);

 

mysqli_stmt_free_result($stmt);

mysqli_close($this->connection);

 

return $autoid;

}

 

/**

* Updates the passed item in the table.

*

* Add authorization or any logical checks for secure access to your data

*

* @param stdClass $item

* @return void

*/

public function updateKiosks($item) {

$stmt = mysqli_prepare($this->connection, "UPDATE $this->tablename SET Station=?, Branch=?, Chain=?, Pingdate=?, Layout=?, Online=?, Clips=?, Extra=? WHERE KioskID=?");

$this->throwExceptiononerror();

mysqli_stmt_bind_param($stmt, 'sssssisii', $item->Station, $item->Branch, $item->Chain, $item->Pingdate->toString('YYYY-MM-dd HH:mm:ss'), $item->Layout, $item->Online, $item->Clips, $item->Extra, $item->KioskID);

$this->throwExceptiononerror();

 

mysqli_stmt_execute($stmt);

$this->throwExceptiononerror();

mysqli_stmt_free_result($stmt);

mysqli_close($this->connection);

}

 

/**

* Deletes the item corresponding to the passed primary key value from

* the table.

*

* Add authorization or any logical checks for secure access to your data

*

*

* @return void

*/

public function deleteKiosks($itemID) {

$stmt = mysqli_prepare($this->connection, "DELETE FROM $this->tablename WHERE KioskID = ?");

$this->throwExceptiononerror();

mysqli_stmt_bind_param($stmt, 'i', $itemID);

mysqli_stmt_execute($stmt);

$this->throwExceptiononerror();

mysqli_stmt_free_result($stmt);

mysqli_close($this->connection);

}

 

 

/**

* Returns the number of rows in the table.

*

* Add authorization or any logical checks for secure access to your data

*

*

*/

public function count() {

$stmt = mysqli_prepare($this->connection, "SELECT COUNT(*) AS COUNT FROM $this->tablename");

$this->throwExceptiononerror();

 

mysqli_stmt_execute($stmt);

$this->throwExceptiononerror();

mysqli_stmt_bind_result($stmt, $rec_count);

$this->throwExceptiononerror();

mysqli_stmt_fetch($stmt);

$this->throwExceptiononerror();

mysqli_stmt_free_result($stmt);

mysqli_close($this->connection);

return $rec_count;

}

 

 

/**

* Returns $numItems rows starting from the $startIndex row from the

* table.

*

* Add authorization or any logical checks for secure access to your data

*

*

*

* @return array

*/

public function getKiosks_paged($startIndex, $numItems) {

$stmt = mysqli_prepare($this->connection, "SELECT * FROM $this->tablename LIMIT ?, ?");

$this->throwExceptiononerror();

mysqli_stmt_bind_param($stmt, 'ii', $startIndex, $numItems);

mysqli_stmt_execute($stmt);

$this->throwExceptiononerror();

$rows = array();

mysqli_stmt_bind_result($stmt, $row->KioskID, $row->Station, $row->Branch, $row->Chain, $row->Pingdate, $row->Layout, $row->Online, $row->Clips, $row->Extra);

while (mysqli_stmt_fetch($stmt)) {

$row->Pingdate = new DateTime($row->Pingdate);

$rows[] = $row;

$row = new stdClass();

mysqli_stmt_bind_result($stmt, $row->KioskID, $row->Station, $row->Branch, $row->Chain, $row->Pingdate, $row->Layout, $row->Online, $row->Clips, $row->Extra);

}

mysqli_stmt_free_result($stmt);

mysqli_close($this->connection);

return $rows;

}

/**

* Utility function to throw an exception if an error occurs

* while running a mysql command.

*/

private function throwExceptiononerror($link = null) {

if($link == null) {

$link = $this->connection;

}

if(mysqli_error($link)) {

$msg = mysqli_errno($link) . ": " . mysqli_error($link);

throw new Exception('MySQL Error - '. $msg);

}

}

}

 

?>

[Christian F.]

Please use the [code][/code] tags around your code, as it helps make both your post and your code a lot easier to read. Also, please don't mess around with the colours, especially not dark green and bold; It made your code virtually impossible to read.

 

As for your performance question: That's actually somewhere between hard and impossible to answer, at least without profiling the code properly. There could be any number of things that causes the slow down, everything from a badly constructed SQL query, missing indices in the DB, slow file system, slow HTTP request, to problems in your code caused by any number of issues that leads to poor performance.