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

Replication Friendliness #14719

Open
RoyBellingan opened this issue Aug 1, 2019 · 3 comments

Comments

@RoyBellingan
Copy link

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

This comment has been minimized.

Copy link
Member

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

This comment has been minimized.

Copy link
Author

commented Aug 1, 2019

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

This comment has been minimized.

Copy link
Member

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
2 participants
You can’t perform that action at this time.