Below is a list of how speed varied by country (measued as the median of loadEventEnd from the NavigationTiming schema), during the five weeks from Dec 23-Jan 26 . The global median was (EDIT:) 1703 milliseconds during this time (1592 milliseconds during January 27).

Queries used (BTW the method to calculate medians is adapted from JonK:

SET @rownum = 0
SELECT AVG(loadEventEnd) AS median_loadEventEnd FROM
  (SELECT (@rownum:= @rownum +1) AS row_number, loadEventEnd FROM (
    SELECT event_loadEventEnd AS loadEventEnd FROM log.NavigationTiming_15033442 WHERE timestamp LIKE '20160127%' AND event_loadEventEnd IS NOT NULL AND wiki = 'enwiki' ORDER BY loadEventEnd) AS unnumberedlist
  ORDER BY loadEventEnd) AS numberedlist
INNER JOIN
  (SELECT COUNT(*) AS total_rows FROM log.NavigationTiming_15033442 WHERE timestamp LIKE '20160127%' AND event_loadEventEnd IS NOT NULL AND wiki = 'enwiki') AS row_counting
ON 1=1
WHERE
numberedlist.row_number >= row_counting.total_rows/2 AND
numberedlist.row_number <= row_counting.total_rows/2+1

SET @rownum = 0
SELECT numberedlist.country, AVG(numberedlist.loadEventEnd) AS median_loadEventEnd, AVG(rows_per_country.total_rows) AS samples FROM
  (SELECT IF(@prev <> ordered_data.country, @rownum:=1, @rownum:= @rownum +1) AS row_number, ordered_data.loadEventEnd, @prev:= ordered_data.country, ordered_data.country AS country FROM (
       SELECT event_loadEventEnd AS loadEventEnd, event_originCountry AS country
       FROM log.NavigationTiming_15033442
       WHERE timestamp BETWEEN '20151223' AND '20160127' AND event_loadEventEnd IS NOT NULL AND wiki = 'enwiki'
       ORDER BY country, loadEventEnd) ordered_data
  ORDER BY country, loadEventEnd) AS numberedlist
INNER JOIN
  (SELECT COUNT(*) AS total_rows, event_originCountry AS country FROM log.NavigationTiming_15033442
  WHERE timestamp BETWEEN '20151223' AND '20160127' AND event_loadEventEnd IS NOT NULL AND wiki = 'enwiki' GROUP BY country)
  AS rows_per_country
ON numberedlist.country = rows_per_country.country
WHERE
numberedlist.row_number >= rows_per_country.total_rows/2 AND
numberedlist.row_number <= rows_per_country.total_rows/2+1
GROUP BY numberedlist.country
ORDER BY numberedlist.loadEventEnd

(edited to add:)
There are some sorting errors in the list (e.g. CF should be further down), also for the mobile version below. The reason is not clear to me; for now I'm assuming that the values are correct and MySQL/MariaDB just messed up the last ORDER BY.

(edited to add:)

SELECT AVG(loadEventEnd) AS median_loadEventEnd FROM
  (SELECT (@rownum:= @rownum +1) AS row_number, loadEventEnd FROM (
    SELECT event_loadEventEnd AS loadEventEnd FROM log.NavigationTiming_15033442 WHERE timestamp BETWEEN '20151223' AND '20160127' AND event_loadEventEnd IS NOT NULL AND wiki = 'enwiki' ORDER BY loadEventEnd) AS unnumberedlist
  ORDER BY loadEventEnd) AS numberedlist
INNER JOIN
  (SELECT COUNT(*) AS total_rows FROM log.NavigationTiming_15033442 WHERE timestamp BETWEEN '20151223' AND '20160127' AND event_loadEventEnd IS NOT NULL AND wiki = 'enwiki') AS row_counting
ON 1=1
WHERE
numberedlist.row_number >= row_counting.total_rows/2 AND
numberedlist.row_number <= row_counting.total_rows/2+1

median_loadEventEnd
1703.0000

...and here is a chart showing the distribution of loadEventEnd globally and for some of the slowest and fastest countries:

In T125414#1987047, @Tbayer wrote:

...and here is a chart showing the distribution of loadEventEnd globally and for some of the slowest and fastest countries:

(with some slight rounding errors due to sampling and presentation)

Thanks, Tilman. For my own edification, what tool did you use to create the chart? It looks like Google Charts, but I am not sure. It is clear and crisp.

In T125414#1988986, @ori wrote:

Thanks, Tilman. For my own edification, what tool did you use to create the chart? It looks like Google Charts, but I am not sure. It is clear and crisp.

Sorry, meant to add the details for this one too: The spreadsheet with the chart and the source data is here (you can change the country names in the green cells to generate the same chart for a different set of countries). Data obtained via:

SELECT FLOOR(LOG2(event_loadEventEnd/1000)*2)/2 AS bucket, event_originCountry AS country, COUNT(*) FROM log.NavigationTiming_15033442 WHERE timestamp BETWEEN '20151223' AND '20160127' AND event_loadEventEnd IS NOT NULL AND wiki = 'enwiki' GROUP BY bucket, country ORDER BY bucket, country

I restricted it to enwiki per your rationale at T119797#1853006 , but not yet to the mobile site - starting the queries for that now, too.

And here is the analogous data and chart restricted to the mobile site (based on seven weeks' worth of data, i.e. two more than above):

loadEventEnd on en.m.wikipedia.org (during Dec 22, 2015 to Feb 8, 2016):

Global median: 2310ms

Chart showing the distribution of loadEventEnd globally and for some of the slowest and fastest countries:

Median speed by country:

(I'll see to make this into a nice color-coded world map.)

Queries used:

Global median:

SET @rownum = 0;
SELECT AVG(loadEventEnd) AS median_loadEventEnd FROM
  (SELECT (@rownum:= @rownum +1) AS row_number, loadEventEnd FROM (
    SELECT event_loadEventEnd AS loadEventEnd FROM log.NavigationTiming_15033442 WHERE timestamp BETWEEN '20151222' AND '20160209' AND event_loadEventEnd IS NOT NULL AND wiki = 'enwiki' AND event_mobileMode IS NOT NULL ORDER BY loadEventEnd) AS unnumberedlist
  ORDER BY loadEventEnd) AS numberedlist
INNER JOIN
  (SELECT COUNT(*) AS total_rows FROM log.NavigationTiming_15033442 WHERE timestamp BETWEEN '20151222' AND '20160209' AND event_mobileMode IS NOT NULL AND event_loadEventEnd IS NOT NULL AND wiki = 'enwiki') AS row_counting
ON 1=1
WHERE
numberedlist.row_number >= row_counting.total_rows/2 AND
numberedlist.row_number <= row_counting.total_rows/2+1;

Median per country:

SET @prev ='init';
SET @rownum = 0;
SELECT numberedlist.country, AVG(numberedlist.loadEventEnd) AS median_loadEventEnd, AVG(rows_per_country.total_rows) AS samples FROM
  (SELECT IF(@prev <> ordered_data.country, @rownum:=1, @rownum:= @rownum +1) AS row_number, ordered_data.loadEventEnd, @prev:= ordered_data.country, ordered_data.country AS country FROM (
       SELECT event_loadEventEnd AS loadEventEnd, event_originCountry AS country
       FROM log.NavigationTiming_15033442
       WHERE timestamp BETWEEN '20151222' AND '20160209' AND event_loadEventEnd IS NOT NULL AND wiki = 'enwiki' AND event_mobileMode IS NOT NULL
       ORDER BY country, loadEventEnd) ordered_data
  ORDER BY country, loadEventEnd) AS numberedlist
INNER JOIN
  (SELECT COUNT(*) AS total_rows, event_originCountry AS country FROM log.NavigationTiming_15033442
  WHERE timestamp BETWEEN '20151222' AND '20160209' AND event_loadEventEnd IS NOT NULL AND wiki = 'enwiki' AND event_mobileMode IS NOT NULL GROUP BY country)
  AS rows_per_country
ON numberedlist.country = rows_per_country.country
WHERE
numberedlist.row_number >= rows_per_country.total_rows/2 AND
numberedlist.row_number <= rows_per_country.total_rows/2+1
GROUP BY numberedlist.country
ORDER BY numberedlist.loadEventEnd;

Distribution per country:

SELECT FLOOR(LOG2(event_loadEventEnd/1000)*2)/2 AS bucket, event_originCountry AS country, COUNT(*) FROM log.NavigationTiming_15033442 WHERE timestamp BETWEEN '20151222' AND '20160209' AND event_loadEventEnd IS NOT NULL AND wiki = 'enwiki' AND event_mobileMode IS NOT NULL GROUP BY bucket, country ORDER BY bucket, country

The spreadsheet with the source data and chart is here, again with an easily changeable country selection.

To recap some of the off-Phab discussion about this:

There are various other interesting directions one can explore about this data from here on. We should figure out what the most important questions are for the purpose of informing actual product decisions. One early thought was to quantify how much connection speed varies among readers in a particular (or in the average) country (using a suitable dispersion measure, e.g. median absolute deviation), but that doesn't seem too relevant right now. For pursuing the idea of using geolocation information to decide whether to serve a version optimized for slow connections, one could define a threshold speed, above which we assume that that version is the better choice for the reader; we could then quickly find all countries where (say) more than 90% of pageviews occur with a slower speed. Etc.

My 2 cents:

If you want to evaluate actual loading speeds you also need to take a look at the 90th percentile as performance-wise you want to examine the loading event if most of resources come from cache (median) and if is a complete fresh request (90th percentile). The latency distribution is bimodal.

Without those two numbers you cannot know if improvements in performance are truly effective. Or even, what performance really is.

In T125414#2027275, @Nuria wrote:

My 2 cents:

If you want to evaluate actual loading speeds you also need to take a look at the 90th percentile as performance-wise you want to examine the loading event if most of resources come from cache (median) and if is a complete fresh request (90th percentile). The latency distribution is bimodal.

Without those two numbers you cannot know if improvements in performance are truly effective. Or even, what performance really is.

Thanks Nuria - I already read your thoughtful and interesting Wikitech notes, including the observation that "latency is bimodal"before running these queries. It is however my understanding that on Wikimedia sites, considerably more than 90% of pageviews are cached (96-98% at one point in late 2014), i.e. that this assumption about the 90th percentile is not true on our case. The distribution graphs above appear to confirm this: while that second uncached mode may surely exist, it is not clearly discernable globally and for these example countries, at least not at these resolutions and sample sizes. Anyway, I'll let the performance team weigh in, who have the most experience in measuring this kind of thing and have been relying on medians quite frequently.

I've covered some of the above data in a recent tech talk, and took the occasion to visualize the country data in two maps:
https://commons.wikimedia.org/wiki/File:Median_Wikipedia_page_load_times_by_country_(mobile,_enwiki,_Dec_2015-Feb_2016).svg https://commons.wikimedia.org/wiki/File:Median_Wikipedia_page_load_times_by_country_(desktop%2Bmobile,_enwiki,_Dec_2015-Jan_2016).svg

@Tbayer: Really Nice Maps! Would love a link to the talk if any.

It is however my understanding that on Wikimedia sites, considerably more than 90% of pageviews are cached (96-98% at one point in late 2014), i.e. that this assumption about the 90th percentile is not true on our case

This is incorrect, I am talking about client side caching. not server side. Server side you are correct, our requests are cached to a high degree. Client side no, our users request data (just like any web user) with a mix of full and cold caches. Think that every time we deploy a new javascript version of a file every user needs to fetch it and use it until we update it again. This fetching times are going to show on a 90th percentile, not a median. Let me know if this makes sense.

Okay to close this task?

In T125414#2327204, @dr0ptp4kt wrote:

Okay to close this task?

Yes, from my perspective it was all done some months ago ;)
@ori , I assume you don't need anything else here currently? Happy to do followup analyses as needed. (There have been some other uses for this data since, e.g. here.)

In T125414#2143382, @Nuria wrote:

@Tbayer: Really Nice Maps! Would love a link to the talk if any.

Thanks! The complete slides are here in case they didn't cross your path in the meantime: https://commons.wikimedia.org/wiki/File:New_readership_data_(Wikimedia_Foundation_Tech_Talk).pdf

It is however my understanding that on Wikimedia sites, considerably more than 90% of pageviews are cached (96-98% at one point in late 2014), i.e. that this assumption about the 90th percentile is not true on our case

This is incorrect, I am talking about client side caching. not server side. Server side you are correct, our requests are cached to a high degree. Client side no, our users request data (just like any web user) with a mix of full and cold caches. Think that every time we deploy a new javascript version of a file every user needs to fetch it and use it until we update it again. This fetching times are going to show on a 90th percentile, not a median. Let me know if this makes sense.

The percentage quoted was actually correct ;) but yes, I was talking about server-side caching, and I agree that client-side caching should be taken into account too. That however also means that the "Latency is bimodal" claim was too simplistic in another sense as well (two different kind of caching would correspond to four modes instead of two).
In any case, as we have seen here, it is at odds with the actual data from the English Wikipedia. I have left a comment on the talk page of the Wikitech documentation page: https://wikitech.wikimedia.org/wiki/Talk:Performance/Analytics#.22Latency_is_bimodal.22.2C_and_caching . As before, I guess that the performance team may have other valuable insights.

That however also means that the "Latency is bimodal" claim was too simplistic in another sense as well (two different kind of caching would correspond to four modes >instead of two).

Given the percentage of our backend caching (i.e. we end up serving mostly from varnish >90% of the time) the signal you are most likely to see is the bimodality due to frontend caching. So, no, you would not see four modes easily in our data. In enwiki you are bundling up all users all countries and thus the bimodality is affected by connection speeds. Makes sense? That doesn't mean that the median is a good representation overall of our user base. Reduce your analysis to users with similar connections and locations and you will see the difference. These are well known issues that affect any website deployed globally, not just enwiki.

See: https://www.igvita.com/2012/04/04/measuring-site-speed-with-navigation-timing/