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

Segment archiving for "Year" period takes very long for new or edited segments #18524

Closed
Starker3 opened this issue Dec 20, 2021 · 10 comments
Closed

Segment archiving for "Year" period takes very long for new or edited segments #18524

Starker3 opened this issue Dec 20, 2021 · 10 comments
Labels
Bug For errors / faults / flaws / inconsistencies etc. c: Performance For when we could improve the performance / speed of Matomo. worksforme The issue cannot be reproduced and things work as intended.
Milestone
For Prioritization

Comments

@Starker3
Copy link
Contributor

While troubleshooting an issue with segment archiving, I noticed that "Year" periods for newly created or recently edited segments take extremely long to process when compared to the time it takes the "Month" periods to process.
(A few seconds for "Month" periods vs almost 30 minutes for "Year" period)

In this example I created a simple test segment where I set the condition to Action Type is pageviews with process_new_segments_from = "editLast93" in the config file.
In the core:archive output it shows that the correct dates are being processed and everything seems fine for the month periods:

INFO [2021-12-19 22:16:16] 618228  Archived website id 1, period = month, date = 2021-12-01, segment = 'actionType==pageviews', 16 visits found. Time elapsed: 7.455s
INFO [2021-12-19 22:16:29] 618228  Archived website id 1, period = month, date = 2021-11-01, segment = 'actionType==pageviews', 25 visits found. Time elapsed: 1.291s
INFO [2021-12-19 22:16:41] 618228  Archived website id 1, period = month, date = 2021-10-01, segment = 'actionType==pageviews', 22 visits found. Time elapsed: 1.547s

But when it comes to the "Year" period it takes an exceptionally long time when compared to the "Month" periods which all took only a few seconds:

INFO [2021-12-19 22:24:20] 618228  Archived website id 1, period = year, date = 2021-01-01, segment = 'actionType==pageviews', 63 visits found. Time elapsed: 417.794s

While trying to reproduce the issue I I noticed that it didn't happen when creating a segment with the same conditions as previously processed (I.e. a duplicate segment), but creating a new segment with a different definition results in the same issue when using editLastN or lastN in the config file.

Here are two archive logs, first with process_new_segments_from = "editLast93" in the config.ini.php
editLast93-archive.log

INFO [2021-12-19 23:31:16] 684152  Archived website id 1, period = year, date = 2021-01-01, segment = 'actionType!=contents', 88 visits found. Time elapsed: 1826.489s

Second with process_new_segments_from = "last93" in the config.ini.php
last93-archive.log

INFO [2021-12-20 00:05:28] 777211  Archived website id 1, period = year, date = 2021-01-01, segment = 'actionType!=events', 75 visits found. Time elapsed: 1799.830s

Both of the above archive runs took only a few seconds to process the "Month" periods for these newly created segments

  • Matomo Version: 4.6.2
  • PHP Version: 7.3.22
@Starker3 Starker3 added the Potential Bug Something that might be a bug, but needs validation and confirmation it can be reproduced. label Dec 20, 2021
@Starker3
Copy link
Contributor Author

For more info:
browser archiving is disabled and enforced, segments are pre-processed only:

browser_archiving_disabled_enforce = 1
enable_create_realtime_segments = 0
enable_browser_archiving_triggering = 0
process_new_segments_from = "last93"

@bx80
Copy link
Contributor

bx80 commented Dec 20, 2021

@Starker3 I've done a few tests, but haven't managed to recreate this. Any chance of seeing the console command you used for archiving? Did you have development mode enabled for the tests?

@Starker3
Copy link
Contributor Author

@bx80 I just used ./console core:archive for testing initially.

The archive logs attached were from ./console core:archive --force-idsites=Y --force-idsegments=XX

I did some testing with a different Site ID that has generated visit data and did not see the issue, so I was thinking that it might be specific to sites that have visits over a long period of time, eg. at least one visit for each day over the last year?

Otherwise I can setup an account for you on the Matomo site and setup SSH access if that might help.

@Starker3
Copy link
Contributor Author

I ran the archiving queries again to get debug information, all of the previous queries were run as follows:
./console core:archive --force-idsites=1 --force-idsegments=24 -vvv

This is what shows in the running processes when its processing the year period:

