23/12/2019 - GO, MYSQL
List below contains some useful information and can be very beneficial while working with database driven Go application so it's up to you to test and apply to your own code. The most of the information below is coming from Go database/sql tutorial and The Ultimate Guide To Building Database - Driven Apps with Go documents.
The most important thing you will learn here and must take care of is the usage of db.Prepare function. It can really hit you very very hard if you don't follow the suggestions, don't understand what actually it does and how it does! On top of the information written below, I highly suggest you to read second link above because there are more in-depth explanations on why.
If you are 100% sure that the data you are about the use as part of your SQL queries (insert, update, delete) is safe (SQL injection free), you should use db.Exec instead of relying on db.Prepare statement. The reason is because, whether you have an argument placeholder in the query or not, prepared statements will waste resources by issuing three queries (Prepare, Execute and Close) just to do one job whereas directly executing statements will issue only one query (Query). The example below is meant to insert a single row into a table. The prepared option will triple the number of network round-trips. However, the direct execution option will cause one network round-trip. Note: If you wish to prepare parameters explicitly, fmt.Sprintf() with the %q placeholder could be used - e.g. fmt.Sprintf(`INSERT INTO table (name, age, active) VALUES (%q, %d, %t)`)
func Insert() (int64, error) {
stmt, err := db.Prepare("INSERT INTO users (name) VALUES (?)")
if err != nil {
return 0, err
}
defer stmt.Close()
res, err := stmt.Exec("inanzzz")
if err != nil {
return 0, err
}
id, err := res.LastInsertId()
if err != nil {
return 0, err
}
return id, nil
}
2019-12-28T21:12:53.517599Z 3 Prepare INSERT INTO user (name) VALUES (?)
2019-12-28T21:12:53.518932Z 3 Execute INSERT INTO user (name) VALUES ('inanzzz')
2019-12-28T21:12:53.527352Z 3 Close stmt
func Insert() (int64, error) {
res, err := db.Exec("INSERT INTO user (name) VALUES ('inanzzz')")
if err != nil {
return 0, err
}
id, err := res.LastInsertId()
if err != nil {
return 0, err
}
return id, nil
}
2019-12-28T21:13:11.337554Z 3 Query INSERT INTO user (name) VALUES ('inanzzz')
100 users concurrently sending requests for 10 seconds without a break. The findings below are just rough calculations.
// db.Prepare
Average total requests: 4500
Average request duration: 210ms
// db.Exec
Average total requests: 8500
Average request duration: 120ms
If you are going to run multiple identical queries for different set of data in a loop, keep the prepare db.Prepare outside of the loop and execute stmt.Exec inside of it. The reason is because preparing and closing the same query repeatedly is just going to waste resources for all executions which is obviously useless. Moreover, calling defer stmt.Close() inside the loop is like waiting for disaster to happen because it will potentially cause resource leak. As a result, preparing inside the loop will double the amount of queries. See examples below.
func Insert() error {
for i := 1; i < 4; i++ {
stmt, err := r.database.Prepare(`INSERT INTO users (name) VALUES (?)`)
if err != nil {
return err
}
defer stmt.Close()
_, err := stmt.Exec(fmt.Sprintf("inanzzz-%d", i))
if err != nil {
return err
}
}
return nil
}
As you can see we have total of 9 queries run.
2019-12-28T21:12:53.517599Z 3 Prepare INSERT INTO user (name) VALUES (?)
2019-12-28T21:12:53.518932Z 3 Execute INSERT INTO user (name) VALUES ('inanzzz-1')
2019-12-28T21:12:54.517599Z 3 Prepare INSERT INTO user (name) VALUES (?)
2019-12-28T21:12:54.518932Z 3 Execute INSERT INTO user (name) VALUES ('inanzzz-2')
2019-12-28T21:12:55.517599Z 3 Prepare INSERT INTO user (name) VALUES (?)
2019-12-28T21:12:55.518932Z 3 Execute INSERT INTO user (name) VALUES ('inanzzz-3')
2019-12-28T21:12:56.527352Z 3 Close stmt
2019-12-28T21:12:56.527352Z 3 Close stmt
2019-12-28T21:12:56.527352Z 3 Close stmt
func Insert() error {
stmt, err := db.Prepare(`INSERT INTO users (name) VALUES (?)`)
if err != nil {
return err
}
defer stmt.Close()
for i := 1; i < 4; i++ {
_, err := stmt.Exec(fmt.Sprintf("inanzzz-%d", i))
if err != nil {
return err
}
}
return nil
}
As you can see we have total of 5 queries run.
2019-12-28T21:12:53.517599Z 3 Prepare INSERT INTO user (name) VALUES (?)
2019-12-28T21:12:53.518932Z 3 Execute INSERT INTO user (name) VALUES ('inanzzz-1')
2019-12-28T21:12:54.518932Z 3 Execute INSERT INTO user (name) VALUES ('inanzzz-2')
2019-12-28T21:12:55.518932Z 3 Execute INSERT INTO user (name) VALUES ('inanzzz-3')
2019-12-28T21:12:55.527352Z 3 Close stmt
Use result.LastInsertId() and result.RowsAffected() functions only if you explicitly need to know what last inserted id was or how many rows were affected. Otherwise do not use them by default because although these functions don’t access the database, they may become "blocking" operations if their connections are busy. Also, not all database drivers support these features either.
You should prefer using db.Exec if the statement doesn’t return rows as opposed to db.Query and db.QueryRow. The main reason is because db.Exec straight away releases its connection back to the pool whereas db.Query keeps its connection out of the pool until rows.Close() is called. If you avoid this suggestion, you might cause connection "leak" and server run out of available connections.
If you are using a select statement with or without query placeholders, you should stick with db.Query and db.QueryRow instead of db.Prepare & stmt.Exec combination. The reason is because, if there is no placeholder in the query, db.Query and db.QueryRow will behave like db.Exec to issue only one query (Query) which is what we want.
It defines the amount of connections which should remain idle in the pool after being released. You can verify by running SHOW PROCESSLIST; query in database. The default value is 2. This default value would cause a lot of connections being closed and opened one after another which is something you want to avoid. It leads to extra work and latency as well as delay while waiting for a new connection. You can safely set it to 5 (ideal) or above such as 25 instead.
100 users concurrently sending requests for 10 seconds without a break. The findings below are just rough calculations.
// db.SetMaxIdleConns = 2 (default)
Average total requests: 3800
Average request duration: 250ms
// db.SetMaxIdleConns = 5
Average total requests: 4700
Average request duration: 200ms
// db.SetMaxIdleConns = 25
Average total requests: 5500
Average request duration: 180ms
It defines the maximum number of open connections in the database. The default value unlimited. You have to be careful if you want to change its default value. A change will potentially lead to context deadline exceeded errors if any given query context implements a timeout feature such as context.WithTimeout. There may not be enough open connections to handle pending queries so as a result many queries would timeout. It will also reduce to performance. It is better to avoid changing it unless you are 100% sure.
It defines the maximum amount of time a connection should live. The default value unlimited. The ideal time also depends on the db.SetMaxIdleConns value so the higher db.SetMaxIdleConns, the lower db.SetConnMaxLifetime. Rough examples: db.SetMaxIdleConns: 5 - db.SetConnMaxLifetime: 30 minutes, db.SetMaxIdleConns: 10 - db.SetConnMaxLifetime: 15 minutes. If you have a very busy database driven application, you can keep the numbers higher. Using many long lived idle connections requires high system resources.
Don't call defer rows.Close() inside a loop because it would potentially cause memory or connection "leak".
Create the sql.DB instance only once at the application boot and let all the requests use it. Otherwise you will be opening and closing many TCP connections to the database and potentially crash the server.
Run defer rows.Close() as soon as you can otherwise it will potentially cause connection "leak".
If you can, do your best to avoid db.Prepare in order to increase performance by running less queries and unnecessary network round-trips for duplicated queries. Try to utilise fmt.Sprintf() with the %q placeholder to explicitly prepare parameters.
Don't use db.Query and db.QueryRow if you are not issuing a Select statement. Use db.Exec instead.
If the field in a table is returning a nullable, your struct or variable type must be set as nullable too. e.g. sql.NullString, mysql.NullTime etc.
The db.Query, db.QueryRow and db.Exec functions are not really good at working with parameters of type uint64 so convert them to strings with fmt.Sprint() to avoid surprises.