[MediaWiki-commits] [Gerrit] analytics/multimedia[master]: Fix SQL queries
jenkins-bot has submitted this change and it was merged. ( https://gerrit.wikimedia.org/r/324379 ) Change subject: Fix SQL queries .. Fix SQL queries Update EventLogging IDs (didn't try to be intelligent about it and look at the dates - probably a lot of unions could be dropped now, but I had neither time nor care), find alternative for ImageMetrics data (which was undeployed). Not fixed: optouts (UserDailyContribs was undeployed, don't see any alternative source for user activity). perf is still empty for most wikis (but not all). Not sure what to make of that. Bug: T98449 Change-Id: I7e10ec6ae1b94158b0d1fcb804757e99aee08756 --- M build-perf-tsvs M duration/template.sql M geoperf/template.sql M pageviews/template.sql M perf/cache-miss-ratio.sql M perf/perf-by-upload-time.sql M perf/template.sql M uw-funnel/absolute.sql M uw-funnel/overview.sql M uw-funnel/relative.sql 10 files changed, 79 insertions(+), 84 deletions(-) Approvals: Bartosz Dziewoński: Looks good to me, but someone else must approve Gergő Tisza: Looks good to me, approved jenkins-bot: Verified diff --git a/build-perf-tsvs b/build-perf-tsvs index 21dd01a..8ed5af6 100755 --- a/build-perf-tsvs +++ b/build-perf-tsvs @@ -36,7 +36,7 @@ echo "Updating cache miss ratio data..." tsvpath="$TSV_DIR/cache-miss-ratio.tsv" -$MYSQL_CMD < $CHECKOUT_DIR/perf/cache-miss-ratio.sql > $tsvpath && mv -f $tsvpath $PUBLIC_DIR/cache-miss-ratio.tsv +$MYSQL_CMD < $CHECKOUT_DIR/perf/cache-miss-ratio.sql > $tsvpath && mv -f $tsvpath $PUBLIC_DIR/media-viewer-cache-miss-ratio.tsv chmod 664 $PUBLIC_DIR/media-viewer-cache-miss-ratio.tsv echo "Updating mmv-versus-filepage perf data..." @@ -47,5 +47,5 @@ echo "Updating perf by upload time data..." tsvpath="$TSV_DIR/perf-by-upload-time.tsv" -$MYSQL_CMD < $CHECKOUT_DIR/perf/perf-by-upload-time.sql > $tsvpath && mv -f $tsvpath $PUBLIC_DIR/perf-by-upload-time.tsv -chmod 664 $PUBLIC_DIR/media-viewer-perf-by-upload-time.tsv \ No newline at end of file +$MYSQL_CMD < $CHECKOUT_DIR/perf/perf-by-upload-time.sql > $tsvpath && mv -f $tsvpath $PUBLIC_DIR/media-viewer-perf-by-upload-time.tsv +chmod 664 $PUBLIC_DIR/media-viewer-perf-by-upload-time.tsv diff --git a/duration/template.sql b/duration/template.sql index 5cc6c59..a23cde2 100644 --- a/duration/template.sql +++ b/duration/template.sql @@ -3,22 +3,24 @@ SELECT * FROM ( SELECT DATE_FORMAT(timestamp, '%Y-%m-%d') AS datestring, -SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(event_fullLoadingTime -ORDER BY event_fullLoadingTime SEPARATOR ','), ',', 50/100*COUNT(*)+1), ',', -1) AS filepage_loggedin_50, -SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(event_fullLoadingTime -ORDER BY event_fullLoadingTime SEPARATOR ','), ',', 90/100*COUNT(*)+1), ',', -1) AS filepage_loggedin_90, -SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(event_fullLoadingTime -ORDER BY event_fullLoadingTime SEPARATOR ','), ',', 95/100*COUNT(*)+1), ',', -1) AS filepage_loggedin_95, -SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(event_fullLoadingTime -ORDER BY event_fullLoadingTime SEPARATOR ','), ',', 99/100*COUNT(*)+1), ',', -1) AS filepage_loggedin_99, -SUM(event_samplingFactor) AS filepage_loggedin_population_size +SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(event_firstPaint +ORDER BY event_firstPaint SEPARATOR ','), ',', 50/100*COUNT(*)+1), ',', -1) AS filepage_loggedin_50, +SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(event_firstPaint +ORDER BY event_firstPaint SEPARATOR ','), ',', 90/100*COUNT(*)+1), ',', -1) AS filepage_loggedin_90, +SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(event_firstPaint +ORDER BY event_firstPaint SEPARATOR ','), ',', 95/100*COUNT(*)+1), ',', -1) AS filepage_loggedin_95, +SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(event_firstPaint +ORDER BY event_firstPaint SEPARATOR ','), ',', 99/100*COUNT(*)+1), ',', -1) AS filepage_loggedin_99, +SUM(1000) AS filepage_loggedin_population_size -- NavTiming sampling factor FROM -ImageMetricsLoadingTime_10078363 +NavigationTiming_15485142 WHERE %wiki% -event_navigationType = 'navigate' -AND event_fullLoadingTime IS NOT NULL +event_action = 'view' +AND event_firstPaint IS NOT NULL AND NOT event_isAnon +AND event_mobileMode IS NULL +AND event_namespaceId = 6 AND timestamp < TIMESTAMP(CURDATE()) -- do not show partial data for the current day AND timestamp > TIMESTAMP(CURDATE() - INTERVAL 90 DAY) GROUP BY @@ -26,22 +28,24 @@ ) navtiming_loggedin LEFT JOIN ( SELECT DATE_FORMAT(timestamp, '%Y-%m-%d') AS datestring, -SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(event_fullLoadingTime -ORDER BY event_fullLoadingTime
[MediaWiki-commits] [Gerrit] analytics/multimedia[master]: Fix SQL queries
Gergő Tisza has uploaded a new change for review. https://gerrit.wikimedia.org/r/324379 Change subject: Fix SQL queries .. Fix SQL queries Update EventLogging IDs (didn't try to be intelligent about it and look at the dates - probably a lot of unions could be dropped now, but I had neither time nor care), find alternative for ImageMetrics data (which was undeployed). Not fixed: optouts (UserDailyContribs was undeployed, don't see any alternative source for user activity). perf is still empty for most wikis (but not all). Not sure what to make of that. Bug: T98449 Change-Id: I7e10ec6ae1b94158b0d1fcb804757e99aee08756 --- M build-perf-tsvs M duration/template.sql M geoperf/template.sql M pageviews/template.sql M perf/cache-miss-ratio.sql M perf/perf-by-upload-time.sql M perf/template.sql M uw-funnel/absolute.sql M uw-funnel/overview.sql M uw-funnel/relative.sql 10 files changed, 79 insertions(+), 84 deletions(-) git pull ssh://gerrit.wikimedia.org:29418/analytics/multimedia refs/changes/79/324379/1 diff --git a/build-perf-tsvs b/build-perf-tsvs index 21dd01a..8ed5af6 100755 --- a/build-perf-tsvs +++ b/build-perf-tsvs @@ -36,7 +36,7 @@ echo "Updating cache miss ratio data..." tsvpath="$TSV_DIR/cache-miss-ratio.tsv" -$MYSQL_CMD < $CHECKOUT_DIR/perf/cache-miss-ratio.sql > $tsvpath && mv -f $tsvpath $PUBLIC_DIR/cache-miss-ratio.tsv +$MYSQL_CMD < $CHECKOUT_DIR/perf/cache-miss-ratio.sql > $tsvpath && mv -f $tsvpath $PUBLIC_DIR/media-viewer-cache-miss-ratio.tsv chmod 664 $PUBLIC_DIR/media-viewer-cache-miss-ratio.tsv echo "Updating mmv-versus-filepage perf data..." @@ -47,5 +47,5 @@ echo "Updating perf by upload time data..." tsvpath="$TSV_DIR/perf-by-upload-time.tsv" -$MYSQL_CMD < $CHECKOUT_DIR/perf/perf-by-upload-time.sql > $tsvpath && mv -f $tsvpath $PUBLIC_DIR/perf-by-upload-time.tsv -chmod 664 $PUBLIC_DIR/media-viewer-perf-by-upload-time.tsv \ No newline at end of file +$MYSQL_CMD < $CHECKOUT_DIR/perf/perf-by-upload-time.sql > $tsvpath && mv -f $tsvpath $PUBLIC_DIR/media-viewer-perf-by-upload-time.tsv +chmod 664 $PUBLIC_DIR/media-viewer-perf-by-upload-time.tsv diff --git a/duration/template.sql b/duration/template.sql index 5cc6c59..a23cde2 100644 --- a/duration/template.sql +++ b/duration/template.sql @@ -3,22 +3,24 @@ SELECT * FROM ( SELECT DATE_FORMAT(timestamp, '%Y-%m-%d') AS datestring, -SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(event_fullLoadingTime -ORDER BY event_fullLoadingTime SEPARATOR ','), ',', 50/100*COUNT(*)+1), ',', -1) AS filepage_loggedin_50, -SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(event_fullLoadingTime -ORDER BY event_fullLoadingTime SEPARATOR ','), ',', 90/100*COUNT(*)+1), ',', -1) AS filepage_loggedin_90, -SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(event_fullLoadingTime -ORDER BY event_fullLoadingTime SEPARATOR ','), ',', 95/100*COUNT(*)+1), ',', -1) AS filepage_loggedin_95, -SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(event_fullLoadingTime -ORDER BY event_fullLoadingTime SEPARATOR ','), ',', 99/100*COUNT(*)+1), ',', -1) AS filepage_loggedin_99, -SUM(event_samplingFactor) AS filepage_loggedin_population_size +SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(event_firstPaint +ORDER BY event_firstPaint SEPARATOR ','), ',', 50/100*COUNT(*)+1), ',', -1) AS filepage_loggedin_50, +SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(event_firstPaint +ORDER BY event_firstPaint SEPARATOR ','), ',', 90/100*COUNT(*)+1), ',', -1) AS filepage_loggedin_90, +SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(event_firstPaint +ORDER BY event_firstPaint SEPARATOR ','), ',', 95/100*COUNT(*)+1), ',', -1) AS filepage_loggedin_95, +SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(event_firstPaint +ORDER BY event_firstPaint SEPARATOR ','), ',', 99/100*COUNT(*)+1), ',', -1) AS filepage_loggedin_99, +SUM(1000) AS filepage_loggedin_population_size -- NavTiming sampling factor FROM -ImageMetricsLoadingTime_10078363 +NavigationTiming_15485142 WHERE %wiki% -event_navigationType = 'navigate' -AND event_fullLoadingTime IS NOT NULL +event_action = 'view' +AND event_firstPaint IS NOT NULL AND NOT event_isAnon +AND event_mobileMode IS NULL +AND event_namespaceId = 6 AND timestamp < TIMESTAMP(CURDATE()) -- do not show partial data for the current day AND timestamp > TIMESTAMP(CURDATE() - INTERVAL 90 DAY) GROUP BY @@ -26,22 +28,24 @@ ) navtiming_loggedin LEFT JOIN ( SELECT DATE_FORMAT(timestamp, '%Y-%m-%d') AS datestring, -SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(event_fullLoadingTime -ORDER BY event_fullLoadingTime SEPARATOR ','), ',', 50/100*COUNT(*)+1), ',', -1) AS filepage_anon_50, -
