I have an application running on Node.js and Express using a MySQL database. The driver I'm using is node-orm2. Our users keep reporting a "Too Many Connections" error, and I've diagnosed the problem, but I don't know how to fix it.
Basically what is happening is, every time a new user visits the site, the driver creates a new thread/process for them. I can do a SHOW PROCESSLIST query on the db and it will show up there with a Time of 0.
If the user makes another action that requires the database, that same thread is re-used, and the Time goes back to 0. However, if they do not, the thread stays alive (with a Command of Sleep), and the Time racks up. The db has a max connection limit of 250, and a timeout of 7200.
So basically, if more than 250 people use the site within a period of 2 hours, we see the "Too Many Connections" error.
I tried using a connection pool, but that made the problem worse. Each time a new user used the database, it created 10 threads, and when the 7200 second timeout was reached, the thread respawned.
What options do I have here?
My current method is running a scheduled job every half hour to close threads that have been open for too long, but is there a better way?
I'm using the following settings:
db.settings.set("properties.association_key", "{name}Id");
db.settings.set("instance.autoFetch", true);
db.settings.set("instance.returnAllErrors", true);
db.settings.set("instance.cache", false);
Connection pools might be worth revisiting. One selling point of connection pools is that you can configure them to reclaim idle or stuck connections after a certain amount of time. So even if your application leaks connections the database is protected.
I know that doesn't address your root problem of the threads, but it's probably prudent to reclaim connections in any case.