3395370 innocra+  20   0  916816 736680  20376 R  89.7   2.3   1:38.09 /usr/php/7.3.22/bin/php -q /home/matomo4/console climulti:request -q --matomo-domain= --superuser module=API&method=CoreAdminHome.archiveReports&idSite=1&period=year&date=2021-01-01&format=json&segment=actionType%21%3Ddownloads&trigger=archivephp                                                

3395369 innocra+  20   0    2392    752    684 S   0.0   0.0   0:00.00 sh -c /usr/php/7.3.22/bin/php -q  /home/matomo4/console climulti:request -q --matomo-domain='' --superuser 'module=API&method=CoreAdminHome.archiveReports&idSite=1&period=year&date=2021-01-01&format=json&segment=actionType%21%3Ddownloads&trigger=archivephp' 2>&1

last93-archive-debug.log
The debug log only shows the year archiving query and doesn't indicate any subqueries being run from what I can see in the logs.

I enabled development mode like you mentioned to see if that would get us more info.

This is the archive run with debug output but it seems the output file is too large to upload to GitHub in a comment.
It also just shows the last query run as the year period, so it's probably not much help but also shows the Avoiding segment subquery due to missing start date and/or an end date. Please ensure a start date and/or end date is set when initializing a segment if it's used to build a query.

I managed to grab the following query close to the end of the archiving with development mode enabled:

| 46228983 | 217102_matomo4 | 2a00:b6e0:1:200:177::1:58656 | innocraft-staff-richard_matomo4 | Query   |    0 | Sending data | SELECT arc1.idarchive, arc1.value, arc1.name, arc1.ts_archived, arc1.date1 as startDate, arc2.value as nb_visits, arc3.value as nb_visits_converted
                     FROM matomo_archive_numeric_2021_03 arc1
                     LEFT JOIN matomo_archive_numeric_2021_03 arc2 on arc2.idarchive = arc1.idarchive and (arc2.name = 'nb_visits')
                     LEFT JOIN matomo_archive_numeric_2021_03 arc3 on arc3.idarchive = arc1.idarchive and (arc3.name = 'nb_visits_converted')
                     WHERE arc1.idsite = '1'
                         AND arc1.date1 = '2021-03-23'
                         AND arc1.date2 = '2021-03-23'
                         AND arc1.period = '1'
                         AND (((arc1.name IN ('done7257a261717bbab525cefd921ebc025e','done7257a261717bbab525cefd921ebc025e.VisitsSummary'))))
                         
                         AND arc1.ts_archived IS NOT NULL
                     ORDER BY arc1.ts_archived DESC, arc1.idarchive DESC |    0.000 |

So I performed the archiving tests again to see if I could grab more of the queries that were being executed sort of in the middle of the year archive:

SELECT idarchive FROM `matomo_archive_numeric_2021_04` WHERE idsite = '1' AND date1 = '2021-04-25 00:00:00' AND date2 = '2021-04-25 00:00:00' AND period = '1' AND name = 'done5a0a1c3642cbe2656666e798c12d6e8e.VisitsSummary' AND ts_archived < '2021-12-21 01:33:08' AND idarchive < '472701'

SELECT arc1.idarchive, arc1.value, arc1.name, arc1.ts_archived, arc1.date1 as startDate, arc2.value as nb_visits, arc3.value as nb_visits_converted
                     FROM matomo_archive_numeric_2021_03 arc1
                     LEFT JOIN matomo_archive_numeric_2021_03 arc2 on arc2.idarchive = arc1.idarchive and (arc2.name = 'nb_visits')
                     LEFT JOIN matomo_archive_numeric_2021_03 arc3 on arc3.idarchive = arc1.idarchive and (arc3.name = 'nb_visits_converted')
                     WHERE arc1.idsite = '1'
                         AND arc1.date1 = '2021-03-28'
                         AND arc1.date2 = '2021-03-28'
                         AND arc1.period = '1'
                         AND (((arc1.name IN ('done5a0a1c3642cbe2656666e798c12d6e8e','done5a0a1c3642cbe2656666e798c12d6e8e.VisitsSummary','done5a0a1c3642cbe2656666e798c12d6e8e.Goals'))))
                         
                         AND arc1.ts_archived IS NOT NULL
                     ORDER BY arc1.ts_archived DESC, arc1.idarchive DESC

