@nelhefni opened this Issue on March 24th 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.

@diosmosis commented on March 24th 2021 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.

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