Identifying query intensive mod?
 

Howdy,

I run a server with two active VB forums, one is mine (twtex.com) and the other person (st-owners.com) pays me for server space. We have been seeing some very large spikes in our server load several times per day. Using mtop, we have determined that ALL of the queries causing the spikes are coming from the stowners site. So we logged the queries lasting for 2 seconds or more and here is a sampling of what we are repeatedly seeing:


Here is a list of Mods that the stowners admin has installed and running:

Does anyone know if any of these mods are prone to long MySQL queries?

That query looks to be doing a count of how many unread posts the user has.

That is interesting... I have the Display Unread Posts 1.01 installed on the twtex.com site and we are not seeing similar issues. Both sites have about the same number of users online at any given time. Might any of those other mods listed also result in an output like that shown above?

vbadvanced (a couple of the modules)... do you both run CMPS?

Yes, we both run cpms

Same modules?

Maybe a couple, not sure, that's something we haven't compared. Doesn't seem like running the same modules would matter to me since we have our own Databases.

You are correct, its not the same ones, its the ones that you might have running that the other site doesn't. That could very well be where the problem is. Some CMPS modules are query intensive.

I only use the default modules that come with CMPS for twtex. I have added a few custom ones, but they do not hit the DB at all. They are just static pages that never change.

Mellow, do you have any 3rd party modules installed?

Yes, sure do.

There are a couple that show newest and hottest links/downloads.

There's also a module with tabs as well.

All the others are the basic cmps modules and some that are custom but based of the basic ones.. similar to the news block , pulling from a specific forum.

Nothing else is hitting the database.

I guess I still don't see a 'smoking gun'. We could start disabling blocks and see if that makes a difference.

Well, I would look first for mods that do a search similar to unread posts, something that has to do a different DB fetch for depending on each user that views the page.

That code is definitely from my old vb 3.5 unread posts mod (v1.01 seems reasonable).

Looking at the number of rows examined, and the time taken, I would say you are almost certainly missing the dateline index on the post table. Add that index and I think you will cure the problem.

BTW, if you are not running vb 3.5 anymore, then you should update to the latest version, it will add the index when you install it.

Howdy Paul,

I (the twtex.com site) am running VB 3.6.5 with the Display Unread Posts v1.0.1 with no problems. The St-owners site is on VB 3.6.4 and running the same version of the Unread Posts, and is having the load issues. Was this dateline index added from 3.6.4 to 3.6.5?

How would you go about adding the index manually if Mellow is not ready to upgrade his VB to the latest version?

The dateline index does not exist in default vb, I think the mod included instructions to add it at one point but I'm not sure.

Anyway, just use this SQL command ;

[sql]ALTER TABLE post ADD INDEX (dateline) ;[/sql]

However, since you are both running vb 3.6, so you should really be running this version of the mod (which as I said, creates the index automatically).

https://vborg.vbsupport.ru/showthread.php?t=123886

The version you are currently using is very old.

Thanks Paul! :up: I will just upgrade the mod for my site. :D

Done, thanks Paul.

Is there a way of knowing if the index is there or not? I don't have myphpadmin handy at the moment. Can a query from admincp show that? I do see a MUL in the Key column of the dateline attribute for the post table but I think that was there before.

Either way, better to have a current version.

Well, monitor and see if that fixes the situation.

Man, that was a nice easy upgrade!

Yes, just run

Thank you sir!

Well now... things have gotten interesting...

Mellow and I both upgraded to the latest version of the mod. That made BOTH sites start cranking out long queries... I was monitoring using mtop and was seeing multiple queries from both of our sites hitting from 1-6 seconds in length and it ran the server load up to a sustained average of 2.5 or so, when it normally is around .50 or less.

On a hunch, I removed the new version and the queries from my site stopped showing up on the mtop listing. I contacted Mellow and he removed it from his site. Since that time, there have been no queries over 1 second and the average server load is back down where it should be.

What is odd is that the old version (1.0.1) was working perfectly for me prior to the upgrade. I would reinstall it just to see what happens, but I deleted it from VB and now cannot find the old file I downloaded! DOH!

Paul, what's the difference between the one you posted and this one:
https://vborg.vbsupport.ru/showthread.php?t=123931

It seems they are both for the welcome box and 3.6.x.. ???

I reinstalled the old version. It's working perfectly for me and the query time/server load is very low. I very rarely see a query last over 1 second when monitoring with mtop.

Mellow, whichever version you have installed now seems to be working pretty well. I am still seeing the occasional appearance of STO queries over 1 second, but the server loads are still staying around .50 and lower.

Strange that the newest version would make both of our sites start banging out really long queries?