MySQLのToo many connectionsの対処法の補足 ~バルクインサートを添えて~

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

# 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する際にリアルタイム性が必要ないのであれば、「バルクインサート」など様々な方法があると思います。
とりあえず、今回はバルクインサートの[サンプル](https://github.com/hmarf/DBConnection_sample)を残したいと思います。

```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](https://qiita.com/misakiagata)さんの記事が楽しみです！