Update about 1 million rows in MySQL table every 1 hour

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_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;
} 


By the looks of things you are executing (at least want to) one million + 1 query every hour. That's a lot. Depending on what you wan't to do with your data you need to rethink your structure.

1. Fetching/calculating only when needed
2. Run all calculations in SQL

By the looks of things you are executing (at least want to) one million + 1 query every hour. That's a lot. Depending on what you wan't to do with your data you need to rethink your structure.

1. Fetching/calculating only when needed
2. Run all calculations in SQL

Thank's for answer. If I run all calculations in SQL for example:

PHP Code:

$this->db->set("game_available_balance", "game_available_balance + " . $game_balance_in_hour_amount, FALSE; 


is this SQL query will be faster than if I make calculations in php?

And another question: if will use batch update, is it more faster than foreach? If yes, can you write an example of batch update for my case?

By running calculation in SQL, I mean you need to skip your foreach completely and do all math in said SQL instead. Only passing in $currency_numbers_after_dot, $game_currency_percentage_max, $game_currency_speed_in_hour to the query and let it do everything for you.

Batch update are a third option, didn't think of that one. You can find an example on batch update in the user guide. It will be faster as you get 10 000 instead of 1 000 000 queries (per default). But not as fast as running it with one query.
https://codeigniter.com/userguide3/datab...ating-data

As you expect more than 1 000 000 users, you need to plan ahead. As it will just get slower every time.

By the looks of things you are executing (at least want to) one million + 1 query every hour. That's a lot. Depending on what you wan't to do with your data you need to rethink your structure.

1. Fetching/calculating only when needed
2. Run all calculations in SQL

Thank's for answer. If I run all calculations in SQL for example:

PHP Code:

$this->db->set("game_available_balance", "game_available_balance + " . $game_balance_in_hour_amount, FALSE; 


is this SQL query will be faster than if I make calculations in php?

And another question: if will use batch update, is it more faster than foreach? If yes, can you write an example of batch update for my case?

I’m not actually going to answer your question about the framework but just wanted to mention that for each loops captures all the data into memory. That would be a very bad thing with large data. I believe a while loop would save you on memory issues. While loop should go line by line resulting in lower memory usage.

@olegrain,

As a suggestion, you might want to consider what your database configurations are set to and whether or not you may need more memory/space. Also, you might want to verify that your update query is efficient. You might need to do some database maintenance as well.

@olegrain,

I used Cron on a daily basis to download and convert three XLS Spreadsheets to MySql Database Tables. This task used to take over a minute to complete... also the task used to frequently fail because of 100% CPU loading sometimes necessitating a reboot

After numerous attempts at refactoring eventually the same task now completes in just over ten seconds and now runs every hour!

The drastic time reduction was due to the frequent use of PHP flush(). Try giving the flush command a whirl and see if it makes a difference.