Staggering SQL Stats For Nuke!

Site Admin/Owner Joined: Aug 27, 2002 Posts: 17088

The following are nauseating tracking results for the impact that 1 user has on 4 seemingly innocent mouse clicks on my site.

Code:

125 queries to database - Initial entry (index.html)


 99 queries to database - Selecting a post from scrolling block


115 queries to database - Selecting the Administration menu from admin block


 86 queries to database - Selecting Preferences from admin menu


---


1113

Now, extrapolate that to concurrent users. Folks, I'm an old dba from DB2, Oracle, and several relics. These stats just shouldn't be Rolling Eyes

Is it any wonder that Hosts don't want nuke sites on their servers?

Well, let's start the ball rolling here and let's see what we can come up with to make this 'database server killer application' behave better Wink

Insane Joined: Jul 30, 2004 Posts: 85

phpNuke has lead to alot of people getting kicked off servers. The resources it takes up at the moment are... horrifying to say the least.

Which leads me to the question, do alot of the Nuke community know about this? Or do they only look at the (Quote Raven)"bells and whistles"(Unquote).

By adding features such as protector etc. The number rises even more! Which is a shame because these are the kinds of features that are actually useful in a CMS.

Client Joined: Jul 18, 2003 Posts: 977

Raven wrote:

Folks, I'm an old dba from DB2, Oracle, and several relics. These stats just shouldn't be Rolling Eyes

Is it any wonder that Hosts don't want nuke sites on their servers?

Wow my site must be killing you, eh Raven?

Posted: Thu Sep 16, 2004 3:49 pm

I've also noticed it makes a ton of readdir requests on some pages like 1100 on each your account page. But those are pretty fast compared to the db queries.

Posted: Fri Sep 17, 2004 11:13 am

Raven wrote:

The following are nauseating tracking results for the impact that 1 user has on 4 seemingly innocent mouse clicks on my site.

Code:

125 queries to database - Initial entry (index.html)


 99 queries to database - Selecting a post from scrolling block


115 queries to database - Selecting the Administration menu from admin block


 86 queries to database - Selecting Preferences from admin menu


---


1113

How many of those queries are due to the Site Info and the Who is Where blocks? I've heard from others that blocks such as these are query-hungry. How much would removing or not having those types of blocks impact the number of queries for the same scenario above?

Posted: Fri Sep 17, 2004 12:23 pm

Those 2 blocks add 15 queries with 1 registered member. Obviously that will increase per member, but remember that my original stats were based on only 1 member too.

Posted: Fri Sep 17, 2004 1:08 pm

Lets blame it on blith thats easier then spending our endless hours of free time optimizing the code. Wink

Posted: Fri Sep 17, 2004 1:14 pm

Seriously though cache the pages to non logged hits. That would eliminate a ton of crawler hits from barkin off MySQL time. Reminds me I'm gonna change my gzip to level 1 or 2 I've been running it at 9 just to see if I could. But it isn't gaining enough from 1-9 to be worth that much of a hit on the server.

When folks see how well caching works everyone will want to try it Mr. Green

Posted: Fri Sep 17, 2004 2:48 pm

But the fact is that phpNuke shouldn't need workarounds such as caching, bigger and better servers, g-zipping for speed etc.

It's the code that is the cause of all this, security, speed, contact with devs. This is all practically nonexistent, if it weren't for Raven, Chatserv, Bob, Six, and all the other great coders, (internationally) then FB's creation would've croaked long ago.

Posted: Fri Sep 17, 2004 3:17 pm

Rage,

While I share your contempt in many ways, the caching and compression schemes are written for heavy traffic, regardless of poor architecture, and are not 'work-arounds'. Add in poor achitecting and you become the Titanic. We need all the extra help we can muster Wink

Posted: Fri Sep 17, 2004 4:21 pm

Yes Raven, but I really feel that following FB's code is ludicrous.

Caching, fine, but the code itself is still flawed in many ways and thinking 'outside of the box' wont improve performance as much as many hope. Smile

I understand we need all the help we can muster, I feel like we're being led to someplace to die. Laughing

Posted: Sat Sep 18, 2004 1:16 pm

Speaking of performance where has steve111 been ? He has a good eye for speed tweaks.

