61 queries were being executed at the same time, reading millions of rows, exhausting the memory of one of the servers, potentially crashing it, and getting complains from several other replica users of general slowness.
Account restricted to 1 connection per user until the problem is solved.
(I don't like the word "abusing" here very much as it sounds to me as a English-as-a-foreign-language speaker that someone is knowingly doing something that they are aware of that they shouldn't do. In our setup, database servers (and others) are black boxes where ordinary users have no real clue what resources are being used, much less do they have the expectation that they could crash a server just by running queries. Many tools have huge, eh, potential for optimization of queries and workflow, but it can have chilling effects if users think that there are lines in the sand invisible to them that label them abusers if crossed. IMHO as long as the queries are run with the intention of providing a useful service, the resources are just used. There may be a problem because resources are finite, shared, etc. and until that problem is solved restricting access is a good solution, but a guilty conscience does not necessarily optimize the queries in the technically best way :-).)
Hi jcrespo, ptwikis is a wide project with many different small tools used by the portuguese community. Do you know where I can find some database logs to figure out what happened?
PS. Thanks for the less guilty new title :-)
Yes, I can share the logs with you, but privately as I am not sure if it contains (your) private data.
I've sent @Crang115 a snapshot of the tools.ptwikis SHOW PROCESSLIST mysql account, filtered only by his own queries. No other user's private information was shared. [his == ptwiki]
I shared the logs with @Alchimista @He7d3r and @Danilo . The problem is being caused by the Wiki Loves * tools. I didn't have much time today to give it a closer look but I can say that the queries can definitely be optimized but it is still not clear to me why so many were executed at the same time.
I was already thinking about refactoring this tools to have one single tool optimized for all commons' photo contests. Maybe it is now the best time to do it.
I disabled the tools that made these queries. The tool can show useful informations about WLE and WLM, but the tool need to have a cache and need also other improvements before be reenabled.
Yes, I will do it now. Please do not enable it (make sure it is not enabled by accident, for example, on restart) until those problems are fixed.