はじめに

注意事項

本記事において、起こったトラブルに関して、筆者Ikuyは一切の責任をおいませんので、ご了承ください
また、下記を読み進められた場合は、上記注意事項を了解したものとします
各自テストデータ等を使用して、DBの理解を深めるための参考程度にされるといいかと思います

エラーのお話

＊各項目に番号を振っています
そのエラー解決の詳細はお話の後にまとめます
私＝デプロイ担当
DB=mysql

チーム開発時のエラー
アプリは完成間近でしたが、各自のDBの状況がバラバラで、カラムのエラーが出ていました
チームメンバーの一人がmasterからPullしたデータには
RemoveCategoryFromItems
のファイルがありました（ItemsTableのcategoryカラムは不要なカラム）
しかし彼のDBにはそもそもItemsTableにcategoryカラムはありません

①そこで、RemoveCategoryFromItemsのmigrationファイルよりも前にAddCategoryToItemsを作成し、作る=>消すという流れにすることでことなきを得ました

しかし、私は彼に「そのブランチは死にブランチ（今回のDBのカラム調整のためだけのブランチ）にして、新たにブランチを切って」と言いませんでした

…ここで言わなかったことがのちに大きな問題となったのです

私は彼のPullRequestをmargeし、デプロイしました
さて、localで残りの作業を、と思ったその時あることに気がつきました

私のLocalDBのItemsTableにcategoryカラムが復活していたのです！！
まさかと思い、デプロイ環境でのDBを確認すると…
categoryカラムが鎮座しておりました！！

絶望に満ちながらいろんな仮説を立てて

②SeqelProを駆使して両方のcategoryカラムを葬り去ることができました

めでたしめでたし

解説

しょうもない話を読んでくれてありがとうございました
順番に解説します

①migrationファイルの日時を変更する

前提知識：migrationファイルの基本は日付の若い順に処理がなされる

migrationファイルには長ったらしい名前がついています
例：20191219104345_remove_price_from_posts.rb　←Aとします
手前の数字が作成日時で、add以降が何をしているファイルかということが書いています。
今、上記のファイルがすでにあったとして以下を実行すると、

$ rails generate migration AddPriceToPosts
Running via Spring preloader in process 11435
      invoke  active_record
      create    db/migrate/20191219104555_add_price_to_posts.rb #←Bとします

現在日時のmigrationファイルが作成されます
①の問題を解決するには、down状態のそのファイルを削除するか、migrationファイルを、問題のmigrationファイルより前に差し込む必要があります。
今回はなぜか、その問題のmigrationファイルを通したかったので、Bのファイル名の日付部分を20191218~~に変更しました
そうすることで、Aのファイルよりも前にBのファイルが読み込まれるので、カラムを作成した後に削除することができました

②デプロイ環境のmigrationを修正する

こいつが厄介でした
なんせデプロイ環境ですからね、それに完成間近でしたし
今回のエラーは特殊（？）でremoveのmigrationファイルがUP状態（通っている）なのに、カラムが消えていなかったのです
さらに、なぜか、カラムの型も書き忘れていたため、どうしようもない状態で、rollbackも効かないので修正のしようがありませんでした
localだけであれば、

$ rails db:migrate:down VERSION="migrationファイルの日付"

で強制的に落としてしまえばいいのですが、今回はデプロイ環境も修正しないといけません
そこで目をつけたのが、SequelProの中のschema_migrationsです

ここの日付を消してやればdownになるかもという仮説を元に、localでやると

Mac_Ikuy:F-market-f mac_ikuy$ rails db:migrate:status

database: F-market-f_development

 Status   Migration ID    Migration Name
--------------------------------------------------
   up     000             ********** NO FILE **********
   up     20191204065204  Devise create users
   up     20191204105446  Create items
   up     20191209055720  Create categories
   up     20191209060814  Create images
   up     20191209061107  Create comments
   up     20191209061413  Create orders
   up     20191209062019  Create addresses
   up     20191210060525  Add birthday to users
   up     20191210061030  Remove birth from users
   up     20191210105227  Change datatype phone number of users
   up     20191210111956  Change datatype many of addresses
   up     20191211092256  Rename prefecture column to address
   up     20191214035200  Remove address from users
   up     20191214045010  Create cards
   up     20191216055953  Create sns credentials
   up     20191218040119  Add column to items
  down    20191218053313  Remove category from items

見事にダウンしてくれました
これで正しい記述に変更し、migrateすると…

database: F-market-f_development

 Status   Migration ID    Migration Name
