Hi Dave, 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 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? 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