Improve the performance and decrease the memory usage when running the upgrade

[jonasraoni]

Describe the bug
At the issue #8929, the user had an issue with memory limit.
I think it's worth to add an ini_set('memory_limit', -1), at least for the upgrade, this should probably be also backported to at least OJS 3.3.

According to the user the installation has a 30GB database (must be probably the size of a .sql file) and more than 18K users.

Solution
Fix the upgrade scripts, which shouldn't be consuming so much memory.

What application are you using?
OJS 3.4rc3

[asmecher]

@jonasraoni, do you have a stack trace or exception indicating where the out-of-memory condition occurred? We should be processing data in batches, so it shouldn't be necessary to store lots of it in memory.

[jonasraoni]

Ah, I didn't mind about asking a stack trace for the user, PHP just shows the last executed line (which will probably be something inside a framework). I just got curious about what could be eating so much memory 🤔

We should be processing data in batches, so it shouldn't be necessary to store lots of it in memory.

Yeah, it's better to not download any data from the database, unless we really need to :)

[shantanu198713]

Hi @jonasraoni

I have already set the memory_limit from 2024MB to 4048MB in php.ini file but the same error appeared for the second time.

Error Log

2023-06-14 04:02:24 [848 entries removed]
2023-06-14 04:02:24 [migration: PKP\migration\upgrade\v3_4_0\I8060_UpdateUserLocalesDefaultToEmptyArrayFromNull]
2023-06-14 04:03:36 [migration: PKP\migration\upgrade\v3_4_0\I7245_UpdateUserLocaleStringToParsableJsonString]

Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 31457288 bytes) in F:\wamp64\www\ojs34\lib\pkp\lib\vendor\laravel\framework\src\Illuminate\Database\Connection.php on line 453