SELECT  /*+ MAX_EXECUTION_TIME(7200000) */  /* segmenthash 4d2c67b1324c54f984faa692d330a988 */ /* sites 1 */ /* 2021-12-09,2021-12-09 */ /* Core */ /* trigger = CronArchive */
				count(distinct log_visit.idvisitor) AS `1`, 
			count(*) AS `2`, 
			sum(log_visit.visit_total_actions) AS `3`, 
			max(log_visit.visit_total_actions) AS `4`, 
			sum(log_visit.visit_total_time) AS `5`, 
			sum(case log_visit.visit_total_actions when 1 then 1 when 0 then 1 else 0 end) AS `6`, 
			sum(case log_visit.visit_goal_converted when 1 then 1 else 0 end) AS `7`, 
			count(distinct log_visit.user_id) AS `39`
			FROM
				matomo_logtmpsegmentf2f33469ee951a0648dc8cd9cbbc13fe AS logtmpsegmentf2f33469ee951a0648dc8cd9cbbc13fe INNER JOIN matomo_log_visit AS log_visit ON log_visit.idvisit = logtmpsegmentf2f33469ee951a0648dc8cd9cbbc13fe.idvisit

SELECT idarchive FROM `matomo_archive_numeric_2021_03` WHERE idsite = '1' AND date1 = '2021-03-01 00:00:00' AND date2 = '2021-03-01 00:00:00' AND period = '1' AND name = 'donea4f39d10edb6487cdfe714b3b5ce6586.VisitsSummary' AND ts_archived < '2021-12-21 01:35:15' AND idarchive < '2634003'

SELECT arc1.idarchive, arc1.value, arc1.name, arc1.ts_archived, arc1.date1 as startDate, arc2.value as nb_visits, arc3.value as nb_visits_converted
                     FROM matomo_archive_numeric_2021_03 arc1
                     LEFT JOIN matomo_archive_numeric_2021_03 arc2 on arc2.idarchive = arc1.idarchive and (arc2.name = 'nb_visits')
                     LEFT JOIN matomo_archive_numeric_2021_03 arc3 on arc3.idarchive = arc1.idarchive and (arc3.name = 'nb_visits_converted')
                     WHERE arc1.idsite = '1'
                         AND arc1.date1 = '2021-03-16'
                         AND arc1.date2 = '2021-03-16'
                         AND arc1.period = '1'
                         AND (((arc1.name IN ('donea4f39d10edb6487cdfe714b3b5ce6586','donea4f39d10edb6487cdfe714b3b5ce6586.VisitsSummary','donea4f39d10edb6487cdfe714b3b5ce6586.Goals'))))
                         
                         AND arc1.ts_archived IS NOT NULL
                     ORDER BY arc1.ts_archived DESC, arc1.idarchive DESC

SELECT  /*+ MAX_EXECUTION_TIME(7200000) */  /* WP IGNORE ERROR */ 1 FROM matomo_logtmpsegmentcfdf25885284b3d1f16c08f1ed96383e LIMIT 1

SELECT arc1.idarchive, arc1.value, arc1.name, arc1.ts_archived, arc1.date1 as startDate, arc2.value as nb_visits, arc3.value as nb_visits_converted
                     FROM matomo_archive_numeric_2021_05 arc1
                     LEFT JOIN matomo_archive_numeric_2021_05 arc2 on arc2.idarchive = arc1.idarchive and (arc2.name = 'nb_visits')
                     LEFT JOIN matomo_archive_numeric_2021_05 arc3 on arc3.idarchive = arc1.idarchive and (arc3.name = 'nb_visits_converted')
                     WHERE arc1.idsite = '1'
                         AND arc1.date1 = '2021-05-17'
                         AND arc1.date2 = '2021-05-17'
                         AND arc1.period = '1'
                         AND (((arc1.name IN ('donea4f39d10edb6487cdfe714b3b5ce6586','donea4f39d10edb6487cdfe714b3b5ce6586.VisitsSummary','donea4f39d10edb6487cdfe714b3b5ce6586.Goals'))))
                         
                         AND arc1.ts_archived IS NOT NULL
                     ORDER BY arc1.ts_archived DESC, arc1.idarchive DESC |    0.000 |
| 46247939 | 217102_matomo4 | 2a00:b6e0:1:200:177::1:60972 | innocraft-staff-richard_matomo4