--------------------------------------------------
   up     000             ********** NO FILE **********
   up     20191204065204  Devise create users
   up     20191204105446  Create items
   up     20191209055720  Create categories
   up     20191209060814  Create images
   up     20191209061107  Create comments
   up     20191209061413  Create orders
   up     20191209062019  Create addresses
   up     20191210060525  Add birthday to users
   up     20191210061030  Remove birth from users
   up     20191210105227  Change datatype phone number of users
   up     20191210111956  Change datatype many of addresses
   up     20191211092256  Rename prefecture column to address
   up     20191214035200  Remove address from users
   up     20191214045010  Create cards
   up     20191216055953  Create sns credentials
   up     20191218040119  Add column to items
   up     20191218053313  Remove category from items

ちゃんとUPになってくれました
もちろんカラムも消えています
同様に、デプロイ環境でも同様にdownさせ、その状態でデプロイをかけると成功しました！！！

追加情報

ちなみに、先ほどのSequelProの中のschema_migrationsですが、コマンドでrollbackかけると自動的に最新のものが消えます
同じところ（さっきまで存在していた場所）に、消えた日付を直接書き込んで元に戻したら、UP状態になります

ぜひ、自分のテスト用のディレクトリでDBをいじって遊んでみてください

最後に

もう一度書きますが、何かあっても責任は負いません
ですので、会社や、大切なDBに対しては一次ソースの情報を元に作業を行なってください

今更ながら、データベースの整理をしていて - の付いたデータベースを削除する際にあれ？となったので。。。

ターミナル.

#mysql にログインします。rootの部分はユーザー名
$ mysql -u root




#データベースの確認
mysql> show databases;

+-----------------------------------+
| Database                          |
+-----------------------------------+
| information_schema                |
| mysql_development                 |
| mysql_test                        |
| mysql-app_development             |
| mysql-app_test                    |
+-----------------------------------+





#通常の削除
mysql> drop database mysql_development;

実行結果
=> Query OK, 0 rows affected (0.00 sec)





# - の付いたデータベースの場合は
mysql> drop database mysql-app_development;

実行結果
=> ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-app_test' at line 1

エラーになってしまう。。。。






