Varnish hits performance per file upload month
ActivePublic
Actions

Authored by • Gilles on Dec 16 2014, 1:13 PM.

Tags

Referenced Files

	F22555: Varnish_hits_performance_per_file_upload_month
	Dec 19 2014, 8:41 AM

	F22554: Varnish_hits_performance_per_file_upload_month
	Dec 19 2014, 8:38 AM

	F21394: Varnish_hits_performance_per_file_upload_month
	Dec 16 2014, 1:13 PM

Subscribers

None

	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)