Load time increases on Special:CentralAuth for unregistered accounts
Open, Needs TriagePublic
Actions

Assigned To

None

Authored By

	RhinosF1
	Jun 19 2020, 6:46 PM

Description

On Special:CentralAuth, The load times increase (signifcantly on miraheze running 1.34, not as much on WMF) for unregistered accounts.

I'm not if this expected but it's concerningly slow on Miraheze so I wonder if anything can be made more efficent.

Load times

-	Main Page	Registered User	Unregistered User
WMF	200ms	332ms	743ms
Miraheze	290ms	1171ms	18313ms

Database query times
SELECT * from (local|global)users where (l|g)u_users = <type>;

Miraheze

-	Registered User	Unregistered User
Localuser	0.016	0.001
Globaluser	0.001	0.001

Source: https://phabricator.miraheze.org/P319

WMF

-	Registered	Unregistered
localuser	0.00	0.00
globaluser	0.00	0.00

Source: Reedy on the replica

Details

Related Changes in Gerrit:

	Subject	Repo	Branch	Lines +/-
	Mark connections in CentralAuthUser::importLocalNames() for reuse	mediawiki/extensions/CentralAuth	master	+3 -1

Customize query in gerrit

Related Objects

Mentioned Here: P11610 (An Untitled Masterwork)

Event Timeline

RhinosF1 created this task.Jun 19 2020, 6:46 PM

Restricted Application added subscribers: Reception123, Aklapper. · View Herald TranscriptJun 19 2020, 6:46 PM

RhinosF1 updated the task description. (Show Details)Jun 19 2020, 6:46 PM

I have added the query times for Miraheze. While I am at it I will add in the WMF ones as well.

WMF stats added.

• Examknow added a project: User-Examknow.Jun 19 2020, 6:59 PM

• Examknow moved this task from Radar to Miraheze Related on the User-Examknow board.

RhinosF1 updated the task description. (Show Details)Jun 19 2020, 7:44 PM

RhinosF1 updated the task description. (Show Details)Jun 19 2020, 7:51 PM

RhinosF1 moved this task from Radar to Miraheze-Linked on the User-RhinosF1 board.Jun 19 2020, 8:08 PM

Reedy added a project: Performance Issue.Jun 19 2020, 8:13 PM

taavi subscribed.Jun 19 2020, 8:17 PM

Due to https://github.com/wikimedia/mediawiki-extensions-CentralAuth/blob/master/includes/CentralAuthUser.php#L2362

profile:

P11610 (An Untitled Masterwork)

1	97.81% 4220.354 1 - SpecialCentralAuth::execute
2	87.76% 3786.793 1 - CentralAuthUser::queryAttached
3	86.78% 3744.417 697 - CentralAuthUser::localUserData
4	80.69% 3481.750 3498 - Wikimedia\Rdbms\Database::executeQuery
5	80.18% 3459.786 3498 - Wikimedia\Rdbms\Database::executeQueryAttempt
6	75.95% 3277.207 3498 - Wikimedia\Rdbms\DatabaseMysqli::doQuery
7	75.47% 3256.660 3498 - mysqli::query

