Identifying query intensive mod?

[Tourmeister]

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.

[Paul M]

Originally Posted by Tourmeister

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?

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.

[Tourmeister]

Originally Posted by Paul M

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?

[Paul M]

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.

[Tourmeister]

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

[Mellow]

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.

[Tourmeister]

Man, that was a nice easy upgrade!

[Paul M]

Originally Posted by Mellow

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?

Yes, just run

show index from post

[Mellow]

Thank you sir!

[Tourmeister]

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!