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

Huge query on Matomo hundreds of thousands of rows on repeat #17376

Closed
StevieKay90 opened this issue Mar 22, 2021 · 7 comments
Closed

Huge query on Matomo hundreds of thousands of rows on repeat #17376

StevieKay90 opened this issue Mar 22, 2021 · 7 comments
Labels
duplicate For issues that already existed in our issue tracker and were reported previously.

Comments

@StevieKay90
Copy link

We have an issue where the following query, seemingly on repeat is pushing our db requirements for Matamo through the roof;

SELECT visit_last_action_time , visit_first_action_time , idvisitor , idvisit , user_id , visit_exit_idaction_url , visit_exit_idaction_name , visitor_returning , visitor_seconds_since_first , visitor_seconds_since_order , visitor_count_visits , visit_goal_buyer , location_country , location_region , location_city , location_latitude , location_longitude , referer_name , referer_keyword , referer_type , idsite , profilable , visit_entry_idaction_url

we have disabled all plugins and this query still runs ad nauesum

Expected Behavior

Massive query should not be on repeat

Current Behavior

At present we required a lot of extra resource to run our Matomo instance. On RDS we notice a vast query which was seemingly on repeat over and over;

image (2)

image (4)

Possible Solution

The issue does not lie with plugins, we removed all non essential plugins and the query is still appearing
We are going to try and cache this query on a proxy in front of the db's

Steps to Reproduce (for Bugs)

Regular setup, it happens continually on our system

Context

Not sure what else to put, other than we don't know why this is happening!
We are trying to pinpoint it but its not plugins, maybe its on the tracker.php ?

Your Environment

matomo_system_check (2).txt
@StevieKay90 StevieKay90 added the Potential Bug Something that might be a bug, but needs validation and confirmation it can be reproduced. label Mar 22, 2021
@tsteur
Copy link
Member

tsteur commented Mar 22, 2021

Hi @StevieKay90 could you have a look at #16904 and see if that's maybe the same problem?

@StevieKay90
Copy link
Author

StevieKay90 commented Mar 22, 2021 •
edited

Thanks for the quick reply @tsteur !

That looks interesting, over the last year i've noticed this;
a lot of user_ids share the same visitorid (i have no idea why), is there another way to solve this?

image

This might have been the issue i was having with redis a few weeks ago

@tsteur
Copy link
Member

tsteur commented Mar 22, 2021

Are you using Matomo 4? There by default this should not be the case unless you changed enable_userid_overwrites_visitorid config option. If you aren't on Matomo 4, then I would recommend upgrading and this might fix the issue already.

#16904 (comment) could help too

@StevieKay90
Copy link
Author

StevieKay90 commented Mar 22, 2021 •
edited

@tsteur that screenshot was from matomo 3 (i took it about 5 months ago when i noticed) - i've actually just checked and we don't get this anymore (multi visitor_id per user_id) . We've recently upgraded to matomo 4 and that is where we found this issue we are running into with the big query.

We've added in the enable_userid_overwrites_visitorid=1 now but it doesn't seem to do much. What can we do?

we track lots of client websites and they all use set user_id. is it someway our clients have implemented the tags that could be tripping us up?

@tsteur
Copy link
Member

tsteur commented Mar 22, 2021

If you didn't have the problem before, then enable_userid_overwrites_visitorid =0 would actually help you in that case likely. It might be worth a try at least temporary to see if it fixes something (might need to run it for an hour or so).

Regarding the implementation it's really hard to say without knowing details etc unfortunately.

@StevieKay90
Copy link
Author

hi @tsteur enable_userid_overwrites_visitorid =0 seems to have done the trick, a massive thanks :)

It was either that or we also noticed the following at the bottom of the config so we removed it.

[QueuedTracking]
notify_queue_threshold_single_queue = 250000

We seem to be on an even keel now!

@tsteur tsteur added duplicate For issues that already existed in our issue tracker and were reported previously. and removed Potential Bug Something that might be a bug, but needs validation and confirmation it can be reproduced. labels Mar 24, 2021
@tsteur
Copy link
Member

tsteur commented Mar 24, 2021

Glad to know this helped. I'll close this for now as a duplicate of #16904 :)

It should definitely not have improved by notify_queue_threshold_single_queue.

@tsteur tsteur closed this as completed Mar 24, 2021
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
duplicate For issues that already existed in our issue tracker and were reported previously.
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
2 participants
@tsteur @StevieKay90