Re: [CompInaBox-discuss] Interesting new twist to CIB....

[Eric D. N. <nie...@MI...>]

Some more information:  The main cause of the overusage was
a runaway process that was causing the stats rebuild to not set its  
"updated"
flag; therefore it thought that the stats were always stale.

However there seems to be some strong candidate queries for clean up.

The queries for calculating how much a given person owes to the  
competition are "wordy"
and muttiple.  Furthermore they can not at present be collected into  
a single meta query to
aggregate them.  Thus there is an explosion of N+1 style queries to  
generate team
invoices or master lists for the competition.  This happens on every  
stats rebuild, and on many
admin page views so its a rather common and "expensive" web-server to  
database server
query.

I think the way ahead is to move the code for calculating
   a) deadline met
   b) registration fee total
   c) person paid
   d) person waived
should be moved out of CompDB.inc and into a
stroed procedure in the Database.

This will have two benefits
1) reduction of inter-machine traffic
2) easier aggregation of monetary totals

It will probably still have roughly the same number of queries, but most
wont have to travel across the wire and the "API" will look cleaner.

Not sure when I'll get to it, but just wanted to get the idea into  
the archives.

Eric




On Oct 27, 2005, at 11:23 AM, Eric D Nielsen wrote:

> The five open competitions (with four have near simultaneous  
> deadlines) finally
> caused me to go over my bandwidth quotas.  Nothing too bad and I  
> don't expect
> similar concentrated surges to occur too often so I'm not worried.   
> However it
> was very enlightening to look into the bandwidth distribution.
>
> Basically something like 90% of the bandwidth is between the  
> database server and
> the web server; web/email traffic is a small percentage.
>
> As an immediate stop-gap measure to gain some protection from  
> further overages
> this month I've throttled back the stats rebuild to only check for  
> changes
> every three minutes instead of every minute.
>
> However I don't know at this time what time of use cases contribute  
> to the
> intra-server bandwidth:
>
> Comp/Team Admin operations tend to be rather "heavy" but are less  
> common and
> accomplish more per page.
>
> Stats Rebuild are extremely data intensive and are load dependent  
> in frequency
>
> Regular SD pages are rather light, but very frequent
>
> Result Tracker is moderate, but not in heavy use yet.
>
> Does anyone have any thoughts on how I can attempt to better  
> measure/quantify
> the bandwidth breakdown to figure out where we should try to  
> optimize, if
> anywhere?
>
> Eric
>
>
>
> -------------------------------------------------------
> This SF.Net email is sponsored by the JBoss Inc.
> Get Certified Today * Register for a JBoss Training Course
> Free Certification Exam for All Training Attendees Through End of 2005
> Visit http://www.jboss.com/services/certification for more information
> _______________________________________________
> CompInaBox-discuss mailing list
> Com...@li...
> https://lists.sourceforge.net/lists/listinfo/compinabox-discuss
>