97.81% 4220.354      1 - SpecialCentralAuth::execute
87.76% 3786.793      1 - CentralAuthUser::queryAttached
86.78% 3744.417    697 - CentralAuthUser::localUserData
80.69% 3481.750   3498 - Wikimedia\Rdbms\Database::executeQuery
80.18% 3459.786   3498 - Wikimedia\Rdbms\Database::executeQueryAttempt
75.95% 3277.207   3498 - Wikimedia\Rdbms\DatabaseMysqli::doQuery
75.47% 3256.660   3498 - mysqli::query
70.06% 3023.025   2802 - Wikimedia\Rdbms\Database::query
60.29% 2601.312   2119 - Wikimedia\Rdbms\DBConnRef::__call
60.03% 2590.259   2099 - Wikimedia\Rdbms\Database::select
40.44% 1745.122   1398 - Wikimedia\Rdbms\DBConnRef::select
25.49% 1100.048    722 - Wikimedia\Rdbms\LoadBalancer::getConnection
25.34% 1093.259    722 - Wikimedia\Rdbms\LoadBalancer::getServerConnection
24.55% 1059.456    700 - Wikimedia\Rdbms\LoadBalancer::getConnectionRef
20.91% 902.220    697 - section.query-m: SELECT ipb_expiry,ipb_block_email,ipb_anon_only,ipb_create_account,ipb_enable_autoblock,ipb_allow_usertalk,comment_ipb_reason.comment_text AS `ipb_reason_text`,comment_ipb_reason.comment_data AS `ipb_reason_data`,comment_ipb_reason.comment_id AS `ipb_reas
19.81% 854.947    698 - Wikimedia\Rdbms\DBConnRef::selectRow
19.77% 852.997    698 - Wikimedia\Rdbms\Database::selectRow
18.16% 783.684    697 - section.query-m: SELECT user_id,user_email,user_email_authenticated,user_password,user_editcount,user_registration FROM `user` WHERE user_name = 'X' LIMIT N 
17.52% 756.006    697 - UserGroupMembership::getMembershipsForUser
15.97% 689.181    697 - section.query-m: SELECT ug_user,ug_group,ug_expiry FROM `user_groups` WHERE ug_user = 'X' 
12.40% 535.094    722 - Wikimedia\Rdbms\LoadBalancer::isMasterConnectionReadOnly
12.24% 528.095    700 - Wikimedia\Rdbms\LoadBalancer::getForeignConnection
12.21% 526.797    772 - BagOStuff::getWithSetCallback
11.85% 511.403    766 - Wikimedia\Rdbms\LoadBalancer::Wikimedia\Rdbms\{closure}
11.83% 510.419    698 - Wikimedia\Rdbms\DatabaseMysqlBase::serverIsReadOnly
11.11% 479.579    696 - Wikimedia\Rdbms\Database::selectDomain
11.10% 478.809    696 - Wikimedia\Rdbms\DatabaseMysqlBase::doSelectDomain
10.93% 471.506    698 - section.query-m: SELECT @@GLOBAL.read_only AS Value

non existing user:

 99.61% 12236.400      1 - MediaWiki::run
 99.45% 12216.595      1 - MediaWiki::main
 99.12% 12176.972      1 - MediaWiki::performRequest
 99.10% 12174.617      1 - MediaWiki\Special\SpecialPageFactory::executePath
 99.10% 12174.528      1 - SpecialPage::run
 97.66% 11997.071      1 - SpecialCentralAuth::execute
 97.41% 11966.150      1 - CentralAuthUser::queryUnattached
 97.41% 11966.149      1 - CentralAuthUser::listUnattached
 97.40% 11965.048      1 - CentralAuthUser::lazyImportLocalNames
 97.39% 11964.197      1 - CentralAuthUser::importLocalNames
 90.37% 11102.072  13686 - Wikimedia\Rdbms\Database::executeQuery
 89.65% 11013.307  13686 - Wikimedia\Rdbms\Database::executeQueryAttempt
 83.88% 10304.187  13686 - Wikimedia\Rdbms\DatabaseMysqli::doQuery
 83.21% 10221.576  13686 - mysqli::query
 63.31% 7777.062   9131 - Wikimedia\Rdbms\Database::query
 58.99% 7247.020   4581 - Wikimedia\Rdbms\LoadBalancer::getConnection
100 74266:getConnectionRefWikimedia\Rdbms\LoadBalancer:
 58.68% 7208.112   4581 - Wikimedia\Rdbms\LoadBalancer::getServerConnection
 37.44% 4599.089   4581 - Wikimedia\Rdbms\DBConnRef::__call
 37.43% 4598.716   4561 - Wikimedia\Rdbms\DBConnRef::selectField
 37.32% 4585.099   4561 - Wikimedia\Rdbms\Database::selectField
 37.09% 4556.471   4566 - Wikimedia\Rdbms\Database::select
 33.79% 4150.596   4559 - section.query-m: SELECT user_id FROM `user` WHERE user_name = 'X' LIMIT N 
 29.37% 3608.228   4563 - Wikimedia\Rdbms\LoadBalancer::getForeignConnection
 28.93% 3553.630   4581 - Wikimedia\Rdbms\LoadBalancer::isMasterConnectionReadOnly
 28.53% 3505.399   4629 - BagOStuff::getWithSetCallback
 27.86% 3422.502   4670 - Wikimedia\Rdbms\LoadBalancer::Wikimedia\Rdbms\{closure}
 27.82% 3417.227   4560 - Wikimedia\Rdbms\DatabaseMysqlBase::serverIsReadOnly
 27.74% 3408.314   4555 - Wikimedia\Rdbms\Database::selectDomain
 27.71% 3403.973   4555 - Wikimedia\Rdbms\DatabaseMysqlBase::doSelectDomain
 25.66% 3152.646   4560 - section.query-m: SELECT @@GLOBAL.read    0 74266    0     0   5729      0 --:--:--  0:00:12 _only AS Value
  1.88% 231.225    555 - Hooks::callHook

Change 606764 had a related patch set uploaded (by Reedy; owner: Reedy):
[mediawiki/extensions/CentralAuth@master] Mark connections in CentralAuthUser::importLocalNames() for reuse

https://gerrit.wikimedia.org/r/606764

gerritbot added a project: Patch-For-Review.Jun 19 2020, 11:06 PM

Change 606764 abandoned by Reedy:
Mark connections in CentralAuthUser::importLocalNames() for reuse

https://gerrit.wikimedia.org/r/606764

Gopavasanth removed a project: Patch-For-Review.Jun 20 2020, 4:36 PM

RhinosF1 updated the task description. (Show Details)Jun 20 2020, 5:45 PM

Aklapper edited projects, added affects-Miraheze; removed User-RhinosF1.Aug 15 2022, 1:19 PM

TheresNoTime removed a subscriber: RhinosF1.Dec 15 2022, 11:36 PM

Bugreporter moved this task from Backlog to Special:CentralAuth on the MediaWiki-extensions-CentralAuth board.Mar 5 2024, 11:42 AM