huge queries hanging on DB - how to troubleshoot?

Next topic

Author	Message
Massimiliano Bariola	Friday 15 September 2006 6:03:29 am Hi, I am running an eZp 3.7.5 installation + mySQL. via mytop, I frequently find hung queries made like this: SELECT DISTINCT ezcontentobject., ezcontentclass.name as class_name, ezcontentobject_tree. , ezcontentobject_name.name as name, ezcontentobject_name.real_translation FROM ezsearch_tmp__0, ezsearch_tmp__1, ezsearch_tmp__2, ezsearch_tmp__3, ezsearch_tmp__4, ezsearch_tmp__5, ezsearch_tmp__6, ezsearch_tmp__7, ezsearch_tmp__8, ezsearch_tmp__9, ezsearch_tmp__10, ezsearch_tmp__11, ezsearch_tmp__12, ezsearch_tmp__13, ezsearch_tmp__14, ezsearch_tmp__15, ezsearch_tmp__16, ezsearch_tmp__17, ezsearch_tmp__18, ezsearch_tmp__19, ezsearch_tmp__20, ezsearch_tmp__21, ezsearch_tmp__22, ezsearch_tmp__23, ezsearch_tmp__24, ezsearch_tmp__25, ezsearch_tmp__26, ezsearch_tmp__27, ezsearch_tmp__28, ezsearch_tmp__29, ezsearch_tmp__30, ezsearch_tmp__31, ezsearch_tmp__32, ezsearch_tmp__33, ezsearch_tmp__34, ezsearch_tmp__35, ezsearch_tmp__36, ezsearch_tmp__37, ezsearch_tmp__38 , ezcontentobject, ezcontentclass, ezcontentobject_tree , ezcontentobject_name WHERE ezsearch_tmp__0.contentobject_id=ezsearch_tmp__1.contentobject_id AND ezsearch_tmp__0.contentobject_id=ezsearch_tmp__2.contentobject_id AND ezsearch_tmp__0.contentobject_id=ezsearch_tmp__3.contentobject_id AND ezsearch_tmp__0.contentobject_id=ezsearch_tmp__4.contentobject_id AND ezsearch_tmp__0.contentobject_id=ezsearch_tmp__5.contentobject_id AND ezsearch_tmp__0.contentobject_id=ezsearch_tmp__6.contentobject_id AND ezsearch_tmp__0.contentobject_id=ezsearch_tmp__7.contentobject_id AND ezsearch_tmp__0.contentobject_id=ezsearch_tmp__8.contentobject_id AND ezsearch_tmp__0.contentobject_id=ezsearch_tmp__9.contentobject_id AND ezsearch_tmp__0.contentobject_id=ezsearch_tmp__10.contentobject_id AND ezsearch_tmp__0.contentobject_id=ezsearch_tmp__11.contentobject_id AND ezsearch_tmp__0.contentobject_id=ezsearch_tmp__12.contentobject_id AND ezsearch_tmp__0.contentobject_id=ezsearch_tmp__13.contentobject_id AND ezsearch_tmp__0.contentobject_id=ezsearch_tmp__14.contentobject_id AND ezsearch_tmp__0.contentobject_id=ezsearch_tmp__15.contentobject_id AND ezsearch_tmp__0.contentobject_id=ezsearch_tmp__16.contentobject_id AND ezsearch_tmp__0.contentobject_id=ezsearch_tmp__17.contentobject_id AND ezsearch_tmp__0.contentobject_id=ezsearch_tmp__18.contentobject_id AND ezsearch_tmp__0.contentobject_id=ezsearch_tmp__19.contentobject_id AND ezsearch_tmp__0.contentobject_id=ezsearch_tmp__20.contentobject_id AND ezsearch_tmp__0.contentobject_id=ezsearch_tmp__21.contentobject_id AND ezsearch_tmp__0.contentobject_id=ezsearch_tmp__22.contentobject_id AND ezsearch_tmp__0.contentobject_id=ezsearch_tmp__23.contentobject_id AND ezsearch_tmp__0.contentobject_id=ezsearch_tmp__24.contentobject_id AND ezsearch_tmp__0.contentobject_id=ezsearch_tmp__25.contentobject_id AND ezsearch_tmp__0.contentobject_id=ezsearch_tmp__26.contentobject_id AND ezsearch_tmp__0.contentobject_id=ezsearch_tmp__27.contentobject_id AND ezsearch_tmp__0.contentobject_id=ezsearch_tmp__28.contentobject_id AND ezsearch_tmp__0.contentobject_id=ezsearch_tmp__29.contentobject_id AND ezsearch_tmp__0.contentobject_id=ezsearch_tmp__30.contentobject_id AND ezsearch_tmp__0.contentobject_id=ezsearch_tmp__31.contentobject_id AND ezsearch_tmp__0.contentobject_id=ezsearch_tmp__32.contentobject_id AND ezsearch_tmp__0.contentobject_id=ezsearch_tmp__33.contentobject_id AND ezsearch_tmp__0.contentobject_id=ezsearch_tmp__34.contentobject_id AND ezsearch_tmp__0.contentobject_id=ezsearch_tmp__35.contentobject_id AND ezsearch_tmp__0.contentobject_id=ezsearch_tmp__36.contentobject_id AND ezsearch_tmp__0.contentobject_id=ezsearch_tmp__37.contentobject_id AND ezsearch_tmp__0.contentobject_id=ezsearch_tmp__38.contentobject_id AND ezcontentobject.id=ezsearch_tmp__0.contentobject_id AND ezcontentobject.contentclass_id = ezcontentclass.id and ezcontentclass.version = '0' and ezcontentobject.id = ezcontentobject_tree.contentobject_id and ezcontentobject_tree.node_id = ezcontentobject_tree.main_node_id and ezcontentobject_tree.contentobject_id = ezcontentobject_name.contentobject_id and ezcontentobject_tree.contentobject_version = ezcontentobject_name.content_version and ezcontentobject_name.content_translation = 'ita-IT' ORDER BY ezcontentobject.published ASCLIMIT 0, 10 I can imagine it relates to some search being made on my site; I cannot understand what kind of search could generate a search that requires almost 40 temporary tables. the server crashes around once a week and I suspect that these hung queries can be among the causes which concur to kill the machine. Could someone from eZ help me understand what could cause these huge queries, so that I may try to better configure eZp / disallow the kind of searches which cause them? thank you, regards Massimiliano
Massimiliano Bariola	Friday 15 September 2006 7:27:46 am Seems I have found what it is. some user was entering search strings made of a sentence with 40 words and eZpublish created one tmp table per each word. Solution: I limited the number of different words to 8 and for the time being I see no more hung queries. Suggestion to eZ: a new config option to set maximum number of words in search string so that there will be no need to cope for it in template code.
Carlos Revillo	Tuesday 22 January 2008 5:34:51 am hello. I know this is a very old post, but i think this is exactly the issue we have... How do you limited the number of different words to search? I'm looking at the documentation with no luck... I know i could try to edit the php files, but i'd prefer to leave ez kernel as it was. Thank you.
Massimiliano Bariola	Wednesday 23 January 2008 2:45:51 am Hi Carlos, at the time I solved it by developing a simple extension (you don't really need it; you can use an override template) which did a fetch - search. the text to be searched was limited in number of words via template code. it goes like this: {set $text = $text\|explode(' ')\|extract_left(8)\|implode(' ')} this way, I only take the first 8 words. then I do my search like this: {def $resultsArray = fetch(content, search, hash(text, $text))} Of course you can finetune the search fetch with all the needed optional parameters. There had been talk from eZ to implement a maximum word count ini setting, but last time I checked - a year ago - there wasn't any yet. HTH, Massimiliano
Carlos Revillo	Wednesday 23 January 2008 12:44:30 pm Hi again. Thanks for your reply. i have "overriden" content/search template and limited the search to only eight words. all is ok now. i've got some located search phrases wich made mysql shows a process with statistics status for a long time (the more words, the more it took). But i still i have some doubts about all this. we also have other search phrases longer than those who makes mysql server crazy, and this longer phrases doesn't have any problem. search results was presented in seconds. The other thing it would like to note, is that this "problematic" phrases doesn't give any problem with mysql 5. i have tested the same installation, same database and diferent mysql servers. so... maybe is a mysql 4 related issue... btw. it would be nice that configuration setting to limit these phrases. thanks again for your time.