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

Jump to bottom

Live API can timeout on very large Piwik servers #6786

Closed
mattab opened this issue Dec 2, 2014 · 2 comments
Closed

Live API can timeout on very large Piwik servers #6786

mattab opened this issue Dec 2, 2014 · 2 comments
Assignees
@mattab
Labels
Bug For errors / faults / flaws / inconsistencies etc. c: Performance For when we could improve the performance / speed of Matomo.
Milestone
2.10.0

Comments

@mattab
Copy link
Member

mattab commented Dec 2, 2014

The goal of this issue is to investigate the Live API plugin in a high traffic environment, and make the Live plugin render fast at all times in such environment.

Here is an example long running query triggered by Live API:

SELECT sub.*
            FROM (

            SELECT
                log_inner.*
            FROM
                (
            SELECT
                log_visit.*
            FROM
                piwik_log_visit AS log_visit
                LEFT JOIN piwik_log_link_visit_action AS log_link_visit_action ON log_link_visit_action.idvisit = log_visit.idvisit
            WHERE
                ( log_visit.idsite in ('1')
                AND log_visit.visit_last_action_time >= '2014-10-02 00:00:00' )
                AND
                ( ( log_link_visit_action.idaction_url IS NOT NULL AND (log_link_visit_action.idaction_url <> '' OR log_link_visit_action.idaction_url = 0) ) )
            GROUP BY log_visit.idvisit
                ) AS log_inner
            ORDER BY
                idsite, visit_last_action_time DESC
                 LIMIT 0, 400
            ) AS sub
            GROUP BY sub.idvisit
            ORDER BY sub.visit_last_action_time DESC

On this Piwik instance, it was running for hours already. When I run this query on the demo, it takes 13 seconds. Ideally, creating the Visitor Log / Live API output should fall within 1-2 seconds.
@mattab mattab added Bug For errors / faults / flaws / inconsistencies etc. c: Performance For when we could improve the performance / speed of Matomo. labels Dec 2, 2014
@mattab mattab added this to the Piwik 2.10.0 milestone Dec 2, 2014
mattab added a commit that referenced this issue Dec 2, 2014
@mattab
Refs #6786 refactor inner query building. This code needs to be smart…
a7e0d07 
…er to avoid Mysql load too much data in memory, which is causing the performance problem
mattab added a commit that referenced this issue Dec 2, 2014
@mattab
Refs #6786 adding support for $limit parameter to the Segment query b…
32f371e 
…uilder.
mattab added a commit that referenced this issue Dec 2, 2014
@mattab
Refs #6786 Using the LIMIT feature of the segment query builder, let'…
6a29392 
…s see if tests still pass?
mattab added a commit that referenced this issue Dec 2, 2014
@mattab
Refs #6786 adding couple unit tests
8f84fb1
mattab added a commit that referenced this issue Dec 3, 2014
@mattab
Refs #6786 switch to forcing the inner group by which should hopefull…
8dda0d1 
…y increase performance by limiting amount of data selected in memory for the log_inner query
mattab added a commit that referenced this issue Dec 3, 2014
@mattab
Refs #6786 My whole point was not to GROUP BY in the inner query as i…
3a23dd7 
…t's slow to group BY before LIMITing
mattab added a commit that referenced this issue Dec 3, 2014
@mattab
Refs #6786 Fix tests
8864412
mattab added a commit that referenced this issue Dec 3, 2014
@mattab
Refs #6786 Don't throw as it's a valid case
6cb2bc0
mattab added a commit that referenced this issue Dec 3, 2014
@mattab
Refs #6786 starting to make sense, but still tests dont pass
4ea14e9
mattab added a commit that referenced this issue Dec 3, 2014
@mattab
Refs #6786 markQueryAsSelectAll()
abbf252
mattab added a commit that referenced this issue Dec 3, 2014
@mattab
Refs #6786 There is a possibility that the current codebase contains …
ec4d72d 
…a bug in the inner segment queries logic which is used to always group by idvisit but maybe in some cases this doesn't make sense or would create bugs in the data
mattab added a commit that referenced this issue Dec 3, 2014
@mattab
Refs #6786 missed from earlier commit
b359ac2
@mattab mattab self-assigned this Dec 3, 2014
@mattab mattab mentioned this issue Dec 5, 2014
Merged
@mattab
Copy link
Member Author

mattab commented Dec 6, 2014

Here is the same query as in description but fixed up after refactoring:

SELECT sub.*
            FROM (

            SELECT
                log_inner.*
            FROM
                (
            SELECT
                log_visit.*
            FROM
                piwik_log_visit AS log_visit
                LEFT JOIN piwik_log_link_visit_action AS log_link_visit_action ON log_link_visit_action.idvisit = log_visit.idvisit
            WHERE
                ( log_visit.idsite in ('1')
                AND log_visit.visit_last_action_time >= '2014-10-02 00:00:00' )
                AND
                ( ( log_link_visit_action.idaction_url IS NOT NULL AND (log_link_visit_action.idaction_url <> '' OR log_link_visit_action.idaction_url = 0) ) )
            ORDER BY
                idsite, visit_last_action_time DESC
                 LIMIT 0, 400
                ) AS log_inner
            ORDER BY
                idsite, visit_last_action_time DESC
                 LIMIT 0, 400
            ) AS sub
            GROUP BY sub.idvisit
            ORDER BY sub.visit_last_action_time DESC

It runs in less than 1.8s on the demo. This is at least 10x faster and much more at higher scale.

Actual commit post refactor that mostly fixed it a37254a (also the ORDER BY and LIMIT were forwarded to the inner query, although im not sure if this was really required)

I will close the issue but I'm not saying it's the end of Live plugin performance. Though clearly this was a real bug in the logic when datasets are huge.

@mattab
Copy link
Member Author

mattab commented Dec 6, 2014

Fixed in #6817

@mattab mattab closed this as completed Dec 6, 2014
@tsteur tsteur mentioned this issue Jan 12, 2016
Closed
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@mattab mattab

Labels
Bug For errors / faults / flaws / inconsistencies etc. c: Performance For when we could improve the performance / speed of Matomo.
Projects
None yet
Milestone
2.10.0
Development

No branches or pull requests
1 participant
@mattab