#　- の付いたデータベースを削除する際は ` (バッククオートで囲う)

 `  は　shift+@　で入力

mysql> drop database `mysql-app_development`;

実行結果
=> Query OK, 0 rows affected (0.00 sec)

フレームワークのクエリビルダでQueryを作って、データを取り出して実行！としたときに、期待した結果が得られないのは日常茶飯事です。そんなとき原因調査の一つとして、実際に実行されたSQLの確認することがあります。

今回はそんなときに使える「実行したQueryのオブジェクトからSQLを生成」する方法です。

SQLを取り出す（ただしバインド前）

cakephpのAPIリファレンスを眺めていると、sql()という、まんまなメソッドが見つかります。

さあこれで一件落着、とはいきません。なぜなら、このメソッドで出力されるのは各種値がバインドされる前のSQLとなります。例えば以下のような感じです。

SELECT * FROM tab WHERE id=:c0

WHEREなどに指定した値が「:c0」のようなプレースホルダになっていて、実際に実行したSQLそのものではありません。

バインドを取り出す

ならば、ということでバインドのデータを取り出します。getValueBinder()というメソッドになります。さらに続けて、bindings()というメソッドを実行すればバインドの情報を取得できます。バインドの情報は以下のような配列でした。

$bind = $query->getValueBinder()->bindings();

/* bindの中身
[
  ":c0" => [
    "value" => "11",
    "type" => null,
    "placeholder" => "c0"
  ]
]
*/

キーがプレースホルダ、valueが置き換える値になっているようです。

バインドを適応する

やり方はいろいろあると思いますが、例えば以下です。

function toSql($query)
{
    $sql = $query->sql();
    $bind = $query->getValueBinder()->bindings();

    // プレースホルダを値で文字列置換。値はクォートで囲むこと。
    foreach($bind as $param => $value) {
        $sql = mb_ereg_replace($param, "'{$value["value"]}'", $sql);
    }
    return $sql;
}

こんな関数を用意しておけばQueryのオブジェクトを渡すだけでSQLを取得できます。

おわりに

デバッグコンソールからアクセスできるようなところにこんな関数を定義しておくと、Query実行後あたりでブレークしてその場で呼び出し、実行したSQLを確認できるので便利です。

余談ですが、laravelでも同じ流れでSQLを構築できます。メソッドがそれぞれtoSql()、getBindings()になります。

MySQL Advent Calendar 2019 の19日目です。
アドベントカレンダーをたまたま見にいったら、たまたま今日と明日が空いていたので、
なんとなく飛び入り参加させていただきました。

はじめに

みなさんクエリを書く時JoinしまくったりSubqueries入れまくったりでめちゃくちゃ長いクエリ作ってませんか？?
あとで見直して『なんだこの長いクエリ…』とか、説明だけ読んで『あーなるほどそのデータとってくるためのクエリね！なるほどー！読みたくねぇ…』とかなってませんか？？？?
まぁ仕方ないこともあるんですが…?
そんなあなたに TEMPORARY TABLE の紹介です。

なにそれ

その名の通り、セッションを繋いでいる間だけ存在する一時テーブルのことです。
テーブルをサクッと作れて、
必要な情報のみを突っ込みクエリをわかりやすく分割できる、
MySQLではもちろんPostgreSQLやOracleでも使える、
なんなら INDEX も張れる
とっても優れた機能です?
設定すればRDSでも動かせます。

ただし色々と罠があり、権限ないと使えなかったり、他の人から見えなかったり、1クエリ内で2回呼び出せなかったり、セッション切れてテーブルつくりなおしになったり、性能にものを言わせたり（実は遅くなったり）、リソース食ったりするので、本番で使うときは注意が必要（詳しいことは知らんが?

つくってみる

基本的には CREATE TABLE と同じように作れます。
ので、普通に定義してみたり

CREATE temporary TABLE `tmp_pref` (
  `pref_id` INT(11) NOT NULL,
  `string`  VARCHAR(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC

構造を真似たり

CREATE temporary TABLE `tmp_pref` like pref;

なんなら SELECT したものをそのまま INDEX 付きで突っ込めます⭐️

CREATE temporary TABLE `tmp_pref`
(
    primary key pr (pref_id),
    INDEX string_key (string)
)
SELECT *
FROM station_all
WHERE pref_id = 1 ;

分解してみる

さてさて本題
今年の1月中に本を注文した男性の最寄駅の第1住所を人数付きで知りたい。
というクソみたいなクエリを考えてみます?
（実際にある駅データさんのデータとken_all、その他適当なテーブルを頭の中で構成したのであっているか怪しい。

SELECT ka.address1,
       count(ka.address1)
FROM purchase p
JOIN user u
    ON u.user_id = p.user_id
JOIN station_all sa
    ON sa.station_cd = u.station_cd
JOIN ken_all ka
    ON ka.post_code = sa.post
WHERE p.create_datetime
    BETWEEN '2019-01-01 00:00:00'
        AND '2019-01-31 23:59:59'
    AND p.item_type IN (
       SELECT type_id
       FROM item_type
       WHERE type_name LIKE '%book' 
    ) 
    AND u.sex = 1
GROUP BY  ka.address1
;

1個ずつ分解してみます。
とはいえ分解量は INDEX が効いている範囲とか、ここまでは一つだろとか、人それぞれだと思うので適当にわかりやすそうな単位で…

まずは今年1月に購入履歴のあるユーザ情報から☝️
ここでitem_type に INDEX を張ってやります。（もともとない想定で

CREATE temporary TABLE `purchase_uesr_tmp`
(
    INDEX item_type_key (item_type)
)
SELECT u.user_id,
       u.station_id,
       p.item_type, 
FROM purchase p
JOIN user u
    ON u.user_id = p.user_id
WHERE p.create_datetime
    BETWEEN '2019-01-01 00:00:00'
        AND '2019-01-31 23:59:59'
    AND u.sex = 1
; 

INDEX を張ってやったので本を買った人をみていきましょう✌️

CREATE temporary TABLE `book_purchase_uesr_tmp`
(
    INDEX station_cd_key (station_cd)
)
SELECT station_cd
FROM purchase_uesr_tmp put
WHERE 
    item_type IN (
       SELECT type_id
       FROM item_type
       WHERE type_name LIKE '%book' 
    ) 
;

ではあとやっちゃいたいところですが、一旦 address1 に INDEX を張ってみます?

CREATE temporary TABLE `book_purchase_uesr_address_tmp`
(
    INDEX address1_key (address1)
)
SELECT ka.address1,
FROM book_purchase_uesr_tmp bput
JOIN station_all sa
    ON sa.station_cd = bput.station_cd
JOIN ken_all ka
    ON ka.post_code = sa.post
;

らすと

SELECT address1,
       count(address1)
FROM book_purchase_uesr_address_tmp
GROUP BY  ka.address1
;

まとめ

さてさて分解してみていかがだったでしょうか？？？
最初より長くなりすぎて逆に分からんって？？それは書いていて思ったので、これくらい短いとまとめた方がわかりやすいかもしれませんね…
さらにいうと MySQL8.0 から WITH句 つかえますからね。そんなに使用頻度ないかもしれませんよね。
とはいえ INDEX 貼ることで速くなったりするので、実行計画とにらめっこしながら分解作業するのも速度を上げる1つの手ですよ?

ということで皆さんもたのしい TEMPORARY TABLE 生活を?

おまけ

以前いた職場でDBにioDriveを使っていたため、W/Rがとっても速くて（その時代では）それなら TEMPORARY TABLE 使った方が早いと すごい人Sさん 言われて初めて存在を知りました。
HDDとか書き込み遅いとtmp書き出しに時間が…なんてこともあるはずなので一概に早くあるとは言えません。ご注意ください。

参考になれば資料

https://dev.mysql.com/doc/refman/8.0/en/create-table.html
https://dev.mysql.com/doc/refman/8.0/en/create-temporary-table.html
https://www.post.japanpost.jp/zipcode/download.html
https://www.ekidata.jp/

Docker上でMySQLにログインするには、docker composeのコマンドと一緒にmysqlのログインコマンドを入力する必要があります。

Docker上では以下のコマンドでMySQLにログインできます。

$ docker exec -it [コンテナ名] mysql -u root -p

では、確認していきます。
まず、docker-compose psコマンドで、MySQLのコンテナが起動していることを確認します。

$ docker-compose ps
指定されたパスが見つかりません。
  Name                Command               State                  Ports
---------------------------------------------------------------------------------------
composer   /bin/sh /docker-entrypoint ...   Exit 0
laravel    docker-php-entrypoint /usr ...   Exit 0
mysql      docker-entrypoint.sh mysql ...   Up       0.0.0.0:33306->3306/tcp, 33060/tcp
nginx      nginx -g daemon off;             Up       0.0.0.0:8080->80/tcp
php-fpm    docker-php-entrypoint php-fpm    Up       9000/tcp

コンテナ名（Name）がmysqlになっているコンテナが起動中のmysqlコンテナです。

さっそく、ログインしてみます。

ターミナル

$ docker exec -it mysql mysql -u root -p
Enter password:

ログイン成功後に、生成されているデータベースを一覧表示できていることがわかります。

実行結果

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9

~(省略)~

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>show datases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.01 sec)

以上です。
Docker勉強中で分からないことが多いので、積極的に勉強中です！

頻繁に実施したのでメモ。utf8で作成されていたデータベースをutf8mb4に置きかえる作業手順です。
もっと簡単にできるといいなぁ。

前提

• データベースインスタンスの文字セットはutf8mb4になっていること。mysql utf8mb4で検索すれば多数の記事あり

以下の手順を実行した環境

• RDS for MySQL
• 移行元のMySQLサーババージョン(mysql> select version();) => 5.6.41-log
• 移行先のMySQLサーババージョン(mysql> select version();) => 5.7.26-log
• mysql client / mysqldumpのバージョン => 5.6.46
• Amazon Linux2 上で実行

手順

概要手順

• 既存データベース内容をダンプ
• ダンプしたファイル内のutf8をutf8mb4に置き換え
• データベース再作成(削除・作成)
• utf8mb4化したダンプファイルをインポート

詳細手順

まずはデータベースをダンプします。

$ mysqldump -h <dbhost> -u<dbuser> -p<dbpassword> <dbname> > dump.sql

utf8 を utf8mb4 に置き換えます。下記ではsed使ってますがもちろんどういう方法でもOKです。

$ sed -e 's/utf8 /utf8mb4 /g' ./dump.sql > ./dump_utf8mb4.sql

DB作成権限のあるユーザでmysqlクライアントに接続します。

$ mysql -u<user> -p -h <host>;

【オプション】データベースを削除。

すでに当該データベースを利用するアプリケーションが稼働していて、一時的にでも削除できない場合は、別データベースを作成・移行して、アプリケーションからの接続先を変更しても問題ありません。むしろダンプ失敗していたりして復元できない可能性も考慮するとそのほうが安全です。(上記で出力したダンプファイルは特定のデータベースには依存していません(use databaseもcreate databaseもない))

mysql> ★注意★ drop database <dbname>;

データベースを作成

mysql> create database <dbname> DEFAULT CHARACTER SET utf8mb4;

作成したデータベースに移動

mysql> use <dbname>;

utf8mb4化したダンプファイルをインポート

mysql> source dump_utf8mb4.sql;

動作確認

任意の文字列カラムにビール(?)、寿司(?)を入力してエラーがでなければOKです。

utf8mb4になっていなければ以下のエラーが発生します。?の場合。

SQL Error [1366] [HY000]: Incorrect string value: '\xF0\x9F\x8D\xA3' for column 'カラム名' at row 1
  Incorrect string value: '\xF0\x9F\x8D\xA3' for column 'カラム名' at row 1

はじめに

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

はじめに

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