[go web development] configure database connection pool

Time:2022-5-27

In this section, we will explain how connection pooling works behind it and explore how configuring a database can change or optimize its performance.

Note: the database series contains quite a few theories, which are interesting but not important to the construction of applications. If you find it too difficult, you can browse it first and then look back.

So sql How does a DB connection pool work?

The most important thing to understand is that sql A DB pool contains two types of connections – “in use” connections and “idle” connections. When you use a connection to perform database operations (such as executing SQL statements or query rows), the connection is marked as in use. After the task is completed, the connection is marked as idle.

When you use go to perform database operations, it first checks whether there are free connections available in the pool. If a connection is available, go will reuse the existing connection and mark it as in use during the task. If there are no free connections in the pool when you need free connections, go will create a new connection.

When go reuses an idle connection in the pool, any problems associated with that connection are handled gracefully. The abnormal connection will be automatically retried twice before it is abandoned. At this time, go will delete the abnormal connection from the pool and create a new connection to perform the task.

Configure connection pool

Connection pooling has four methods that we can use to configure the behavior of connection pooling. Let’s discuss one by one.

Setmaxopenconns method

The setmaxopenconns() method allows you to set the maximum number of “open” connections (active + idle connections) in the pool. By default, the number of open connections is unlimited.

Note that “open” connections are equal to “in use” plus “idle” connections, not just “in use” connections.

Generally speaking, the larger maxopenconns is set, the more database queries can be executed concurrently, and the lower the risk that the connection pool itself will become a bottleneck in the application.

But making it infinite is not the best choice. By default, PostgreSQL sets a maximum of 100 open connections. If this limit is reached, it will cause the PQ driver to return a “sorry, too many clients already” error.

Note: the maximum number of open connections can be limited in postgresql Conf file for Max_ Connections setting.

To avoid this error, it makes sense to limit the number of open connections in the pool to less than 100, leaving enough space for other applications or sessions that need to use PostgreSQL.

Another advantage of setting the maxopenconns limit is that it acts as a very basic flow limiter to prevent the database from being overwhelmed by a large number of tasks at the same time.

But there is an important warning about setting the upper limit. If the maxopenconns limit is reached and all connections are in use, any new database task will be forced to wait until there are free connections. In our API context, a user’s HTTP request may “hang” indefinitely while waiting for an idle connection. Therefore, in order to alleviate this situation, it is important to use the context to set the timeout for the database task. We will explain how to handle it later.

Setmaxidleconns method

The setmaxidleconns() method is used to set the maximum number of free connections in the pool. By default, the maximum number of idle connections is 2.

Theoretically, allowing more free connections in the pool will increase performance. Because it reduces the probability of establishing new connections from scratch, it helps to save resources.

But be aware that there is a cost to maintaining an idle connection. It consumes memory that could otherwise be used for applications and databases, and it may become unavailable if a connection is idle for too long. For example, MySQL automatically closes any connection that has not been used for 8 hours by default.

Therefore, setting maxidleconns too high may cause more connections to become unavailable and waste resources than using a smaller free connection pool. Therefore, it is necessary to keep an appropriate amount of idle connections. Ideally, you just want to keep one connection free and available for quick use.

Another thing to point out is that the maxidleconns value should always be less than or equal to maxopenconns. Go enforces this and automatically reduces the maxidleconns value if necessary.

Setconnmaxlifetime method

The setconnmaxlifetime() method is used to set the limit value of connmaxlifetime, which indicates the maximum time a connection remains available. The lifetime of the default connection is unlimited and is permanently available.

If the value of connmaxlifetime is set to 1 hour, it means that all connections will be marked as invalid after one hour after they are created and cannot be reused. However, it should be noted that:

  • This does not guarantee that a connection will exist in the pool for an entire hour; It is possible that a connection becomes unavailable for some reason and is automatically closed before that.
  • The connection can still be used more than an hour after it is created – only after that time it cannot be reused.
  • This is not an idle timeout. Connections expire one hour after they are created, not one hour after they are idle.
  • Go runs a background cleanup operation every second to delete expired connections from the pool.

Theoretically, setting connmaxlifetime to infinity (or set it to a very long life cycle) will improve performance because it will reduce the number of new connections. However, in some cases, it is useful to set the short-term survival time. For example:

  • If the SQL database forcibly sets the maximum lifetime for the connection, it is more reasonable to set connmaxlifetime to a shorter time.
  • Helps with database replacement

If you decide to set connmaxlifetime on the connection pool, be sure to remember how often connections expire (and then recreate). For example, if there are 100 open connections in the connection pool and the connmaxlifetime is 1 minute, your application can kill and recreate up to 1.67 connections per second on average. You don’t want too much frequency to affect performance.

Setconnmaxidletime method

The setconnmaxidletime() method was introduced in go version 1.15 to configure connmaxidletime. The effect is similar to that of connmaxlifetime, but the maximum idle time of a connection before it is marked as invalid is set here. For example, if we set connmaxidletime to 1 hour, any connections that have been idle in the pool for 1 hour since the last use will be marked as expired and deleted by the background cleanup operation.

This configuration is very useful because it means that we can set a relatively high limit on the number of idle connections in the pool, but we can periodically free resources by deleting idle connections that are no longer really used.

A wave of practice

So there is a lot of information to absorb. What does this mean in practice? We summarize all the above contents into some feasible points.

1. As a rule of thumb, you should explicitly set the maxopenconns value. This value should be lower than the hard limit of the database and operating system on the number of connections. You can also consider keeping it at a fairly low level to serve as a basic flow restriction.

