RE: MySQL Virtual Host and Traffic Module
Hi there Dave :) I still haven't got time to configure it, but I've been checking tracking SVN changes. Jorge, > -Original Message- > From: Dave Ingram [mailto:d...@dmi.me.uk] > Sent: domingo, 19 de Abril de 2009 22:14 > To: modules-dev@httpd.apache.org > Subject: Re: MySQL Virtual Host and Traffic Module > > 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 > >>> > >>> > >
Re: MySQL Virtual Host and Traffic Module
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
RE: MySQL Virtual Host and Traffic Module
Ops! Didn't saw that It was in the end of the email already :) > -Original Message- > From: Jorge Bastos [mailto:mysql.jo...@decimal.pt] > Sent: sexta-feira, 17 de Abril de 2009 19:54 > To: modules-dev@httpd.apache.org > Subject: RE: MySQL Virtual Host and Traffic Module > > http://www.dmi.me.uk/code/apache/mod_sqltemplate/ > > this module will do the work for you, I still haven't got time to test > it > but for sure will do. > > > > > -Original Message- > > From: Eldho [mailto:eldhokp...@gmail.com] > > Sent: sexta-feira, 17 de Abril de 2009 19:49 > > To: modules-dev@httpd.apache.org > > Subject: Re: MySQL Virtual Host and Traffic Module > > > > > > 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. &
RE: MySQL Virtual Host and Traffic Module
http://www.dmi.me.uk/code/apache/mod_sqltemplate/ this module will do the work for you, I still haven't got time to test it but for sure will do. > -Original Message- > From: Eldho [mailto:eldhokp...@gmail.com] > Sent: sexta-feira, 17 de Abril de 2009 19:49 > To: modules-dev@httpd.apache.org > Subject: Re: MySQL Virtual Host and Traffic Module > > > 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 > >> > >> > > > > > > > > -- > View this message in context: http://www.nabble.com/MySQL-Virtual-Host- > and-Traffic-Module-tp22579200p23103676.html > Sent from the Apache HTTP Server - Module Writers mailing list archive > at Nabble.com.
Re: MySQL Virtual Host and Traffic Module
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 >> >> > > > -- View this message in context: http://www.nabble.com/MySQL-Virtual-Host-and-Traffic-Module-tp22579200p23103676.html Sent from the Apache HTTP Server - Module Writers mailing list archive at Nabble.com.
Re: MySQL Virtual Host and Traffic Module
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
RE: MySQL Virtual Host and Traffic Module
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
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