Posted: Mon Sep 20, 2004 2:38 am

Given the way phpbb is integrated with nuke, the use of counters and other per session statistics monitoring, IP tracking and Who_is_where as well as other monitoring that is going on by various security programs, coupled with blocks of news and forum feeds and variables in the login block alone, I'm really surprised anyone's numbers are that low. Additionally, with any other dynamic content or variables added into the mix, I don't see how you can reduce the number of SQL queries.

I guess my real problem is not with queries, it's with the code itself. If we are going to monitor activities and have dynamic content (determined by user properties or different content for different groups of users), we are always going to take a hit on the DB side. I've never really had a problem with this as with sharepoint portal server I was seeing 250 queries to MS-SQL on the homepage load, nevermind the backend queries to Exchange when a user logged in. And with Office Front-end, it went up to 600+ queries! Talk about being a pig!

Anyway... if you have a decent server, this isn't really an issue, as after all it's not just about being a query, but rather it's the TYPE of query and how much of a load that query draws.

Again, caching can really makes a difference here but it too must be configured specifically to address the data in question and the performance of query types to make a huge difference (Raven and other folks familiar with Sybase and DB2 should definately know what I'm talking about!) Caching the wrong data and/or failing to property maintain (regularly index) the right data is just as performance damaging as doing nothing at all. Prioritizing queries can also be a problem, it really depends on how the application is written. Some are obviously better than others, but to be honest, I have yet to see a nuke site where this has become a really serious issue. I mean if you are taking 10,000 hits a day, you are making enough money to buy a dedicated SQL server, so what's the issue again?

registered · Posted: Tue Sep 21, 2004 5:26 am

If you want to get some idea of how many sql queries it takes to generate various Nuke pages, I've added code to the 'overall footer' on my site that tracks this (bottom of every page).

Check it out: www.lenon.com

As far as caching is concerned, I was running a cache on my Open Directory Project module for a while. I was never convinced this sped things up on my site, but I don't get that much traffic. What freaked me was the amount of space it requires. I checked it a couple of weeks ago, and the OPD cache had consumed 498MB on my server. So, I turned it off and forgot about caching. A few more days, and that alone would have got me booted!

To tell you the truth, all things considered, I don't know any way around this without a colossal effort. I think it would be easier finding a new host... Very Happy

Posted: Tue Sep 21, 2004 6:39 am

lol yeah there has to be a trash cleanup or you could wind up seriously over your disc space allotment!

Both jpcache and cache-lite seem to do a reasonable job of cleaning up the junk files.

Ammended:
Of course most of us won't try to cache the complete ODP database Wink

Regular Joined: Jun 02, 2004 Posts: 88

OK.... Back to Raven's original post.....

I am not a coder, but a "modifier" and user.... with that being said, I extend my help and other's from nukehelp.biz to do what we can to jump start this effort.

Just thinking out loud, I would think that the Project module could be used to separate/catgegorize tasks and assists a concerted effort. We could brainstorm the areas needing possible attention, assign leaders, etc. and then methodically move forward in an effort to reduce queries so that php nuke can be as effecient as possible.

I offer nukehelp.biz as is needed, to host the Project module, or simply as giving suggestions, ideas, and/or directions.

What I am basically saying is that I am unsure of the current direction of PHP Nuke, or whether there is actually any direction or written plans. With the success of Security Patches and Sentinel, the most recent crises seems to be over, with only any new attack method needing attention, should there be one. Maybe with organization and planning, "things" can be done towards effeciency with positive results. And maybe with colaborative efforts, ideas, suggestions, etc., progress can be made in making PHP Nuke use less database resources.

well...... What do you all think?

one more thought...... maybe it is just me, but it would be nice to see goals set, and a clear-cut direction so that we can plan ahead.....

***edit*** On nukehelp, I could create a new set of Forum categories listing the specific brainstormed categories so that anyone can contribute ideas. I think something like that would be good for keeping track of suggestions, and also in organizing/separating thoughts so there will be less confusion...... just a thought.....

***edit #2*** a suggestion..... Just thinking out loud, we could maximize our efforts by giving more time to the actual php nuke coders... For example, since I am not a coder, I could help or do the organization/clarification parts. In other words, time is lost if the "coders" here must attend to things that are not "code-specific" like in setting up organization, or having to answer forum questions that are not related to their project piece. Some of us can volunteer to answer forum postings on various websites so that the coders do not "abandon" users/members. Maybe a concentrated effort could lead to positive results. again.....just thinking out loud here, and offering ideas.

Posted: Tue Sep 21, 2004 2:15 pm

I'm not sure there is support here for a consorted effort or not. For people running small to moderate sized sites PHPNuke serves them fairly well as it is. For the rest there are alternatives. It is entirely up to Raven if he wishes to work toward something of this nature with his repackaged distributions.

NSN has often offered up improvements but has gotten very little in the way of kudos or acknowledgments for the effort.

With the exception of Chatserv most of us here are considered to be usurpers and trouble makers by specific sub sets within the community. CPG-Nuke for all intents was moving in this direction but it seems to have moved beyond just a "Speed Nuke Version" lately.

Some of the issues:
Will organized code get integrated into the main application?

Should it be donated to the project to further support FB?

NukeCops Bundle was a huge success but in order to be on good terms with FB the effort was hindered due to its relation to public released versions and Fb's whims as to whether or not to use submitted fixes. NukeCops was given the opportunity to work with DJMaze and create just such distro but that was coldly rejected by NC thus CPG-Nuke evolved on its own.

Without turning to a flame session here I'd just like to say that there are many personal and professional reasons not to put forth a consorted effort such as the one you propose. Though the your idea is great and deserves a whack so to speak.

Posted: Tue Sep 21, 2004 2:30 pm

I understand the politics..... probably more so than one would believe. Smile

To me, I could care less if the code were offered to whoever. To me, it is a matter of making improvements, and possibly helping in directions that make sense to some of us. Maybe the efforts can be offered to FB, or put in a distro.... does not matter. Maybe the efforts could/would still be compatible with 7.5+ or maybe not. I have no idea.

If politics are an issue to some, then maybe they can choose to not participate. I and others would not have a problem with that.

Well..... I wish to put a positive spin at least on the thoughts of initiation of such a project. Anyone else wish to give input?

Posted: Tue Sep 21, 2004 2:38 pm

I'm only speaking for myself, but where Raven, Chatserv, Bob and select others lead my in-use code will surely follow.

Posted: Tue Sep 21, 2004 2:44 pm

SpottedHog, I have set forth a similar desire in http://www.ravenphpscripts.com/postxf1105-0-0.html . You may or may not have read it, but it is worth the read.

Posted: Tue Sep 21, 2004 2:50 pm

Maybe going back to the Fork or not to Fork question once again isn't a bad idea.

Posted: Tue Sep 21, 2004 2:56 pm

I think on the next to the last page I scaled it down a little from my Nirvana. I stll think that is probably a viable alternative.

Posted: Tue Sep 21, 2004 3:38 pm

I had followed that thread way back when, but I just reread it all..... phew!!!!! Smile

Well..... Raven, I think we are thinking along the same lines. In our current times, maximizing talents would help get/keep a project going. It seems like the other thread, there were decisions made, plans at least thought of, but something happened..... probably the security issues....

If a restart is wanted/needed, I can help in the organization, management, and vehicles for optimizing time. Of course we all know the main coders here, and I would think we could all assist by giving you all more time to "do the voodoo that you do so well". (shades of Blazing Saddles) Smile

Welll......... Raven, are you and "the others" wanting to drive a stake in the ground and get started? From that to fork or not, it surely appears enough interest is there.

***edit*** If this is restarted, maybe setting up ground rules to help keep out politics and some needless chatter may be necessary to keep focus. "Mounting up and riding off in all directions" is not very effecient. Smile

Posted: Tue Sep 21, 2004 3:52 pm

I'm not a coder, but I'll pledge my support for this. I'll toss in a couple of bucks now and then. I'll test code. I'll help write documentation.

Where the Wizards of Nuke lead, I shall follow

Posted: Tue Sep 21, 2004 6:28 pm

"Mounting up and riding off in all directions" is not very effecient.

LMAO did you read the same thread I did? Seriously the Cowboys have left the building.