Page MenuHomePhabricator
Create Task
Maniphest T360163

Slow query in ApiQueryAllUsers with new block schema
Closed, ResolvedPublic
Actions

Description

SELECT  1=0 AS `hu_deleted`,user_name,user_id  FROM `user`    WHERE (user_name LIKE 'M%' ESCAPE '`' ) AND (NOT EXISTS (SELECT  1  FROM `block_target` `hu_block_target` JOIN `block` ON ((bl_target=hu_block_target.bt_id))   WHERE (hu_block_target.bt_user=user_id) AND bl_deleted = 1  ))  ORDER BY user_name LIMIT 11
Error
normalized_message
Expectation (readQueryTime <= 5) by ApiMain::setRequestExpectations 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/libs/rdbms/querybuilder/SelectQueryBuilder.php(723): Wikimedia\Rdbms\DBConnRef->select(array, array, array, string, array, array)
#9 /srv/mediawiki/php-1.42.0-wmf.21/includes/api/ApiQueryBase.php(415): Wikimedia\Rdbms\SelectQueryBuilder->fetchResultSet()
#10 /srv/mediawiki/php-1.42.0-wmf.21/includes/api/ApiQueryAllUsers.php(253): ApiQueryBase->select(string)
#11 /srv/mediawiki/php-1.42.0-wmf.21/includes/api/ApiQuery.php(704): ApiQueryAllUsers->execute()
#12 /srv/mediawiki/php-1.42.0-wmf.21/includes/api/ApiMain.php(1946): ApiQuery->execute()
#13 /srv/mediawiki/php-1.42.0-wmf.21/includes/api/ApiMain.php(922): ApiMain->executeAction()
#14 /srv/mediawiki/php-1.42.0-wmf.21/includes/api/ApiMain.php(893): ApiMain->executeActionWithErrorHandling()
#15 /srv/mediawiki/php-1.42.0-wmf.21/includes/api/ApiEntryPoint.php(158): ApiMain->execute()
#16 /srv/mediawiki/php-1.42.0-wmf.21/includes/MediaWikiEntryPoint.php(199): MediaWiki\Api\ApiEntryPoint->execute()
#17 /srv/mediawiki/php-1.42.0-wmf.21/api.php(44): MediaWiki\MediaWikiEntryPoint->run()
#18 /srv/mediawiki/w/api.php(3): require(string)
#19 {main}

Details

Related Changes in Gerrit:
SubjectRepoBranchLines +/-
block: Use a scalar subquery in HideUserUtils::getExpressionmediawiki/coremaster+10 -8
Customize query in gerrit

Related Objects
Search...

Event Timeline

tstarling created this task.Mar 14 2024, 10:56 PM
Reedy added a project: Performance Issue.Mar 14 2024, 11:15 PM
tstarling mentioned this in T355034: Deploy new block_target schema.Mar 14 2024, 11:21 PM
tstarling added a comment.Mar 15 2024, 12:26 AM
MariaDB [enwiki]> explain SELECT  1=0 AS `hu_deleted`,user_name,user_id  FROM `user`    WHERE (user_name LIKE 'M%' ESCAPE '`' ) AND (NOT EXISTS (SELECT  1  FROM `block_target` `hu_block_target` JOIN `block` ON ((bl_target=hu_block_target.bt_id))   WHERE (hu_block_target.bt_user=user_id) AND bl_deleted = 1  ))  ORDER BY user_name LIMIT 11;
+------+--------------+-----------------+-------+-----------------+-----------+---------+------------------------------+---------+--------------------------+
| id   | select_type  | table           | type  | possible_keys   | key       | key_len | ref                          | rows    | Extra                    |
+------+--------------+-----------------+-------+-----------------+-----------+---------+------------------------------+---------+--------------------------+
|    1 | PRIMARY      | user            | range | user_name       | user_name | 257     | NULL                         | 7763136 | Using where; Using index |
|    2 | MATERIALIZED | hu_block_target | range | PRIMARY,bt_user | bt_user   | 5       | NULL                         | 810518  | Using where; Using index |
|    2 | MATERIALIZED | block           | ref   | bl_target       | bl_target | 4       | enwiki.hu_block_target.bt_id | 1       | Using where              |
+------+--------------+-----------------+-------+-----------------+-----------+---------+------------------------------+---------+--------------------------+
3 rows in set (0.004 sec)

If I understand this correctly, it's turning the subquery into a materialized temporary table with 810k rows.

tstarling added a comment.Mar 15 2024, 1:24 AM

We used that subquery in 9 different places, so ideally I'd like to save it.

A dependent scalar subquery is not eligible for materialization, so this query is fast:

SELECT user_name,user_id FROM user 
WHERE 
  COALESCE(
    (SELECT max(bl_deleted) FROM block_target JOIN block ON bl_target=bt_id WHERE bt_user=user_id),
    0
  )=0 
  AND user_name LIKE 'M%' ORDER BY user_name LIMIT 11;

I've found two other ways to do this, but they don't retain the neat encapsulation of HideUserUtils::getExpression().

You can set a variable:

MariaDB [enwiki]> SET STATEMENT optimizer_switch='materialization=off' FOR  SELECT  1=0 AS `hu_deleted`,user_name,user_id  FROM `user`    WHERE (user_name LIKE 'M%' ESCAPE '`' ) AND (NOT EXISTS (SELECT  1  FROM `block_target` `hu_block_target` JOIN `block` ON ((bl_target=hu_block_target.bt_id))   WHERE (hu_block_target.bt_user=user_id) AND bl_deleted = 1  ))  ORDER BY user_name LIMIT 11;
...
11 rows in set (0.002 sec)

Or use a join:

MariaDB [enwiki]> SELECT user_name,user_id  FROM `user` LEFT JOIN (block_target JOIN block ON bl_target=bt_id AND bl_deleted=1) ON bt_user=user_id    WHERE (user_name LIKE 'M%' ESCAPE '`' ) AND bl_id IS NULL LIMIT 11;
11 rows in set (0.001 sec)

I tried using IN instead of EXISTS, but the query plan was the same, due to a well-documented optimisation.

gerritbot added a comment.Mar 15 2024, 2:06 AM

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

[mediawiki/core@master] block: Use a scalar subquery in HideUserUtils::getExpression

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

gerritbot added a project: Patch-For-Review.Mar 15 2024, 2:06 AM
tstarling moved this task from Ready 🎬 to Review/Feedback 💬 on the Community-Tech (CommTech-Kanban) board.Mar 15 2024, 2:52 AM
gerritbot added a comment.Mar 17 2024, 10:21 PM

Change 1011210 merged by jenkins-bot:

[mediawiki/core@master] block: Use a scalar subquery in HideUserUtils::getExpression

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

Maintenance_bot removed a project: Patch-For-Review.Mar 17 2024, 10:30 PM
ReleaseTaggerBot added a project: MW-1.42-notes (1.42.0-wmf.23; 2024-03-19).Mar 17 2024, 11:01 PM
tstarling closed this task as Resolved.Mar 25 2024, 12:52 AM
• 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