Gilles has uploaded a new change for review.
https://gerrit.wikimedia.org/r/180744
Change subject: Update network performance schema queries
......................................................................
Update network performance schema queries
Bug: T84852
Change-Id: I102bafd6519570129427f52248cc81f6630cbd07
---
M geoperf/template.sql
M perf/cache-miss-ratio.sql
M perf/template.sql
3 files changed, 24 insertions(+), 0 deletions(-)
git pull ssh://gerrit.wikimedia.org:29418/analytics/multimedia
refs/changes/44/180744/1
diff --git a/geoperf/template.sql b/geoperf/template.sql
index 0ec307c..02424e1 100644
--- a/geoperf/template.sql
+++ b/geoperf/template.sql
@@ -24,4 +24,7 @@
UNION ALL
SELECT timestamp, event_total, event_country, event_type, wiki FROM
MultimediaViewerNetworkPerformance_10596581
WHERE %wiki% %metricwhere% AND timestamp < TIMESTAMP(CURDATE()) AND timestamp
>= TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 10 DAY)) AND event_total > 20 AND
event_country != ''
+UNION ALL
+SELECT timestamp, event_total, event_country, event_type, wiki FROM
MultimediaViewerNetworkPerformance_10774577
+WHERE %wiki% %metricwhere% AND timestamp < TIMESTAMP(CURDATE()) AND timestamp
>= TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 10 DAY)) AND event_total > 20 AND
event_country != ''
) MultimediaViewerNetworkPerformanceUnioned GROUP BY country HAVING
sample_size > 10 ORDER BY mean ASC
\ No newline at end of file
diff --git a/perf/cache-miss-ratio.sql b/perf/cache-miss-ratio.sql
index b11a70e..ff1b7af 100644
--- a/perf/cache-miss-ratio.sql
+++ b/perf/cache-miss-ratio.sql
@@ -24,6 +24,18 @@
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_10774577
+ 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;
diff --git a/perf/template.sql b/perf/template.sql
index 5401fbe..191b230 100644
--- a/perf/template.sql
+++ b/perf/template.sql
@@ -2,6 +2,12 @@
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))
+UNION ALL
+SELECT timestamp FROM MultimediaViewerNetworkPerformance_10774577
+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
@@ -30,4 +36,7 @@
UNION ALL
SELECT %metricfields% FROM MultimediaViewerNetworkPerformance_10596581
WHERE %wiki% timestamp < TIMESTAMP(CURDATE()) AND timestamp >=
TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 30 DAY)) AND event_total > 20 AND
%metricwhere%
+UNION ALL
+SELECT %metricfields% FROM MultimediaViewerNetworkPerformance_10774577
+WHERE %wiki% timestamp < TIMESTAMP(CURDATE()) AND timestamp >=
TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 30 DAY)) AND event_total > 20 AND
%metricwhere%
) AS MultimediaViewerNetworkPerformanceUnioned GROUP BY datestring ORDER BY
datestring ASC ) stats USING (datestring)
\ No newline at end of file
--
To view, visit https://gerrit.wikimedia.org/r/180744
To unsubscribe, visit https://gerrit.wikimedia.org/r/settings
Gerrit-MessageType: newchange
Gerrit-Change-Id: I102bafd6519570129427f52248cc81f6630cbd07
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
