Connection management with a large number of databases to back up #455
Description
@deitch said:
I am looking through the code, here is largely how it works on each loop.
for each writer (can have variants, but generally one per database)
open a connection
for each schema to be backed up in the database (which is mainly one database)
run through the SQL commands (high level): select the schema, begin the transaction, dump the data, complete the transaction
return
So it might be opening one per database, It depends on how the underlying driver interprets and pools sql.Open("mysql", dbconn.MySQL()). Of course, we don’t want to depend on that, but better control.
What would be really helpful is a way to reproduce this while tracking it. What is the simplest way to do it? A single database server with 5 databases? How would you run it and report the connections, so we can see what the database sees as connections while running it all through?
We should move this into a github issue, so we can track it. Do you mind opening one?
The easiest way to reproduce this would be to have a database server with say 10 databases to back up with some non-trivial content so it takes a small amount of time. Then reduce max_connections on the database server to say 2 and run the backup. That should quickly exhaust the connections and you'll get mysql errors for the other backup attempts. I was able to see these in the logs of the mysql-backup docker container.
As to how to prevent it, if you're happy to sequentially back up databases one by one then forcing a single connection is probably the way to go. I'm not familiar with Go but I'll take a look and see :)