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

Real time map and Visitor Log may be slow on a very large traffic site #12722

Closed
mattab opened this issue Apr 12, 2018 · 9 comments · Fixed by #12756
Closed

Real time map and Visitor Log may be slow on a very large traffic site #12722

mattab opened this issue Apr 12, 2018 · 9 comments · Fixed by #12756
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.
Milestone
3.6.0

Comments

@mattab
Copy link
Member

mattab commented Apr 12, 2018 •
edited

On a site with 100,000 pageviews and 1M events per day, the Real time map is very slow to load. The slow SQL query is:

SELECT sub.* FROM ( 
 SELECT log_visit.* 
 FROM log_visit AS log_visit 
 WHERE log_visit.idsite in ('28') 
      AND log_visit.visit_last_action_time > '1969-12-31 23:59:59' AND log_visit.visit_last_action_time >= '2018-03-14 08:05:48' 
 ORDER BY idsite DESC, visit_last_action_time DESC 
 LIMIT 0, 100 ) 
AS sub 
GROUP BY sub.idvisit 
ORDER BY sub.visit_last_action_time DESC LIMIT 100

It would be great if we could find a way to make the Real time map load faster.

The exact same query is triggered from the Visitor Log for the same day/website.
@mattab mattab added the c: Performance For when we could improve the performance / speed of Matomo. label Apr 12, 2018
@mattab mattab added this to the 3.6.0 milestone Apr 12, 2018
@mattab mattab changed the title Real time map is slow on a very large traffic site Real time map and Visitor Log may be slow on a very large traffic site Apr 12, 2018
@fdellwing
Copy link
Contributor

fdellwing commented Apr 12, 2018 •
edited

Can anybody explain this?

AND log_visit.visit_last_action_time > '1969-12-31 23:59:59' AND log_visit.visit_last_action_time >= '2018-03-14 08:05:48'

My understanding is, that the query will first check for every visit that happened after time 0 and than afterwards for everything from current time. So it will check every row 2 times, but only rows with second check true will be included.

@tsteur
Copy link
Member

tsteur commented Apr 12, 2018

Yeah the time range is huge!

@mattab mattab modified the milestones: 3.6.0, 3.5.0 Apr 12, 2018
@sgiehl
Copy link
Member

sgiehl commented Apr 20, 2018

The reason for that is imho located in https://github.com/matomo-org/matomo/blob/3.x-dev/plugins/UserCountryMap/javascripts/realtime-map.js#L151
the -1 given as minTimestamp is interpreted as timestamp (as it's not empty), which results in 1969-12-31 23:59:59. Setting it to 0 instead of -1 should remove the additional where

@tsteur
Copy link
Member

tsteur commented Apr 21, 2018

Good find 👍

@sgiehl sgiehl mentioned this issue Apr 21, 2018
Merged
@sgiehl
Copy link
Member

sgiehl commented Apr 21, 2018

I've created a PR for that. But I'm not sure if it will improve the performance of the real time map much...

@mattab
Copy link
Member Author

mattab commented Apr 23, 2018

Will mark as closed for now as we have at least fixed the obvious issue. I would re-open another issue then in case the performance problems persist

@mattab mattab closed this as completed Apr 23, 2018
@tsteur
Copy link
Member

tsteur commented Apr 23, 2018

I'll reopen it right now as it is not properly fixed. I could create a new issue instead but there is no point in it.

@tsteur tsteur reopened this Apr 23, 2018
tsteur added a commit that referenced this issue Apr 23, 2018
@tsteur
fix #12722
a2ee7d0 
Only look at last 3 days for real time map. In theory even only the last 1-2 days is enough since it is called real time so for sure we wouldn't need to include anything older than 24 hours.
@tsteur tsteur mentioned this issue Apr 23, 2018
Merged
mattab pushed a commit that referenced this issue Apr 23, 2018
@tsteur @mattab
fix #12722 (#12756)
6c3ed18 
Only look at last 3 days for real time map. In theory even only the last 1-2 days is enough since it is called real time so for sure we wouldn't need to include anything older than 24 hours.
mattab added a commit that referenced this issue Apr 23, 2018
@mattab
Revert "fix #12722 (#12756)"
52da45a 
This reverts commit 6c3ed18.
mattab added a commit that referenced this issue Apr 23, 2018
@mattab
Revert "Show information in visitor profile if dataset was "truncated…
2dbb1dd 
…"" (#12761)

* Revert "Fix syntax vor 'SHOW COLUMNS FROM' (#12759)"

This reverts commit f69c1f6.

* Revert "fix #12722 (#12756)"

This reverts commit 6c3ed18.

* Revert "When setting or resetting User ID, do not update the Visitor ID in the first party cookie (#12742)"

This reverts commit fdb161b.

* Revert "Show information in visitor profile if dataset was "truncated" (#12376)"

This reverts commit 14ba6a4.
@Findus23 Findus23 reopened this Apr 23, 2018
@mattab mattab modified the milestones: 3.5.0, 3.6.0 Apr 23, 2018
@mattab
Copy link
Member Author

mattab commented Jun 28, 2018

@Findus23 you re-opened the issue, did you experience it again or have some more info maybe?

@Findus23
Copy link
Member

@mattab Thanks for the info.
I have no other explaination than having misclicked the button as I have nothing to say to this issue.

@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 Aug 28, 2018
InfinityVoid pushed a commit to InfinityVoid/matomo that referenced this issue Oct 11, 2018
@tsteur @InfinityVoid
fix matomo-org#12722 (matomo-org#12756)
e967587 
Only look at last 3 days for real time map. In theory even only the last 1-2 days is enough since it is called real time so for sure we wouldn't need to include anything older than 24 hours.
InfinityVoid pushed a commit to InfinityVoid/matomo that referenced this issue Oct 11, 2018
@mattab @InfinityVoid
Revert "Show information in visitor profile if dataset was "truncated…
0a81d73 
…"" (matomo-org#12761)

* Revert "Fix syntax vor 'SHOW COLUMNS FROM' (matomo-org#12759)"

This reverts commit f69c1f6.

* Revert "fix matomo-org#12722 (matomo-org#12756)"

This reverts commit 6c3ed18.

* Revert "When setting or resetting User ID, do not update the Visitor ID in the first party cookie (matomo-org#12742)"

This reverts commit fdb161b.

* Revert "Show information in visitor profile if dataset was "truncated" (matomo-org#12376)"

This reverts commit 14ba6a4.
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. not-in-changelog For issues or pull requests that should not be included in our release changelog on matomo.org.
Projects
None yet
Milestone
3.6.0
Development

Successfully merging a pull request may close this issue.

Improve performance for real time map matomo-org/matomo
5 participants
@tsteur @mattab @sgiehl @Findus23 @fdellwing