Page MenuHomePhabricator
Paste P159

Varnish hits performance per file upload month
ActivePublic

Authored by Gilles on Dec 16 2014, 1:13 PM.
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))
UNION ALL
SELECT event_uploadTimestamp FROM MultimediaViewerNetworkPerformance_10774577
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' AND ( event_varnish1hits > 0 OR event_varnish2hits > 0 OR event_varnish3hits > 0)
UNION ALL
SELECT event_type, event_total, event_uploadTimestamp, wiki FROM MultimediaViewerNetworkPerformance_10774577
WHERE event_uploadTimestamp < TIMESTAMP(CURDATE()) AND event_uploadTimestamp >= TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 730 DAY)) AND event_total > 20 AND event_type = 'image' AND ( event_varnish1hits > 0 OR event_varnish2hits > 0 OR event_varnish3hits > 0)
) AS MultimediaViewerNetworkPerformanceUnioned GROUP BY datestring ORDER BY datestring ASC ) stats USING (datestring)

Event Timeline

Gilles edited the content of this paste. (Show Details)Dec 16 2014, 1:13 PM
Gilles changed the title of this paste from untitled to Varnish hits performance per file upload month.
Gilles updated the paste's language from autodetect to autodetect.
Gilles edited the content of this paste. (Show Details)Dec 19 2014, 8:38 AM
Gilles edited the content of this paste. (Show Details)Dec 19 2014, 8:41 AM