SELECT value, name, idsite, date1, date2, ts_archived
                                FROM matomo_archive_numeric_2021_06
                                WHERE idarchive IN (126295,126299,126299,126299,126299,126299,126299)
                                  AND name IN ('Goal_1_nb_conversions','Goal_1_nb_visits_converted','Goal_1_revenue','Goal_2_nb_conversions','Goal_2_nb_visits_converted','Goal_2_revenue','Goal_3_nb_conversions','Goal_3_nb_visits_converted','Goal_3_revenue','Goal_4_nb_conversions','Goal_4_nb_visits_converted','Goal_4_revenue','Goal_5_nb_conversions','Goal_5_nb_visits_converted','Goal_5_revenue','Goal_6_nb_conversions','Goal_6_nb_visits_converted','Goal_6_revenue','Goal_0_nb_conversions','Goal_0_nb_visits_converted','Goal_0_revenue','Goal_0_revenue_subtotal','Goal_0_revenue_tax','Goal_0_revenue_shipping','Goal_0_revenue_discount','Goal_0_items','Goal_-1_nb_conversions','Goal_-1_nb_visits_converted','Goal_-1_revenue','Goal_-1_items','Goal_nb_conversions','Goal_nb_visits_converted','Goal_revenue')
                             ORDER BY ts_archived ASC

INSERT IGNORE INTO matomo_archive_numeric_2021_08 (idarchive, idsite, date1, date2, period, ts_archived, name, value)
                  VALUES ('160946','1','2021-08-24','2021-08-24','1','2021-12-21 01:37:18','donea884fac1e18fd49f7ed61a0377add362.VisitsSummary','2') ON DUPLICATE KEY UPDATE value = '2'

SELECT idarchive FROM `matomo_archive_numeric_2021_04` WHERE idsite = '1' AND date1 = '2021-04-12 00:00:00' AND date2 = '2021-04-12 00:00:00' AND period = '1' AND name = 'doneed03f388d2845497ed599b584f9c55c0.VisitsSummary' AND ts_archived < '2021-12-21 01:38:44' AND idarchive < '473019'

INSERT IGNORE INTO matomo_archive_numeric_2021_08 (idarchive, idsite, date1, date2, period, ts_archived, name, value)
                  VALUES ('161066','1','2021-08-16','2021-08-22','2','2021-12-21 01:39:02','doneed03f388d2845497ed599b584f9c55c0.VisitsSummary','2') ON DUPLICATE KEY UPDATE value = '2'

@tsteur
Copy link
Member

tsteur commented Dec 21, 2021

btw you will also want to paste any other config ini setting in here, especially from the General section. If there's anything in Debug section that be good to know too

@Starker3
Copy link
Contributor Author

Sure @tsteur Here is the [General] section:

[General]
release_channel = "latest_beta"
action_title_category_delimiter = "/"
browser_archiving_disabled_enforce = 1
enable_create_realtime_segments = 0
process_new_segments_from = "last93"
enable_browser_archiving_triggering = 0
archiving_custom_ranges[] = "last90"
force_ssl = 1
login_allow_logme = 1
enable_plugin_upload = 1
rearchive_reports_in_past_last_n_months = "last0"
salt = "..."
trusted_hosts[] = "..."
instance_id = "..."

I don't have a [Debug] section in my config.ini.php

@Starker3
Copy link
Contributor Author

Starker3 commented Feb 9, 2022

FYI this is still happening on 4.7.1 for newly created segments
image

@Starker3
Copy link
Contributor Author

I retested this on 5.0.0 to see if the issue remains. It does indeed still seem to be happening for the year period report. In this log I've removed most of the 0 visit days for readability. You'll see at the end of the log the year period takes much longer than any other individual period by an extreme degree.

I managed to also capture some of the queries on the database directly which seem to indicate that for some reason the archiver is processing individual day periods that are part of the year period in order to generate the year period report. In the queries below the log you'll see dates in queries that were not processed according to the core:archive logs.

