This is currently causing the popular Meta tools to malfunction. E.g. all the tools under https://tools.wmflabs.org/meta/stalktoy/, which are needed for steward tasks such as Stewards requests.

https://tools.wmflabs.org/meta/crossactivity/Krinkle

Unable s6.web.db.svc.eqiad.wmflabs, s8.web.db.svc.eqiad.wmflabs, ..

Exception: SQLSTATE[HY000] [1226] User 's52256' has exceeded the 'max_user_connections' resource (current value: 1)

I don't see a relation between these tools and Pywikibot-catfiles, so perhaps one of them using the wrong database credentials. Or, if they really are run from the same account, perhaps the bot should move to a different Tools project.

According to https://tools.wmflabs.org/contact/, s52256 (52256) belongs to tools.meta, not tools.drtrigonbot. Looks like that might be using the wrong db credentials indeed.

Some of the potential queries causing the issue (there were multiple of these running in parallel):

Info: SELECT
                ipb_by_text,
                ipb_address,
                comment_text AS ipb_reason,
                DATE_FORMAT(ipb_timestamp, "%Y-%b-%d") AS timestamp,
                DATE_FORMAT(ipb_expiry, "%Y-%b-%d") AS expiry,
                ipb_anon_only
            FROM
                ipblocks
                LEFT JOIN comment ON ipb_reason_id = comment_id
            WHERE
                (ipb_range_start <= 'v6-260088003980255009B2521EDAE937EA' AND ipb_range_end >= 'v6-260088003980255009B2521EDAE937EA')
                OR (ipb_range_start >= 'v6-260088003980255009B2521EDAE937EA' AND ipb_range_end <= 'v6-260088003980255009B2521EDAE937EA')
         /*8b4924e8*/

I asked about this in #wikimedia-cloud, but unfortunately never got an answer. The only recent change is updating queries for T166732. The above query had two changes: (a) changed ipb_reason to comment_text AS ipb_reason, and (b) added LEFT JOIN comment ON ipb_reason_id = comment_id. I'm not sure why that makes the query so much slower, and it's hard to experiment without EXPLAIN access. Suggestions are welcome! I'd rather not revert the update entirely, since the ipb_reason column will be removed soon.

In T217853#5011265, @Pathoschild wrote:

it's hard to experiment without EXPLAIN access

https://wikitech.wikimedia.org/wiki/Help:MySQL_queries#Optimizing_queries

You may be able to use https://tools.wmflabs.org/sql-optimizer to check how the optimizer is treating your query.

Just to be clear of why action was taken- normally we allow tools to use whatever resources they want, but we got a complain on IRC about lagging on the wikireplica web and this was observed the #1 cause of CPU starvation.

Thanks! Could it be related to the comment view?

• The old query performs a simple select on all ipblocks rows. It's not ideal, but I don't know of a more optimal way and it didn't cause issues before. The WHERE can be simplified to ipb_range_start = ? AND ipb_range_end = ? in this case, but it doesn't affect the query plan.
• The new query is identical, but adds a left join on comment. Besides the expected eq_ref join, this also adds ten dependent subqueries on tables like revision and image. This version apparently causes high CPU.

Both take equivalent time though (≈2 seconds), even when I use SQL_NO_CACHE and change the WHERE values.

I reversed the update temporarily. That should solve the high CPU usage if it's due to the query changes (rather than some other change on Toolforge), but the tools will break with the upcoming comment change.

I've removed the rate limit, then we will be able see how the cpu behaves: https://grafana.wikimedia.org/d/000000342/node-exporter-server-metrics?panelId=13&fullscreen&orgId=1&var-node=labsdb1009:9100&from=now-12h&to=now