DB Pool Implementation in Golang and Its Impact

Why we have implemented DB Connection Pool

In Go Language We have developed an API for client global search in Onboarding based on Client Id, Pan Number and Mobile Number which creates the connection to the database on each request and closes the connection after performing required query operations. Which results in overall 3 requests to the database server. The API is used in JIFFY APP and Neuron Web App which has a comparatively high user base i.e more than 4 Lakhs, which results in high frequency of requests to the API ultimately resulting in increase in load on the database server. To overcome the above mentioned challenge we have done some research to optimize the API performance and effective database utilization where we underwent the connection pool mechanism which can be implemented.

What we have done for implementation

We have done some changes and initiated the database connection pool at the time of booting the Go application, which means we are establishing the specific set of connections to the database server with following configuration :-

  func OnboardingConnectionPool() {
	log.Info("OnboardingConnectionPool Initiated: ")
	dbDriver := os.Getenv("DB_DRIVER")
	dbName := os.Getenv("DB_NAME")
	dbUser := os.Getenv("DB_USERNAME")
	dbPassword := os.Getenv("DB_PASSWORD")
	dbTcp := "@tcp(" + os.Getenv("DB_HOST") + ":" + os.Getenv("DB_PORT") + ")/"
	db, err := gorm.Open(dbDriver, dbUser+":"+dbPassword+dbTcp+dbName+"?charset=utf8&parseTime=True")

	DB_MAX_IDLE_CONN, _ := strconv.Atoi(os.Getenv("DB_MAX_IDLE_CONN"))
	DB_MAX_OPEN_CONN, _ := strconv.Atoi(os.Getenv("DB_MAX_OPEN_CONN"))
	DB_MAX_IDLE_TIME, _ := strconv.Atoi(os.Getenv("DB_MAX_IDLE_TIME"))
	DB_MAX_LIFE_TIME, _ := strconv.Atoi(os.Getenv("DB_MAX_LIFE_TIME"))
	// Max Ideal Connection
	db.DB().SetMaxIdleConns(DB_MAX_IDLE_CONN)
	// Max Open Connection
db.DB().SetMaxOpenConns(DB_MAX_OPEN_CONN)
	// Idle Connection Timeout
	db.DB().SetConnMaxIdleTime(time.Duration(DB_MAX_IDLE_TIME) * time.Second)
	// Connection Lifetime
	db.DB().SetConnMaxLifetime(time.Duration(DB_MAX_LIFE_TIME) * time.Second)
	log.Info("@OnboardingConnectionPool MYSQL MAX Open Connections: ",

	// This is for analyzing the stats after setting a connection

db.DB().Stats().MaxOpenConnections)
	log.Info("@OnboardingConnectionPool MYSQL Open Connections: ",
db.DB().Stats().OpenConnections)
	log.Info("@OnboardingConnectionPool MYSQL InUse Connections: ",
db.DB().Stats().InUse)
log.Info("@OnboardingConnectionPool MYSQL Idle Connections: ", db.DB().Stats().Idle)
	onboardingDB = db
	if err != nil {
		log.Error("OnboardingConnectionPool Error: ", err)
	}
}

And Initiating DB pool when application boots, as follows

func main() {

	err := godotenv.Load(".env")
	if err != nil {
		log.Error("Error loading .env file")
	}
	router := Routes.SetupRouter()
	**Controllers.OnboardingConnectionPool()**
	router.Static("/assets", "./assets")
	router.Run(":" + os.Getenv("APP_PORT"))
}

What Was The Impact?

Before implementing Connection Pool

K6 Load testing results :-

Virtual Users :- 10
Time Interval :- 10s
Success % :- 93%

Failure rate increases as the open / idle connections to the database server increases and results in not serving the future requests received on the server.

After Implementing Connection pool

K6 Load testing results :- Virtual Users :- 10
Time Interval :- 10s
Success % :- 100%

This helps in fulfilling the requests until the database server is shut down by any external actions. And the application will rebuild the connection pool with a fixed number of connections and start serving again.

Some Best Practices To Keep In Mind When We are going to Implement Connection Pool

Setting the maximum number of open connections to the database

• By default the maximum number of open connections is unlimited if we have not set the limit. And if the connection to the server exceeds the value max_open_connections configured in respective database configuration then new queries will be blocked until a connection becomes available.
• Ideally the value must be the fraction of the number of connections that the database can handle.

Setting the maximum number of Idle connections to the database

• By default, the max_idle_connections value is 2.
• If the number of queries per second is high, then you’re likely to be in a situation where connections are being created and disposed of immediately due to this low default value.
• Ideal value to be set here is fraction of the value of max_open_connection whether it’s 25%, 50% or 75% or even 100% as per the load pattern of the application

Setting the connection maximum Life Time

• Sets the maximum amount of time a connection may be reused.
• By default, there’s no limit on the connection age, but you’ll want to set this if you’re also setting the max idle connections.
• You should set the lower connection max lifetime if the max idle connections percentage is higher.
• If you have different types of load within a single service, instead of having a single connection pool, consider having separate ones. That makes the idle connections a bit less efficient but it will prevent the 100% utilization from blocking other queries.
• If you have High & Complex database use, consider batching requests. A single more expensive operation is better than one hundred tiny ones.