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:d...@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
