PePiPoo Posted January 24, 2008 Share Posted January 24, 2008 Recently my board has been getting increasing numbers of IPS Driver Errors, to the point where it's virtually unusable for hours in the middle of the day. The problem appears to be that (some) search queries are taking extremely long times to complete, and all the other queries coming in are just backing up. I turned on MySQL slow query logging and a couple of examples are shown below. Can anyone suggest where the problem is and what I can do to fix it? # Time: 080123 13:48:39 # User@Host: pepipoo[pepipoo] @ localhost [] # Query_time: 392 Lock_time: 0 Rows_sent: 409 Rows_examined: 134607 SELECT p.pid, p.queued, t.approved, t.forum_id FROM ibf_posts p LEFT JOIN ibf_topics t ON ( p.topic_id=t.tid ) WHERE (t.forum_id IN (17) AND t.approved=1 AND p.queued=0) AND MATCH(post) AGAINST ('+time out' IN BOOLEAN MODE) ORDER BY p.post_date desc LIMIT 1000; # Time: 080124 1:38:41 # User@Host: pepipoo[pepipoo] @ localhost [] # Query_time: 359 Lock_time: 0 Rows_sent: 1000 Rows_examined: 16739 SELECT p.pid, p.queued, t.approved, t.forum_id FROM ibf_posts p LEFT JOIN ibf_topics t ON ( p.topic_id=t.tid ) WHERE (t.forum_id IN (28,43,5,4,30,17,2,1,3,6,22,18,9,12,15) AND t.approved=1 AND p.queued=0) AND MATCH(post) AGAINST ('+twice the limit' IN BOOLEAN MODE) ORDER BY p.post_date desc LIMIT 1000; Link to comment Share on other sites More sharing options...
AndyF Posted January 24, 2008 Share Posted January 24, 2008 I`l move your topic to "Server Management, Resources, Optimization" :) Link to comment Share on other sites More sharing options...
bfarber Posted January 24, 2008 Share Posted January 24, 2008 The only way to "resolve" this (outside of throwing more memory to mysql) is installing Sphinx and using the Sphinx search module for IPB. Link to comment Share on other sites More sharing options...
PePiPoo Posted January 24, 2008 Share Posted January 24, 2008 Not the kind of fix I was hoping for, I have to admit. I didn't think 240k posts on a board was enough to warrant that kind of solution! I see that the SphinxhForIPB requirements include a dedicated server. In your experience is that really necessary, or would a VPS be OK? Link to comment Share on other sites More sharing options...
bfarber Posted January 24, 2008 Share Posted January 24, 2008 A VPS is fine, you just need access to install it, modify a config file and launch the daemon. You'll also need to install a couple crontabs (*basically you need root or nearly root access to the box, which a VPS generally does give you). 240,000 posts is a decent amount - MySQL's fulltext engine is, unfortunately, relatively week and resource intensive. Searches that take 10 seconds with MySQL fulltext will take less than a second with Sphinx. I'm hopeful MySQL will incorporate the improvements in some future update, or attempt to tackle this issue on their own at least. Other things affect the searching too by the way. How much memory is in the box, how your my.cnf is configured, how many people are online, what else is happening on the server (other sites, irc chat rooms, email daemons, etc. all use up resources) and so on. We don't use Sphinx on this server, for example, and we have quite a few posts. Link to comment Share on other sites More sharing options...
PePiPoo Posted January 24, 2008 Share Posted January 24, 2008 Thanks for the advice. I'll install Sphinx and hopefully that'll take care of most of my problem.. With regard to other tasks etc on my server, IPB contributes by far the heaviest load. Those IPB search queries take about 20 times longer than any others to execute, the next longest being these:# Time: 080124 0:38:27 # User@Host: pepipoo[pepipoo] @ localhost [] # Query_time: 12 Lock_time: 0 Rows_sent: 1000 Rows_examined: 1000 SELECT topic_id FROM ibf_posts WHERE pid IN(235229,235056,235019,234992,234971,234944,234921,234896,234891,234833,234766 ,234764,234734,234508,234471,234438,234384,234379,234355,234341,234241,234224,23 4203,234169,234134,234058,234042,234016,233958,233895,233882,233839,233769,23374 3 ,233692,233690,233544,233527,233523,233517,233495,233457,233346,23329,232912,232 805,232734,232719,232712,232691,232579,232517,232508,232464,232462,232355,232277 , 232200,232074,232072,231698,231648,231471,231406,231403,231402,231395,231357,231 3 53,231325,231322,231200,231189,231163,231161,231156,231117,231106,231083,231079, 2 30776,230734,230587,230459,230430,230258,230018,229962,229879,229870,229810,2297 32,....................... Link to comment Share on other sites More sharing options...
ErwinB Posted January 25, 2008 Share Posted January 25, 2008 Have you used the support tools in the ACP to check your DB against missing indexes at least? If your DB is OK then yes I think Spinx will help you a lot. But you should consider what bfarber said, it's not only IP.Board/MySQL.... If you are running on a little server with an old processor, very few memory and a slow hard drive, that's what may happen. Hardware and software configurations are important things. If you're on a mutualized hosting, then you may have reached the point where you have to consider to upgrade your offer or switch to a VPS or Dedicated Server. By the way, if you have these problems on a mutualized, you can choose to change your host first... Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.