Call Stack:
    0.0001     392200   1. {main}() F:\wamp64\www\ojs34\tools\upgrade.php:0
    0.1296   15538512   2. PKP\cliTool\UpgradeTool->execute() F:\wamp64\www\ojs34\tools\upgrade.php:22
    0.1296   15538512   3. PKP\cliTool\UpgradeTool->upgrade() F:\wamp64\www\ojs34\lib\pkp\classes\cliTool\UpgradeTool.php:69
    0.1491   17097288   4. PKP\install\Installer->execute() F:\wamp64\www\ojs34\lib\pkp\classes\cliTool\UpgradeTool.php:96
    0.1538   17465808   5. PKP\install\Installer->executeInstaller() F:\wamp64\www\ojs34\lib\pkp\classes\install\Installer.php:209
 1407.6074   21910368   6. PKP\install\Installer->executeAction($action = ['type' => 'migration', 'file' => NULL, 'attr' => ['class' => 'PKP\\migration\\upgrade\\v3_4_0\\I7245_UpdateUserLocaleStringToParsableJsonString']]) F:\wamp64\www\ojs34\lib\pkp\classes\install\Installer.php:294
 1407.6182   21916336   7. PKP\migration\upgrade\v3_4_0\I7245_UpdateUserLocaleStringToParsableJsonString->up() F:\wamp64\www\ojs34\lib\pkp\classes\install\Installer.php:463
 1407.6183   21919848   8. Illuminate\Support\LazyCollection->each($callback = class Closure { virtual $closure = "$this->PKP\migration\upgrade\v3_4_0\{closure}", public $this = class PKP\migration\upgrade\v3_4_0\I7245_UpdateUserLocaleStringToParsableJsonString { protected $connection = NULL; public $withinTransaction = TRUE; protected array $_attributes = [...]; protected PKP\install\Installer $_installer = class APP\install\Upgrade { ... } }; public $parameter = ['$user' => '<required>'] }) F:\wamp64\www\ojs34\lib\pkp\classes\migration\upgrade\v3_4_0\I7245_UpdateUserLocaleStringToParsableJsonString.php:45
 1407.6185   21921568   9. Illuminate\Database\Connection->cursor($query = 'select `user_id`, `locales` from `users` where `locales` <> ?', $bindings = [0 => '[]'], $useReadPdo = TRUE) F:\wamp64\www\ojs34\lib\pkp\lib\vendor\laravel\framework\src\Illuminate\Collections\Traits\EnumeratesValues.php:234
 1407.6185   21922328  10. Illuminate\Database\Connection->run($query = 'select `user_id`, `locales` from `users` where `locales` <> ?', $bindings = [0 => '[]'], $callback = class Closure { virtual $closure = "$this->Illuminate\Database\{closure}", public $static = ['useReadPdo' => TRUE]; public $this = class Illuminate\Database\MySqlConnection { protected $pdo = class PDO { ... }; protected $readPdo = NULL; protected $database = 'ojs34'; protected $readWriteType = NULL; protected $tablePrefix = ''; protected $config = [...]; protected $reconnector = class Closure { virtual $closure = "$this->Illuminate\Database\{closure}", ... }; protected $queryGrammar = class Illuminate\Database\Query\Grammars\MySqlGrammar { ... }; protected $schemaGrammar = class Illuminate\Database\Schema\Grammars\MySqlGrammar { ... }; protected $postProcessor = class Illuminate\Database\Query\Processors\MySqlProcessor { ... }; protected $events = class Illuminate\Events\Dispatcher { ... }; protected $fetchMode = 5; protected $transactions = 0; protected $transactionsManager = class Illuminate\Database\DatabaseTransactionsManager { ... }; protected $recordsModified = TRUE; protected $readOnWriteConnection = FALSE; protected $queryLog = [...]; protected $loggingQueries = FALSE; protected $totalQueryDuration = 1404036.54; protected $queryDurationHandlers = [...]; protected $pretending = FALSE; protected $beforeExecutingCallbacks = [...]; protected $doctrineConnection = class Doctrine\DBAL\Connection { ... }; protected $doctrineTypeMappings = [...] }; public $parameter = ['$query' => '<required>', '$bindings' => '<required>'] }) F:\wamp64\www\ojs34\lib\pkp\lib\vendor\laravel\framework\src\Illuminate\Database\Connection.php:456
 1407.6185   21922328  11. Illuminate\Database\Connection->runQueryCallback($query = 'select `user_id`, `locales` from `users` where `locales` <> ?', $bindings = [0 => '[]'], $callback = class Closure { virtual $closure = "$this->Illuminate\Database\{closure}", public $static = ['useReadPdo' => TRUE]; public $this = class Illuminate\Database\MySqlConnection { protected $pdo = class PDO { ... }; protected $readPdo = NULL; protected $database = 'ojs34'; protected $readWriteType = NULL; protected $tablePrefix = ''; protected $config = [...]; protected $reconnector = class Closure { virtual $closure = "$this->Illuminate\Database\{closure}", ... }; protected $queryGrammar = class Illuminate\Database\Query\Grammars\MySqlGrammar { ... }; protected $schemaGrammar = class Illuminate\Database\Schema\Grammars\MySqlGrammar { ... }; protected $postProcessor = class Illuminate\Database\Query\Processors\MySqlProcessor { ... }; protected $events = class Illuminate\Events\Dispatcher { ... }; protected $fetchMode = 5; protected $transactions = 0; protected $transactionsManager = class Illuminate\Database\DatabaseTransactionsManager { ... }; protected $recordsModified = TRUE; protected $readOnWriteConnection = FALSE; protected $queryLog = [...]; protected $loggingQueries = FALSE; protected $totalQueryDuration = 1404036.54; protected $queryDurationHandlers = [...]; protected $pretending = FALSE; protected $beforeExecutingCallbacks = [...]; protected $doctrineConnection = class Doctrine\DBAL\Connection { ... }; protected $doctrineTypeMappings = [...] }; public $parameter = ['$query' => '<required>', '$bindings' => '<required>'] }) F:\wamp64\www\ojs34\lib\pkp\lib\vendor\laravel\framework\src\Illuminate\Database\Connection.php:720
 1407.6185   21922328  12. Illuminate\Database\Connection->Illuminate\Database\{closure:F:\wamp64\www\ojs34\lib\pkp\lib\vendor\laravel\framework\src\Illuminate\Database\Connection.php:435-456}($query = 'select `user_id`, `locales` from `users` where `locales` <> ?', $bindings = [0 => '[]']) F:\wamp64\www\ojs34\lib\pkp\lib\vendor\laravel\framework\src\Illuminate\Database\Connection.php:753
 1407.6187   21949136  13. PDOStatement->execute() F:\wamp64\www\ojs34\lib\pkp\lib\vendor\laravel\framework\src\Illuminate\Database\Connection.php:453

[jonasraoni]

Thanks for the log @shantanu198713 =]

@asmecher Here we can add a where('locales', '=', '')->update(['locales' => '[]']) before the loop, then cover the loop with a chunk, perhaps there are other places that need this 🤔

[asmecher]

@jonasraoni, that sounds good; can you put together a PR?

[shantanu198713]

Hi @jonasraoni

I have set memory_limit -1 in php.ini file and the upgrade is running but now getting a memory_limit error in EventLogLocalized. Please suggest to me if I can update anything.

Error log

