Run OPTIMIZE TABLE on each table in a separate SQL query to work better with replication #14719
Labels
c: Performance
For when we could improve the performance / speed of Matomo.
Help wanted
Beginner friendly issues or issues where we'd highly appreciate community's help and involvement.
Milestone
Tonight One of our server started to lag due to matomo starting a
OPTIMIZE TABLE archive_numeric_* and archive_blob_*
Of all the table (in a single query), that took on main DB almost 90min (and would have taken a similar amount on the slave).
A quick solution is to iterate over and perform 1 query per table, It will still hang on the bigger one, but is a much more reasonable delay.
Another solution can be to also set a different
SET SESSION gtid_domain_id=1
https://mariadb.com/kb/en/library/parallel-replication/
https://dev.mysql.com/doc/refman/5.7/en/replication-gtids.html
So those maintenance job will run on a side thread, and real time ingestion will keep running.
Code wise is probably just a few lines.
This should be an option that will be enabled (if needed).
In case is a big installation (the one that suffer of that problem) the DBA / Admin will probably know what to do and what we are talking about (we just have to leave a few link to documentation).
If you are interested In this second proposal I can try to write some code example.
But again code wise is just a matter of
SET SESSION gtid_domain_id=XYZ;
The text was updated successfully, but these errors were encountered: