I'm sure that there's somewhere goes wrong with my code .. have looking around but find no clue for this... hopefully there's someone able to give a hand here in stack.. My website basically designed under Opencart framework, and now it's only for user to come into my website only view article. Each user enter my website from facebook or other social media will lead to a page which only perform 2 database query. And both of the query are not even join(I research that there's somehow slow down when joining table) other table. ok lets straight into the point. Few days ago my website got sudden break down and i'm checking from google analytics it's around 1000 active user within 5 minutes at that time. And i login into server and check on the processlist, i found out that there's hundred of sleep process stuck in the list.
| Id | User | Host | db | Command | Time | State | Info |
| 28573 | goodycom_user | localhost | goodycom_db | Sleep | 9 | | |
| 28577 | goodycom_user | localhost | goodycom_db | Sleep | 9 | | |
| 28627 | goodycom_user | localhost | goodycom_db | Sleep | 8 | | |
| 28778 | goodycom_user | localhost | goodycom_db | Sleep | 6 | | |
| 28919 | goodycom_user | localhost | goodycom_db | Sleep | 5 | | |
| 28931 | goodycom_user | localhost | goodycom_db | Sleep | 4 | | |
| 29026 | goodycom_user | localhost | goodycom_db | Sleep | 3 | | |
| 29088 | goodycom_user | localhost | goodycom_db | Sleep | 3 | | |
| 29106 | goodycom_user | localhost | goodycom_db | Sleep | 2 | | |
| 29108 | goodycom_user | localhost | goodycom_db | Sleep | 2 | | |
| 29113 | goodycom_user | localhost | goodycom_db | Sleep | 2 | | |
| 29132 | goodycom_user | localhost | goodycom_db | Sleep | 2 | | |
| 29152 | goodycom_user | localhost | goodycom_db | Sleep | 2 | | |
| 29177 | goodycom_user | localhost | goodycom_db | Sleep | 2 | | |
| 29272 | goodycom_user | localhost | goodycom_db | Sleep | 0 | | |
| 29299 | goodycom_user | localhost | goodycom_db | Sleep | 0 | | |
| 29308 | goodycom_user | localhost | goodycom_db | Sleep | 0 | | |
| 29310 | goodycom_user | localhost | goodycom_db | Sleep | 0 | | |
| 29311 | goodycom_user | localhost | goodycom_db | Sleep | 0 | | |
| 29313 | goodycom_user | localhost | goodycom_db | Sleep | 0 | | |
| 29314 | goodycom_user | localhost | goodycom_db | Sleep | 0 | | |
| 29315 | goodycom_user | localhost | goodycom_db | Sleep | 0 | | |
| 29317 | goodycom_user | localhost | goodycom_db | Sleep | 0 | | |
| 29318 | goodycom_user | localhost | goodycom_db | Sleep | 0 | | |
| 29319 | goodycom_user | localhost | goodycom_db | Sleep | 0 | | |
| 29320 | goodycom_user | localhost | goodycom_db | Sleep | 0 | | |
| 29321 | goodycom_user | localhost | goodycom_db | Sleep | 0 | | |
| 29322 | goodycom_user | localhost | goodycom_db | Sleep | 0 | | |
| 29323 | goodycom_user | localhost | goodycom_db | Sleep | 0 | | |
| 29324 | goodycom_user | localhost | goodycom_db | Sleep | 0 | | |
| 29325 | goodycom_user | localhost | goodycom_db | Sleep | 0 | | |
| 29326 | goodycom_user | localhost | goodycom_db | Sleep | 0 | | |
| 29327 | goodycom_user | localhost | goodycom_db | Sleep | 0 | | |
| 29328 | goodycom_user | localhost | goodycom_db | Sleep | 0 | | |
| 29329 | goodycom_user | localhost | goodycom_db | Sleep | 0 | | |
| 29330 | goodycom_user | localhost | goodycom_db | Sleep | 0 | | |
| 29331 | goodycom_user | localhost | goodycom_db | Sleep | 0 | | |
| 29332 | goodycom_user | localhost | goodycom_db | Sleep | 0 | | |
| 29333 | goodycom_user | localhost | goodycom_db | Sleep | 0 | | |
| 29334 | goodycom_user | localhost | goodycom_db | Sleep | 0 | | |
| 29335 | goodycom_user | localhost | goodycom_db | Sleep | 0 | |
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
From here may notice that all the time of sleep is below 10, which i can't set to kill process if there's other user in my website that is not viewing the content. (For testing purpose i have disable all login functions and so on and left only view article, if i kill process, some calculation at backend will be kill as well)
Here is my mysql query
public function getArticle($article_id){
$query = $this->db->query("SELECT * FROM article where article_id = '" . (int)$article_id. "'");
return $query->row;
}
public function getArticleCategories(){
$query = $this->db->query("SELECT * FROM article_category ORDER BY name asc");
return $query->rows;
}
and the class of db query..
final class MySQLi {
private $link;
public function __construct($hostname, $username, $password, $database, $port = '3306') {
$this->link = new \mysqli($hostname, $username, $password, $database, $port);
if ($this->link->connect_error) {
trigger_error('Could not make a database link (' . $this->link->connect_errno . ') ' . $this->link->connect_error);
exit();
}
$this->link->set_charset("utf8");
$this->link->query("SET SQL_MODE = ''");
}
public function query($sql) {
$query = $this->link->query($sql);
if (!$this->link->errno) {
if ($query instanceof \mysqli_result) {
$data = array();
while ($row = $query->fetch_assoc()) {
$data[] = $row;
}
$result = new \stdClass();
$result->num_rows = $query->num_rows;
$result->row = isset($data[0]) ? $data[0] : array();
$result->rows = $data;
$query->close();
return $result;
} else {
return true;
}
} else {
trigger_error('Error: ' . $this->link->error . '<br />Error No: ' . $this->link->errno . '<br />' . $sql);
}
}
public function escape($value) {
return $this->link->real_escape_string($value);
}
public function countAffected() {
return $this->link->affected_rows;
}
public function getLastId() {
return $this->link->insert_id;
}
public function __destruct() {
$this->link->close();
}
}
which remain the original one from opencart.
My network guy recommend me to have litespeed install and replace apache to overcome this. Hooray~ after installing 15 days trial litespeed, my website finally up and go smoothly. But something goes wrong again after few minutes, i enabled back the login function and i notice that all the user that login able to view other user's information due to the cache or what i dont know.. and finally i got my network guy disable the cache for user login but what you guess.. the user's image manager(each user got their own directory and not accessible by other user) being mix up .. few seconds i'm watching "A"'s directory.. after few seconds it jump to "B"'s directory when i refresh. In the end i'm using cookies to bypass the user_id which still being cache by the litespeed.. And now .. today, the website got down again... and my boss request that if i'm able to modify the script to help some.. doing research still.. but still find no help.. sorry for the broken english.. hope anyone here able to help on this ..
Thanks..
