Sql Query Causing Server load

phpBB SEO Premod for phpBB3 support forum.
This premodded version of phpBB3 includes the three different type of URL rewriting for phpBB3 by phpBB SEO. It comes with several other Search Engine Optimization mods installed.

Moderator: Moderators

Sql Query Causing Server load

Postby yaashul » Wed May 16, 2012 8:45 am

MySQL had hit its maximum number of allowed connections (300) due to locked queries against the phpbb3 database. In particular, this query had been running for a long period of time locking other queries behind it due to a subsequent UPDATE query:

Code: Select all
SELECT p.topic_id FROM (phpbb_search_wordmatch m1 CROSS JOIN phpbb_search_wordmatch m2 CROSS JOIN phpbb_search_wordmatch m3 CROSS JOIN phpbb_search_wordmatch m4 CROSS JOIN phpbb_search_wordmatch m5 CROSS JOIN phpbb_search_wordmatch m6 CROSS JOIN phpbb_search_wordmatch m7 CROSS JOIN phpbb_search_wordmatch m8 CROSS JOIN phpbb_search_wordmatch m9 CROSS JOIN phpbb_search_wordmatch m0) LEFT JOIN phpbb_posts p ON (m0.post_id = p.post_id) LEFT JOIN phpbb_topics t ON (p.topic_id = t.topic_id) WHERE m0.word_id = 14410 AND m1.word_id = 14411 AND m1.post_id = m0.post_id AND m2.word_id = 75227 AND m2.post_id = m0.post_id AND m3.word_id = 75228 AND m3.post_id = m0.post_id AND m4.word_id = 14410 AND m4.post_id = m0.post_id AND m5.word_id = 14411 AND m5.post_id = m0.post_id AND m6.word_id = 75227 AND m6.post_id = m0.post_id AND m7.word_id = 75228 AND m7.post_id = m0.post_id AND m8.word_id = 1830 AND m8.post_id = m0.post_id AND m9.word_id = 1947 AND m9.post_id = m0.post_id AND p.post_approved = 1 AND p.forum_id NOT IN (11, 12, 30, 134, 153, 154, 155, 156, 157, 158, 159, 163) GROUP BY p.topic_id, t.topic_last_post_time ORDER BY t.topic_last_post_time DESC;


Can someone please explain to me what I can do to resolve this issue?
yaashul
PR1
PR1
 
Posts: 158
Joined: Wed Aug 29, 2007 1:21 am

Advertisement

Re: Sql Query Causing Server load

Postby Stephen26 » Wed May 16, 2012 9:03 am

Seems like a hung (huge?) search query, I'm not sure. In case your board uses the "Fulltext native" search backend I would suggest changing to "Fulltext mysql" (ACP -> Maintenance -> Search index) especially on bigger boards. This will also reduce the size of your DB by up to 1/3.
I install and configure phpBB-SEO (and other mods) for a fair price and very fast. If you are interested simply send me a private message.
Stephen26
phpBB SEO Team
phpBB SEO Team
 
Posts: 427
Joined: Mon May 03, 2010 7:06 am

Re: Sql Query Causing Server load

Postby yaashul » Wed May 16, 2012 5:28 pm

can u suggest me which script call such query? is it related topic search query?
yaashul
PR1
PR1
 
Posts: 158
Joined: Wed Aug 29, 2007 1:21 am

Re: Sql Query Causing Server load

Postby HB » Wed May 16, 2012 6:09 pm

I think you missed Stephen's point. The fulltext MySQL search would generate more efficient queries. The "word_id" listed multiple times in your posted query indicate its using phpBB's search table approach. The solution is to go to ACP > Maintenance > search, delete the "Fulltext native" search table and create the "Fulltext mysql" index. Depending on the size of your site, it may take a few hours to finish.
Dan Kehn
HB
phpBB SEO Team
phpBB SEO Team
 
Posts: 1509
Joined: Mon Oct 16, 2006 2:25 am

Re: Sql Query Causing Server load

Postby yaashul » Thu May 17, 2012 3:39 am

I got the point and I have alread set it up but still it doesn't answer my query. Which script call such kind of query as per phpbb they never use this kind of query in there script so it must be some of the phpbb-seo related mod.
yaashul
PR1
PR1
 
Posts: 158
Joined: Wed Aug 29, 2007 1:21 am


Return to phpBB SEO Premod

 


  • Related topics
    Replies
    Views
    Last post

Who is online

Users browsing this forum: Google Adsense [Bot] and 18 guests