Very high memory usage when migrating measurable

[Starker3]

We had another user that had a similar experience as report in #38
However, in this case there was at least an error that was output:
ERROR [2022-08-01 11:12:19] 348340 Fatal error encountered: /var/www/matomo/libs/Zend/Db/Statement/Pdo.php(292): Allowed memory of 6442450944 bytes exhausted (tried to allocate 8192 bytes) [Query: , CLI mode: 1]

The user needed to increase the allowed memory to all available memory (AFAIK 16gb at least) in order for the migration to complete.
I asked the user to send us the size of the log_ tables, since it seems that there is an array created and held in memory for LogActionMigration which might be kept for the entire Migration.
But the size of the tables for this database are not very large, so the used memory seems to be quite excessive:

+--------------------+----------------------------------------------------+------------+
| Database | Table | Size in MB |
+--------------------+----------------------------------------------------+------------+
| MatomoDBName | log_link_visit_action | 1418.98 |
| MatomoDBName | log_visit | 1310.73 |
| MatomoDBName | log_action | 12.03 |
| MatomoDBName | log_media | 2.80 |
| MatomoDBName | log_media_plays | 0.08 |
| MatomoDBName | log_form | 0.05 |
| MatomoDBName | log_abtesting | 0.05 |
| MatomoDBName | log_hsr | 0.05 |
| MatomoDBName | log_hsr_event | 0.05 |
| MatomoDBName | log_conversion | 0.05 |
| MatomoDBName | log_hsr_blob | 0.03 |
| MatomoDBName | log_hsr_site | 0.03 |
| MatomoDBName | log_form_page | 0.03 |
| MatomoDBName | log_profiling | 0.03 |
| MatomoDBName | log_conversion_item | 0.03 |
| MatomoDBName | log_funnel | 0.03 |
| MatomoDBName | log_form_field | 0.02 |
+--------------------+----------------------------------------------------+------------+

[sgiehl]

Could possibly help to change usages of foreach like here:
https://github.com/matomo-org/plugin-Migration/blob/4.x-dev/Migrations/BaseMigration.php#L40-L46
to a while / array_shift combination, as that is more memory friendly.

[skitoxe]

@Starker3 I have the same issues using this plugin migrating a measurable. I increased the ram of the machine just for the migration to 16GB but it still fails. It fails with two different outcomes.

1. If you run the migration with "memory_limit=-1" to give it unlimited memory. The kernel will kill it off.

[1420252.600778] oom-kill:constraint=CONSTRAINT_NONE,nodemask=(null),cpuset=/,mems_allowed=0,global_oom,task_memcg=/user.slice/user-12345.slice/session-49881.scope,task=php80,pid=812345,uid=0
[1420252.600789] Out of memory: Killed process 831359 (php80) total-vm:22579652kB, anon-rss:14536068kB, file-rss:0kB, shmem-rss:888kB, UID:0 pgtables:44748kB oom_score_adj:0
[1420253.375071] oom_reaper: reaped process 831359 (php80), now anon-rss:0kB, file-rss:0kB, shmem-rss:888kB

2. If you give it lets say memory_limit=12228M to give the OS some breathing space. Then PHP goes oom and dies.

PHP Fatal error:  Allowed memory size of 12821987328 bytes exhausted (tried to a                                                                         llocate 8160000 bytes) in /var/www/matomo/libs/Zend/Db/Statement/Pdo.php on line                                                                                          233
ERROR [2022-11-30 19:41:43] 835938  Fatal error encountered: /var/www/matomo/lib                                                                                         s/Zend/Db/Statement/Pdo.php(233): Allowed memory size of 12821987328 bytes exhau                                                                                         sted (tried to allocate 8160000 bytes) [Query: , CLI mode: 1]
Matomo encountered an error: Allowed memory size of 12821987328 bytes exhausted                                                                                          (tried to allocate 8160000 bytes) (which lead to: Error: array (
  'type' => 1,
  'message' => 'Allowed memory size of 12821987328 bytes exhausted (tried to all                                                                                         ocate 8160000 bytes)',
  'file' => '/var/www/matomo/libs/Zend/Db/Statement/Pdo.php',
  'line' => 233,
  'backtrace' => ' on /var/www/matomo/libs/Zend/Db/Statement/Pdo.php(233)

All this when trying to migrate a measurable with "4241620 visits".

As it stands it seems the only way around this is to increase the RAM of the machine to extreme amounts , then migrate and lower ram back down.

[skitoxe]

UPDATE: After increasing RAM to 32GB i managed to migrate the site in 2h13mins.