For the project we developed in this series of articles, we limited maxopenconns to 25 connections. I find this a reasonable initial value for small to medium-sized web applications and APIs, but ideally, you should adjust this value based on benchmark and stress test results.

2. In general, larger maxopenconns and maxidleconns values lead to better performance. However, the effect is gradually decreasing, and you should note that too many idle connections (connections are not reused) will actually lead to performance degradation and unnecessary resource consumption.

Because maxidleconns should always be less than or equal to maxopenconns, we also limit maxidleconns to 25 connections for this project.

3. In order to reduce the risk of point 2 above, the connmaxidletime value should usually be set to delete idle connections that have not been used for a long time. In this project, we will set the connmaxidletime duration to 15 minutes.

4. Connmaxlifetime can be set to infinite by default, unless your database imposes a hard limit on the connection life cycle, or you need it to assist in some operations, such as gracefully exchanging databases. These are not applicable to this project, so we will keep the default unrestricted configuration.

Configure connection pool

Instead of hard coding these configurations, update cmd/api/main The go file reads the configuration through command line parameters.

The connmaxidletime value is interesting because we want it to be passed for a period of time, and finally we need to convert it to go time Duration type. There are several options:

1. We can use an integer to represent the number of seconds (or minutes) and convert it to time Duration.

2. We can use a string representing the duration — such as “5S” (5 seconds) or “10m” (10 minutes) — and then use time The parseduration() function parses it.

3. Both methods work well, but in this project we will use option 2. Continue and update cmd/api/main Go files are as follows:

File: cmd/api/main.go


package main

import (
    "context" 
    "database/sql"
    "flag"
    "fmt"
    "log"
    "net/http"
    "os"
    "time"
    _ "github.com/lib/pq"
)

const version = "1.0.0"

//Add maxopenconns, maxidleconns and MaxIdleTime fields to store the connection pool configuration
type config struct {
    port int
    env  string
    db   struct {
                dsn          string
                maxOpenConns int
                maxIdleConns int
                maxIdleTime  int
    }
}
type application struct {
    config config
    logger *log.Logger
}

func main() {
    var cfg config
    flag.IntVar(&cfg.port, "port", 4000, "API server port")
    flag.StringVar(&cfg.env, "env", "development", "Environment (development|staging|production)")
    flag.StringVar(&cfg.db.dsn, "db-dsn", "postgres://greenlight:[email protected]/greenlight", "PostgreSQL DSN")
   
     //Read the connection pool configuration from the command parameters into the config structure
    flag.IntVar(&cfg.db.maxOpenConns, "db-max-open-conns", 25, "PostgreSQL max open connections")
    flag.IntVar(&cfg.db.maxIdleConns, "db-max-idle-conns", 25, "PostgreSQL max idle connections") 
    flag.StringVar(&cfg.db.maxIdleTime, "db-max-idle-time", "15m", "PostgreSQL max connection idle time")
    flag.Parse()
    logger := log.New(os.Stdout, "", log.Ldate|log.Ltime)
  
    //Call the opendb () help function (see below) to create a connection pool
    db, err := openDB(cfg)
    if err != nil {
        logger.Fatal(err)
    }
  
    //Defer call so that the connection pool is closed before the main() function exits.
    defer db.Close()
  
    //Print database connection success log
    logger.Printf("database connection pool established")
    app := &application{config: cfg,
        logger: logger}
    srv := &http.Server{
        Addr: fmt.Sprintf(":%d", cfg.port), Handler: app.routes(),
        IdleTimeout: time.Minute,
        ReadTimeout: 10 * time.Second, WriteTimeout: 30 * time.Second,
    }
    logger.Printf("starting %s server on %s", cfg.env, srv.Addr)
    err = srv.ListenAndServe()
    logger.Fatal(err)
}

func openDB(cfg config) (*sql.DB, error) {
    db, err := sql.Open("postgres", cfg.db.dsn)
    if err != nil {
        return nil, err
    }
  
        //Set the maximum number of open connections. Note that a value less than or equal to 0 means unlimited connections
        db.SetMaxOpenConns(cfg.db.maxOpenConns)

        //Set the number of idle connections. Less than or equal to 0 means unlimited
        db.SetMaxIdleConns(cfg.db.maxIdleConns)

        //Parse the idle time string to time Duration type
        duration, err := time.ParseDuration(cfg.db.maxIdleTime)
        if err != nil {
            return nil, err
        }

        //Set maximum idle timeout
        db.SetConnMaxIdleTime(duration)
    ctx, cancel := context.WithTimeout(context.Background(), 5*time.Second)
    defer cancel()
 
    err = db.PingContext(ctx)
    if err != nil {
        return nil, err
    }
    return db, nil
}

If you run the application again, everything should work properly. You won’t notice any changes, and we can’t demonstrate the effects of these configurations at this time. But later in the project, we will do some load tests and explain how to use db The stats() method monitors the status of the connection pool in real time. At that point, you will see some of the settings we discussed in this article.

Recommended Today

Use vite to build vue3.0+ts+element plus+sass project

Installing the vite environment yarn create @vitejs/app Initialize vue+ts items with vite yarn create @vitejs/app project-name Project name, enter Select ‘Vue’ and enter Select ‘Vue ts’ enter complete Follow the steps to follow the prompts in the above figure cd project-name yarn yarn dev Run successfully Configure host vite.config.tsConfigure host and alias import { defineConfig […]