Cross posting to mobile-l.
---------- Forwarded message ----------
From: *Sean Pringle* <springle(a)wikimedia.org>
Date: Wednesday, June 24, 2015
Subject: [Analytics] analtyics-store eventlogging UNION queries
To: "A mailing list for the Analytics Team at WMF and everybody who has an
interest in Wikipedia and analytics." <analytics(a)lists.wikimedia.org>
Below are a couple examples of recent slow queries running on
analytics-store. They both use subqueries and UNION. Neither push the
WHERE conditions down to the subqueries, which means indexes are not
used. If you include WHERE clauses in both inner and outer queries you
will achieve index usage, especially for these sorts of eventlogging
queries on event_action or wiki.
MySQL / MariaDB query optimizer does do constant propagation, but
UNION tends to trip it up in all sorts of exciting ways. Be verbose,
abandon DRY principle, and make your life easier :-)
Current query:
SELECT Month.Date, COALESCE(Web.Web, 0) AS Web FROM (
SELECT DATE_FORMAT( ADDDATE(CURDATE() - INTERVAL 30 - 1 DAY,
@num:=@num+1), '%Y-%m-%d' ) AS Date
FROM seq_1_to_100, (SELECT @num:=-1) num LIMIT 30 ) AS Month LEFT
JOIN (
SELECT DATE(timestamp) AS Date,
SUM(1) AS Web FROM (
SELECT timestamp, wiki, event_username, event_action,
event_namespace, event_userEditCount FROM MobileWebEditing_5644223
UNION SELECT timestamp, wiki, event_username,
event_action, event_namespace, event_userEditCount
FROM MobileWebEditing_6077315
UNION SELECT timestamp, wiki, event_username,
event_action, event_namespace, event_userEditCount
from MobileWebEditing_6637866
UNION SELECT timestamp, wiki, event_username,
event_action, event_namespace, event_userEditCount
from MobileWebEditing_7675117
UNION SELECT timestamp, wiki, event_username,
event_action, event_namespace, event_userEditCount
from MobileWebEditing_8599025
) as MobileWebEditing
WHERE event_namespace != 0 AND event_action = 'success' AND wiki
!= 'testwiki'
GROUP BY Date
) AS Web ON Month.Date = Web.Date;
Version with WHERE clauses pushed down to subqueries:
SELECT Month.Date, COALESCE(Web.Web, 0) AS Web FROM (
SELECT DATE_FORMAT( ADDDATE(CURDATE() - INTERVAL 30 - 1 DAY,
@num:=@num+1), '%Y-%m-%d' ) AS Date
FROM seq_1_to_100, (SELECT @num:=-1) num LIMIT 30 ) AS Month LEFT
JOIN (
SELECT DATE(timestamp) AS Date,
SUM(1) AS Web FROM (
SELECT timestamp, wiki, event_username, event_action,
event_namespace, event_userEditCount FROM MobileWebEditing_5644223
UNION SELECT timestamp, wiki, event_username,
event_action, event_namespace, event_userEditCount
FROM MobileWebEditing_6077315 WHERE event_namespace !=
0 AND event_action = 'success' AND wiki != 'testwiki'
UNION SELECT timestamp, wiki, event_username,
event_action, event_namespace, event_userEditCount
from MobileWebEditing_6637866 WHERE event_namespace !=
0 AND event_action = 'success' AND wiki != 'testwiki'
UNION SELECT timestamp, wiki, event_username,
event_action, event_namespace, event_userEditCount
from MobileWebEditing_7675117 WHERE event_namespace !=
0 AND event_action = 'success' AND wiki != 'testwiki'
UNION SELECT timestamp, wiki, event_username,
event_action, event_namespace, event_userEditCount
from MobileWebEditing_8599025 WHERE event_namespace !=
0 AND event_action = 'success' AND wiki != 'testwiki'
) as MobileWebEditing
WHERE event_namespace != 0 AND event_action = 'success' AND wiki
!= 'testwiki'
GROUP BY Date
) AS Web ON Month.Date = Web.Date;
Current query (hours):
SELECT day,
`Attempted saves` / `Loads` AS "All wikis",
`Attempted saves - dewiki` / `Loads - dewiki` AS "dewiki",
`Attempted saves - enwiki` / `Loads - enwiki` AS "enwiki",
`Attempted saves - eswiki` / `Loads - eswiki` AS "eswiki",
`Attempted saves - frwiki` / `Loads - frwiki` AS "frwiki",
`Attempted saves - ptwiki` / `Loads - plwiki` AS "plwiki",
`Attempted saves - plwiki` / `Loads - ptwiki` AS "ptwiki" FROM (
SELECT DATE( timestamp ) AS day,
SUM( IF( event_action = 'ready', 1, 0 ) ) AS "Loads",
SUM( IF( event_action = 'ready' AND wiki = 'dewiki', 1, 0 ) ) AS
"Loads - dewiki",
SUM( IF( event_action = 'ready' AND wiki = 'enwiki', 1, 0 ) ) AS
"Loads - enwiki",
SUM( IF( event_action = 'ready' AND wiki = 'eswiki', 1, 0 ) ) AS
"Loads - eswiki",
SUM( IF( event_action = 'ready' AND wiki = 'frwiki', 1, 0 ) ) AS
"Loads - frwiki",
SUM( IF( event_action = 'ready' AND wiki = 'plwiki', 1, 0 ) ) AS
"Loads - plwiki",
SUM( IF( event_action = 'ready' AND wiki = 'ptwiki', 1, 0 ) ) AS
"Loads - ptwiki",
SUM( IF( event_action = 'saveAttempt', 1, 0 ) ) AS "Attempted saves",
SUM( IF( event_action = 'saveAttempt' AND wiki = 'dewiki', 1, 0 )
) AS "Attempted saves - dewiki",
SUM( IF( event_action = 'saveAttempt' AND wiki = 'enwiki', 1, 0 )
) AS "Attempted saves - enwiki",
SUM( IF( event_action = 'saveAttempt' AND wiki = 'eswiki', 1, 0 )
) AS "Attempted saves - eswiki",
SUM( IF( event_action = 'saveAttempt' AND wiki = 'frwiki', 1, 0 )
) AS "Attempted saves - frwiki",
SUM( IF( event_action = 'saveAttempt' AND wiki = 'plwiki', 1, 0 )
) AS "Attempted saves - plwiki",
SUM( IF( event_action = 'saveAttempt' AND wiki = 'ptwiki', 1, 0 )
) AS "Attempted saves - ptwiki" FROM (
SELECT event_action, wiki, timestamp
FROM Edit_10604157
UNION ALL
SELECT event_action, wiki, timestamp
FROM Edit_10676603
) AS valid_edit_schema_revisions
GROUP BY day
ORDER BY day
) as internalQuery;
Version with WHERE clauses pushed down to subqueries:
SELECT day,
`Attempted saves` / `Loads` AS "All wikis",
`Attempted saves - dewiki` / `Loads - dewiki` AS "dewiki",
`Attempted saves - enwiki` / `Loads - enwiki` AS "enwiki",
`Attempted saves - eswiki` / `Loads - eswiki` AS "eswiki",
`Attempted saves - frwiki` / `Loads - frwiki` AS "frwiki",
`Attempted saves - ptwiki` / `Loads - plwiki` AS "plwiki",
`Attempted saves - plwiki` / `Loads - ptwiki` AS "ptwiki" FROM (
SELECT DATE( timestamp ) AS day,
SUM( IF( event_action = 'ready', 1, 0 ) ) AS "Loads",
SUM( IF( event_action = 'ready' AND wiki = 'dewiki', 1, 0 ) ) AS
"Loads - dewiki",
SUM( IF( event_action = 'ready' AND wiki = 'enwiki', 1, 0 ) ) AS
"Loads - enwiki",
SUM( IF( event_action = 'ready' AND wiki = 'eswiki', 1, 0 ) ) AS
"Loads - eswiki",
SUM( IF( event_action = 'ready' AND wiki = 'frwiki', 1, 0 ) ) AS
"Loads - frwiki",
SUM( IF( event_action = 'ready' AND wiki = 'plwiki', 1, 0 ) ) AS
"Loads - plwiki",
SUM( IF( event_action = 'ready' AND wiki = 'ptwiki', 1, 0 ) ) AS
"Loads - ptwiki",
SUM( IF( event_action = 'saveAttempt', 1, 0 ) ) AS "Attempted saves",
SUM( IF( event_action = 'saveAttempt' AND wiki = 'dewiki', 1, 0 )
) AS "Attempted saves - dewiki",
SUM( IF( event_action = 'saveAttempt' AND wiki = 'enwiki', 1, 0 )
) AS "Attempted saves - enwiki",
SUM( IF( event_action = 'saveAttempt' AND wiki = 'eswiki', 1, 0 )
) AS "Attempted saves - eswiki",
SUM( IF( event_action = 'saveAttempt' AND wiki = 'frwiki', 1, 0 )
) AS "Attempted saves - frwiki",
SUM( IF( event_action = 'saveAttempt' AND wiki = 'plwiki', 1, 0 )
) AS "Attempted saves - plwiki",
SUM( IF( event_action = 'saveAttempt' AND wiki = 'ptwiki', 1, 0 )
) AS "Attempted saves - ptwiki" FROM (
SELECT event_action, wiki, timestamp
FROM Edit_10604157
WHERE event_action in ('saveAttempt', 'ready')
UNION ALL
SELECT event_action, wiki, timestamp
FROM Edit_10676603
WHERE event_action in ('saveAttempt', 'ready')
) AS valid_edit_schema_revisions
GROUP BY day
ORDER BY day
) as internalQuery;
--
DBA @ WMF
_______________________________________________
Analytics mailing list
Analytics(a)lists.wikimedia.org <javascript:;>
https://lists.wikimedia.org/mailman/listinfo/analytics
Hey, when debugging web stuff on the iOS simulator, I see that I can change
the device going to the menu bar Hardware > Device. But I can't seem to
find how to change operating system versions, I'm stuck with 8.2.
I'm sure this is super obvious for the iOSers and I would appreciate some
pointers :)
Thanks!
Moving over to mobile-l.
On Monday, June 8, 2015, Brian Gerstle <bgerstle(a)wikimedia.org> wrote:
> Hey all,
>
> I've added some tests to our regression document in preparation for 4.1.5.
> In a nutshell:
>
> - Added "Languages & Sites" section for switching languages (including
> Norwegian) as a stopgap to check startup on various languages w/o crashing
> - Added a test (81) for deleting "Recent" pages which are also "Saved"
> and ensuring those pages are still "Saved" and available offline
> - Clarified some tests in the "Offline Functionality" section (78-80)
> - Added "Crash Reporting" to make sure we don't accidentally break
> crash reporting again!
> - Added a link to YouTube for data import instructions for migration
> testing
>
> Let me know if you think I forgot anything,
>
> Brian
>
> --
> EN Wikipedia user page: https://en.wikipedia.org/wiki/User:Brian.gerstle
> IRC: bgerstle
>
Hi friends,
Recently we've merged in Gather a patch so that the extension uses
precompiled templates ( Custom TemplateParser for MobileFrontend and Gather
https://phabricator.wikimedia.org/T102941 ) because of a bug in HHVMs
closure creation ( https://phabricator.wikimedia.org/T102937 ).
Once hhvm is patched and running everywhere we feel like it is safer to
keep precompiled templates on Gather since the extension makes fair use of
templates sharing them between the server and js client code.
If you are developing or plan to develop patches for the Gather extension
be sure to include $wgGatherRecompileTemplate = true in LocalSettings.php or
equivalent during development. That way templates will be recompiled and
written to disk every time you visit the page that renders them, so that
you can submit them with your patches.
We'll keep an eye on the tracking bug and remove this when it is safe (it
is a bit cumbersome, but the best solution considering the tradeoffs right
now).
I've updated the Extension:Gather page and added the information (
https://www.mediawiki.org/wiki/Extension:Gather#Development).
Cheers.
One of the reasons we've always worried about using the open Ogg and WebM
formats on iPhones and iPads is that we don't get to make use of the
hardware MP4/H.264 codec... using the main CPU cores is presumed to drain
the battery faster.
I've done a first-pass test measuring energy usage of native-code WebM/Ogg
playback using the new energy reporting in the Xcode 7 / iOS 9 beta:
https://brionv.com/log/2015/06/19/webm-and-ogg-energy-usage-on-ios-9-beta-w…
The good news is that playback of buffered data at my target resolutions
(360p for 32-bit, 720p for 64-bit) is barely under the "Low" mark on the
energy drain meter! :D
The bad news is that H.264 playback with the native widget reports
post-buffer-download energy drain even lower, at the "Zero" mark... if you
can believe that!
(In both cases, reported drain is significantly higher during network
download, at least on my fast wifi.)
But "Low" sounds pretty good... If folks would like to see more concrete
measures, I can rig up my test to run continuously until the battery runs
out and time it.
-- brion
Background: mobile wants to gain more confidence in its browser tests
by running a subset of browser tests on a case by case basis [0].
Good news: I've got a proof of concept running and Barry the browser
test bot has given some legitimate helpful reviews to Gather [1].
Even better news: It's proving itself valuable already [2].
As you can see in the messages the bot has posted on [3] we have a
couple of options on display option format for his reviews.
So.. hopefully this short experience has sold you all already.
This script is currently a manual job and needs a bit of tweaking
before we can put it in a cron job/run it always - it needs to watch
for new commits and then run a modification of the above script on a
per case basis (if two versions of it run in parallel we have an
issue).
Definitely something we should push for next sprint!
Long live Barry bot!
Devs... (everyone else now of what follows is likely to be useful):
I got the labs instance up and running on:
http://gather-browser-tests.wmflabs.org/wiki/Main_Page
Most of you in readership team should be able to ssh
gather-browser-tests.eqiad.wmflabs
Let me know if you have no access.
It's currently working via a script that you can find here:
/srv/mediawiki/extensions/Gather/tests/browser/Barry.sh
[0] https://phabricator.wikimedia.org/T100293
[1] https://gerrit.wikimedia.org/r/#/q/reviewer:jdlrobson%252Bbarry%2540gmail.c…
[2] https://gerrit.wikimedia.org/r/#/c/218731/
Moving to mobile-l.
On Friday, June 12, 2015, Sam Smith <samsmith(a)wikimedia.org> wrote:
> Hey web slingers,
>
> If there is a regression introduced by a patch, then please revert that
> patch as soon as you've identified it and let the team know via
> Phabricator, email, or both. Reverting the commit will often be cheaper to
> do than fixing the regression in a follow-on patch, but there'll
> undoubtedly be exceptions, which we'll deal with (and learn from) as a team.
>
> Fixing the regression in a follow-on patch means that:
>
> - *master won't be deployable* until the patch has been reviewed,
> tested, and merged, which should be communicated to the Release Engineering
> team
> - reviewers might have to drop what they're working on in order to get
> it reviewed
> - what if the original patch was lower priority?
> - we should be cognisant of the cost of context switching
> - the commit history will be dirty
>
> *Master should always be depoyable.*
>
> –Sam
>
Hi,
*TLDR:* Wikigrok proved that readers are interested in and capable of
making casual, mobile contributions to Wikipedia. We are putting continued
development of the 'Wikigrok' casual contribution feature on hold until we
have a plan for optimally harnessing this interest/capability.
*Background*
Given the growth of mobile traffic on wikipedia and the challenges inherent
to traditional editing on a mobile device, Wikigrok was proposed as a way
to test if regular wikipedia readers would be interested in making smaller,
more casual contributions to wikimedia projects while reading Wikipedia on
a mobile device.
*Results*
By early 2015, the results were in: readers were relatively interested in
engaging with the feature[1]. Some oft-quoted comparisons include:
- 3x the number of unique responders as mobile editors during test
period (4.5K editors, 12.3K WikiGrokkers), even with WG on sample of
articles & users
- 1.5x better clickthrough than 2014 Fundraising full-screen mobile
banner
(I actually do not have references for these, as they are borrowed quotes)
Furthermore, we found that the quality of responses was rather high [2,3].
*Future*
The original thought was to use these responses to fill in gaps in Wikidata
and our initial test results (2 weeks worth) were successfully ported over
in late April [4]. However, in order to production-ize the system, we would
have to:
1. scale and develop queries against the new wikidata query service
2. create an article parser to identify potential multiple choice
answers for each question
3. create a system for attributing aggregated results to the specific
contributors (per Wikidata bot request discussion[5])
None of these are unsurpassable, but we have learned a great deal and, at
this stage, we believe that further effort should be devoted to evaluating
areas of need and fit before we commit additional efforts to specifically
porting information into Wikidata.
Please feel free to reach out if you have any questions or concerns about
this decision.
Best,
Jon
[1] https://meta.wikimedia.org/wiki/Research:WikiGrok/Test2
[2] Quality of responses, version A:
https://www.wikidata.org/wiki/File:All_Campagins,_Scatterplot,_version_(a).…
[3] Quality of responses, version B:
https://www.wikidata.org/wiki/File:All_Campaigns,_Scatterplot,_version_(b).…
[4] *https://www.wikidata.org/wiki/Special:Contributions/WikiGrok?limit=500
<https://www.wikidata.org/wiki/Special:Contributions/WikiGrok?limit=500>*
[5]
https://www.wikidata.org/wiki/Wikidata:Requests_for_permissions/Bot/WikiGrok