Skip to content

Run OPTIMIZE TABLE on each table in a separate SQL query to work better with replication #14719

@RoyBellingan

Description

@RoyBellingan

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;

Metadata

Metadata

Assignees

Labels

Help wantedBeginner friendly issues or issues where we'd highly appreciate community's help and involvement.c: PerformanceFor when we could improve the performance / speed of Matomo.

Type

No type

Projects

No projects

Milestone

Relationships

None yet

Development

No branches or pull requests

Issue actions