Choice - Best Stock Broker in India
  • About
  • App
  • Services
    Services
  • Research
  • Partners
  • Contact
  • Log In
  • Open Demat Account
  • Home
  • Blog
  • ...
  • DB Pool Implementation in Golang and Its Impact
  • ...
    DB Pool Implementation in Golang and Its Impact

DB Pool Implementation in Golang and Its Impact

DB Pool Implementation in Golang and Its Impact
  • Published Date: April 21, 2022
  • Updated Date: September 08, 2023
  • By Team Choice

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.

Recommended for you

loading

Understanding XIRR in Mutual Funds: A Detailed Guide

loading

Yamini Investments Company Ltd Right Issue 2025

loading

Trans India House Impex Ltd Right Issue 2025

loading

Crude Oil Price Forecast for Next Week

Choice Financial Services
  • Services

  • Broking & Distribution
  • Wealth Planning
  • Insurance
  • Loans
  • Capital Advisory
  • Management Consultancy
  • Government Advisory
  • Tax Advisory
  • Company

  • Our Team
  • Investors
  • Calculator
  • Careers
  • Contact Us
  • Refer & Earn
  • FAQ’s
  • Resources

  • Fundamental
  • Technical
  • Blog
  • Pricing
  • Downloads
  • News & Media
  • Offer Document
  • Track Record
  • Investor Charter
  • Investor Grievances
  • Online KYC Updation
  • Quick Links

  • Open Demat Account
  • Corporate Demat Account
  • NRI Demat Account
  • Minor Demat Account
  • Lowest Brokerage
  • Investor Charter
  • Investor Awareness
  • Watchout Investors
  • Investor's Advisory
  • Disclaimer
  • CEBPL Policies & Disclosures
  • CFPL Policies & Disclosures
  • Sachet Portal
  • Direct Pay-in

Choice International Limited , Sunil Patodia Tower,
J B Nagar, Andheri(East),
Mumbai 400099.

Monday - Friday : 08:30 am - 7:00 pm
Saturday : 10:00 am - 4:00 pm

+91-88-2424-2424

care@choiceindia.com

Google Play
App StoreApp Store
  • Made with in India
  • Privacy Policy
  • Terms & Conditions

Choiceinternational. CIN - L67190MH1993PLC071117
Choice Equity Broking Private Limited: SEBI Reg No. Broking - INZ000160131 ( BSE - 3299 ) | ( NSE - 13773 ) | ( MSEI - 73200 ) | ( MCX - 40585 ) | ( NCDEX - 01006 ).
Depository Participant SEBI Reg. No. - IN - DP - 84 - 2015 , DP ID CDSL - 12066900 , NSDL ID - IN301895. Research Analyst - INH000000222
Choice Wealth Private Limited: AMFI - Registered Mutual Fund Distributor. Association of Mutual Funds in India Registration Number - ARN - 78908.
Initial Registration: 15th March 2010 Valid Till: 14th March 2027.
Pension Fund Regulatory and Development Authority (PFRDA) - POPSE52022022 | Affiliated with POP HDFC Pension Management Company.
Choice Finserv Private Limited: NBFC Registration Number : N - 13.02216

Choice Insurance Broking Private Limited: IRDAI License No: 167, License Valid Till: 29-05-2025 | Category : Direct ( Life & General )
Registered Office: Choice International Limited, Sunil Patodia Tower, J B Nagar, Andheri East, Mumbai, Maharashtra 400099.
For any Grievances / Queries email at ig@choiceindia.com & care@choiceindia.com | Online Dispute Resolution Link: https://smartodr.in/login

Cautionary Message :

  1. Sharing of trading credentials – login id & passwords including OTP’s:- Keep Your Password/Pin and OTP’s private & confidential to avoid any misuse or unauthorised trades. Please ensure that you do not share it with any one.
  2. Trading in leveraged products like options without proper understanding, which could lead to losses
  3. Writing / selling options or trading in option strategies based on tips, without basic knowledge & understanding of the product and its risks
  4. Dealing in unsolicited tips through Whatsapp, Telegram, YouTube, Facebook, SMS, calls, etc.
  5. Trading in “Options” based on recommendations from unauthorised / unregistered investment advisors and influencers

Disclaimer:
1. *Investments in securities market are subject to market risks, read all the related documents carefully before investing.
2. In addition to client based business, we are also doing proprietary trading.
3. Brokerage will not exceed the SEBI prescribed limit.

Research Disclaimer and Disclosure inter-alia as required under Securities and Exchange Board of India (Research Analysts) Regulations, 2014

Choice Equity Broking Private Limited (“CEBPL”) is a registered Research Analyst Entity (Reg. No. INH000000222 ) (hereinafter be referred as “CEBPL”). (CIN. NO.: U65999MH2010PTC198714).

Reg. Address: Sunil Patodia Tower, J B Nagar, Andheri(East), Mumbai 400099. Tel. No. 022-6707 9999 .

Compliance Officer: Mr.Prashant Salian. Tel. 022-67079999 - Ext-2310
Email- Prashant.salian@choiceindia.com

Grievance officer: Deepika Singhvi Tel.022-67079999- Ext-834.
Email- ig@choiceindia.com

Research Disclaimer: Investment in the securities market is subject to market risks. Read all the related documents carefully before investing. Registration granted by SEBI, and certification from NISM in no way guarantee performance of the intermediary or provide any assurance of returns to investors.

© Choice International Limited. All Rights Reserved.