CODEWITHSUNDEEP
sqlperformancegoconnection-pooling
Mert Serin
Mert Serin

Reputation: 308

Performance difference between global database connection and opening connection everytime on Golang

In my current project I was opening a new database connection every time when user makes request. For example:

func login(w http.ResponseWriter, r *http.Request) {

...

db, err := sqlx.Connect("postgres", "user=postgres password=*** dbname=postgres")

if err != nil {
    ErrorWithJSON(w, err.Error(), http.StatusBadRequest)
    return
}

db.SetMaxIdleConns(0)
db.SetConnMaxLifetime(time.Second * 30)

user, err := loginManager(db, m)

...

err = db.Close()

}

When I searched for other people's code, I've seen that most of the developers create a global variable for database connection, set it on the main and use this variable on entire project.

I was wondering is there any difference between these approaches? If I use global variable will there be any latency when 5 different users makes requests for register/login etc. If there will be latency, should I create multiple database connections and store them in a slice for future requests so I can pick randomly when users make request. Like a simple load balancer, I don't know?

Sorry for multiple questions. Thank you!

Upvotes: 4

Views: 3256

Answers (1)

icza
icza

Reputation: 418297

Yes, there might be a huge performance difference (might be several order of magnitude depending on the nature of queries you run and on system and server configuration).

The sqlx.DB type wraps (embeds) an sql.DB type, which manages a pool of connections:

DB is a database handle representing a pool of zero or more underlying connections. It's safe for concurrent use by multiple goroutines.

The sql package creates and frees connections automatically; it also maintains a free pool of idle connections. If the database has a concept of per-connection state, such state can only be reliably observed within a transaction.

Every time you open a new connection, a lot of things have to happen in the "background": connection string has to be parsed, a TCP connection has to be estabilished, authentication / authorization must be performed, resources must be allocated at both sides (client and server) etc. These are just the main, obvious things. Even though some of these may be provided / implemented optimized, cached, there is still a significant overhead compared to having a single DB instance which might have multiple established, authenticated connections ready in a pool, waiting to be used / utilized.

Also quoting from sql.Open():

The returned DB is safe for concurrent use by multiple goroutines and maintains its own pool of idle connections. Thus, the Open function should be called just once. It is rarely necessary to close a DB.

sqlx.Connect() which you used calls sqlx.Open() which is "the same as sql.Open, but returns an *sqlx.DB instead".

So all in all, use a single, global sqlx.DB or sql.DB instance, and share / use that everywhere. It provides you automatic connection- and connection pool management. This will provide you the best performance. You may fine-tune the connection pool with the DB.SetConnMaxLifetime(), DB.SetMaxIdleConns() and DB.SetMaxOpenConns() methods.

Idle connections (DB.SetMaxIdleConns()) are those that are not in-use currently, but sitting in the pool, waiting for someone to pick them up. You should definitely have some of these, e.g. 5 or 10 of them, or even more. DB.SetConnMaxLifetime() controls how long a new connection may be used. Once it grows older than this, it will be closed (and a new one will be opened if needed). You shouldn't change this, default behavior is never to expire connections. Basically all defaults are sensible, you should only play with them if you experience performance problems. Also, read docs of these methods to have a clear picture.

See this similar, possible duplicate question:

mgo - query performance seems consistently slow (500-650ms)

Upvotes: 5

Related Questions