Page MenuHomePhabricator
Create Task
Maniphest T288792

Improve performance of CentralAuth
Open, Needs TriagePublic
Actions

Description

I see this:

[13-Aug-2021 01:04:04]  [pool www] pid 19764
script_filename = /srv/mediawiki/w/api.php
[0x00007f1da5e1e7a0] query() /srv/mediawiki/w/includes/libs/rdbms/database/DatabaseMysqli.php:46
[0x00007f1da5e1e720] doQuery() /srv/mediawiki/w/includes/libs/rdbms/database/Database.php:1396
[0x00007f1da5e1e5a0] executeQueryAttempt() /srv/mediawiki/w/includes/libs/rdbms/database/Database.php:1311
[0x00007f1da5e1e3f0] executeQuery() /srv/mediawiki/w/includes/libs/rdbms/database/DatabaseMysqlBase.php:202
[0x00007f1da5e1e2f0] doSelectDomain() /srv/mediawiki/w/includes/libs/rdbms/database/Database.php:2803
[0x00007f1da5e1e280] selectDomain() /srv/mediawiki/w/includes/libs/rdbms/loadbalancer/LoadBalancer.php:1271
[0x00007f1da5e1e130] getForeignConnection() /srv/mediawiki/w/includes/libs/rdbms/loadbalancer/LoadBalancer.php:991
[0x00007f1da5e1e060] getServerConnection() /srv/mediawiki/w/includes/libs/rdbms/loadbalancer/LoadBalancer.php:960
[0x00007f1da5e1df70] getConnection() /srv/mediawiki/w/includes/libs/rdbms/loadbalancer/LoadBalancer.php:1099
[0x00007f1da5e1dea0] getConnectionRef() /srv/mediawiki/w/extensions/CentralAuth/includes/CentralAuthUser.php:2351
[0x00007f1da5e1ddc0] importLocalNames() /srv/mediawiki/w/extensions/CentralAuth/includes/CentralAuthUser.php:2338
[0x00007f1da5e1dd40] lazyImportLocalNames() /srv/mediawiki/w/extensions/CentralAuth/includes/CentralAuthUser.php:2229
[0x00007f1da5e1dcd0] listUnattached() /srv/mediawiki/w/extensions/CentralAuth/includes/CentralAuthPrimaryAuthenticationProvider.php:454
[0x00007f1da5e1dbf0] testUserForCreation() /srv/mediawiki/w/includes/auth/AuthManager.php:1027
[0x00007f1da5e1db00] canCreateAccount() /srv/mediawiki/w/includes/api/ApiQueryUsers.php:311
[0x00007f1da5e1d8a0] execute() /srv/mediawiki/w/includes/api/ApiQuery.php:286
[0x00007f1da5e1d7a0] execute() /srv/mediawiki/w/includes/api/ApiMain.php:1647
[0x00007f1da5e1d720] executeAction() /srv/mediawiki/w/includes/api/ApiMain.php:617
[0x00007f1da5e1d660] executeActionWithErrorHandling() /srv/mediawiki/w/includes/api/ApiMain.php:588
[0x00007f1da5e1d600] execute() /srv/mediawiki/w/api.php:90

in the php-fpm slowlog. For large wikis having it foreach over each wiki name and then start a connection is a slow process (because for example it has to create over 4,000 queries).

Somehow this should be changed to reduce queries and improve performance.

Related Objects

Event Timeline

Paladox created this task.Aug 13 2021, 1:42 AM
Restricted Application added a subscriber: Aklapper. · View Herald TranscriptAug 13 2021, 1:42 AM
taavi added a subscriber: taavi.Aug 13 2021, 4:03 AM

That specific code path was eliminated in T150506: Avoid lazyImportLocalNames() master writes on GET requests (Run a script to backfill them once for all).

Paladox added a comment.Sep 8 2021, 4:41 PM

Could also improve the performance for Special:CentralAuth. Some ideas are creating a job to do the edit stat updates rather then doing it all on page load.

I presume the reason why logins are slow is the same reason that page is slow?

taavi added a comment.Sep 8 2021, 6:05 PM
In T288792#7340009, @Paladox wrote:

Could also improve the performance for Special:CentralAuth. Some ideas are creating a job to do the edit stat updates rather then doing it all on page load.

Which stat counters? I'd rather not duplicate practically all displayed per-wiki user data to the CA database.

