Gilles has uploaded a new change for review. https://gerrit.wikimedia.org/r/180136
Change subject: Query image performance by upload time ...................................................................... Query image performance by upload time Per month, there isn't enough data to do this per day yet Bug: T76035 Change-Id: I8c536036ba91c9326a9036f085dae6a54207c2d9 --- M build-perf-tsvs A perf/perf-by-upload-time.sql M perf/template.sql 3 files changed, 38 insertions(+), 1 deletion(-) git pull ssh://gerrit.wikimedia.org:29418/analytics/multimedia refs/changes/36/180136/1 diff --git a/build-perf-tsvs b/build-perf-tsvs index 0f3afa8..21dd01a 100755 --- a/build-perf-tsvs +++ b/build-perf-tsvs @@ -39,9 +39,13 @@ $MYSQL_CMD < $CHECKOUT_DIR/perf/cache-miss-ratio.sql > $tsvpath && mv -f $tsvpath $PUBLIC_DIR/cache-miss-ratio.tsv chmod 664 $PUBLIC_DIR/media-viewer-cache-miss-ratio.tsv - echo "Updating mmv-versus-filepage perf data..." tsvpath="$TSV_DIR/mvp_mmv_versus_filepage.tsv" $MYSQL_CMD < $CHECKOUT_DIR/perf/mmv-versus-filepage.sql > $tsvpath && mv -f $tsvpath $PUBLIC_DIR/media-viewer-perf-mmv-versus-filepage.tsv chmod 664 $PUBLIC_DIR/media-viewer-perf-mmv-versus-filepage.tsv echo " Done!" + +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 diff --git a/perf/perf-by-upload-time.sql b/perf/perf-by-upload-time.sql new file mode 100644 index 0000000..e54627a --- /dev/null +++ b/perf/perf-by-upload-time.sql @@ -0,0 +1,30 @@ +SET group_concat_max_len = 10485760; +SELECT * FROM (SELECT concat(substring(event_uploadTimestamp, 1, 4), '-', substring(event_uploadTimestamp, 5, 2)) AS datestring FROM ( +SELECT event_uploadTimestamp FROM MultimediaViewerNetworkPerformance_10596581 +WHERE event_uploadTimestamp < TIMESTAMP(CURDATE()) AND event_uploadTimestamp >= TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 730 DAY)) +) AS MultimediaViewerNetworkPerformanceUnioned GROUP BY datestring ORDER BY datestring ASC) dates + +LEFT OUTER JOIN + +( SELECT concat(substring(event_uploadTimestamp, 1, 4), '-', substring(event_uploadTimestamp, 5, 2)) AS datestring, +EXP(AVG(LOG(event_total))) AS image_time_mean, +EXP(STD(LOG(event_total))) AS image_time_std, +COUNT(*) AS image_sample_size, +CAST(SUBSTRING_INDEX(SUBSTRING_INDEX( + GROUP_CONCAT(event_total ORDER BY event_total SEPARATOR ','), + ',', 1/100 * COUNT(*) + 1), ',', -1) AS DECIMAL) AS image_time_1st_percentile, +CAST(SUBSTRING_INDEX(SUBSTRING_INDEX( + GROUP_CONCAT(event_total ORDER BY event_total SEPARATOR ','), + ',', 50/100 * COUNT(*) + 1), ',', -1) AS DECIMAL) AS image_time_50th_percentile, +CAST(SUBSTRING_INDEX(SUBSTRING_INDEX( + GROUP_CONCAT(event_total ORDER BY event_total SEPARATOR ','), + ',', 90/100 * COUNT(*) + 1), ',', -1) AS DECIMAL) AS image_time_90th_percentile, +CAST(SUBSTRING_INDEX(SUBSTRING_INDEX( + GROUP_CONCAT(event_total ORDER BY event_total SEPARATOR ','), + ',', 95/100 * COUNT(*) + 1), ',', -1) AS DECIMAL) AS image_time_95th_percentile, +CAST(SUBSTRING_INDEX(SUBSTRING_INDEX( + GROUP_CONCAT(event_total ORDER BY event_total SEPARATOR ','), + ',', 99/100 * COUNT(*) + 1), ',', -1) AS DECIMAL) AS image_time_99th_percentile FROM ( +SELECT event_type, event_total, event_uploadTimestamp, wiki FROM MultimediaViewerNetworkPerformance_10596581 +WHERE event_uploadTimestamp < TIMESTAMP(CURDATE()) AND event_uploadTimestamp >= TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 730 DAY)) AND event_total > 20 AND event_type = 'image' +) AS MultimediaViewerNetworkPerformanceUnioned GROUP BY datestring ORDER BY datestring ASC ) stats USING (datestring) \ No newline at end of file diff --git a/perf/template.sql b/perf/template.sql index 5401fbe..15ab7b3 100644 --- a/perf/template.sql +++ b/perf/template.sql @@ -2,6 +2,9 @@ SELECT * FROM (SELECT concat(substring(timestamp, 1, 4), '-', substring(timestamp, 5, 2), '-', substring(timestamp, 7, 2)) AS datestring FROM ( SELECT timestamp FROM MultimediaViewerNetworkPerformance_7917896 WHERE timestamp < TIMESTAMP(CURDATE()) AND timestamp >= TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 30 DAY)) +UNION ALL +SELECT timestamp FROM MultimediaViewerNetworkPerformance_10596581 +WHERE timestamp < TIMESTAMP(CURDATE()) AND timestamp >= TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 30 DAY)) ) AS MultimediaViewerNetworkPerformanceUnioned GROUP BY datestring ORDER BY datestring ASC) dates LEFT OUTER JOIN -- To view, visit https://gerrit.wikimedia.org/r/180136 To unsubscribe, visit https://gerrit.wikimedia.org/r/settings Gerrit-MessageType: newchange Gerrit-Change-Id: I8c536036ba91c9326a9036f085dae6a54207c2d9 Gerrit-PatchSet: 1 Gerrit-Project: analytics/multimedia Gerrit-Branch: master Gerrit-Owner: Gilles <gdu...@wikimedia.org> _______________________________________________ MediaWiki-commits mailing list MediaWiki-commits@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/mediawiki-commits
