Segments containing log_action.name (for example page_url) contains are very slow #19581
Labels
c: Performance
For when we could improve the performance / speed of Matomo.
Milestone
Comments
tsteur
added
the
c: Performance
|
It might not be too hard to test the performance benefits of using a segment <-> action link table, something like the following on a large dataset with a complex segment could be tested with and without using the link table to compare performance. The trade off would be efficiently matching new actions created by the tracker to existing segments. Perhaps if we could do this in code then it might just be a single read (get the segment definitions) and a single write (bulk insert into the link table). -- Migration
CREATE TABLE log_link_segment_action (
idaction bigint(10) UNSIGNED,
idsegment bigint(10) UNSIGNED,
PRIMARY KEY (idaction, idsegment)
);-- Migration or when a new segment is added (the where clause is the segment definition)
INSERT INTO log_link_segment_action
(SELECT idaction, 1 AS idsegement FROM log_action WHERE ( name LIKE CONCAT('%', 'njw', '%') AND type = 10 ));// When a new log_action record is added by the tracker
$segments = Piwik\Plugins\SegmentEditor\AAPI\API::getInstance()->getAll(1);
foreach ($segments as $s) {
if (matchSegmentInCode($s['definition], $action){
// INSERT INTO log_link_segment_action (idaction, idsegment) VALUES ($s[id], $idaction);
}
}-- Analytic queries using join instead of expensive contains query
SELECT * FROM log_link_visit_action va
LEFT JOIN log_link_segment_action sa ON sa.idaction = va.idaction_url
WHERE sa.idsegment = 1; |
11 tasks
11 tasks
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
refs #8850 and #8867
When someone creates a segment like
page_url contains '%...%'orpage_title contains '%...%'orevent_name contains '%...%'then these kind of queries are very slow as it requires a full table scan onlog_action. They result in a where clause for example like this( ( log_link_visit_action.idaction_url IN (SELECT idaction FROM log_action WHERE ( name LIKE CONCAT('%', 'njw', '%') AND type = 10 )) ) ). With a lot of data inlog_actiontable these queries can be extremely slow, cause a lot of CPU usage etc.As it's one of Matomo's biggest bottleneck, we have investigated a few times in the past whether we can optimise this query but couldn't find any solution.
In #8867 there was a config
enable_segments_subquery_cachecreated but this doesn't help all that much as the cache might become outdated (it caches the data for 1 hour) and the cache re-usage would be limited during that hour as we already usetemporary tableswhen archiving to only needing to execute this query once.I'm now creating this issue again in case there are other ways to make this faster. Like maybe we can maintain MySQL tables for each such segment definition so that we not only write
log_actionentries inlog_actionbut also eg in a tablelog_action_pageurl_contains_fooand we reuse the same log_action IDs etc in that table. This way we wouldn't need to run thecontainsquery at all but could change it to join that table instead. This of course might cause more writes during tracking and not sure this is beneficial or worth it. There might be also other ideas.The text was updated successfully, but these errors were encountered: