Hi @MyIcecream. could you check how many entries are in the table archive_invalidations? And also if the is an entry in the options table with the name ReArchiveList and maybe how big that option_value is.

Hi, Thanks for replying: Do you mean these?
mysql> select count() from matomo_archive_invalidations;
+----------+
| count() |
+----------+
| 40187578 |
+----------+
mysql> select max(length(option_value)) from matomo_option;
+---------------------------+
| max(length(option_value)) |
+---------------------------+
| 5033211 |
+---------------------------+

Seems like there are quite too many invalidation records. Might be caused be the high number of websites.
@diosmosis do you think it would be safe to clean the table and maybe the option and restart the archiving?

If I empty this table, will I lose data? such as all domains， just reset the archive?

I guess cleaning the table matomo_archive_invalidations, might only cause that some archives aren't triggered correctly. But that can be fixed by invalidating the archives manually later if needed.

What should I do？

Stop any archiving processes. Afterwards try running a TRUNCATE TABLE matomo_archive_invalidations and then trigger the archiving again.

This seems similar to this issue: #17474 caused by updating from 3.X to one of the later 4.X's that had upgrade fixes for certain bugs in earlier 4.X releases. Truncating archive_invalidations and clearing ReArchiveList may solve the issue.

SELECT LENGTH(option_value) FROM matomo_option WHERE option_name = 'ReArchiveList';
+----------------------+
| LENGTH(option_value) |
+----------------------+
| 117 |
+----------------------+
SELECT option_value FROM matomo_option WHERE option_name = 'ReArchiveList';
+-----------------------------------------------------------------------------------------------------------------------+
| option_value |
+-----------------------------------------------------------------------------------------------------------------------+
| a:1:{i:0;s:99:"{"idSites":"all","pluginName":"VisitFrequency","report":null,"startDate":1606089600,"segment":null}";} |
+-----------------------------------------------------------------------------------------------------------------------+

Do you mean I can only execute these sql?
mysql> DELETE FROM matomo_option WHERE option_name = 'ReArchiveList';
mysql> TRUNCATE TABLE matomo_archive_invalidations;

@MyIcecream yes, those two queries look right.

It worked, thank you all @diosmosis @sgiehl

Glad to hear it worked! I'll close the issue.

This issue has been mentioned on Matomo forums. There might be relevant details there:

https://forum.matomo.org/t/matomo-on-premise-scaling/45262/4