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

Caching id actions in general cache for faster segmented archive SQL queries #8861

Merged
merged 10 commits into from Sep 29, 2015
Merged

Caching id actions in general cache for faster segmented archive SQL queries #8861

merged 10 commits into from Sep 29, 2015

Conversation

mattab
Copy link
Member

@mattab mattab commented Sep 28, 2015

How to enable

To enable, set: enable_segments_subquery_cache = 1 under [General] in config/config.ini.php

Tweak cache

; 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

@mattab mattab added c: Performance For when we could improve the performance / speed of Matomo. Pull Request WIP Indicates the current pull request is still work in progress and not ready yet for a review. not-in-changelog For issues or pull requests that should not be included in our release changelog on matomo.org. labels Sep 28, 2015
@mattab mattab added this to the 2.15.0 milestone Sep 28, 2015
@mattab
Copy link
Member Author

mattab commented Sep 28, 2015

TODOs

  • add a test that checks the cache is actually hit when cache is enabled
  • check why build is failing on CI but passing locally
  • think about cache lifetime value: make it configurable
  • add feature to not cache when there are more than N idaction returned, eg. 100,000
    • 100,000 should fit for most max_allowed_packet setting values
  • test on dev
  • tests on real world server
  • create follow up issue: maybe we should enable the subquery cache by default in Piwik (Maybe targeted for 3.0.0?)

fixes #8850 cc @quba

@mattab mattab mentioned this pull request Sep 28, 2015
Closed
@mattab
Copy link
Member Author

mattab commented Sep 29, 2015

Logging

Added some logging for cache hits / misses, simply looks like this:

$ cat tmp/logs/piwik.log  | grep cache

DEBUG VisitsSummary[2015-09-29 00:09:11] Segment subquery cache SAVE (for 'xyzzzz' and SQL 'SELECT idaction FROM piwik_log_action WHERE ( name NOT LIKE CONCAT('%', ?, '%')  AND type = 1 )')'
DEBUG VisitsSummary[2015-09-29 00:09:11] Segment subquery cache HIT (for 'xyzzzz' and SQL 'SELECT idaction FROM piwik_log_action WHERE ( name NOT LIKE CONCAT('%', ?, '%')  AND type = 1 ))
DEBUG Goals[2015-09-29 00:09:11] Segment subquery cache HIT (for 'xyzzzz' and SQL 'SELECT idaction FROM piwik_log_action WHERE ( name NOT LIKE CONCAT('%', ?, '%')  AND type = 1 ))
DEBUG Goals[2015-09-29 00:09:11] Segment subquery cache HIT (for 'xyzzzz' and SQL 'SELECT idaction FROM piwik_log_action WHERE ( name NOT LIKE CONCAT('%', ?, '%')  AND type = 1 ))
DEBUG Goals[2015-09-29 00:09:11] Segment subquery cache HIT (for 'xyzzzz' and SQL 'SELECT idaction FROM piwik_log_action WHERE ( name NOT LIKE CONCAT('%', ?, '%')  AND type = 1 ))
DEBUG Bandwidth[2015-09-29 00:09:11] Segment subquery cache HIT (for 'xyzzzz' and SQL 'SELECT idaction FROM piwik_log_action WHERE ( name NOT LIKE CONCAT('%', ?, '%')  AND type = 1 ))
DEBUG Actions[2015-09-29 00:09:11] Segment subquery cache HIT (for 'xyzzzz' and SQL 'SELECT idaction FROM piwik_log_action WHERE ( name NOT LIKE CONCAT('%', ?, '%')  AND type = 1 ))
DEBUG VisitsSummary[2015-09-29 00:09:25] Segment subquery cache HIT (for 'xyzzzz' and SQL 'SELECT idaction FROM piwik_log_action WHERE ( name NOT LIKE CONCAT('%', ?, '%')  AND type = 1 ))
DEBUG VisitsSummary[2015-09-29 00:09:25] Segment subquery cache HIT (for 'xyzzzz' and SQL 'SELECT idaction FROM piwik_log_action WHERE ( name NOT LIKE CONCAT('%', ?, '%')  AND type = 1 ))
DEBUG Goals[2015-09-29 00:09:25] Segment subquery cache HIT (for 'xyzzzz' and SQL 'SELECT idaction FROM piwik_log_action WHERE ( name NOT LIKE CONCAT('%', ?, '%')  AND type = 1 ))

or when there are more than 100,000 IDs returned and the cache is not updated:

DEBUG DevicesDetection[2015-09-29 01:06:31] [4aecf] Segment subquery cache SKIPPED SAVE (too many IDs returned by subquery: 354100 ids)'

mattab pushed a commit that referenced this pull request Sep 29, 2015
Merge pull request #8861 from piwik/8850
5ce6703 
Caching id actions in general cache for faster segmented archive SQL queries
@mattab mattab merged commit 5ce6703 into master Sep 29, 2015
@mattab
Copy link
Member Author

mattab commented Sep 29, 2015

Merged, will be included in 2.15.0-b14

@mattab mattab mentioned this pull request Sep 29, 2015
Closed
2 tasks
@mattab
Copy link
Member Author

mattab commented Sep 29, 2015

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

diosmosis
diosmosis reviewed Sep 29, 2015
View reviewed changes
core/Tracker/TableLogAction/Cache.php
$this->isEnabled = (bool)Config::getInstance()->General['enable_segments_subquery_cache'];
$this->limitActionIds = Config::getInstance()->General['segments_subquery_cache_limit'];
$this->lifetime = Config::getInstance()->General['segments_subquery_cache_ttl'];
$this->logger = StaticContainer::get('Psr\Log\LoggerInterface');
Copy link
Member

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

If a class has dependencies, it should be stored in DI. This class should be stored in DI.

mattab added a commit that referenced this pull request Sep 30, 2015
@mattab
Remove lazy flush refs #8861
04627a4
@mattab mattab deleted the 8850 branch October 2, 2015 04:14
@mattab mattab mentioned this pull request Oct 9, 2015
Closed
@mattab mattab mentioned this pull request Oct 25, 2019
Open
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Reviewers
No reviews
Assignees
No one assigned
Labels
c: Performance For when we could improve the performance / speed of Matomo. not-in-changelog For issues or pull requests that should not be included in our release changelog on matomo.org. Pull Request WIP Indicates the current pull request is still work in progress and not ready yet for a review.
Projects
None yet
Milestone
2.15.0
Development

Successfully merging this pull request may close these issues.

None yet
2 participants
@mattab @diosmosis