Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

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

Closed
RoyBellingan opened this issue Aug 1, 2019 · 4 comments · Fixed by #18032
Closed
Assignees
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

Comments

@RoyBellingan
Copy link

RoyBellingan commented Aug 1, 2019

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;

@tsteur
Copy link
Member

tsteur commented Aug 1, 2019

@RoyBellingan you could also disable [General] enable_sql_optimize_queries = 0 in the config/config.ini.php just btw. Did you verify it improves things when only executing one table at a time?

@RoyBellingan
Copy link
Author

Thank @tsteur I will give a look on this option.

Verified no, but as a general rule the replication hang until the currunt query beeing replicated is not finished.
So yes, splitting in smaller will lead always to some delay, but smaller.
What I can try is if having
https://mariadb.com/kb/en/library/replication-and-binary-log-system-variables/#slave_parallel_mode
Optimistic can help in such case whiteout doing any change in the code...

@tsteur
Copy link
Member

tsteur commented Aug 1, 2019

@RoyBellingan I think a fix be quite easy to simply iterate over an array here https://github.com/matomo-org/matomo/blob/3.12.0-b1/core/Db.php#L459 and execute each optimize table individually. This should be all that is needed to do here (if there's more to do we might change prioritisation)

@mattab mattab added the c: Performance For when we could improve the performance / speed of Matomo. label Jan 21, 2020
@mattab mattab changed the title Replication Friendliness Run OPTIMIZE TABLE on each table in a separate SQL query to work better with replication Jan 21, 2020
@mattab mattab added this to the 4.2.0 milestone Jan 21, 2020
@tsteur tsteur added the Help wanted Beginner friendly issues or issues where we'd highly appreciate community's help and involvement. label Jul 22, 2021
@bx80 bx80 self-assigned this Sep 19, 2021
@bx80 bx80 mentioned this issue Sep 20, 2021
11 tasks
@RoyBellingan
Copy link
Author

Thank you (Sorry I totally forget about this )

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
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.
Projects
None yet
Development

Successfully merging a pull request may close this issue.

4 participants