@mattab opened this Issue on September 29th 2015 Member

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.

; 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


  • [ ] 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 commented on June 7th 2019 Member

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 commented on June 7th 2019 Member

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 commented on June 18th 2019 Member
Powered by GitHub Issue Mirror