I use Codeigniter 3.1.11 and have a question. I need to update about 1 million (in future will be much more) rows in MySQL table every 1 hour by Cron. But problem is if I update more than about 200-300 rows using this code, my server CPU is 100% loaded and table is stopping update after about 200-300 rows. And I even have to restart the PHP on my server to get the server back to normal.
What I do wrong?
How to do this task correctly, so that the query to the database is executed quickly and there is no heavy load on the server.
This is the code from controller:
function cron_rows_update() {
$this->members_model->rows_update();
}
This is the code from model:
function rows_update() {
$currency_numbers_after_dot = $this->currencies_model->get_currency('ONE', 'My currencty')['numbers_after_dot'];
$game_currency_percentage_max = $this->settings_model->get_settings_details('game_rating_percentage_max')['value'];
$game_currency_speed_in_hour = $this->settings_model->get_settings_details('game_currency_speed_in_hour')['value'];
$this->db->from('members');
$query = $this->db->get();
if($query->num_rows() > 0) {
foreach($query->result_array() as $row) {
$game_total_balance = round($row['game_vault_balance'] + $row['game_available_balance'], $currency_numbers_after_dot);
// Game Rating Calculate
// Rating Part1
// Rating Part1_1
if ($row['game_vault_balance'] == '0') {
$rating_part1_1 = '0';
}
if ($row['game_vault_balance'] > '0' AND $row['game_vault_balance'] < '20') {
$rating_part1_1 = '0.1';
}
if ($row['game_vault_balance'] > '20'
AND $row['game_vault_balance'] < '2000') {
$max_game_vault_balance = '2000';
$percent = floor($row['game_vault_balance'] * 100 / $max_game_vault_balance);
$additional_rating = '0.05' * $percent / 100;
$rating_part1_1 = round('0.1' + $additional_rating, 2);
}
if ($row['game_vault_balance'] >= '2000') {
$rating_part1_1 = '0.15';
}
// Rating Part1_2
if ($game_total_balance == '0') {
$PER_part1_2 = '0';
}
if ($game_total_balance > '0' AND $game_total_balance < '20') {
$rating_part1_2 = '0.1';
}
if ($game_total_balance > '20' AND $game_total_balance < '2000') {
$max_game_total_balance = '2000';
$percent = floor($game_total_balance * 100 / $max_game_total_balance);
$additional_rating = '0.05' * $percent / 100;
$rating_part1_2 = round('0.1' + $additional_rating, 2);
}
if ($game_total_balance >= '2000') {
$rating_part1_2 = '0.15';
}
// Rating part1_3
$rating_part1_3 = '0';
// Rating part1_4
$PER_part1_4 = '0';
// Rating part2
$PER_part2 = '0';
// Rating part3
$PER_part3 = '0';
// Calculate all rating
$rating = round($rating_part1_1 + $rating_part1_2 + $rating_part1_3 + $rating_part1_4 + $rating_part2 + $rating_part3, 2);
if ($rating <= '1') {
$rating_member = $rating;
}
if ($rating > '1') {
$rating_member = floor($rating);
}
// Game balance calculate
$amount_from_game_vault_in_hour = $game_currency_speed_in_hour / '100' * $row['game_vault_balance'];
$new_balance_in_hour = ($game_currency_percentage_max / '100') * $row['rating'] * $amount_from_game_vault_in_hour;
$game_balance_in_hour_amount = $amount_from_game_vault_in_hour + $new_balance_in_hour;
// Update row in members table
if ($game_total_balance > '0') {
$this->db->where("UserID", $row['UserID']);
$this->db->set("game_vault_balance", "game_vault_balance - " . $amount_from_game_vault_in_hour, FALSE);
$this->db->set("game_available_balance", "game_available_balance + " . $game_balance_in_hour_amount, FALSE);
$this->db->set("rating", $rating_member, FALSE);
$this->db->set("game_rating_and_balance_update_last_time", 'NOW()', FALSE);
$this->db->update("members");
}
}
}
return;
}
Advertisement
Answer
Apart from the fact that your code is a little bit confusing ($PER_part2 and $PER_part3 are unused) i would do the following:
The script will not work with result_array for more than 1 million iterations.
The reason is, result_array stores all data in an array – and sooner or later you’ll get a memory limit problem.
In order to avoid this you have to use unbuffered_row.
This method returns a single result row without prefetching the whole result in memory.
Take a look at their documentation here. (section unbuffered_row)
The next thing i would change are your if blocks – i would use the if/else syntax here. (it isn’t that big of a difference but considering your amount of rows – it might helps)
Also, i outsourced the block where you calculate your rating twice with the same logic behind.
Basically the following should work:
function rows_update()
{
$currency_numbers_after_dot = $this->currencies_model->get_currency('ONE', 'My currencty')['numbers_after_dot'];
$game_currency_percentage_max = $this->settings_model->get_settings_details('game_rating_percentage_max')['value'];
$game_currency_speed_in_hour = $this->settings_model->get_settings_details('game_currency_speed_in_hour')['value'];
$this->db->from('members');
$query = $this->db->get();
$arrUpdateBatchData = [];
while ($row = $query->unbuffered_row('array'))
{
$game_total_balance = round($row['game_vault_balance'] + $row['game_available_balance'], $currency_numbers_after_dot);
if ($game_total_balance > 0) {
// Game Rating Calculate
// Rating Part1
// Rating Part1_1
$rating_part1_1 = $this->getRatingPart($row['game_vault_balance']);
$rating_part1_2 = $this->getRatingPart($game_total_balance);
// Rating part1_3
$rating_part1_3 = 0;
// Rating part1_4
$rating_part1_4 = 0;
// Rating part2
$PER_part2 = '0';
// Rating part3
$PER_part3 = '0';
// Calculate all rating
$rating = round($rating_part1_1 + $rating_part1_2 + $rating_part1_3 + $rating_part1_4 + $rating_part2 + $rating_part3, 2);
if ($rating <= 1) {
$rating_member = $rating;
}
elseif ($rating > 1) {
$rating_member = floor($rating);
}
// Game balance calculate
$amount_from_game_vault_in_hour = $game_currency_speed_in_hour / '100' * $row['game_vault_balance'];
$new_balance_in_hour = ($game_currency_percentage_max / '100') * $row['rating'] * $amount_from_game_vault_in_hour;
$game_balance_in_hour_amount = $amount_from_game_vault_in_hour + $new_balance_in_hour;
$arrUpdateData = [
'UserID' => $row['UserID'],
'game_vault_balance' => ($row['game_vault_balance'] - $amount_from_game_vault_in_hour),
'game_available_balance' => ($row['game_available_balance'] - $game_balance_in_hour_amount),
'rating' => $rating_member,
'game_rating_and_balance_update_last_time' => date('Y-m-d H:i:s')
];
$arrUpdateBatchData[] = $arrUpdateData;
}
if (count($arrUpdateBatchData) > 500)
{
$this->db->update_batch('members', $arrUpdateBatchData, 'UserID');
$arrUpdateBatchData = [];
}
}
//update last items
if (count($arrUpdateBatchData) > 0)
{
$this->db->update_batch('members', $arrUpdateBatchData, 'UserID');
$arrUpdateBatchData = [];
}
return;
}
function getRatingPart($val)
{
if ($val == 0) {
$rating_part = 0;
}
elseif ($val > 0 AND $val < 20)
{
$rating_part = '0.1';
}
elseif ($val > 20 AND $val < 2000)
{
$max_game_vault_balance = 2000;
$percent = floor($val * 100 / $max_game_vault_balance);
$additional_rating = 0.05 * $percent / 100;
$rating_part = round(0.1 + $additional_rating, 2);
}
else {
$rating_part = 0.15;
}
return $rating_part;
}