18 Sep
2010
18 Sep
'10
9:19 p.m.
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
users(a)xwiki.org
http://lists.xwiki.org/mailman/listinfo/users