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.

Sign up for GitHub

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

Jump to bottom

Enable segment subquery cache (queries with Contains/Does not contain on log_action) #8867

Closed
2 tasks
mattab opened this issue Sep 29, 2015 · 4 comments
Closed
2 tasks

Enable segment subquery cache (queries with Contains/Does not contain on log_action) #8867

mattab opened this issue Sep 29, 2015 · 4 comments
Labels
c: Performance For when we could improve the performance / speed of Matomo. Enhancement For new feature suggestions that enhance Matomo's capabilities or add a new report, new API etc. not-in-changelog For issues or pull requests that should not be included in our release changelog on matomo.org.
Milestone
Backlog (Help wan...

Comments

@mattab
Copy link
Member

mattab commented Sep 29, 2015 •
edited

In #8861 we introduced a mechanism to cache some of the subqueries done in Piwik archiving process. These speedups could technically bring up to 50% speed improvement in some particular cases. It is un-expected that the cache should make the execution time slower, but it could occur in some cases.

[General]
; whether to enable subquery cache for Custom Segment archiving queries
enable_segments_subquery_cache = 1
; Any segment subquery that matches more than segments_subquery_cache_limit IDs will not be cached,
; and the original subquery executed instead.
segments_subquery_cache_limit  = 100000
; TTL: Time to live for cache files, in seconds. Default to 60 minutes
segments_subquery_cache_ttl  = 3600

Steps:

  • Verify the limit setting is set to safe value (was initially set to 100,000)
  • Enable by default for all users in global.ini.php
@mattab mattab added Enhancement For new feature suggestions that enhance Matomo's capabilities or add a new report, new API etc. c: Performance For when we could improve the performance / speed of Matomo. labels Sep 29, 2015
@mattab mattab added this to the Short term milestone Sep 29, 2015
This was referenced Sep 29, 2015
Merged
Closed
@mattab mattab mentioned this issue Jan 30, 2018
Closed
@mattab
Copy link
Member Author

mattab commented Jun 7, 2019

This is a a persistent issue in production, Matomo Archiving is often slow or often fails for some segments that include "Page URL" or "Action" criteria. Could we enable this setting by default?

@mattab
Copy link
Member Author

mattab commented Jun 7, 2019

Problem is that for some cases, enabling this setting actually makes archiving slower overall. We end up this slow SQL query eg. SELECT idaction FROM log_action WHERE ( name LIKE CONCAT('example.com/page', '%') AND type = 1 ) which take 30min or more.

So we cannot just enable this feature as it may not always help and rather make things worse. Requires more investigation.

@tsteur
Copy link
Member

tsteur commented Jun 18, 2019

Maybe #14535 is interesting too?

@mattab
Copy link
Member Author

mattab commented Oct 17, 2019

now that we have awesome cached SQL queries for querying logs in archiving, we don't need to enable the subquery cache anymore. Ideally we would even remove completely the feature from the codebase!

@mattab mattab closed this as completed Oct 17, 2019
@mattab mattab mentioned this issue Oct 25, 2019
Open
@mattab mattab added the not-in-changelog For issues or pull requests that should not be included in our release changelog on matomo.org. label Oct 25, 2019
@tsteur tsteur mentioned this issue Jul 28, 2022
Open
@tsteur tsteur mentioned this issue Oct 21, 2022
Closed
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
c: Performance For when we could improve the performance / speed of Matomo. Enhancement For new feature suggestions that enhance Matomo's capabilities or add a new report, new API etc. not-in-changelog For issues or pull requests that should not be included in our release changelog on matomo.org.
Projects
None yet
Milestone
Backlog (Help wanted)
Development

No branches or pull requests
2 participants
@tsteur @mattab