Figured out the problem.
When users search for stuff, they cause huge joins that take 2-3 minutes to run. The tables are all locked while this runs of course. So for 2-3 minutes (on this particular query, some could be faster, and some could be even slower, i just caught this one), nothing works. All the other queries just sit spinlocked on the db. This time 80 queries piled up in 2 minutes. With 250 max connections to the db, that could run out and generate the error.
Even with 5000 max connections tho, even if we never hit the max, the users see the site loading and not displaying anything however.
Slow queries/searches must die.
The tables involved in this are all indexed (except topic status, I added an index, didnt help):
SELECT p.POST_ID FROM ubbt_POSTS p LEFT JOIN ubbt_POSTS pp on p.POST_ID=pp.POST_ID, ubbt_TOPICS t WHERE p.POST_IS_APPROVED = '1' AND t.TOPIC_STATUS <> 'M' AND t.FORUM_ID IN ('1','2','3','4','5','6','7','9','10','11','12','15','17','18','19','20','21','22','23','25','26','27','29','30','31','32','33','35','39','41','42','43','44') AND p.TOPIC_ID = t.TOPIC_ID AND MATCH p.POST_SUBJECT AGAINST ('\\"nothing else matters\\"' IN BOOLEAN MODE) ORDER BY p.POST_POSTED_TIME DESC LIMIT 300;
Not sure what generated that query, but it took 2 minutes to run.
No, the machine is relatively fast, this is just a really large UBB forum system (~91,000 topics,
another query that gummed things up:
select t1.TOPIC_ID,t1.POST_ID,t2.USER_DISPLAY_NAME,t1.TOPIC_CREATED_TIME,t1.TOPIC_LAST_REPLY_TIME,t1.TOPIC_SUBJECT, t1.TOPIC_STATUS,t1.TOPIC_IS_APPROVED,t1.TOPIC_ICON,t1.TOPIC_VIEWS,t1.TOPIC_REPLIES,t1.TOPIC_TOTAL_RATES, t1.TOPIC_RATING,t3.USER_NAME_COLOR,t2.USER_MEMBERSHIP_LEVEL,t1.USER_ID,t1.TOPIC_IS_STICKY,t1.TOPIC_LAST_POSTER_ID, t1.TOPIC_LAST_POSTER_NAME,t1.TOPIC_LAST_POST_ID,t1.TOPIC_IS_EVENT,t1.TOPIC_HAS_FILE,t1.TOPIC_HAS_POLL,t1.TOPIC_POSTER_NAME,t1.TOPIC_THUMBNAIL,t4.POST_BODY,t3.USER_GROUP_IMAGES from ubbt_TOPICS as t1 left join ubbt_USERS as t2 on t1.USER_ID = t2.USER_ID left join ubbt_USER_PROFILE as t3 on t1.USER_ID = t3.USER_ID left join ubbt_POSTS as t4 on t1.POST_ID = t4.POST_ID where t1.FORUM_ID = 10 and t1.TOPIC_IS_STICKY = '0' AND t1.TOPIC_IS_APPROVED = '1' ORDER BY t2.USER_DISPLAY_NAME asc LIMIT 25;
Im not getting how ubb admin panel is telling me there's 500mb of data in the tables, and Ive got a InnoDB cache/buffer size setting in my.cnf of 2048Mb. You'd think there'd be enough space to mess around in and make things super fast.
What's the solution here? Throwing faster machinery at the problem? How about optimizing these queries or something?
Another solution is to run mysql in ramdisk only and replicate to actual disk. Or use a disk-backed ram disk system... seems all like a hack to avoid fixing the root problem tho.
