有分片的MySQL数据库解决方案时，如何使用go-sql-driver？

Question

Reading this article: http://go-database-sql.org/accessing.html

It says that the sql.DB object is designed to be long-lived and that we should not Open() and Close() databases frequently. But what should I do if I have 10 different MySQL servers and I have sharded them in a way that I have 511 databases in each server for example the way Pinterest shards their data with MySQL?

https://medium.com/@Pinterest_Engineering/sharding-pinterest-how-we-scaled-our-mysql-fleet-3f341e96ca6f

Then would I not need to constantly access new nodes with new databases all the time? As I understand then I have to Open and Close the database connection all the time depending on which node and database I have to access.

It also says that:

If you don’t treat the sql.DB as a long-lived object, you could experience problems such as poor reuse and sharing of connections, running out of available network resources, or sporadic failures due to a lot of TCP connections remaining in TIME_WAIT status. Such problems are signs that you’re not using database/sql as it was designed.

Will this be a problem? How should I solve this issue then?

Answer 1

I am also interested in the question. I guess there could be such solution:

1. Minimize number of idle connection in pool db.SerMaxIdleConns(N)

2. Make map[serverID]*sql.DB. When you have no such connection - add it to map.

3. Make Dara more local - so backends usually go to “their” databases. However Pinterest seems not to use it.

4. Increase number of sockets and files on backend machines so they can keep more open connections.

5. Provide some reasonable idle timeout so very old unused connections could be closed.