Mailing-List: contact modules-dev-help@httpd.apache.org; run by ezmlm
Precedence: bulk
Reply-To: modules-dev@httpd.apache.org
Received-SPF: pass (athena.apache.org: local policy)
Message-ID: <49EB9408.40005@dmi.me.uk>
Date: Sun, 19 Apr 2009 22:13:44 +0100
From: Dave Ingram <dave@dmi.me.uk>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-GB;
 rv:1.8.1.21) Gecko/20090302 Thunderbird/2.0.0.21 Mnenhy/0.7.6.666
MIME-Version: 1.0
To: modules-dev@httpd.apache.org
Subject: Re: MySQL Virtual Host and Traffic Module
References: <003001c9a7cd$16250930$426f1b90$@com.au>
 <49C11314.8020700@dmi.me.uk> <000301c9a82f$b993f2e0$2cbbd8a0$@com.au>
 <49C1DFC3.1050202@dmi.me.uk> <23103676.post@talk.nabble.com>
In-Reply-To: <23103676.post@talk.nabble.com>
Content-Type: multipart/alternative;
 boundary="------------040301060107090104010802"

--------------040301060107090104010802
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Hi Eldho,

I'm the author mod_sqltemplate, which sounds like it does what you're 
after (as Jorge kindly pointed out). It's currently under mostly-active 
development, and I'm definitely open to bug reports and suggestions.


Dave


Eldho wrote:
> Hi Vaughan,
>
> What is the status of this module. Actually I am searching for a module 
> that write all the vhost  configuration to a database and read it from db
> also.
>
> thanks.
>
> Eldho
>
>
>
> Dave Ingram wrote:
>   
>> Hi Vaughan,
>>     
>>> Thanks for the response. I haven't thought of doing the SQL query the way
>>> you suggested, however I agree that it will cause unnecessary load on
>>> busy
>>> servers and I would like to keep this as efficient as possible.
>>>
>>> The second option sounds more reasonable. I have already used threading
>>> to
>>> make a function which ticks on a configurable interval so I suppose each
>>> child process would dump data for each of its vhosts at this interval,
>>> using
>>> a query similar to what you have suggested.
>>>   
>>>       
>> I think that's probably the most sensible approach. It does mean that 
>> you won't have up-to-the-moment statistics, and I would guess that you'd 
>> have to play about with different intervals as the number of hosts grows 
>> in order for it to scale. You may also want to consider somehow 
>> staggering the updates, so they don't all happen at once. It may also be 
>> advisable to perform an UPDATE rather than an INSERT... ON DUPLICATE 
>> UPDATE once your module knows that there is a value that can be updated 
>> (i.e. after the query has run once in the simple case, or once this 
>> day/hour/etc in the complex case).
>>
>>     
>>> I think I might go with the second option for the time being and see how
>>> it
>>> goes but I am still interested to know if there is a way to store per
>>> vhost
>>> data across children?
>>>   
>>>       
>> I would be interested to know how things turn out, and I'd be interested 
>> to see the final module. I've been thinking about writing a custom 
>> bandwidth monitoring/limiting module myself, but if I don't need to 
>> reinvent the wheel...
>>
>> I'm afraid I can't answer this question in a definite way, though. One 
>> module that should store per-vhost data like this is mod_cband 
>> <http://sourceforge.net/projects/cband/>, so that might be worth looking 
>> into.
>>
>> As a side note, I'd be interested to know how you create/template the 
>> virtual hosts. I myself have written a database-backed templating module 
>> that could be used for virtual hosting 
>> (http://www.dmi.me.uk/code/apache/mod_sqltemplate/) and I'm curious to 
>> see other approaches.
>>
>> Thanks,
>>
>>
>> Dave
>>
>>     
>>> Thanks,
>>> Vaughan
>>>
>>> -----Original Message-----
>>> From: Dave Ingram [mailto:dave@dmi.me.uk] 
>>> Sent: Thursday, 19 March 2009 12:28 AM
>>> To: modules-dev@httpd.apache.org
>>> Subject: Re: MySQL Virtual Host and Traffic Module
>>>
>>> Vaughan,
>>>
>>>   
>>>       
>>>> What I have so far are 2 filters which gather the inbound traffic and
>>>> outbound traffic for each transaction. These work ok and when logging
>>>> transactions to file all of the in/out byte amounts appear to be
>>>> correct.
>>>> The first problem however, is that each child has its own set of memory
>>>>     
>>>>         
>>> and
>>>   
>>>       
>>>> therefore keeps its own totals per virtual host. This also means that
>>>> multiple logging events occur for each transaction. I could just log
>>>> this
>>>> all to database but it would 1) be inefficient and 2) cause the size of
>>>>     
>>>>         
>>> the
>>>   
>>>       
>>>> database to grow quite quickly.
>>>>   
>>>>     
>>>>         
>>> It sounds to me like you could go two ways with this. I don't know the
>>> format of your database table, but it should be possible to update it
>>> atomically using something like:
>>>
>>> INSERT INTO bandwidth (vhost_id, bw_in, bw_out) VALUES (42, 1124,
>>> 5023409) ON DUPLICATE KEY UPDATE bw_in = bw_in + 1124, bw_out = bw_out +
>>> 5023409
>>>
>>> but that could lead to a lot of load. Another way might be for each
>>> child to collect statistics and only flush to the database periodically,
>>> say every 30 seconds (perhaps configurable on a per-vhost basis, so that
>>> load-heavy sites could have larger update intervals). It would still be
>>> possible to use the query above though.
>>>
>>> This query could probably even be updated to split statistics on a
>>> date/time basis, if you require more granular reporting.
>>>
>>> Or have I missed/misunderstood something?
>>>
>>>
>>> Dave
>>>
>>>   
>>>       
>>
>>     
>
>   


--------------040301060107090104010802--