Last modified: 2014-02-10 08:31:09 UTC
On profiling it is taking 123s on average, on my test it took over 40s on translatewiki.net. Truncated query: query: SELECT rc_user_text AS user_name,rc_user_text,user_id,COUNT(*) AS recentedits,MAX(ipb_deleted) AS ipb_deleted FROM `bw_recentchanges` FORCE INDEX (rc_user_text) INNER JOIN `bw_user` ON ((rc_user_text=user_name)) LEFT JOIN `bw_ipblocks` ON ((user_id
http://www.mediawiki.org/wiki/Special:ActiveUsers https://github.com/mediawiki/core/commits/master/includes/specials/SpecialActiveusers.php I bet it's from https://github.com/mediawiki/core/commit/341552f6618be866571de5e0f1a6c78038bb856a https://gerrit.wikimedia.org/r/#/c/17945/
Need to grab a full SQL query and run EXPLAIN on it....
Query wikidb (23) (slave): SELECT /* IndexPager::buildQueryInfo (ActiveUsersPager) Reedy */ rc_user_text AS user_name,rc_user_text,user_id,COUNT(*) AS recentedits,MAX(ipb_deleted) AS ipb_deleted FROM `mw_recentchanges` FORCE INDEX (rc_user_text) INNER JOIN `mw_user` ON ((rc_user_text=user_name)) LEFT JOIN `mw_ipblocks` ON ((user_id=ipb_user) AND ipb_auto = '0') WHERE (rc_user > 0) AND (ipb_deleted IS NULL OR ipb_deleted = 0) AND (rc_log_type IS NULL OR rc_log_type != 'newusers') AND (rc_timestamp > '20120917182934')
mysql> explain SELECT /* IndexPager::buildQueryInfo (ActiveUsersPager) Reedy */ rc_user_text AS user_name,rc_user_text,user_id,COUNT(*) AS recentedits,MAX(ipb_deleted) AS ipb_deleted FROM `recentchanges` FORCE INDEX (rc_user_text) INNER JOIN `user` ON ((rc_user_text=user_name)) LEFT JOIN `ipblocks` ON ((user_id=ipb_user) AND ipb_auto = '0') WHERE (rc_user > 0) AND (ipb_deleted IS NULL OR ipb_deleted = 0) AND (rc_log_type IS NULL OR rc_log_type != 'newusers') AND (rc_timestamp > '20120917182934'); +----+-------------+---------------+--------+---------------+-----------+---------+------------------------------------------+-------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------------+--------+---------------+-----------+---------+------------------------------------------+-------+-------------+ | 1 | SIMPLE | recentchanges | ALL | rc_user_text | NULL | NULL | NULL | 11096 | Using where | | 1 | SIMPLE | user | eq_ref | user_name | user_name | 257 | mediawikiwiki.recentchanges.rc_user_text | 1 | Using index | | 1 | SIMPLE | ipblocks | ref | ipb_user | ipb_user | 4 | mediawikiwiki.user.user_id | 1 | Using where | +----+-------------+---------------+--------+---------------+-----------+---------+------------------------------------------+-------+-------------+ 3 rows in set (0.01 sec)
I guess the problem is recentchanges table which is not using index. It's scanning millions of row in translatewiki.net
(In reply to comment #1) > http://www.mediawiki.org/wiki/Special:ActiveUsers > > > https://github.com/mediawiki/core/commits/master/includes/specials/SpecialActiveusers.php > > I bet it's from > https://github.com/mediawiki/core/commit/0 > > https://gerrit.wikimedia.org/r/#/c/17945/ Can you revert that?
(In reply to comment #6) > (In reply to comment #1) > > http://www.mediawiki.org/wiki/Special:ActiveUsers > > > > > > https://github.com/mediawiki/core/commits/master/includes/specials/SpecialActiveusers.php > > > > I bet it's from > > https://github.com/mediawiki/core/commit/0 > > > > https://gerrit.wikimedia.org/r/#/c/17945/ > > Can you revert that? https://gerrit.wikimedia.org/r/28457
Makes only marginal difference: 101 rows in set (35.61 sec) mysql> explain SELECT /* IndexPager::buildQueryInfo (ActiveUsersPager) Nike */ rc_user_text AS user_name,rc_user_text,user_id,COUNT(*) AS recentedits,MAX(ipb_user) AS blocked FROM `bw_recentchanges` FORCE INDEX (rc_user_text) INNER JOIN `bw_user` ON ((rc_user_text=user_name)) LEFT JOIN `bw_ipblocks` ON ((user_id=ipb_user) AND ipb_auto = '0' AND ipb_deleted = '1') WHERE (rc_user > 0) AND (ipb_deleted IS NULL) AND (rc_log_type IS NULL OR rc_log_type != 'newusers') AND (rc_timestamp >= '20120917223021') GROUP BY rc_user_text,user_id ORDER BY rc_user_text LIMIT 101; +----+-------------+------------------+--------+---------------+--------------+---------+-----------------------------------------+--------+-------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------------+--------+---------------+--------------+---------+-----------------------------------------+--------+-------------------------+ | 1 | SIMPLE | bw_recentchanges | index | rc_user_text | rc_user_text | 273 | NULL | 155136 | Using where | | 1 | SIMPLE | bw_user | eq_ref | user_name | user_name | 257 | mediawiki.bw_recentchanges.rc_user_text | 1 | Using index | | 1 | SIMPLE | bw_ipblocks | ref | ipb_user | ipb_user | 4 | mediawiki.bw_user.user_id | 1 | Using where; Not exists | +----+-------------+------------------+--------+---------------+--------------+---------+-----------------------------------------+--------+-------------------------+ 3 rows in set (0.01 sec)
That pages was slow on translatewiki before my changes, because translatewiki has set RCMaxAge to a high value and that result in many rows in the recentchange table. It is hard to say, which query will give the best result for this.
(In reply to comment #9) > That pages was slow on translatewiki before my changes, because translatewiki > has set RCMaxAge to a high value and that result in many rows in the > recentchange table. > > It is hard to say, which query will give the best result for this. It just seemed a likely candidate as it had been deployed, then it was noticed it was slow. Granted, it's not that commit. Reverted https://gerrit.wikimedia.org/r/#/c/28689/
Can this problem still be seen?
http://translatewiki.net/wiki/Special:ActiveUsers It's certainly not fast... <!-- Served in 12.943 secs. --> Less than half a second on enwiki
query reduced with Gerrit change #51290
(In reply to comment #12) > <!-- Served in 12.943 secs. --> (In reply to comment #13) > query reduced with Gerrit change #51290 -rakkaus:#mediawiki-i18n- [nike] updated translatewiki.net to 7ca8090 2013-03-15 14:32:34 +0000 (45 seconds ago) Now: <!-- Served in 7.325 secs. --> <!-- Served in 6.851 secs. -->
special page was removed with Gerrit change #55807
Please revert this change. This page is (was?) very useful on many small and even medium wikis with relatively low number of edits and it was not a performance hit there. The summarized, up-to-date information about active users or even a list of active users is very useful. If there is a performance problem on some wikis (translatewiki?) it should be solved in a per-wiki way. I see at least three ways: 1. limit time period or number of analyzed edits 2. use some caching/locking to avoid this call be called too often 3. disable it in per-wiki way only on wikis where a problem occurs Or, at least, offer an alternative while removing this page.
(CC-ing code submitter and reviewers.)
One thing that slowed it down was showing the count of the recent edits (an expensive GROUP BY). It could have used a LIMIT 10 subquery for each or just not have that count at all and maybe just use user_editcount (total edits, recent or not).
*** Bug 46938 has been marked as a duplicate of this bug. ***
(In reply to comment #16) > Please revert this change. > > This page is (was?) very useful on many small and even medium wikis with > relatively low number of edits and it was not a performance hit there. > The summarized, up-to-date information about active users or even a list of > active users is very useful. > If there is a performance problem on some wikis (translatewiki?) it should be > solved in a per-wiki way. I see at least three ways: > > 1. limit time period or number of analyzed edits > > 2. use some caching/locking to avoid this call be called too often > > 3. disable it in per-wiki way only on wikis where a problem occurs > > Or, at least, offer an alternative while removing this page. +1 on this revert. I'm sorry, but it was a 'axe it' mentality based load on wikipedia because of its unique - large data set. If the query is trash, tweak or improve it. Pull it from somewhere else. Too many smaller wikis rely on what is already a small set of metric data for Mediawiki. Rate limit it, cache it or allow for disabling with a boolean switch.
If the addition of this feature was "poorly thought out," surely its removal had even less consideration. Looking at <https://gerrit.wikimedia.org/r/55807>, was this feature really removed in just 20 minutes? Won't this be a breaking change in the MediaWiki API (among the other ripple effects)? (In reply to comment #12) > http://translatewiki.net/wiki/Special:ActiveUsers > > It's certainly not fast... > > <!-- Served in 12.943 secs. --> So on the wiki that set its "recent changes" to be _years_, it's slightly slow. On a giant production database, however... > Less than half a second on enwiki This really justified removing the feature in the course of an hour? I'm a bit baffled here.
It was slow on WMF too: [23:45:38] binasher> ^demon: i noticed that one of the enwiki slaves was lagging a bunch and getting a bunch of ActiveUsersPager queries.. looks like an uncommonly called page, but the query results in just about a full scan of recentchanges with an on disk temp table [23:46:03] binasher> takes around 2 minutes on a busy server if all pages are in the bufferpool [23:46:08] binasher> and 8 if not
Hi, We have been using this metric in Tamil Wikipedia to identify and encourage new and active users. Can we get this feature back, please? At least, you can switch this on and off per wiki or provide an option to run this through extensions. Thanks.
+1 to Ravishankar. In Lezgi Wikipedia we have the rules of administrators election - voter must have at least 10 edits in the «Articles», «Templates» and/or «Category» namespaces within the last 30 days.
I wonder, why I didn't read it in the wikitech-ambassadors mailing list. Perhaps this change was just not submitted to that mailing list?
(In reply to comment #25) > I wonder, why I didn't read it in the wikitech-ambassadors mailing list. > Perhaps this change was just not submitted to that mailing list? Related: bug 47350 ("Restore Special:ActiveUsers prior to the release of MediaWiki 1.21")
If the query really is that slow, could be re-done as a cached special page perhaps.
*** Bug 47559 has been marked as a duplicate of this bug. ***
(In reply to comment #15) > special page was removed with Gerrit change #55807 Back with Gerrit change #58686 Gerrit change #62408 refactored the query. Please test if this helps with the performance. Thanks.
Serves in about one second on translatewiki.net now. Should we close this bug now?
(In reply to comment #30) > Serves in about one second on translatewiki.net now. Should we close this bug > now? Probably. On the other hand, it should be restored on en.wiki with 1.22wmf4 on Monday, May 20, 2013, so we could as well wait a week and see.
Restore Active Users in small Wikipedias! The decision to remove it was harmful for small Wikis!
(In reply to comment #32) > Restore Active Users in small Wikipedias! The decision to remove it was > harmful > for small Wikis! As I said, it's going to be restored on all wikis (and then disabled again on some wikis if needed). See the calendar for 1.22wmf4 code update: https://www.mediawiki.org/wiki/MediaWiki_1.22/Roadmap
All wikis now on 1.22wmf4, it is still active on enwiki, looks like the new query is not slow anymore -> FIXED
(In reply to comment #34) > All wikis now on 1.22wmf4, it is still active on enwiki, looks like the new > query is not slow anymore -> FIXED It's still sometimes slow for enwiki/wikidatawiki, not sure exactly why though.
For the records, on en.wiki it's now like this: Served by mw1022 in 0.453 secs. Served by mw1077 in 0.343 secs. Served by mw1043 in 0.467 secs.
Query cache and other caching layers likely affect any attempts at benchmarking.
Change 110299 had a related patch set uploaded by Nemo bis: Made ActiveUsers use querycache and do staggered updates on view https://gerrit.wikimedia.org/r/110299
Change 110299 merged by jenkins-bot: Made ActiveUsers use querycache and do staggered updates on view https://gerrit.wikimedia.org/r/110299
Change 111258 had a related patch set uploaded by Aaron Schulz: Made ActiveUsers use querycache and do staggered updates on view https://gerrit.wikimedia.org/r/111258
Change 111258 merged by jenkins-bot: Made ActiveUsers use querycache and do staggered updates on view https://gerrit.wikimedia.org/r/111258
