@StevieKay90 opened this Issue on March 22nd 2021

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


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

@tsteur commented on March 22nd 2021 Member

Hi @StevieKay90 could you have a look at https://github.com/matomo-org/matomo/issues/16904 and see if that's maybe the same problem?

@StevieKay90 commented on March 22nd 2021

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?


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

@tsteur commented on March 22nd 2021 Member

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.

https://github.com/matomo-org/matomo/issues/16904#issuecomment-789273870 could help too

@StevieKay90 commented on March 22nd 2021

@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 commented on March 22nd 2021 Member

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 commented on March 23rd 2021

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.

notify_queue_threshold_single_queue = 250000

We seem to be on an even keel now!

@tsteur commented on March 24th 2021 Member

Glad to know this helped. I'll close this for now as a duplicate of https://github.com/matomo-org/matomo/issues/16904 :)

It should definitely not have improved by notify_queue_threshold_single_queue.

This Issue was closed on March 24th 2021
Powered by GitHub Issue Mirror