Bug No change in MYSQL statistics after install/uninstall

[hugh]

Hi,

I was running the old lite version of this mod, then recently upgraded to the new lite and finally paid version. However despite stated savings in the admincp of 240,000 and 360,000 saved queries per day respectively, I'm seeing no change in MYSQL statistics in my monitoring as per the attached. Please help me understand this, thanks...

Timeline:

Upgraded to paid version 2.5 8th July.
Disabled paid version 2.5 10th July.

 

[Fillip H.]

Did you not have the Lite version installed prior to the 3rd then?

 

[hugh]

No lite 2.0.1 was installed for as long as it was available almost.

 

[Fillip H.]

From what I can tell by the graph, when you disabled vB Optimise your queries per second increased from < 2 to 2.1 on its lowest point, and from ~6 to 7.5 on its highest point.

Keep in mind that you should only look at the green portions - obviously vB Optimise can't cache anything other than SELECT queries

If you doubt the fact that vB Optimise actually caches the queries it caches, turn on debug mode in vBulletin via config.php, then add &explain=1 after the URL - you'll see a complete list of queries ran. Then, turn on vB Optimise and ensure all the cachers are enabled, then refresh the same page again. You'll find that less queries are ran

 

[hugh]

I get a "page not found" error when I add '&explain=1' to IANA — Example domains.

 

[hugh]

Clicking on the ? link to explain queries I'm not seeing any information from vboptimize.

 

[Fillip H.]

You wouldn't see any info from vB Optimise on that page, that's not what I said

 

[hugh]

So why the error? vbseo?

 

[Fillip H.]

It doesn't matter, the (?) link to explain queries is what I was trying to get you to see

 

[hugh]

I see. Thanks.

 

[Fillip H.]

I think there may have been a misunderstanding on my part here; Are you asking about the difference in queries per sec in the Lite version vs the Pro version, or are you asking about the difference in queries per sec with vB Optimise enabled vs disabled?

If I misread your post (I assumed you meant "enabled vs disabled") then I do apologise

If you did mean "enabled vs disabled", to clarify:

On the list of explain queries you got when you clicked the (?) link, with vB Optimise disabled you can do a Ctrl+F for template and you should see a SELECT query from the template table, that has a bunch of comma-separated numbers in the WHERE portion.

This query should vanish when vB Optimise is enabled, provided it's turned on and configured correctly (i.e. opcache operator set to either Filecache or whatever else is available on your server) and Cache Templates is enabled.

Note: This could still happen even with those conditions met, since if mods are coded incorrectly they require a very small query executed to fetch templates, but it would only have one number in the WHERE portion instead of a long, long list.

 

[hugh]

I appreciate the added information and I don't doubt this mod is doing some work, what concerns me is how few queries are being saved and how little benefit in page generation time I'm seeing. For example loading forum.php with and without the mod enable I see a 0.01 second difference from a saving of 13 queries...

With

Page Generation 0.40362 seconds
Memory Usage 19,007KB
Queries Executed 18 (?)

Without

Page Generation 0.41139 seconds
Memory Usage 18,669KB
Queries Executed 31 (?)

 

[hugh]

On the CMS homepage:

Without

Page Generation 1.89505 seconds
Memory Usage 23,473KB
Queries Executed 96 (?)

With

Page Generation 1.99706 seconds
Memory Usage 24,230KB
Queries Executed 72 (?)

Page Generation 0.23011 seconds
Memory Usage 17,442KB
Queries Executed 46 (?)

Without

Page Generation 0.18649 seconds
Memory Usage 16,218K
Queries Executed 58 (?)

Page Generation 0.21905 seconds
Memory Usage 16,218KB
Queries Executed 58 (?)

With

Page Generation 2.16695 seconds
Memory Usage 24,104KB
Queries Executed 84 (?)

Page Generation 0.30380 seconds
Memory Usage 17,229KB
Queries Executed 46 (?)

 

[Cosmic]

I appreciate the added information and I don't doubt this mod is doing some work, what concerns me is how few queries are being saved and how little benefit in page generation time I'm seeing. For example loading forum.php with and without the mod enable I see a 0.01 second difference from a saving of 13 queries...

With

Page Generation 0.40362 seconds
Memory Usage 19,007KB
Queries Executed 18 (?)

Without

Page Generation 0.41139 seconds
Memory Usage 18,669KB
Queries Executed 31 (?)

I think you might have a misunderstanding of what benefit vBO has/what it is designed to do.

You're saying it's not saving many queries - but it's reducing the number of queries by almost half. vBO is designed to reduce the number of queries to your database, which CAN slow your site down if there are a bunch of concurrent connections going on - it's not specifically designed to reduce page load times, that's just a fortunate side effect for people who have very busy sites that stressed their server with the number of queries. If you want an idea of how vBO helps with page load times you should wait until your server is at peak load and then disable/enable vBO. You'll probably get very different results (though perhaps not if your server is vastly overqualified for the traffic you currently get).

Basically vBO will reduce the strain on your server and make your site more capable of handling large numbers of concurrent users, and let your current server setup last longer than it would otherwise.

Hopefully that clears things up for you!

 

[hugh]

I'll admit my configuration was heavily optimised before this mod was installed but my reporting is showing a 5% to 10% drop in select queries at best.

 

[Fillip H.]

That's still pretty normal, the pages with the heaviest reductions may not always be the one loaded. If a page where vB Optimise caches 5% of queries gets loaded 100x more than a page where it saves 50%, the stats will veer towards 5-10%

 

[hugh]

I'll accept that. What improvements are planned for the future of this mod if any?

 

[Fillip H.]

There are currently no outstanding feature requests that are feasible to implement, AFAIK. We're always listening and looking, when we dig through the vB files