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

Funnel reports for month period takes ages to generate #17385

Closed
nelhefni opened this issue Mar 24, 2021 · 1 comment
Closed

Funnel reports for month period takes ages to generate #17385

nelhefni opened this issue Mar 24, 2021 · 1 comment
Labels
answered For when a question was asked and we referred to forum or answered it.

Comments

@nelhefni
Copy link

nelhefni commented Mar 24, 2021

After updating an existing Funnel to add a new step, the next cron job will try to generate past reports.

My current settings:
rearchive_reports_in_past_last_n_months = last1

Meaning, the cron job will try to generate all past reports since February 1st.
For my Funnel, each daily report since February 1st took only a couple of minutes to generate successfully, and each weekly report took around 10 minutes. But it seems that the monthly report for February took 27 hours!

Here is an extract of the logs for the same cron job instance:

INFO [2021-03-22 00:49:46] 107  Archived website id 1, period = week, date = 2021-02-22, segment = '', plugin = Funnels, report = 31, 95089 visits found. 
Time elapsed: 627.893s
INFO [2021-03-22 01:03:50] 107  Archived website id 1, period = week, date = 2021-02-15, segment = '', plugin = Funnels, report = 31, 103042 visits found.
Time elapsed: 717.295s
INFO [2021-03-22 01:18:47] 107  Archived website id 1, period = week, date = 2021-02-08, segment = '', plugin = Funnels, report = 31, 105727 visits found.
Time elapsed: 762.520
INFO [2021-03-22 01:35:01] 107  Archived website id 1, period = week, date = 2021-02-01, segment = '', plugin = Funnels, report = 31, 114578 visits found.
Time elapsed: 828.300s
INFO [2021-03-23 04:40:39] 107  Archived website id 1, period = month, date = 2021-02-01, segment = '', plugin = Funnels, report = 31, 418436 visits found.
Time elapsed: 97522.738s

You can see that the monthly report took 97522 seconds, while each weekly report took between 700 and 800 seconds.

We deployed performance insight on AWS RDS to try to monitor the queries.
There is actually no query that took more than 1 second, they are all very fast.

Here are some of those fast queries:


INSERT INTO matomo_logtmpsegment4caf174c1c52723c9f299eaeabd6e9b2 (idvisit)
    SELECT
            distinct log_visit.idvisit as idvisit
    FROM
            matomo_log_visit AS log_visit
    WHERE
            ( log_visit.visit_last_action_time >= '2021-02-18 13:00:00'
            AND log_visit.visit_last_action_time <= '2021-02-19 12:59:59'
            AND log_visit.idsite IN ('1') )
            AND
              ( log_visit.visitor_returning = '0' )
                      ORDER BY
                          log_visit.idvisit ASC

SELECT  /*+ MAX_EXECUTION_TIME(7200000) */ 
        log_action1.name AS `label`, 
    count(distinct log_link_visit_action.idvisit) AS `2`, 
    count(distinct log_link_visit_action.idvisitor) AS `1`, 
    count(*) AS `3`, 
    AVG(log_link_visit_action.product_price) AS `avg_price_viewed`
    FROM
            matomo_logtmpsegmentf4938b7b1ce8d76fac6ed880e4f827af AS logtmpsegmentf4938b7b1ce8d76fac6ed880e4f827af INNER JOIN matomo_log_link_visit_action AS log_link_visit_action ON log_link_visit_action.idvisit = logtmpsegmentf4938b7b1ce8d76fac6ed880e4f827af.idvisit LEFT JOIN matomo_log_action AS log_action1 ON log_action1.idaction = log_link_visit_action.idaction_product_sku
    WHERE
            log_link_visit_action.idaction_product_sku is not null
    GROUP BY
            log_action1.name
		
SELECT  /*+ MAX_EXECUTION_TIME(7200000) */ 
        log_action1.name AS `label`, 
    count(distinct log_link_visit_action.idvisit) AS `2`, 
    count(distinct log_link_visit_action.idvisitor) AS `1`, 
    count(*) AS `3`, 
    AVG(log_link_visit_action.product_price) AS `avg_price_viewed`
    FROM
            matomo_logtmpsegmenta04727f7f3b247334d7731c5b6a8cc96 AS logtmpsegmenta04727f7f3b247334d7731c5b6a8cc96 INNER JOIN matomo_log_link_visit_action AS log_link_visit_action ON log_link_visit_action.idvisit = logtmpsegmenta04727f7f3b247334d7731c5b6a8cc96.idvisit LEFT JOIN matomo_log_action AS log_action1 ON log_action1.idaction = log_link_visit_action.idaction_product_cat4
    WHERE
            log_link_visit_action.idaction_product_cat4 is not null
    GROUP BY
            log_action1.name

...

The process seems to loop on those queries forever, simply changing the date in log_visit.visit_last_action_time and log_visit.visit_last_action_time and the name of the temporary table.
Could you please assess what could be the root cause of this issue?

Expected Behavior

As it took around 10 minutes to generate the report for each week of February, I would expect the monthly report for February to take less than an hour to generate.

Current Behavior

It took 27 hours to generate the monthly report for February.

Your Environment

We are using Matomo 4.2.1 and Funnel 4.0.5.

@nelhefni nelhefni added the Potential Bug Something that might be a bug, but needs validation and confirmation it can be reproduced. label Mar 24, 2021
@diosmosis
Copy link
Member

Hi @nelhefni , thank you for creating this issue. In this issue tracker we only manage issues around Matomo core. For premium feature related enquiries please email our support at shop@innocraft.com Thanks again @nelhefni and sorry for the inconvenience.

@sgiehl sgiehl added answered For when a question was asked and we referred to forum or answered it. and removed Potential Bug Something that might be a bug, but needs validation and confirmation it can be reproduced. labels Apr 28, 2021
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
answered For when a question was asked and we referred to forum or answered it.
Projects
None yet
Development

No branches or pull requests

3 participants