[Rejected] mysql CPU usage suddenly high (caused by mybb)

[UzGz]

Hello eveyrone, i have a strange problem that might not be directly related to mybb but since 24hours I have some very high mysql cpu usage, I tried to turn off mybb board, cpu usage came back to normal, then turned mybb back on and mysql cpu usage went up again, but I don't understand what's causing this. 


I did not do any change for quite a long time on my board, just installed a plugin to "view unread posts" a week ago. And deactivating it doesn't change anything.
I tried SHOW FULL PROCESSLIST in mysql but I don't see any queries

I have a 50k members / 18k posts forum.
30-50 people connected at the same time in average. And never any problem like that before (for quite a long time)

Did anybody encounter a problem like this before ?

Also i'm on ubuntu 12.04 with plesk and I moved from IP.Board to mybb (maybe this can cause any database issue idk)
Sorry if wrong forum.

[dragonexpert]

Are you on shared hosting? You might also try my Fast Search plugin because searches are not optimized at all by default and my plugin makes them much better. It made a site with over 1 million posts go fro minute plus searches or failures to a couple seconds at most. Server load was also drastically reduced.

[andrewjs18]

Are you on shared hosting?  You might also try my Fast Search plugin because searches are not optimized at all by default and my plugin makes them much better.  It made a site with over 1 million posts go fro minute plus searches or failures to a couple seconds at most.  Server load was also drastically reduced.

what makes your search better than the default?

[UzGz]

Are you on shared hosting? You might also try my Fast Search plugin because searches are not optimized at all by default and my plugin makes them much better. It made a site with over 1 million posts go fro minute plus searches or failures to a couple seconds at most. Server load was also drastically reduced.

I'll try that thx, and no I have my own dedicated server.

---

I did try disabling search for guest without any changes.. I really don't know how to debug this. Can't find any usefull logs about what's going on..

[dragonexpert]

Are you on shared hosting?  You might also try my Fast Search plugin because searches are not optimized at all by default and my plugin makes them much better.  It made a site with over 1 million posts go fro minute plus searches or failures to a couple seconds at most.  Server load was also drastically reduced.

what makes your search better than the default?

Queries generated are more server friendly and also it doesn't use so much PHP processing. The default search uses a IN condition in the WHERE clause that contains tids and pids that aren't on the current page for example. Longer queries take longer to execute.

[UzGz]

I can't find what's causing this.. anybody knows how to debug mysql queries / process from phpmyadmin or mybb admin panel or anything else ? CPU usage by mysql is still 20-25% for 3 days now
Navigation on the forum is normal thought, no error, no long loading pages.. I don't understand :/

[.m.]

though you are using a dedicated server, your web host support should be able to analyze & assist (they have better tools)

have you tried general search results & mybb related search results

[dragonexpert]

One of the options is to display debug information in the footer of pages. Clicking the link for it shows you all the queries, how much time they took to execute.

[broatcast]

If you running a linux box you can just change the "my.cnf" from your mysql or mariadb server to log wich querys are taking so much ram (mostly they take some time to) so you are able to log this by adding the follow to your my.cnf

[mysqld]
# Set Slow Query Log
long_query_time = 1
slow_query_log = 1
slow_query_log_file = "/var/log/mysql/slowquery.log"

And in phpmyadmin check if you turn off the performance_schema, additional check you have may turned on the version system on any of the tables.

Otherwise we need go more deply to check whats wrong with your db server.

[andrewjs18]

I installed dragonexpert's fast search plugin on a forum I admin with ~532k posts or so and it cut down the page generation in half or more on all searches. crazy!!!