I presume the reason why logins are slow is the same reason that page is slow?

Probably different reasons.

Paladox added a comment.Sep 8 2021, 6:19 PM
In T288792#7340274, @Majavah wrote:
In T288792#7340009, @Paladox wrote:

Could also improve the performance for Special:CentralAuth. Some ideas are creating a job to do the edit stat updates rather then doing it all on page load.

Which stat counters? I'd rather not duplicate practically all displayed per-wiki user data to the CA database.

I presume the reason why logins are slow is the same reason that page is slow?

Probably different reasons.

I think it just checks each wikis edit table (which isn't future realistic). What if you had 20,000 wikis or a million? Very expensive to run.

taavi added a comment.Sep 8 2021, 6:21 PM
In T288792#7340314, @Paladox wrote:

I think it just checks each wikis edit table (which isn't future realistic). What if you had 20,000 wikis or a million? Very expensive to run.

Core already caches edit counts in the user table. CentralAuth reads it from there, but it takes a while as it needs to read it from every individual wiki.

Paladox added a comment.Sep 8 2021, 6:22 PM
In T288792#7340317, @Majavah wrote:
In T288792#7340314, @Paladox wrote:

I think it just checks each wikis edit table (which isn't future realistic). What if you had 20,000 wikis or a million? Very expensive to run.

Core already caches edit counts in the user table. CentralAuth reads it from there, but it takes a while as it needs to read it from every individual wiki.

Yeh but that's very expensive as a query has to be open to the database for each foreach and also reading it.

Paladox added a comment.Sep 8 2021, 6:28 PM
97.91% 13510.184      1 - wfIndexMain
97.91% 13510.050      1 - MediaWiki::run
97.58% 13463.819      1 - MediaWiki::main
93.93% 12961.417      1 - MediaWiki::performRequest
93.63% 12919.524      1 - MediaWiki\SpecialPage\SpecialPageFactory::executePath
93.63% 12919.118      1 - SpecialPage::run
93.56% 12910.165      1 - SpecialCentralAuth::execute
64.36% 8880.423      1 - CentralAuthUser::queryAttached
62.24% 8587.526    573 - CentralAuthUser::localUserData
36.17% 4990.544   1152 - Wikimedia\Rdbms\LoadBalancer::getConnectionRef
36.14% 4986.084   1172 - Wikimedia\Rdbms\LoadBalancer::getConnection
35.56% 4906.078   1172 - Wikimedia\Rdbms\LoadBalancer::getServerConnection
29.84% 4117.510   2885 - Wikimedia\Rdbms\Database::executeQuery
29.10% 4015.047   1149 - Wikimedia\Rdbms\LoadBalancer::getForeignConnection
29.10% 4014.878   2885 - Wikimedia\Rdbms\Database::executeQueryAttempt
27.97% 3859.694   3989 - array_map
27.73% 3825.916      1 - SpecialCentralAuth::showWikiLists
27.69% 3820.468      1 - SpecialCentralAuth::listAccounts
27.68% 3818.692    573 - SpecialCentralAuth::listWikiItem
24.96% 3444.532   2317 - Wikimedia\Rdbms\Database::query
24.10% 3326.006   2885 - Wikimedia\Rdbms\DatabaseMysqli::doQuery
23.48% 3240.000   2885 - mysqli::query
22.88% 3157.551      6 - Wikimedia\Rdbms\LoadBalancer::reallyOpenConnection
22.84% 3150.868      6 - Wikimedia\Rdbms\Database::initConnection
22.83% 3150.843      6 - Wikimedia\Rdbms\Database::doInitConnection
22.83% 3150.825      6 - Wikimedia\Rdbms\DatabaseMysqlBase::open
22.79% 3145.182      6 - Wikimedia\Rdbms\DatabaseMysqli::mysqlConnect
22.79% 3144.691      6 - mysqli::real_connect
22.50% 3104.466   1750 - Wikimedia\Rdbms\DBConnRef::__call
22.15% 3056.303   1729 - Wikimedia\Rdbms\Database::select
17.05% 2353.276   4227 - Message::format
16.46% 2271.586   4158 - Message::text
15.22% 2100.284   1151 - Wikimedia\Rdbms\DBConnRef::select
13.29% 1834.294    573 - SpecialCentralAuth::formatEditcount
10.49% 1447.528   4932 - Message::fetchMessage
 9.95% 1372.513   4875 - MessageCache::get
 9.48% 1307.394    574 - MediaWiki\User\UserGroupManager::getUserGroupMemberships
 8.20% 1131.602   4205 - Message::transformText
 7.77% 1072.799   4218 - MessageCache::transform
 7.59% 1046.845   2867 - WikiMap::getWiki
 7.53% 1039.686   2867 - WikiMap::getWikiReferenceFromWgConf
 7.48% 1032.239    583 - Parser::transformMsg
 7.47% 1030.075    583 - Parser::preprocess
 7.24% 999.163    575 - Wikimedia\Rdbms\DBConnRef::selectRow
 7.20% 993.341    575 - Wikimedia\Rdbms\Database::selectRow
 7.09% 978.543   6510 - MediaWiki\HookContainer\HookContainer::run
 6.79% 936.543   1230 - BagOStuff::getWithSetCallback
 6.36% 878.152   8601 - SiteConfiguration::get
 6.24% 860.782    589 - Parser::replaceVariables
 6.12% 844.115   1172 - Wikimedia\Rdbms\LoadBalancer::isMasterConnectionReadOnly
 5.78% 797.323    573 - section.query-m: SELECT ipb_id,ipb_expiry,ipb_block_email,ipb_anon_only,ipb_create_account,ipb_enable_autoblock,ipb_allow_usertalk,ipb_sitewide,comment_ipb_reason.comment_text AS `ipb_reason_text`,comment_ipb_reason.comment_data AS `ipb_reason_data`,comment_ipb_reason.com
 5.69% 785.503    573 - section.query-m: SELECT ug_user,ug_group,ug_expiry FROM `user_groups` WHERE ug_user = N 
 5.65% 779.592   6624 - SiteConfiguration::processSetting
 5.41% 746.282    568 - Wikimedia\Rdbms\Database::selectDomain
 5.39% 743.393    568 - Wikimedia\Rdbms\DatabaseMysqlBase::doSelectDomain
 5.33% 734.860    716 - Wikimedia\Rdbms\LoadBalancer::Wikimedia\Rdbms\{closure}
 5.30% 730.969    578 - Wikimedia\Rdbms\DatabaseMysqlBase::serverIsReadOnly
 5.26% 726.021    573 - section.query-m: SELECT user_id,user_email,user_name,user_email_authenticated,user_password,user_editcount,user_registration FROM `user` WHERE user_name = 'X' LIMIT N 
 5.20% 717.200   4875 - MessageCache::getMessageFromFallbackChain
 5.11% 705.037   4939 - MessageCache::getMessageForLang
 4.98% 686.718    589 - PPFrame_Hash::expand
 4.40% 607.256    584 - Parser::braceSubstitution
 4.37% 603.294   4879 - MessageCache::getMsgFromNamespace
 4.34% 598.332    573 - SpecialCentralAuth::formatBlockStatus
 4.24% 585.299    578 - section.query-m: SELECT @@GLOBAL.read_only AS Value
 3.89% 536.204   4875 - MediaWiki\HookContainer\HookRunner::onMessageCache__get
 3.82% 526.899   1719 - SpecialCentralAuth::foreignLink
 3.65% 504.298  11516 - MediaWiki\HookContainer\HookContainer::callLegacyHook
 3.60% 496.659      1 - MediaWiki::{closure}
taavi added a comment.Sep 8 2021, 6:49 PM
In T288792#7340318, @Paladox wrote:

Yeh but that's very expensive as a query has to be open to the database for each foreach and also reading it.

The slow log you posted seems to be an expensive edge case with hundreds of attached wikis. An average Wikimedia global account is attached to 3.43 wikis. MW is able to re-use connections to the same section and the reads it is doing are indexed. I'm not really sure the extra used disk space and additional complexity is worth the benefit for a relatively small amount of accounts. I'm still happy to review some patches (as long as they don't involve schema changes as I'd rather not do those here), but I don't intend to write anything myself.

Content licensed under Creative Commons Attribution-ShareAlike 3.0 (CC-BY-SA) unless otherwise noted; code licensed under GNU General Public License (GPL) or other open source licenses. By using this site, you agree to the Terms of Use, Privacy Policy, and Code of Conduct. · Wikimedia Foundation · Privacy Policy · Code of Conduct · Terms of Use · Disclaimer · CC-BY-SA · GPL