Re: [xwiki-users] Bad HQL queries
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 " where 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@xwiki.org > http://lists.xwiki.org/mailman/listinfo/users ___ users mailing list users@xwiki.org http://lists.xwiki.org/mailman/listinfo/users
Re: [xwiki-users] Bad HQL queries
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@xwiki.org http://lists.xwiki.org/mailman/listinfo/users
Re: [xwiki-users] Bad HQL queries
Shouldn't there be a setting in DBCP configs?
paul
Le 18-janv.-10 à 13:44, Anca Luca a écrit :
> Hi Sergiu,
>
> On 01/18/2010 02:06 PM, Sergiu Dumitriu wrote:
>> On 01/18/2010 10:15 AM, Anca Luca wrote:
>>> Hi Flavius,
>>>
>>> On 01/17/2010 05:23 PM, Flavius Olaru wrote:
Hi,
I've done a installation on my localhost (Windows XP machine) of XE
2.1.1, Tomcat 6 and MySQL 5.1.
After this i wrote a bad hql query and executed it which brought
MySQL
to spyke the CPU usage to 100% and even more rendering both the
localhost and database unusable.
{{velocity}}
#set($badhql = ", BaseObject obj, StringProperty stringprop,
LargeStringProperty largestringprop where (doc.name like '%$text
%' or
doc.content like '%$text%') or (obj.name=doc.fullName and
stringprop.id.id=obj.id and stringprop.value like '%$text%') or
(obj.name=doc.fullName and largestringprop.id.id=obj.id and
largestringprop.value like '%$text%')")
>>>
>>> as we already discussed (but logging here for posterity), I don't
>>> see why this
>>> would be called a "bad hql". It highly depends on the specific
>>> instance, the
>>> data in it, etc. Any query which is 'good' on one instance can be
>>> 'bad' on
>>> another one.
>>
>> This one IS bad, no matter how you try to look at it. It joins all
>> documents with all possible objects, and all possible string and
>> largestring properties, since the link between them is OR-ed, not
>> AND-ed. It should be a left(or right?)-join relationship, but
>> frankly in
>> my early, hasty tests with this kinds of joins in HQL, I never got
>> it to
>> work, since there are 4 tables to join. So, another approach would
>> be to
>> put the property test in an "or exists(...)", which is not as
>> performant
>> as a left join, but at least it selects properly matched objects and
>> properties.
>
> 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.
>
> Thanks,
> Anca
>
>>
>>>
>>> Also, the responsiveness / unresponsiveness of the server depends
>>> on the
>>> configuration of the machine, but I agree that there can be
>>> queries& instances
>>> that would render any machine (build with current technologies)
>>> unusable.
>>>
>>> I personally prefer to be able to write whatever I want, even if
>>> it renders the
>>> machine unusable, I needed it in some situations and I prefer this
>>> to _not_ be
>>> enforced: if I want a query to take longer, I should be able to do
>>> it and setup
>>> my own safety nets (warn user, etc). After all, it's an
>>> application platform and
>>> the application developers are (anyway, should be, in an ideal
>>> world)
>>> responsible of what they're writing.
>>>
#set($ok = $list.addAll(0, $xwiki.searchDocuments("$badhql")))
{{include document="XWiki.Results"/}}
{{/velocity}}
The bad thing is that $xwiki.searchDocuments brought me to this
state
(no programming rights.).
>>>
>>> If there would be a possibility to allow 'bad queries' only for
>>> programming APIs
>>> (considering that all other calls should be "safe"), then this
>>> could be a decent
>>> middle way, otherwise I'm for no limits.
>>>
Now my question is: Are there any remedies against this? MySQL
configuration statements that prevent this kind of behaviour?
>>>
>>> as Oana was remarking earlier in a conversation, the settings
>>> could be done on
>>> database server side, regardless of xwiki, and depending on what
>>> your specific
>>> needs on an instance are.
>>>
>>> Thanks,
>>> Anca
>>
> ___
> users mailing list
> users@xwiki.org
> http://lists.xwiki.org/mailman/listinfo/users
___
users mailing list
users@xwiki.org
http://lists.xwiki.org/mailman/listinfo/users
Re: [xwiki-users] Bad HQL queries
Hi Sergiu,
On 01/18/2010 02:06 PM, Sergiu Dumitriu wrote:
> On 01/18/2010 10:15 AM, Anca Luca wrote:
>> Hi Flavius,
>>
>> On 01/17/2010 05:23 PM, Flavius Olaru wrote:
>>> Hi,
>>>
>>> I've done a installation on my localhost (Windows XP machine) of XE
>>> 2.1.1, Tomcat 6 and MySQL 5.1.
>>> After this i wrote a bad hql query and executed it which brought MySQL
>>> to spyke the CPU usage to 100% and even more rendering both the
>>> localhost and database unusable.
>>>
>>> {{velocity}}
>>> #set($badhql = ", BaseObject obj, StringProperty stringprop,
>>> LargeStringProperty largestringprop where (doc.name like '%$text%' or
>>> doc.content like '%$text%') or (obj.name=doc.fullName and
>>> stringprop.id.id=obj.id and stringprop.value like '%$text%') or
>>> (obj.name=doc.fullName and largestringprop.id.id=obj.id and
>>> largestringprop.value like '%$text%')")
>>
>> as we already discussed (but logging here for posterity), I don't see why
>> this
>> would be called a "bad hql". It highly depends on the specific instance, the
>> data in it, etc. Any query which is 'good' on one instance can be 'bad' on
>> another one.
>
> This one IS bad, no matter how you try to look at it. It joins all
> documents with all possible objects, and all possible string and
> largestring properties, since the link between them is OR-ed, not
> AND-ed. It should be a left(or right?)-join relationship, but frankly in
> my early, hasty tests with this kinds of joins in HQL, I never got it to
> work, since there are 4 tables to join. So, another approach would be to
> put the property test in an "or exists(...)", which is not as performant
> as a left join, but at least it selects properly matched objects and
> properties.
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.
Thanks,
Anca
>
>>
>> Also, the responsiveness / unresponsiveness of the server depends on the
>> configuration of the machine, but I agree that there can be queries&
>> instances
>> that would render any machine (build with current technologies) unusable.
>>
>> I personally prefer to be able to write whatever I want, even if it renders
>> the
>> machine unusable, I needed it in some situations and I prefer this to _not_
>> be
>> enforced: if I want a query to take longer, I should be able to do it and
>> setup
>> my own safety nets (warn user, etc). After all, it's an application platform
>> and
>> the application developers are (anyway, should be, in an ideal world)
>> responsible of what they're writing.
>>
>>> #set($ok = $list.addAll(0, $xwiki.searchDocuments("$badhql")))
>>>
>>> {{include document="XWiki.Results"/}}
>>> {{/velocity}}
>>>
>>> The bad thing is that $xwiki.searchDocuments brought me to this state
>>> (no programming rights.).
>>
>> If there would be a possibility to allow 'bad queries' only for programming
>> APIs
>> (considering that all other calls should be "safe"), then this could be a
>> decent
>> middle way, otherwise I'm for no limits.
>>
>>> Now my question is: Are there any remedies against this? MySQL
>>> configuration statements that prevent this kind of behaviour?
>>
>> as Oana was remarking earlier in a conversation, the settings could be done
>> on
>> database server side, regardless of xwiki, and depending on what your
>> specific
>> needs on an instance are.
>>
>> Thanks,
>> Anca
>
___
users mailing list
users@xwiki.org
http://lists.xwiki.org/mailman/listinfo/users
Re: [xwiki-users] Bad HQL queries
On 01/18/2010 10:15 AM, Anca Luca wrote:
> Hi Flavius,
>
> On 01/17/2010 05:23 PM, Flavius Olaru wrote:
>> Hi,
>>
>> I've done a installation on my localhost (Windows XP machine) of XE
>> 2.1.1, Tomcat 6 and MySQL 5.1.
>> After this i wrote a bad hql query and executed it which brought MySQL
>> to spyke the CPU usage to 100% and even more rendering both the
>> localhost and database unusable.
>>
>> {{velocity}}
>> #set($badhql = ", BaseObject obj, StringProperty stringprop,
>> LargeStringProperty largestringprop where (doc.name like '%$text%' or
>> doc.content like '%$text%') or (obj.name=doc.fullName and
>> stringprop.id.id=obj.id and stringprop.value like '%$text%') or
>> (obj.name=doc.fullName and largestringprop.id.id=obj.id and
>> largestringprop.value like '%$text%')")
>
> as we already discussed (but logging here for posterity), I don't see why this
> would be called a "bad hql". It highly depends on the specific instance, the
> data in it, etc. Any query which is 'good' on one instance can be 'bad' on
> another one.
This one IS bad, no matter how you try to look at it. It joins all
documents with all possible objects, and all possible string and
largestring properties, since the link between them is OR-ed, not
AND-ed. It should be a left(or right?)-join relationship, but frankly in
my early, hasty tests with this kinds of joins in HQL, I never got it to
work, since there are 4 tables to join. So, another approach would be to
put the property test in an "or exists(...)", which is not as performant
as a left join, but at least it selects properly matched objects and
properties.
>
> Also, the responsiveness / unresponsiveness of the server depends on the
> configuration of the machine, but I agree that there can be queries&
> instances
> that would render any machine (build with current technologies) unusable.
>
> I personally prefer to be able to write whatever I want, even if it renders
> the
> machine unusable, I needed it in some situations and I prefer this to _not_ be
> enforced: if I want a query to take longer, I should be able to do it and
> setup
> my own safety nets (warn user, etc). After all, it's an application platform
> and
> the application developers are (anyway, should be, in an ideal world)
> responsible of what they're writing.
>
>> #set($ok = $list.addAll(0, $xwiki.searchDocuments("$badhql")))
>>
>> {{include document="XWiki.Results"/}}
>> {{/velocity}}
>>
>> The bad thing is that $xwiki.searchDocuments brought me to this state
>> (no programming rights.).
>
> If there would be a possibility to allow 'bad queries' only for programming
> APIs
> (considering that all other calls should be "safe"), then this could be a
> decent
> middle way, otherwise I'm for no limits.
>
>> Now my question is: Are there any remedies against this? MySQL
>> configuration statements that prevent this kind of behaviour?
>
> as Oana was remarking earlier in a conversation, the settings could be done on
> database server side, regardless of xwiki, and depending on what your specific
> needs on an instance are.
>
> Thanks,
> Anca
--
Sergiu Dumitriu
http://purl.org/net/sergiu/
___
users mailing list
users@xwiki.org
http://lists.xwiki.org/mailman/listinfo/users
Re: [xwiki-users] Bad HQL queries
Hi Flavius,
On 01/17/2010 05:23 PM, Flavius Olaru wrote:
> Hi,
>
> I've done a installation on my localhost (Windows XP machine) of XE
> 2.1.1, Tomcat 6 and MySQL 5.1.
> After this i wrote a bad hql query and executed it which brought MySQL
> to spyke the CPU usage to 100% and even more rendering both the
> localhost and database unusable.
>
> {{velocity}}
> #set($badhql = ", BaseObject obj, StringProperty stringprop,
> LargeStringProperty largestringprop where (doc.name like '%$text%' or
> doc.content like '%$text%') or (obj.name=doc.fullName and
> stringprop.id.id=obj.id and stringprop.value like '%$text%') or
> (obj.name=doc.fullName and largestringprop.id.id=obj.id and
> largestringprop.value like '%$text%')")
as we already discussed (but logging here for posterity), I don't see why this
would be called a "bad hql". It highly depends on the specific instance, the
data in it, etc. Any query which is 'good' on one instance can be 'bad' on
another one.
Also, the responsiveness / unresponsiveness of the server depends on the
configuration of the machine, but I agree that there can be queries & instances
that would render any machine (build with current technologies) unusable.
I personally prefer to be able to write whatever I want, even if it renders the
machine unusable, I needed it in some situations and I prefer this to _not_ be
enforced: if I want a query to take longer, I should be able to do it and setup
my own safety nets (warn user, etc). After all, it's an application platform
and
the application developers are (anyway, should be, in an ideal world)
responsible of what they're writing.
>#set($ok = $list.addAll(0, $xwiki.searchDocuments("$badhql")))
>
>{{include document="XWiki.Results"/}}
> {{/velocity}}
>
> The bad thing is that $xwiki.searchDocuments brought me to this state
> (no programming rights.).
If there would be a possibility to allow 'bad queries' only for programming
APIs
(considering that all other calls should be "safe"), then this could be a
decent
middle way, otherwise I'm for no limits.
> Now my question is: Are there any remedies against this? MySQL
> configuration statements that prevent this kind of behaviour?
as Oana was remarking earlier in a conversation, the settings could be done on
database server side, regardless of xwiki, and depending on what your specific
needs on an instance are.
Thanks,
Anca
>
___
users mailing list
users@xwiki.org
http://lists.xwiki.org/mailman/listinfo/users
Re: [xwiki-users] Bad HQL queries
I must confess that this was nothing more than the result of a little
Googling. My instincts tell me that there must be something to stop
long running queries. I run postgres and have set it to log all queries
which take over 1/4 second but I know very little about mysql.
Caleb
Paul Libbrecht wrote:
> Caleb,
>
> Very interesting but...
> The last release is of 2004, are you using this with a contemporary
> mysql?
>
> paul
>
>
> Le 17-janv.-10 à 16:49, Caleb James DeLisle a écrit :
>
>> I think at the database level you can tell it to cease if there is a
>> query
>> which goes on for more than x milliseconds.
>>
>> http://mtop.sourceforge.net/
>> mkill can kill long running mysql queries.
>>
>> In postgres it's a configuration parameter:
>> http://www.postgresql.org/docs/8.3/static/runtime-config-client.html#GUC-STATEMENT-TIMEOUT
>>
>> Caleb
>>
>> Flavius Olaru wrote:
>>> Hi,
>>>
>>> I've done a installation on my localhost (Windows XP machine) of XE
>>> 2.1.1, Tomcat 6 and MySQL 5.1.
>>> After this i wrote a bad hql query and executed it which brought
>>> MySQL
>>> to spyke the CPU usage to 100% and even more rendering both the
>>> localhost and database unusable.
>>>
>>> {{velocity}}
>>> #set($badhql = ", BaseObject obj, StringProperty stringprop,
>>> LargeStringProperty largestringprop where (doc.name like '%$text%' or
>>> doc.content like '%$text%') or (obj.name=doc.fullName and
>>> stringprop.id.id=obj.id and stringprop.value like '%$text%') or
>>> (obj.name=doc.fullName and largestringprop.id.id=obj.id and
>>> largestringprop.value like '%$text%')")
>>> #set($ok = $list.addAll(0, $xwiki.searchDocuments("$badhql")))
>>>
>>> {{include document="XWiki.Results"/}}
>>> {{/velocity}}
>>>
>>> The bad thing is that $xwiki.searchDocuments brought me to this state
>>> (no programming rights.).
>>> Now my question is: Are there any remedies against this? MySQL
>>> configuration statements that prevent this kind of behaviour?
>>>
>> ___
>> users mailing list
>> users@xwiki.org
>> http://lists.xwiki.org/mailman/listinfo/users
>
> ___
> users mailing list
> users@xwiki.org
> http://lists.xwiki.org/mailman/listinfo/users
>
___
users mailing list
users@xwiki.org
http://lists.xwiki.org/mailman/listinfo/users
Re: [xwiki-users] Bad HQL queries
Caleb,
Very interesting but...
The last release is of 2004, are you using this with a contemporary
mysql?
paul
Le 17-janv.-10 à 16:49, Caleb James DeLisle a écrit :
> I think at the database level you can tell it to cease if there is a
> query
> which goes on for more than x milliseconds.
>
> http://mtop.sourceforge.net/
> mkill can kill long running mysql queries.
>
> In postgres it's a configuration parameter:
> http://www.postgresql.org/docs/8.3/static/runtime-config-client.html#GUC-STATEMENT-TIMEOUT
>
> Caleb
>
> Flavius Olaru wrote:
>> Hi,
>>
>> I've done a installation on my localhost (Windows XP machine) of XE
>> 2.1.1, Tomcat 6 and MySQL 5.1.
>> After this i wrote a bad hql query and executed it which brought
>> MySQL
>> to spyke the CPU usage to 100% and even more rendering both the
>> localhost and database unusable.
>>
>> {{velocity}}
>> #set($badhql = ", BaseObject obj, StringProperty stringprop,
>> LargeStringProperty largestringprop where (doc.name like '%$text%' or
>> doc.content like '%$text%') or (obj.name=doc.fullName and
>> stringprop.id.id=obj.id and stringprop.value like '%$text%') or
>> (obj.name=doc.fullName and largestringprop.id.id=obj.id and
>> largestringprop.value like '%$text%')")
>> #set($ok = $list.addAll(0, $xwiki.searchDocuments("$badhql")))
>>
>> {{include document="XWiki.Results"/}}
>> {{/velocity}}
>>
>> The bad thing is that $xwiki.searchDocuments brought me to this state
>> (no programming rights.).
>> Now my question is: Are there any remedies against this? MySQL
>> configuration statements that prevent this kind of behaviour?
>>
>
> ___
> users mailing list
> users@xwiki.org
> http://lists.xwiki.org/mailman/listinfo/users
___
users mailing list
users@xwiki.org
http://lists.xwiki.org/mailman/listinfo/users
Re: [xwiki-users] Bad HQL queries
I think at the database level you can tell it to cease if there is a query
which goes on for more than x milliseconds.
http://mtop.sourceforge.net/
mkill can kill long running mysql queries.
In postgres it's a configuration parameter:
http://www.postgresql.org/docs/8.3/static/runtime-config-client.html#GUC-STATEMENT-TIMEOUT
Caleb
Flavius Olaru wrote:
> Hi,
>
> I've done a installation on my localhost (Windows XP machine) of XE
> 2.1.1, Tomcat 6 and MySQL 5.1.
> After this i wrote a bad hql query and executed it which brought MySQL
> to spyke the CPU usage to 100% and even more rendering both the
> localhost and database unusable.
>
> {{velocity}}
> #set($badhql = ", BaseObject obj, StringProperty stringprop,
> LargeStringProperty largestringprop where (doc.name like '%$text%' or
> doc.content like '%$text%') or (obj.name=doc.fullName and
> stringprop.id.id=obj.id and stringprop.value like '%$text%') or
> (obj.name=doc.fullName and largestringprop.id.id=obj.id and
> largestringprop.value like '%$text%')")
> #set($ok = $list.addAll(0, $xwiki.searchDocuments("$badhql")))
>
> {{include document="XWiki.Results"/}}
> {{/velocity}}
>
> The bad thing is that $xwiki.searchDocuments brought me to this state
> (no programming rights.).
> Now my question is: Are there any remedies against this? MySQL
> configuration statements that prevent this kind of behaviour?
>
___
users mailing list
users@xwiki.org
http://lists.xwiki.org/mailman/listinfo/users
[xwiki-users] Bad HQL queries
Hi,
I've done a installation on my localhost (Windows XP machine) of XE
2.1.1, Tomcat 6 and MySQL 5.1.
After this i wrote a bad hql query and executed it which brought MySQL
to spyke the CPU usage to 100% and even more rendering both the
localhost and database unusable.
{{velocity}}
#set($badhql = ", BaseObject obj, StringProperty stringprop,
LargeStringProperty largestringprop where (doc.name like '%$text%' or
doc.content like '%$text%') or (obj.name=doc.fullName and
stringprop.id.id=obj.id and stringprop.value like '%$text%') or
(obj.name=doc.fullName and largestringprop.id.id=obj.id and
largestringprop.value like '%$text%')")
#set($ok = $list.addAll(0, $xwiki.searchDocuments("$badhql")))
{{include document="XWiki.Results"/}}
{{/velocity}}
The bad thing is that $xwiki.searchDocuments brought me to this state
(no programming rights.).
Now my question is: Are there any remedies against this? MySQL
configuration statements that prevent this kind of behaviour?
--
Flavius Olaru
___
users mailing list
users@xwiki.org
http://lists.xwiki.org/mailman/listinfo/users
