Gergő Tisza has uploaded a new change for review.
https://gerrit.wikimedia.org/r/179771
Change subject: Calculate image cache miss ratio
......................................................................
Calculate image cache miss ratio
Bug: T78205
Change-Id: I08c41774c555778c03fbfaa9c2625dbbb01f7013
---
M build-perf-tsvs
A perf/cache-miss-ratio.sql
2 files changed, 35 insertions(+), 0 deletions(-)
git pull ssh://gerrit.wikimedia.org:29418/analytics/multimedia
refs/changes/71/179771/1
diff --git a/build-perf-tsvs b/build-perf-tsvs
index 47b7964..0f3afa8 100755
--- a/build-perf-tsvs
+++ b/build-perf-tsvs
@@ -34,6 +34,12 @@
echo " Done!"
done
+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
+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
diff --git a/perf/cache-miss-ratio.sql b/perf/cache-miss-ratio.sql
new file mode 100644
index 0000000..328f777
--- /dev/null
+++ b/perf/cache-miss-ratio.sql
@@ -0,0 +1,29 @@
+SELECT
+ datestring,
+ SUM(miss) / COUNT(*) miss_ratio,
+ COUNT(*) *1000 total
+FROM (
+ SELECT
+ (event_varnish1hits = 0 AND event_varnish2hits = 0 AND
event_varnish3hits = 0) miss,
+ concat(substring(timestamp, 1, 4), '-', substring(timestamp, 5, 2),
'-', substring(timestamp, 7, 2)) datestring
+ FROM
+ MultimediaViewerNetworkPerformance_7917896
+ WHERE
+ --%wiki%
+ event_type = 'image'
+ AND LENGTH(event_XCache) > 0
+ AND timestamp BETWEEN TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 90 DAY))
AND TIMESTAMP(CURDATE())
+UNION ALL
+ SELECT
+ (event_varnish1hits = 0 AND event_varnish2hits = 0 AND
event_varnish3hits = 0) miss,
+ concat(substring(timestamp, 1, 4), '-', substring(timestamp, 5, 2),
'-', substring(timestamp, 7, 2)) datestring
+ FROM
+ MultimediaViewerNetworkPerformance_10596581
+ WHERE
+ --%wiki%
+ event_type = 'image'
+ AND LENGTH(event_XCache) > 0
+ AND timestamp BETWEEN TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 90 DAY))
AND TIMESTAMP(CURDATE())
+) MultimediaViewerNetworkPerformanceUnioned
+GROUP BY datestring
+ORDER BY datestring ASC;
--
To view, visit https://gerrit.wikimedia.org/r/179771
To unsubscribe, visit https://gerrit.wikimedia.org/r/settings
Gerrit-MessageType: newchange
Gerrit-Change-Id: I08c41774c555778c03fbfaa9c2625dbbb01f7013
Gerrit-PatchSet: 1
Gerrit-Project: analytics/multimedia
Gerrit-Branch: master
Gerrit-Owner: Gergő Tisza <[email protected]>
_______________________________________________
MediaWiki-commits mailing list
[email protected]
https://lists.wikimedia.org/mailman/listinfo/mediawiki-commits
