はじめに
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さんの記事が楽しみです！