Page MenuHomePhabricator
Create Task
Maniphest T360160

Slow query in Special:ListUsers with new block schema
Closed, ResolvedPublic
Actions

Description

SELECT  user_name,MAX(user_id) AS `user_id`,MAX(user_editcount) AS `edits`,MIN(user_registration) AS `creation`,MAX(bl_deleted) AS `deleted`,MAX(bl_sitewide) AS `sitewide`  FROM `user` LEFT JOIN `user_groups` ON ((user_id=ug_user)) LEFT JOIN (`block_target` JOIN `block` ON ((bl_target=bt_id))) ON ((user_id=bt_user) AND bt_auto = 0)    GROUP BY user_name HAVING (deleted = 0 OR deleted IS NULL) ORDER BY user_name LIMIT 51

The query is fast if the HAVING clause is removed.

Error
normalized_message
Expectation (readQueryTime <= 5) by MediaWiki\Actions\ActionEntryPoint::execute not met (actual: {actualSeconds}) in trx #{trxId}:
{query}
exception.trace
from /srv/mediawiki/php-1.42.0-wmf.21/includes/libs/rdbms/TransactionProfiler.php(525)
#0 /srv/mediawiki/php-1.42.0-wmf.21/includes/libs/rdbms/TransactionProfiler.php(334): Wikimedia\Rdbms\TransactionProfiler->reportExpectationViolated(string, Wikimedia\Rdbms\GeneralizedSql, double, string, string)
#1 /srv/mediawiki/php-1.42.0-wmf.21/includes/libs/rdbms/database/TransactionManager.php(615): Wikimedia\Rdbms\TransactionProfiler->recordQueryCompletion(Wikimedia\Rdbms\GeneralizedSql, double, boolean, integer, string, string)
#2 /srv/mediawiki/php-1.42.0-wmf.21/includes/libs/rdbms/database/Database.php(828): Wikimedia\Rdbms\TransactionManager->recordQueryCompletion(Wikimedia\Rdbms\GeneralizedSql, double, boolean, integer, string)
#3 /srv/mediawiki/php-1.42.0-wmf.21/includes/libs/rdbms/database/Database.php(716): Wikimedia\Rdbms\Database->attemptQuery(Wikimedia\Rdbms\Query, string, boolean)
#4 /srv/mediawiki/php-1.42.0-wmf.21/includes/libs/rdbms/database/Database.php(643): Wikimedia\Rdbms\Database->executeQuery(Wikimedia\Rdbms\Query, string, integer)
#5 /srv/mediawiki/php-1.42.0-wmf.21/includes/libs/rdbms/database/Database.php(1350): Wikimedia\Rdbms\Database->query(Wikimedia\Rdbms\Query, string)
#6 /srv/mediawiki/php-1.42.0-wmf.21/includes/libs/rdbms/database/DBConnRef.php(119): Wikimedia\Rdbms\Database->select(array, array, array, string, array, array)
#7 /srv/mediawiki/php-1.42.0-wmf.21/includes/libs/rdbms/database/DBConnRef.php(351): Wikimedia\Rdbms\DBConnRef->__call(string, array)
#8 /srv/mediawiki/php-1.42.0-wmf.21/includes/pager/IndexPager.php(467): Wikimedia\Rdbms\DBConnRef->select(array, array, array, string, array, array)
#9 /srv/mediawiki/php-1.42.0-wmf.21/includes/pager/IndexPager.php(289): MediaWiki\Pager\IndexPager->reallyDoQuery(string, integer, boolean)
#10 /srv/mediawiki/php-1.42.0-wmf.21/includes/pager/IndexPager.php(571): MediaWiki\Pager\IndexPager->doQuery()
#11 /srv/mediawiki/php-1.42.0-wmf.21/includes/specials/SpecialListUsers.php(86): MediaWiki\Pager\IndexPager->getBody()
#12 /srv/mediawiki/php-1.42.0-wmf.21/includes/specialpage/SpecialPage.php(720): MediaWiki\Specials\SpecialListUsers->execute(NULL)
#13 /srv/mediawiki/php-1.42.0-wmf.21/includes/specialpage/SpecialPageFactory.php(1654): MediaWiki\SpecialPage\SpecialPage->run(NULL)
#14 /srv/mediawiki/php-1.42.0-wmf.21/includes/actions/ActionEntryPoint.php(504): MediaWiki\SpecialPage\SpecialPageFactory->executePath(string, MediaWiki\Context\RequestContext)
#15 /srv/mediawiki/php-1.42.0-wmf.21/includes/actions/ActionEntryPoint.php(145): MediaWiki\Actions\ActionEntryPoint->performRequest()
#16 /srv/mediawiki/php-1.42.0-wmf.21/includes/MediaWikiEntryPoint.php(199): MediaWiki\Actions\ActionEntryPoint->execute()
#17 /srv/mediawiki/php-1.42.0-wmf.21/index.php(58): MediaWiki\MediaWikiEntryPoint->run()
#18 /srv/mediawiki/w/index.php(3): require(string)
#19 {main}

Details

Related Changes in Gerrit:
SubjectRepoBranchLines +/-
block: In UsersPager use a subquery instead of HAVING()mediawiki/coremaster+35 -16
Customize query in gerrit

Related Objects
Search...

Event Timeline