INFO      [01:30:45] 185935  ---------------------------
INFO      [01:30:45] 185935  INIT
INFO      [01:30:45] 185935  Running Matomo 5.0.0 as Super User
INFO      [01:30:45] 185935  ---------------------------
INFO      [01:30:45] 185935  NOTES
INFO      [01:30:45] 185935  - Async process archiving supported, using CliMulti.
INFO      [01:30:45] 185935  - Reports for today will be processed at most every 10 seconds. You can change this value in Matomo UI > Settings > General Settings.
INFO      [01:30:45] 185935  - Archiving was last executed without error 25 min 40s ago.
INFO      [01:30:45] 185935  - The following websites do not use the tracker: 
INFO      [01:30:45] 185935  - Will process 1 websites (--force-idsites)
INFO      [01:30:45] 185935  - Will process specified sites: 1
INFO      [01:30:45] 185935  - Limiting segment archiving to following segments:
INFO      [01:30:45] 185935    * visitIp>0.0.0.0;actions>=1
INFO      [01:30:45] 185935  ---------------------------
INFO      [01:30:45] 185935  START
INFO      [01:30:45] 185935  Starting Matomo reports archiving...
INFO      [01:30:45] 185935  0 out of 3 archivers running currently
INFO      [01:30:45] 185935  Start processing archives for site 1.
INFO      [01:30:45] 185935    Will invalidate archived reports for today in site ID = 1's timezone (2023-12-22 00:00:00).
INFO      [01:30:45] 185935  Segment 'campaignSource==c' is not a supported segment
INFO      [01:30:46] 185935    Will invalidate archived reports for yesterday in site ID = 1's timezone (2023-12-21 00:00:00).
INFO      [01:30:46] 185935  Segment 'campaignSource==c' is not a supported segment
INFO      [01:30:46] 185935  Segment 'campaignSource==c' is not a supported segment
INFO      [01:30:51] 185935  Archived website id 1, period = day, date = 2023-12-22, segment = 'visitIp>0.0.0.0;actions>=1', 1 visits found. Time elapsed: 1.049s
INFO      [01:30:51] 185935  Archived website id 1, period = day, date = 2023-12-21, segment = 'visitIp>0.0.0.0;actions>=1', 696 visits found. Time elapsed: 5.223s
INFO      [01:30:51] 185935  Archived website id 1, period = day, date = 2023-12-20, segment = 'visitIp>0.0.0.0;actions>=1', 298 visits found. Time elapsed: 5.223s
INFO      [01:30:53] 185935  Archived website id 1, period = day, date = 2023-12-19, segment = 'visitIp>0.0.0.0;actions>=1', 8 visits found. Time elapsed: 1.051s
INFO      [01:30:56] 185935  Archived website id 1, period = week, date = 2023-12-18, segment = 'visitIp>0.0.0.0;actions>=1', 1003 visits found. Time elapsed: 2.881s
INFO      [01:30:59] 185935  Archived website id 1, period = day, date = 2023-12-05, segment = 'visitIp>0.0.0.0;actions>=1', 201 visits found. Time elapsed: 1.576s
INFO      [01:30:59] 185935  Archived website id 1, period = day, date = 2023-12-04, segment = 'visitIp>0.0.0.0;actions>=1', 155 visits found. Time elapsed: 1.576s
INFO      [01:31:03] 185935  Archived website id 1, period = week, date = 2023-12-04, segment = 'visitIp>0.0.0.0;actions>=1', 356 visits found. Time elapsed: 3.667s
INFO      [01:31:07] 185935  Archived website id 1, period = range, date = 2023-12-02,2023-12-07, segment = 'visitIp>0.0.0.0;actions>=1', 356 visits found. Time elapsed: 3.411s
INFO      [01:31:14] 185935  Archived website id 1, period = month, date = 2023-12-01, segment = 'visitIp>0.0.0.0;actions>=1', 1359 visits found. Time elapsed: 7.584s
INFO      [01:31:59] 185935  Archived website id 1, period = day, date = 2023-06-03, segment = 'visitIp>0.0.0.0;actions>=1', 0 visits found. Time elapsed: 0.530s
INFO      [01:31:59] 185935  Archived website id 1, period = day, date = 2023-06-02, segment = 'visitIp>0.0.0.0;actions>=1', 0 visits found. Time elapsed: 0.530s
INFO      [01:31:59] 185935  Archived website id 1, period = day, date = 2023-06-01, segment = 'visitIp>0.0.0.0;actions>=1', 0 visits found. Time elapsed: 0.263s
INFO      [01:32:00] 185935  Archived website id 1, period = month, date = 2023-06-01, segment = 'visitIp>0.0.0.0;actions>=1', 0 visits found. Time elapsed: 0.372s
INFO      [01:32:00] 185935  Archived website id 1, period = week, date = 2023-05-29, segment = 'visitIp>0.0.0.0;actions>=1', 0 visits found. Time elapsed: 0.350s
**INFO      [01:36:05] 185935  Archived website id 1, period = year, date = 2023-01-01, segment = 'visitIp>0.0.0.0;actions>=1', 19141 visits found. Time elapsed: 244.776s**
INFO      [01:36:05] 185935  Finished archiving for site 1, 244 API requests, Time elapsed: 319.485s [1 / 1 done]
INFO      [01:36:05] 185935  Done archiving!
INFO      [01:36:05] 185935  ---------------------------
INFO      [01:36:05] 185935  SUMMARY
INFO      [01:36:05] 185935  Processed 244 archives.
INFO      [01:36:05] 185935  Total API requests: 244
INFO      [01:36:05] 185935  done: 244 req, 319752 ms, no error
INFO      [01:36:05] 185935  Time elapsed: 319.752s
INFO      [01:36:05] 185935  ---------------------------
INFO      [01:36:05] 185935  SCHEDULED TASKS
INFO      [01:36:05] 185935  Starting Scheduled tasks... 
INFO      [01:36:05] 185935  done
INFO      [01:36:05] 185935  ---------------------------

