I have about 20 people downloading large files from my website. Each of them open 10 connections for a single file. Mod_auth_mysql need to make at least 200 database connections. Not to mention other people keep trying tirelessly with wrong password. At peak time my server is down because of too many database connection error. I try to turn KeepAlive OFF but didn't work.
Is there any suggestion?
tscbh
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
First of all, turn off MySQLKeepAlive. It does not work, and has been removed in the next release.
However, I'm not sure why there would be 10 connections for a single file. Any connection to a protected resource requires authorization. Multiple requests made during a single browser connection (i.e. a web page with five images requires six individual requests) will result in a single connection to MySQL. Requests which occur during different browser connections (i.e. two different web pages) will result in a different connection. MySQL connections are cleaned up when the browser connection is done (i.e. all resources on a web page have been loaded).
With MySQLKeepAlive set anyplace in your configuration, connections do not get closed properly. The problem is caused by improvements in Apache 2.x, which uses processes instead of threads to handle requests. This means for multiple processes to share a connection we will have to use shared memory. But this causes other problems - like properly handling concurrent requests. It's much more complicated than the current process. We've looked at it, but we also haven't had the major time necessary to get it working reliably.
So my first recommendation would be to take out MySQLKeepAlive in all cases in your configuration.
Jerry
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Thanks for your suggestion. I turned mysql persistent connection off, but I still suffer the same problem. Too many connection error.
The only thing I could do now is using mod_limitipconn, I put a connection limit on the downloading directory. This way, mysql will not be authenticate when their connection limit exceed.
The problem is solve, but I'd like to ask you if there is anything I can do to discourage my members from downloading using multiple connections with download manager. I want to find a way to put a delay whenever downloader exceeded their connection limit and closed all their connections. However, I still want them to resume download after disconnected.
If you have any suggestion, please let me know.
Thank you,
tscbh
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Unfortunately, I don't know of any way you can limit the number of connections from any one user. There might be another Apache mod out there, but I'm not aware of it. Of course, there are a lot of modules out there I don't know about.
As I said - we've been looking at a possible solution of using connection pooling in shared memory. But this adds a lotof complexity to the module and we haven't gotten the bugs worked out yet.
Jerry
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
The parameter I suggested you turn off is MySQLKeepAlive, not mysql persistent connections. mod_auth_mysql doesn't use persistent connections, but it does try to keep connections active across multiple requests. Unfortunately, this code does not work properly and has to be completely rewritten. We're working on it, but it's not easy.
Jerry
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Thanks Jerry,
I am looking forward to use the new version of mod_auth_mysql. At the moment, it's quite annoying that I have to delete http-errors.log every few days because people using download managers.
I only allow them to have 4 connections but they keep trying more than the number of connections allowed. The http-errors.log growth to 100MB in just a week.
Hopefully you have the module done soon.
tscbh
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I understand how annoying it can be. But the next version of mod_auth_mysql probably won't fix your problem. We don't try to limit the number of connections; the MySQLKeepAlive parameter's purpose was to allow a single connection to be reused, improving performance.
We are working on allowing some shared connections, but I don' t think we will limit the maximum number of connections. That gets even more difficult.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I have about 20 people downloading large files from my website. Each of them open 10 connections for a single file. Mod_auth_mysql need to make at least 200 database connections. Not to mention other people keep trying tirelessly with wrong password. At peak time my server is down because of too many database connection error. I try to turn KeepAlive OFF but didn't work.
Is there any suggestion?
tscbh
Tscbh,
First of all, turn off MySQLKeepAlive. It does not work, and has been removed in the next release.
However, I'm not sure why there would be 10 connections for a single file. Any connection to a protected resource requires authorization. Multiple requests made during a single browser connection (i.e. a web page with five images requires six individual requests) will result in a single connection to MySQL. Requests which occur during different browser connections (i.e. two different web pages) will result in a different connection. MySQL connections are cleaned up when the browser connection is done (i.e. all resources on a web page have been loaded).
With MySQLKeepAlive set anyplace in your configuration, connections do not get closed properly. The problem is caused by improvements in Apache 2.x, which uses processes instead of threads to handle requests. This means for multiple processes to share a connection we will have to use shared memory. But this causes other problems - like properly handling concurrent requests. It's much more complicated than the current process. We've looked at it, but we also haven't had the major time necessary to get it working reliably.
So my first recommendation would be to take out MySQLKeepAlive in all cases in your configuration.
Jerry
Thanks for your suggestion. I turned mysql persistent connection off, but I still suffer the same problem. Too many connection error.
The only thing I could do now is using mod_limitipconn, I put a connection limit on the downloading directory. This way, mysql will not be authenticate when their connection limit exceed.
The problem is solve, but I'd like to ask you if there is anything I can do to discourage my members from downloading using multiple connections with download manager. I want to find a way to put a delay whenever downloader exceeded their connection limit and closed all their connections. However, I still want them to resume download after disconnected.
If you have any suggestion, please let me know.
Thank you,
tscbh
Unfortunately, I don't know of any way you can limit the number of connections from any one user. There might be another Apache mod out there, but I'm not aware of it. Of course, there are a lot of modules out there I don't know about.
As I said - we've been looking at a possible solution of using connection pooling in shared memory. But this adds a lotof complexity to the module and we haven't gotten the bugs worked out yet.
Jerry
One other thing,
The parameter I suggested you turn off is MySQLKeepAlive, not mysql persistent connections. mod_auth_mysql doesn't use persistent connections, but it does try to keep connections active across multiple requests. Unfortunately, this code does not work properly and has to be completely rewritten. We're working on it, but it's not easy.
Jerry
Thanks Jerry,
I am looking forward to use the new version of mod_auth_mysql. At the moment, it's quite annoying that I have to delete http-errors.log every few days because people using download managers.
I only allow them to have 4 connections but they keep trying more than the number of connections allowed. The http-errors.log growth to 100MB in just a week.
Hopefully you have the module done soon.
tscbh
I understand how annoying it can be. But the next version of mod_auth_mysql probably won't fix your problem. We don't try to limit the number of connections; the MySQLKeepAlive parameter's purpose was to allow a single connection to be reused, improving performance.
We are working on allowing some shared connections, but I don' t think we will limit the maximum number of connections. That gets even more difficult.