After upgrading FactGrid from Debian 10 (Buster) to 11 (Bullseye), I am seeing very high database load from this query (according to SHOW PROCESSLIST):
SELECT /* MediaWiki\Extension\OAuth\Backend\Hooks::getUsedConsumerTags */ DISTINCT ct_tag_id FROM …
I’ve temporarily worked around it with the following patch (on REL1_39 of mediawiki/extensions/OAuth, specifically based on e0c7ffe159):
diff --git a/src/Backend/Hooks.php b/src/Backend/Hooks.php index f0f4660c..7a6d0a40 100644 --- a/src/Backend/Hooks.php +++ b/src/Backend/Hooks.php @@ -155,6 +155,12 @@ EOK; $allTags[] = Utils::getTagName( $row->oarc_id ); } + // Lucas hack: directly return all tags, skip the below query which is apparently very expensive + foreach ( $allTags as $tag ) { + $tags[] = $tag; + } + return true; + // Step 2: Return only those that are in use. $changeTagDefStore = MediaWikiServices::getInstance()->getChangeTagDefStore(); $tagIds = [];
I’ve already tried to optimize the table and it hasn’t helped; I’m still able to reproduce the issue by just git stashing the above patch away.
This is especially weird given that there’s only a single OAuth consumer on this wiki, so if I’m not mistaken, the slow query is literally just:
MariaDB [factgridwikidata]> SELECT DISTINCT ct_tag_id FROM factgridchange_tag WHERE ct_tag_id IN (1); +-----------+ | ct_tag_id | +-----------+ | 1 | +-----------+ 1 row in set (2.426 sec) MariaDB [factgridwikidata]> EXPLAIN SELECT DISTINCT ct_tag_id FROM factgridchange_tag WHERE ct_tag_id IN (1); +------+-------------+--------------------+------+---------------+--------------+---------+-------+---------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+--------------------+------+---------------+--------------+---------+-------+---------+-------------+ | 1 | SIMPLE | factgridchange_tag | ref | ct_tag_id_id | ct_tag_id_id | 4 | const | 3379541 | Using index | +------+-------------+--------------------+------+---------------+--------------+---------+-------+---------+-------------+ 1 row in set (0.001 sec)
Why is it so slow, even though it should only have to find one row and there’s an index on the ct_tag_id? (2.4 seconds isn’t an eternity, but the query seems to run fairly frequently, so that this is still enough to cause issues for the server – there were even some “unable to connect” errors while the database was overloaded.)
Tentatively tagging mariadb-optimizer-bug, since this seemed to happen since the upgrade from MariaDB 10.3 (specifically 10.3.39-0+deb10u2, if I’m not mistaken) to 10.5 (10.5.23-0+deb11u1) – the MediaWiki code wasn’t changed during this upgrade. (Though I’m also surprised that the result of this DB query, cheap or not, isn’t cached somewhere in OAuth, given how often this hook handler seems to be called.)
(Note: Earlier today I had upgraded FactGrid from Debian 9 / Stretch / MariaDB 10.1 to Debian 10 / Buster / MariaDB 10.3, but as far as I could tell, the query only became slow with the Debian 11 / MariaDB 10.5 upgrade.)