2023-06-14 13:16:12 [migration: PKP\migration\upgrade\v3_4_0\I9039_DropDeprecatedFields]
2023-06-14 13:16:12 [migration: APP\migration\upgrade\v3_4_0\I8933_EventLogLocalized]

Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 4194312 bytes) in F:\wamp64\www\ojs34\lib\pkp\lib\vendor\laravel\framework\src\Illuminate\Database\Connection.php on line 421

Call Stack:
    0.0001     392200   1. {main}() F:\wamp64\www\ojs34\tools\upgrade.php:0
    0.2185   17750840   2. PKP\cliTool\UpgradeTool->execute() F:\wamp64\www\ojs34\tools\upgrade.php:22
    0.2185   17750840   3. PKP\cliTool\UpgradeTool->upgrade() F:\wamp64\www\ojs34\lib\pkp\classes\cliTool\UpgradeTool.php:69
    0.2486   19305312   4. PKP\install\Installer->execute() F:\wamp64\www\ojs34\lib\pkp\classes\cliTool\UpgradeTool.php:96
    0.3197   19698304   5. PKP\install\Installer->executeInstaller() F:\wamp64\www\ojs34\lib\pkp\classes\install\Installer.php:209
26251.7144   42050488   6. PKP\install\Installer->executeAction($action = ['type' => 'migration', 'file' => NULL, 'attr' => ['class' => 'APP\\migration\\upgrade\\v3_4_0\\I8933_EventLogLocalized']]) F:\wamp64\www\ojs34\lib\pkp\classes\install\Installer.php:294
26251.7317   42092760   7. PKP\migration\upgrade\v3_4_0\I8933_EventLogLocalized->up() F:\wamp64\www\ojs34\lib\pkp\classes\install\Installer.php:463
26285.9897   42109800   8. PKP\migration\upgrade\v3_4_0\I8933_EventLogLocalized->renameSettings() F:\wamp64\www\ojs34\lib\pkp\classes\migration\upgrade\v3_4_0\I8933_EventLogLocalized.php:64
26308.7491   52584200   9. Illuminate\Database\Query\Builder->pluck($column = 'log_id', $key = ???) F:\wamp64\www\ojs34\lib\pkp\classes\migration\upgrade\v3_4_0\I8933_EventLogLocalized.php:194
26308.7491   52584960  10. Illuminate\Database\Query\Builder->onceWithColumns($columns = [0 => 'log_id'], $callback = class Closure { virtual $closure = "$this->Illuminate\Database\Query\{closure}", public $this = class Illuminate\Database\Query\Builder { public $connection = class Illuminate\Database\MySqlConnection { ... }; public $grammar = class Illuminate\Database\Query\Grammars\MySqlGrammar { ... }; public $processor = class Illuminate\Database\Query\Processors\MySqlProcessor { ... }; public $bindings = [...]; public $aggregate = NULL; public $columns = [...]; public $distinct = FALSE; public $from = 'event_log'; public $indexHint = NULL; public $joins = NULL; public $wheres = [...]; public $groups = NULL; public $havings = NULL; public $orders = NULL; public $limit = NULL; public $offset = NULL; public $unions = NULL; public $unionLimit = NULL; public $unionOffset = NULL; public $unionOrders = NULL; public $lock = NULL; public $beforeQueryCallbacks = [...]; public $operators = [...]; public $bitwiseOperators = [...]; public $useWritePdo = FALSE } }) F:\wamp64\www\ojs34\lib\pkp\lib\vendor\laravel\framework\src\Illuminate\Database\Query\Builder.php:2922
26308.7491   52584960  11. Illuminate\Database\Query\Builder->Illuminate\Database\Query\{closure:F:\wamp64\www\ojs34\lib\pkp\lib\vendor\laravel\framework\src\Illuminate\Database\Query\Builder.php:2918-2922}() F:\wamp64\www\ojs34\lib\pkp\lib\vendor\laravel\framework\src\Illuminate\Database\Query\Builder.php:3230
26308.7491   52584960  12. Illuminate\Database\Query\Builder->runSelect() F:\wamp64\www\ojs34\lib\pkp\lib\vendor\laravel\framework\src\Illuminate\Database\Query\Builder.php:2920
26308.7492   52585432  13. Illuminate\Database\Connection->select($query = 'select `log_id` from `event_log` where `event_type` = ?', $bindings = [0 => 1342177296], $useReadPdo = TRUE) F:\wamp64\www\ojs34\lib\pkp\lib\vendor\laravel\framework\src\Illuminate\Database\Query\Builder.php:2706
26308.7492   52586192  14. Illuminate\Database\Connection->run($query = 'select `log_id` from `event_log` where `event_type` = ?', $bindings = [0 => 1342177296], $callback = class Closure { virtual $closure = "$this->Illuminate\Database\{closure}", public $static = ['useReadPdo' => TRUE]; public $this = class Illuminate\Database\MySqlConnection { protected $pdo = class PDO { ... }; protected $readPdo = NULL; protected $database = 'ojs34'; protected $readWriteType = NULL; protected $tablePrefix = ''; protected $config = [...]; protected $reconnector = class Closure { virtual $closure = "$this->Illuminate\Database\{closure}", ... }; protected $queryGrammar = class Illuminate\Database\Query\Grammars\MySqlGrammar { ... }; protected $schemaGrammar = class Illuminate\Database\Schema\Grammars\MySqlGrammar { ... }; protected $postProcessor = class Illuminate\Database\Query\Processors\MySqlProcessor { ... }; protected $events = class Illuminate\Events\Dispatcher { ... }; protected $fetchMode = 5; protected $transactions = 0; protected $transactionsManager = class Illuminate\Database\DatabaseTransactionsManager { ... }; protected $recordsModified = TRUE; protected $readOnWriteConnection = FALSE; protected $queryLog = [...]; protected $loggingQueries = FALSE; protected $totalQueryDuration = 24229210.930001; protected $queryDurationHandlers = [...]; protected $pretending = FALSE; protected $beforeExecutingCallbacks = [...]; protected $doctrineConnection = class Doctrine\DBAL\Connection { ... }; protected $doctrineTypeMappings = [...] }; public $parameter = ['$query' => '<required>', '$bindings' => '<required>'] }) F:\wamp64\www\ojs34\lib\pkp\lib\vendor\laravel\framework\src\Illuminate\Database\Connection.php:422
26308.7493   52586192  15. Illuminate\Database\Connection->runQueryCallback($query = 'select `log_id` from `event_log` where `event_type` = ?', $bindings = [0 => 1342177296], $callback = class Closure { virtual $closure = "$this->Illuminate\Database\{closure}", public $static = ['useReadPdo' => TRUE]; public $this = class Illuminate\Database\MySqlConnection { protected $pdo = class PDO { ... }; protected $readPdo = NULL; protected $database = 'ojs34'; protected $readWriteType = NULL; protected $tablePrefix = ''; protected $config = [...]; protected $reconnector = class Closure { virtual $closure = "$this->Illuminate\Database\{closure}", ... }; protected $queryGrammar = class Illuminate\Database\Query\Grammars\MySqlGrammar { ... }; protected $schemaGrammar = class Illuminate\Database\Schema\Grammars\MySqlGrammar { ... }; protected $postProcessor = class Illuminate\Database\Query\Processors\MySqlProcessor { ... }; protected $events = class Illuminate\Events\Dispatcher { ... }; protected $fetchMode = 5; protected $transactions = 0; protected $transactionsManager = class Illuminate\Database\DatabaseTransactionsManager { ... }; protected $recordsModified = TRUE; protected $readOnWriteConnection = FALSE; protected $queryLog = [...]; protected $loggingQueries = FALSE; protected $totalQueryDuration = 24229210.930001; protected $queryDurationHandlers = [...]; protected $pretending = FALSE; protected $beforeExecutingCallbacks = [...]; protected $doctrineConnection = class Doctrine\DBAL\Connection { ... }; protected $doctrineTypeMappings = [...] }; public $parameter = ['$query' => '<required>', '$bindings' => '<required>'] }) F:\wamp64\www\ojs34\lib\pkp\lib\vendor\laravel\framework\src\Illuminate\Database\Connection.php:720
26308.7493   52586192  16. Illuminate\Database\Connection->Illuminate\Database\{closure:F:\wamp64\www\ojs34\lib\pkp\lib\vendor\laravel\framework\src\Illuminate\Database\Connection.php:405-422}($query = 'select `log_id` from `event_log` where `event_type` = ?', $bindings = [0 => 1342177296]) F:\wamp64\www\ojs34\lib\pkp\lib\vendor\laravel\framework\src\Illuminate\Database\Connection.php:753
26308.8993   55025576  17. PDOStatement->fetchAll($mode = ???) F:\wamp64\www\ojs34\lib\pkp\lib\vendor\laravel\framework\src\Illuminate\Database\Connection.php:421

My php.ini file setting

[shantanu198713]

Unable to upgrade due to memory_limit error. I am getting the same error after changing the DB for the third time.

And the second memory_limit error is mentioned above.

[jonasraoni]

Thanks! I've just checked the migrations and there's a lot of points that can be improved to decrease the memory usage, I'll add a bunch of fixes.