SQL queries that were captured during the long pause for the year period:

SELECT  /*+ MAX_EXECUTION_TIME(7200000) */  log_vvv.referer_type as label, (case when log_vvv.referer_type=6 then log_vvv.referer_name else log_vvv.referer_name END) as sublabel, sum(revenue) as nb_attribution_revenue_lastNonDirect, count(*) as nb_attribution_conversions_lastNonDirect
          from (SELECT /* segmenthash 3051fe43f94227dc15ed0c094058859b */ /* sites 1 */ /* 2023-02-09,2023-02-10 */ /* MultiChannelConversionAttribution GoalAttribution */ /* trigger = CronArchive */
				log_conversion.idvisitor, log_conversion.revenue, max(log_vpast.visit_last_action_time) as lastaction
			FROM
				matomo_logtmpsegment4ba6a56f5c5aa1668c9b36f28242cfa7 AS logtmpsegment4ba6a56f5c5aa1668c9b36f28242cfa7 INNER JOIN matomo_log_conversion AS log_conversion ON log_conversion.idvisit = logtmpsegment4ba6a56f5c5aa1668c9b36f28242cfa7.idvisit LEFT JOIN matomo_log_visit AS log_visit ON log_conversion.idvisit = log_visit.idvisit RIGHT JOIN matomo_log_visit AS log_vpast ON log_conversion.idvisitor = log_vpast.idvisitor
			WHERE
				log_conversion.idgoal = 0 
                           AND log_vpast.idsite = 1 AND log_vpast.visit_last_action_time >= '2022-11-11 11:00:00' 
                           AND log_vpast.visit_last_action_time <= log_visit.visit_last_action_time
			GROUP BY
				log_conversion.idvisit, log_conversion.buster) as yyy  
          left join matomo_log_visit as log_vvv on log_vvv.idvisitor = yyy.idvisitor 
                                and log_vvv.idsite = 1 
                                and log_vvv.visit_last_action_time = lastaction
          group by label, sublabel |    0.000 |

SELECT  /*+ MAX_EXECUTION_TIME(7200000) */  /* segmenthash 49f545d26cb2fb6dcdbf0276f51bb4f1 */ /* sites 1 */ /* 2023-02-14,2023-02-15 */ /* Goals ProductRecord */ /* trigger = CronArchive */
				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_logtmpsegmenteaa12df6319f8eb86021710eb2554ff9 AS logtmpsegmenteaa12df6319f8eb86021710eb2554ff9 INNER JOIN matomo_log_link_visit_action AS log_link_visit_action ON log_link_visit_action.idvisit = logtmpsegmenteaa12df6319f8eb86021710eb2554ff9.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
				label |    0.000 |

