SQL processes out of control

[swiftor]

hey guys, something funky is up with my install of vbactivity pro 1.10

It was working fine for a few days, until I imported the default set of achievements and had it run against all existing users. While that took a while, it finished without error. But after that, my server just began to crawl. At that point, I uninstalled it, restarted my sql services, and all was good while I sorted out a xcache authentication issue (thanks for the help!)

So tonight I import the product xmls for lite and pro again, and after an hour or two, my SQL is devoured by queries from the mod. Any idea what might be up? Even after disabling the mods, the queries are still eating my server.

Here's a copy and paste:

Id User Host db Command Time State Info
404031 leechprotect localhost leechprotect Sleep 4000 NULL
410841 swiftor_forum localhost swiftor_forums Query 386 preparing SELECT * FROM dbtech_vbactivity_rewards WHERE userid IN( SELECT userid FROM post AS post WHE
410848 swiftor_forum localhost swiftor_forums Query 388 preparing SELECT * FROM dbtech_vbactivity_rewards WHERE userid IN( SELECT userid FROM post AS post WHE
410851 swiftor_forum localhost swiftor_forums Query 387 preparing SELECT * FROM dbtech_vbactivity_rewards WHERE userid IN( SELECT userid FROM post AS post WHE
410852 swiftor_forum localhost swiftor_forums Query 387 preparing SELECT * FROM dbtech_vbactivity_rewards WHERE userid IN( SELECT userid FROM post AS post WHE
410859 swiftor_forum localhost swiftor_forums Query 387 preparing SELECT * FROM dbtech_vbactivity_rewards WHERE userid IN( SELECT userid FROM post AS post WHE
410860 swiftor_forum localhost swiftor_forums Query 387 preparing SELECT * FROM dbtech_vbactivity_rewards WHERE userid IN( SELECT userid FROM post AS post WHE
410866 swiftor_forum localhost swiftor_forums Query 387 preparing SELECT * FROM dbtech_vbactivity_rewards WHERE userid IN( SELECT userid FROM post AS post WHE
410868 swiftor_forum localhost swiftor_forums Query 387 preparing SELECT * FROM dbtech_vbactivity_rewards WHERE userid IN( SELECT userid FROM post AS post WHE
410871 swiftor_forum localhost swiftor_forums Query 387 preparing SELECT * FROM dbtech_vbactivity_rewards WHERE userid IN( SELECT userid FROM post AS post WHE
410872 swiftor_forum localhost swiftor_forums Query 388 preparing SELECT * FROM dbtech_vbactivity_rewards WHERE userid IN( SELECT userid FROM post AS post WHE
410883 swiftor_forum localhost swiftor_forums Query 387 preparing SELECT * FROM dbtech_vbactivity_rewards WHERE userid IN( SELECT userid FROM post AS post WHE
410890 swiftor_forum localhost swiftor_forums Query 384 preparing SELECT * FROM dbtech_vbactivity_rewards WHERE userid IN( SELECT userid FROM post AS post WHE
410899 swiftor_forum localhost swiftor_forums Query 379 preparing SELECT * FROM dbtech_vbactivity_rewards WHERE userid IN( SELECT userid FROM post AS post WHE
410918 swiftor_forum localhost swiftor_forums Query 370 preparing SELECT * FROM dbtech_vbactivity_rewards WHERE userid IN( SELECT userid FROM post AS post WHE
410925 swiftor_forum localhost swiftor_forums Query 367 preparing SELECT * FROM dbtech_vbactivity_rewards WHERE userid IN( SELECT userid FROM post AS post WHE
410970 swiftor_forum localhost swiftor_forums Query 339 preparing SELECT * FROM dbtech_vbactivity_rewards WHERE userid IN( SELECT userid FROM post AS post WHE
410980 swiftor_forum localhost swiftor_forums Query 333 preparing SELECT * FROM dbtech_vbactivity_rewards WHERE userid IN( SELECT userid FROM post AS post WHE
410983 swiftor_forum localhost swiftor_forums Query 332 preparing SELECT * FROM dbtech_vbactivity_rewards WHERE userid IN( SELECT userid FROM post AS post WHE
410984 swiftor_forum localhost swiftor_forums Query 331 preparing SELECT * FROM dbtech_vbactivity_rewards WHERE userid IN( SELECT userid FROM post AS post WHE
410988 swiftor_forum localhost swiftor_forums Query 328 preparing SELECT * FROM dbtech_vbactivity_rewards WHERE userid IN( SELECT userid FROM post AS post WHE
410993 swiftor_forum localhost swiftor_forums Query 327 preparing SELECT * FROM dbtech_vbactivity_rewards WHERE userid IN( SELECT userid FROM post AS post WHE
410994 swiftor_forum localhost swiftor_forums Query 325 preparing SELECT * FROM dbtech_vbactivity_rewards WHERE userid IN( SELECT userid FROM post AS post WHE
410998 swiftor_forum localhost swiftor_forums Query 326 preparing SELECT * FROM dbtech_vbactivity_rewards WHERE userid IN( SELECT userid FROM post AS post WHE
411007 swiftor_forum localhost swiftor_forums Query 324 preparing SELECT * FROM dbtech_vbactivity_rewards WHERE userid IN( SELECT userid FROM post AS post WHE
411010 swiftor_forum localhost swiftor_forums Query 324 preparing SELECT * FROM dbtech_vbactivity_rewards WHERE userid IN( SELECT userid FROM post AS post WHE
411012 swiftor_forum localhost swiftor_forums Query 323 preparing SELECT * FROM dbtech_vbactivity_rewards WHERE userid IN( SELECT userid FROM post AS post WHE
411022 swiftor_forum localhost swiftor_forums Query 320 preparing SELECT * FROM dbtech_vbactivity_rewards WHERE userid IN( SELECT userid FROM post AS post WHE
411024 swiftor_forum localhost swiftor_forums Query 319 preparing SELECT * FROM dbtech_vbactivity_rewards WHERE userid IN( SELECT userid FROM post AS post WHE
411035 swiftor_forum localhost swiftor_forums Query 314 preparing SELECT * FROM dbtech_vbactivity_rewards WHERE userid IN( SELECT userid FROM post AS post WHE
411074 swiftor_forum localhost swiftor_forums Query 294 preparing SELECT * FROM dbtech_vbactivity_rewards WHERE userid IN( SELECT userid FROM post AS post WHE
411084 swiftor_forum localhost swiftor_forums Query 283 preparing SELECT * FROM dbtech_vbactivity_rewards WHERE userid IN( SELECT userid FROM post AS post WHE
411103 swiftor_forum localhost swiftor_forums Query 262 preparing SELECT * FROM dbtech_vbactivity_rewards WHERE userid IN( SELECT userid FROM post AS post WHE
411152 swiftor_forum localhost swiftor_forums Query 241 preparing SELECT * FROM dbtech_vbactivity_rewards WHERE userid IN( SELECT userid FROM post AS post WHE
411157 swiftor_forum localhost swiftor_forums Query 240 preparing SELECT * FROM dbtech_vbactivity_rewards WHERE userid IN( SELECT userid FROM post AS post WHE
411180 swiftor_forum localhost swiftor_forums Query 229 preparing SELECT * FROM dbtech_vbactivity_rewards WHERE userid IN( SELECT userid FROM post AS post WHE
411193 swiftor_forum localhost swiftor_forums Query 222 preparing SELECT * FROM dbtech_vbactivity_rewards WHERE userid IN( SELECT userid FROM post AS post WHE
411237 swiftor_forum localhost swiftor_forums Query 193 preparing SELECT * FROM dbtech_vbactivity_rewards WHERE userid IN( SELECT userid FROM post AS post WHE
411248 swiftor_forum localhost swiftor_forums Query 181 preparing SELECT * FROM dbtech_vbactivity_rewards WHERE userid IN( SELECT userid FROM post AS post WHE
411254 swiftor_forum localhost swiftor_forums Query 178 preparing SELECT * FROM dbtech_vbactivity_rewards WHERE userid IN( SELECT userid FROM post AS post WHE
411255 swiftor_forum localhost swiftor_forums Query 178 preparing SELECT * FROM dbtech_vbactivity_rewards WHERE userid IN( SELECT userid FROM post AS post WHE
411266 swiftor_forum localhost swiftor_forums Query 170 preparing SELECT * FROM dbtech_vbactivity_rewards WHERE userid IN( SELECT userid FROM post AS post WHE
411267 swiftor_forum localhost swiftor_forums Query 169 preparing SELECT * FROM dbtech_vbactivity_rewards WHERE userid IN( SELECT userid FROM post AS post WHE
411268 swiftor_forum localhost swiftor_forums Query 169 preparing SELECT * FROM dbtech_vbactivity_rewards WHERE userid IN( SELECT userid FROM post AS post WHE
411277 swiftor_forum localhost swiftor_forums Query 1 Locked SELECT username from user where `userid`='2'
411287 swiftor_forum localhost swiftor_forums Query 145 preparing SELECT * FROM dbtech_vbactivity_rewards WHERE userid IN( SELECT userid FROM post AS post WHE
411306 swiftor_forum localhost swiftor_forums Query 121 preparing SELECT * FROM dbtech_vbactivity_rewards WHERE userid IN( SELECT userid FROM post AS post WHE
411312 swiftor_forum localhost swiftor_forums Query 117 Sending data SELECT * FROM dbtech_vbactivity_rewards WHERE userid IN( SELECT userid FROM post AS post WHE
411314 swiftor_forum localhost swiftor_forums Query 113 Sending data SELECT * FROM dbtech_vbactivity_rewards WHERE userid IN( SELECT userid FROM post AS post WHE
411315 swiftor_forum localhost swiftor_forums Query 112 Sending data SELECT * FROM dbtech_vbactivity_rewards WHERE userid IN( SELECT userid FROM post AS post WHE
411324 swiftor_forum localhost swiftor_forums Query 100 Sending data SELECT * FROM dbtech_vbactivity_rewards WHERE userid IN( SELECT userid FROM post AS post WHE
411333 swiftor_forum localhost swiftor_forums Query 93 Sending data SELECT * FROM dbtech_vbactivity_rewards WHERE userid IN( SELECT userid FROM post AS post WHE
411334 swiftor_forum localhost swiftor_forums Query 91 Sending data SELECT * FROM dbtech_vbactivity_rewards WHERE userid IN( SELECT userid FROM post AS post WHE
411337 swiftor_forum localhost swiftor_forums Query 84 Sending data SELECT *

 

[Fillip H.]

Those are queries ran on showthread. I'm going to cache those rewards in a 1.1.1 update, which should be coming some time over the course of next week.

 

[swiftor]

Would removing achievements fix this in the meanwhile for me? Others seem to use it no problem, and I have VB installed on a pretty nice hosting package.

 

[Fillip H.]

Disabling Achievements alone doesn't disable the query because "rewards" mean many things (Achievements as well as Medals).

You can fix it by disabling these 3 plugins:
showthread_query
private_showpm_start
announcement_query

Doing that means there's no need to disable Achievements, and they will still be awarded to users, they'll just be hidden from the postbit.


I find it very weird that this query kills your server though, we had one person on vBulletin.org who had a massive member base and he reported no problems beyond a rather unfortunate query that was fixed in v1.1... Oh well, it was already my plan to fix this, as the current method of fetching rewards have other downsides as well.

 

[swiftor]

Thanks for the fast response!

 

[tuwebfacil]

I have a big forum too, and this hack its killing my server too, has been dissapointing since, I am paying for something that I can't use...

 

[Trekkan]

I have a big forum too, and this hack its killing my server too, has been dissapointing since, I am paying for something that I can't use...

Seeing as how this is your first message about the problem (at least publicly), you should probably provide some information to the devs so they can help fix it.

More specifics would be a good place to start. =)

 

[Fillip H.]

I have a big forum too, and this hack its killing my server too, has been dissapointing since, I am paying for something that I can't use...

Try enabling the Slow Query Log in your mysql config, then tell me which query is causing MySQL to slow down, and I'll have it fixed in the next version

 

[swiftor]

I disabled the 3 plugins specified above to tide me over. I can still run the mod (albeit without a few features) until the next version.

You can fix it by disabling these 3 plugins:
showthread_query
private_showpm_start
announcement_query

 

[Fillip H.]

Those 3 plugins are now permanently deleted, as they are no longer needed

v1.1.1 of Lite and Pro is currently in testing here at DBTech, I'm just fixing other minor issues around the forums before giving it to you guys

 

[tuwebfacil]

Honestly I am not very expert on the mysql side, but I will reinstall the hack, and let you know my experience. I know that it's my first post, but I am very glad that you're taking care of it.

If I feel my forum slow, I will contact support so you can take a closer look