• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Update about 1 million rows in MySQL table every 1 hour

#1
Hello! 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? Please, suggest a solution 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:

PHP Code:
function cron_rows_update() {
$this->members_model->rows_update();

 
This is the code from model:

PHP Code:
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_rating2); 
            }
            
            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_rating2); 
            }
            
            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_part32);

            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_hourFALSE);
                
$this->db->set("game_available_balance""game_available_balance + " $game_balance_in_hour_amountFALSE);
                
$this->db->set("rating"$rating_memberFALSE);
                
$this->db->set("game_rating_and_balance_update_last_time"'NOW()'FALSE);
                
$this->db->update("members");
            }
        }
    }
    return;

Reply


Messages In This Thread
Update about 1 million rows in MySQL table every 1 hour - by olegrain - 07-21-2020, 01:20 AM

Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2021 MyBB Group.