Sorry to respond to myself but I really feel I had this information earlier.
The summary: MySQL has no protection on queries that may return a zillion of documents... it honestly tries to accumulate things. This happens if you have a "wrong" query which, for example, has a free variable, e.g., as an argument to searchDocuments: , BaseObject as obj where obj.name=doc.fullName and doc.web != 'AssetTemp' this is arguably false but happens if you fiddle too much with queries... and, as I said, this creates a MySQL that locks any other connection (at least of interest to xwiki). The solution is: never do wrong your queries. But well. The alternate solution is: restart your DB or let your sysadmin do it. Yet another solution But there's a better solution: MySQL's "show processlist". This is an SQL statement that shows you queries currently running, since when, etc. The "killer queries" that I encountered above could easily be identified: - long running - displaying status "copying to tmp table" - start of a flood of locked queries it was then sufficient to "kill xxxx" where xxxx is the id of the query. Before that you can "show full processlist" to get all of the SQL query and start to wonder which XWiki code has created it. I wonder if this is not something that XWiki could support as a tool to debug such issues, ideally with location bound to queries. paul Le 23 juil. 2010 à 00:40, Paul Libbrecht a écrit : > I'm back with this subject, it's really a haunting subject. > > When at this stage, I can only wait that a graceful person restarts > the MySQL thus killing the "bad" query. > > I would wish a DBCP or hibernate configuration for a maximum time > (which I'd put around 30 seconds because indeed, overload may cause > some queries to be very slow, but that would still release my MySQL > after those 30 seconds). > > In many cases, paging doesn't help solve this issue since sorting is > often activated. > > > > Hints would be really welcome. > > thanks in advance > > paul > > > Le 18-janv.-10 à 13:44, Anca Luca a écrit : > >> agreed for this particular case, but the discussion was about hqls >> which take >> too much time. To the limit, somebody might _actually_ need all >> these tables >> joined, and we wouldn't call that "bad", wouldn't we? >> >> If we put the problem like this, then we have a conversation about >> how to >> enforce people to join tables only by foreign keys (which we don't >> even have set >> in xwiki iirc), which is another discussion. > > _______________________________________________ > users mailing list > [email protected] > http://lists.xwiki.org/mailman/listinfo/users _______________________________________________ users mailing list [email protected] http://lists.xwiki.org/mailman/listinfo/users