SELECT  /*+ MAX_EXECUTION_TIME(7200000) */  /* segmenthash 3051fe43f94227dc15ed0c094058859b */ /* sites 1 */ /* 2023-02-26,2023-02-27 */ /* Goals ProductRecord */ /* trigger = CronArchive */
				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_logtmpsegmentb6862e49c66ad216ad3b5f8607ff4c46 AS logtmpsegmentb6862e49c66ad216ad3b5f8607ff4c46 INNER JOIN matomo_log_link_visit_action AS log_link_visit_action ON log_link_visit_action.idvisit = logtmpsegmentb6862e49c66ad216ad3b5f8607ff4c46.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
				label |    0.000 |


SELECT  /*+ MAX_EXECUTION_TIME(2700000) */ 
				
				CASE
					WHEN counter = 50001 THEN '__mtm_ranking_query_others__'
					ELSE `Actions.PageUrl`
				END AS `Actions.PageUrl`
			
				, `nb_uniq_visitors`, `nb_visits`, `bounce_count`
			FROM ( 
			SELECT
				`Actions.PageUrl`,
				
			CASE
				WHEN @counter = 50001 THEN 50001
				ELSE @counter:=@counter+1
			END
		 AS counter
				, `nb_uniq_visitors`, `nb_visits`, `bounce_count`
			FROM
				( SELECT @counter:=0 ) initCounter,
				( SELECT /* CustomReports */ /* segmenthash 3051fe43f94227dc15ed0c094058859b */ /* sites 1 */ /* 2023-03-01,2023-03-02 */ /* CustomReports CustomReport */ /* trigger = CronArchive */
				log_inner.name AS 'Actions.PageUrl', count(distinct log_inner.idvisitor) AS 'nb_uniq_visitors', count(distinct log_inner.idvisit) AS 'nb_visits', sum(case log_inner.visit_total_actions when 1 then 1 when 0 then 1 else 0 end) AS 'bounce_count'
			FROM
				
        (
            
			SELECT
				log_action_idaction_url.name, 
log_visit.idvisitor, 
log_visit.idvisit, 
log_visit.visit_total_actions
			FROM
				matomo_logtmpsegmente1164ee712f663bc747ea77eb8a09c44 AS logtmpsegmente1164ee712f663bc747ea77eb8a09c44 INNER JOIN matomo_log_link_visit_action AS log_link_visit_action ON log_link_visit_action.idvisit = logtmpsegmente1164ee712f663bc747ea77eb8a09c44.idvisit LEFT JOIN matomo_log_action AS log_action_idaction_url ON log_link_visit_action.idaction_url = log_action_idaction_url.idaction LEFT JOIN matomo_log_visit AS log_visit ON log_visit.idvisit = logtmpsegmente1164ee712f663bc747ea77eb8a09c44.idvisit
			WHERE
				(log_action_idaction_url.name is not null AND log_action_idaction_url.type = "1") 
			GROUP BY
				log_action_idaction_url.name, `log_visit`.`idvisit`
			ORDER BY
				NULL
        ) AS log_inner
			GROUP BY
				log_inner.name
			ORDER BY
				nb_uniq_visitors ) actualQuery
		 ) AS withCounter
			GROUP BY counter |    0.000 |

@bx80 bx80 added this to the For Prioritization milestone Dec 29, 2023
@bx80 bx80 added Bug For errors / faults / flaws / inconsistencies etc. c: Performance For when we could improve the performance / speed of Matomo. and removed Potential Bug Something that might be a bug, but needs validation and confirmation it can be reproduced. labels Dec 29, 2023
@bx80
Copy link
Contributor

bx80 commented Dec 29, 2023

Thanks for the follow up @Starker3 , it sounds like we need some more investigation of this behavior on the 5.x codebase. I've tagged this issue as performance related and assigned it for prioritization. We're focusing on performance issues at the moment and this might be a good one to look at (@Stan-vw)

@Stan-vw
Copy link
Contributor

Stan-vw commented Feb 19, 2024

We tried recreating this but couldn't, so we're closing it for now. Please reopen in case you still have this issue.

@Stan-vw Stan-vw closed this as completed Feb 19, 2024
@sgiehl sgiehl added the worksforme The issue cannot be reproduced and things work as intended. label Apr 8, 2024
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
Bug For errors / faults / flaws / inconsistencies etc. c: Performance For when we could improve the performance / speed of Matomo. worksforme The issue cannot be reproduced and things work as intended.
Projects
None yet
Milestone
For Prioritization
Development

No branches or pull requests
5 participants
@tsteur @sgiehl @Starker3 @Stan-vw @bx80