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
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 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 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
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
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. 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
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
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
