はじめに

CA Tech Dojo/Challenge/JOB Advent Calendar 2019の19日目はhmarfが書かせていただきます。私のアドベントカレンダーの担当日の前後に優秀swiftエンジニアの @ostk0069さんと@misakiagataさんがいるのでプレッシャーがすごいです。
この記事は@kenjiszkさんのMySQLでToo many connectionsが出た時の対応についての私なりの補足です。

MySQLで too many connectionsがでたら確認すべきこと

@kenjiszkさんの記事にもありますが、too many connectionがでたら、MySQLのmax connections, processlist, wait_timeout を確認します。

# max_connections の数を確認
mysql> show variables like "%max_connections%";
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 150   |
+-----------------+-------+
1 row in set (0.00 sec)

# 接続されているプロセスを確認
mysql> show processlist;
+----+------+------------------+------+---------+------+----------+------------------+
| Id | User | Host             | db   | Command | Time | State    | Info             |
+----+------+------------------+------+---------+------+----------+------------------+
| 37 | root | localhost        | NULL | Sleep   | 6090 |          | NULL             |
| 38 | user | xxx.xx.x.x       | NULL | Sleep   |  347 |          | NULL             |
| 39 | user | xxx.xx.x.x       | NULL | Sleep   |  347 |          | NULL                      |
| 55 | root | localhost        | NULL | Query   |    0 | starting | show processlist |
+----+------+------------------+------+---------+------+----------+------------------+
4 rows in set (0.01 sec)

# time が長いものを削除
mysql> kill 37;
Query OK, 0 rows affected (0.00 sec)

# wait timeout を確認
mysql> show global variables like 'wait_timeout';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 28800 |
+---------------+-------+
1 row in set (0.01 sec)

考えられる対処方法

max connectionを上げる & wait timeout を制限する
クライアント側で張るconnectionsの数を制限する

max connectionを上げる & wait timeout を制限する

とりあえず、max connection, wait timeout を変更しています。しかし、安易な気持ちでConnectionの数を上げると、メモリ不足を引き起こします。

以下の式でメモリ計算ができます。

メモリ使用量 = グローバルバッファ + (スレッドバッファ * コネクション数)

起動中のサーバー内で変更する場合

mysql> set global max_connections = 1000;
mysql> set global wait_timeout = 1800;

設定ファイルで変更する場合

[mysqld]
max_connections = 1000
wait_timeout = 1800

[余談] 僕の記憶が正しければ、amazon RDS では max connection を上げることができなかったような気がします。AWSでは使用しているインスタンスのメモリから最適な max connection を自動で計算していたはず。一時的に上げることはできるが、どんどん減っていくはずです。（ max connectionを変更できたら大嘘）

clientで張るconnectionの数を制限する

Go言語の場合（一行でconnectionの数を制限できます）

DB.SetMaxOpenConns(100)

たったこれだけです。しかし、とても大切なので忘れないようにしましょう。

またDBにInsertする際にリアルタイム性が必要ないのであれば、「バルクインサート」など様々な方法があると思います。
とりあえず、今回はバルクインサートのサンプルを残したいと思います。

main.go

package main

import (
	"database/sql"
	"fmt"
	"log"
	"net/http"
	"strings"
	"time"

	"github.com/carlescere/scheduler"
	_ "github.com/go-sql-driver/mysql"
)

type insertData struct {
	name      string
	createdAT time.Time
}

func initDB() *sql.DB {
	db, err := sql.Open("mysql", "user:password@tcp(0.0.0.0)/sampleDB?parseTime=true")
	if err != nil {
		panic(err.Error())
	}
	return db
}

func insertDB(ch *chan insertData, db *sql.DB) {

	rescInterface := []interface{}{}
	stmt := "INSERT INTO user(name, createdAt) VALUES"
	insertFlag := false
LOOP:
	for {
		select {
		case data, ok := <-*ch:
			if ok {
				insertFlag = true
				stmt += "(?,?),"
				rescInterface = append(rescInterface, data.name)
				rescInterface = append(rescInterface, data.createdAT)
			}
		default:
			break LOOP
		}
	}

	if insertFlag {
		stmt = strings.TrimRight(stmt, ",")

		_, err := db.Exec(stmt, rescInterface...)
		if err != nil {
			fmt.Println(err)
		}
	}
	return
}

func main() {

	DB := initDB()
	defer DB.Close()

	// connection数 の制限
	DB.SetMaxOpenConns(9)

	channel := make(chan insertData, 100000)

	_, _ = scheduler.Every(5).Seconds().NotImmediately().Run(func() { insertDB(&channel, DB) })

	rootHandler := func(w http.ResponseWriter, r *http.Request) {
		channel <- insertData{
			name:      "test user",
			createdAT: time.Now(),
		}
		w.WriteHeader(200)
	}

	http.HandleFunc("/", rootHandler)
	http.HandleFunc("/ok", func(w http.ResponseWriter, r *http.Request) { w.WriteHeader(200) })

	// start server
	if err := http.ListenAndServe(":8081", nil); err != nil {
		log.Fatal(err)
	}

}

まとめ

コネクションは意識しながら開発していきましょう！
明日の@misakiagataさんの記事が楽しみです！