Very slow queries causing IPS Driver Errors

[PePiPoo]

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;

[AndyF]

I`l move your topic to "Server Management, Resources, Optimization" :)

[bfarber]

The only way to "resolve" this (outside of throwing more memory to mysql) is installing Sphinx and using the Sphinx search module for IPB.

[PePiPoo]

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?

[bfarber]

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.

[PePiPoo]

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,.......................

[ErwinB]

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...