tstarling created this task.Mar 14 2024, 10:39 PM
tstarling mentioned this in T355034: Deploy new block_target schema.Mar 14 2024, 11:21 PM
tstarling updated the task description. (Show Details)Mar 15 2024, 12:09 AM
gerritbot added a comment.Mar 15 2024, 2:48 AM

Change 1011212 had a related patch set uploaded (by Tim Starling; author: Tim Starling):

[mediawiki/core@master] block: In UsersPager use a subquery instead of HAVING()

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

gerritbot added a project: Patch-For-Review.Mar 15 2024, 2:49 AM
tstarling moved this task from Ready 🎬 to Review/Feedback 💬 on the Community-Tech (CommTech-Kanban) board.Mar 15 2024, 2:53 AM
gerritbot added a comment.Mar 18 2024, 11:56 PM

Change 1011212 merged by jenkins-bot:

[mediawiki/core@master] block: In UsersPager use a subquery instead of HAVING()

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

ReleaseTaggerBot added a project: MW-1.42-notes (1.42.0-wmf.23; 2024-03-19).Mar 19 2024, 12:00 AM
Maintenance_bot removed a project: Patch-For-Review.Mar 19 2024, 12:30 AM
dom_walden added a comment.Mar 21 2024, 2:07 PM

For the requests Special:ListUsers?username=text&editsOnly=1 and Special:ListUsers?desc=1&editsOnly=1, the MediaWiki\Pager\IndexPager::buildQueryInfo query takes about 50ms in read-new and about 30ms in read-old (according to the logs). Curiously, this discrepancy only happens while logged out. While logged in, both read-new and read-old queries take about 20ms.

Read-new while logged out Special:ListUsers?username=text&editsOnly=1:

SELECT user_name,MAX(user_id) AS `user_id`,MAX(user_editcount) AS `edits`,MIN(user_registration) AS `creation`,1=0 AS `deleted`,MAX(bl_sitewide) AS `sitewide` FROM `user` LEFT JOIN `user_groups` ON ((user_id=ug_user)) LEFT JOIN (`block_target` JOIN `block` ON ((bl_target=bt_id))) ON ((user_id=bt_user) AND bt_auto = 0) WHERE ((SELECT 1 FROM `block_target` `hu_block_target` JOIN `block` `hu_block` ON ((hu_block.bl_target=hu_block_target.bt_id)) WHERE (hu_block_target.bt_user=user_id) AND hu_block.bl_deleted = 1 ) IS NULL) AND (user_name >= 'Text') AND (user_editcount > 0) GROUP BY user_name ORDER BY user_name LIMIT 51

Read-old while logged out Special:ListUsers?username=text&editsOnly=1:

SELECT user_name,MAX(user_id) AS `user_id`,MAX(user_editcount) AS `edits`,MIN(user_registration) AS `creation`,1=0 AS `deleted`,MAX(ipb_sitewide) AS `sitewide` FROM `user` LEFT JOIN `user_groups` ON ((user_id=ug_user)) LEFT JOIN `ipblocks` ON ((user_id=ipb_user) AND ipb_auto = 0) WHERE (NOT EXISTS (SELECT 1 FROM `ipblocks` `hu_ipblocks` WHERE (hu_ipblocks.ipb_user=user_id) AND hu_ipblocks.ipb_deleted = 1 )) AND (user_name >= 'Text') AND (user_editcount > 0) GROUP BY user_name ORDER BY user_name LIMIT 51

Read-new while logged in Special:ListUsers?username=text&editsOnly=1:

SELECT user_name,MAX(user_id) AS `user_id`,MAX(user_editcount) AS `edits`,MIN(user_registration) AS `creation`,MAX(CAST( (SELECT 1 FROM `block_target` `hu_block_target` JOIN `block` `hu_block` ON ((hu_block.bl_target=hu_block_target.bt_id)) WHERE (hu_block_target.bt_user=user_id) AND hu_block.bl_deleted = 1 ) IS NOT NULL AS SIGNED )) AS `deleted`,MAX(bl_sitewide) AS `sitewide` FROM `user` LEFT JOIN `user_groups` ON ((user_id=ug_user)) LEFT JOIN (`block_target` JOIN `block` ON ((bl_target=bt_id))) ON ((user_id=bt_user) AND bt_auto = 0) WHERE (user_name >= 'Text') AND (user_editcount > 0) GROUP BY user_name ORDER BY user_name LIMIT 51

Tested on a local docker wiki with mariadb.

tstarling closed this task as Resolved.Mar 25 2024, 12:51 AM

Thanks @dom_walden. I ran those three queries in production on enwiki, and I got times of 149ms, 479ms and 7ms, the latter presumably being due to the warm cache. Running the third query on a different server took 73ms.

The log entry in the task description showed a query running for 28 seconds. It occurred on fawiki where the ipblocks table has 71k rows. To reproduce that locally, you would need to create thousands of blocks. The important thing is that the queries are showing the right time order with respect to the number of rows.

For all four block schema performance bugs, I tested my ideas for rectifying the issues directly on production.

• MusikAnimal moved this task from Review/Feedback 💬 to Done 🏁 on the Community-Tech (CommTech-Kanban) board.Apr 10 2024, 4:35 PM
Content licensed under Creative Commons Attribution-ShareAlike (CC BY-SA) 4.0 unless otherwise noted; code licensed under GNU General Public License (GPL) 2.0 or later and 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 · Credits