MySQL予習メモ (2020年12月版)
MySQLとしばらく密にお付き合いすることになりそうなので、色々と予習したことメモ。
InnoDBのバッファプール
- 取得処理では、バッファプールにデータがあればそれ返す。なければ、テーブルスペースから取得してプールして、それを返す。
- 更新系では、プールにデータを書き込む→ログに書き込む→非同期でログファイルの内容がテーブルスペースに書き込まれる。
- テーブルスペースに反映前のログファイルがあるときに、該当データを取得しようとすると、最初にテーブルスペースに書き込まれる。（SELECTだけなのに書き込みが発生する）
- バッファプールにはページ単位で書き込みがされる。
- 物理メモリの75%とかよく言われるらしい。
- ページとは、InnoDBでバッファプールに読み書きする最小単位。1行以上のデータが入る。
- InnoDBでは行の最大サイズはページサイズの半分以下に制限される。
- リンク
MyISAM
- メモリに書き込みして、ディスクへのフラッシュをOS任せにしている。なのでクラッシュしたらフラッシュ前のデータは揮発してしまう。
- トランザクションがなくその分のオーバーヘッドが発生しないため、高速に動作する。
- テーブル単位でロックするため、並行書き込みに優れない。
レプリケーション
- マスターで発行された更新処理を同じ順番でスレーブで実行することで、データの複製を行なっている。
- 実質非同期でデータは複製されている。
- マスターからスレーブに送られるのはバイナリーログ
- バイナリーログの形式
- pt-checksum / pt-table-sync
- リンク
バックアップ
- フルバックアップ(ダンプファイル)と増分バックアップ(バイナリログ)を使ってリストアする。
- バイナリログにはポジション値がある。
- 同じ処理のログでもマスターとスレーブで値が異なる。
- SHOW MASTER STATUSで現在のポジション値を取得できる。mysqldump にオプションつけることで、その値をダンプファイルに含めることができる。
- GTIDはマスタースレーブで共通で使えるポジション値。
- バイナリログファイルのバックアップ
- myswlbinligコマンドで対象サーバに接続して、バイナリログを追跡してバックアップする。
- バイナリログからリストアする
- mysqlbinligコマンドでバイナリログからSQLを復元して、リストアする。
- リンク
こんなのあったんだ
トリガー
- 特定の操作に対してトリガーをセットできる
- （ただ、アプリとDBの両方でトリガーが分散して管理コストがあがるので、柔軟性のあるアプリ側に寄せた方が良さそう。）
- リンク https://dev.mysql.com/doc/refman/5.6/ja/trigger-syntax.html
ストアドルーチン
- 複数の処理をまとめたもので、ルーチンの名前を指定することで呼び出すことができる。ストアドプロシージャと関数。
- リンク
イベントスケジューラ
- DB単体で定期実行ができる。（アプリケーションの定期実行とかぶるので、アプリ側に寄せた方が良さそう。）
- リンク
パフォーマンスチューニング
- Optimizerは、実行計画の決定をする。
- Executer
- 実行計画の通りにHandlerを叩く
- Handlerからの結果をもとに残処理を行う。
- この残処理をさせてしまうのが重たいので、なるべくExecuterに処理をさせないようにする。
- キーワード
Using whereExecuterがフィルターしてるUsing filesortExecuterがソートしている
- Handlerは、DBエンジンの抽象化レイヤー
- インデックス
- 検索時はひとつのテーブルにつき一つのインデックスしか使えない。
- USE INDEXで使用するインデックスを指定できる。
- MySQLはB+木でインデックス作っている。
- 設定値
innodb_buffer_pool_sizeinnodb_log_buffer_sizeテーブルスペースに書き込まれるまで、ログファイルはここにいる。
- リンク
ネクストキーロックとは
- InnoDBで使うロックの一種
- 範囲走査するトランザクション内で、別のセッションが書き込んだレコードが読み取られないように(ファントムリード防止)するためのロック。
- インデックスロックとそのインデックスに至るまでのデータがない範囲(ギャップ)に対してロックがかかる。
- リンク
Percona
- Percona
- MySQLのプロ集団企業
- ツールも色々出してる
SHOWステートメントいろいろ
SHOW GLOBAL STATUS SHOW INNODB STATUS SHOW MASTER STATUS SHOW SLAVE STATUS
Rails+RspecでActionMailerのテスト
Rsils+RspecでActionMailerのテストの書き方知らなかったのでメモ。
## 設定 config.action_mailer.delivery_method = :test ## テスト expect(ActionMailer::Base.deliveries.size).to eq(1) # メールを送った件数 expect(ActionMailer::Base.deliveries.first.to.first).to match(mail_address) expect(ActionMailer::Base.deliveries.first.subject.to_s).to match(/SubjectText/) expect(ActionMailer::Base.deliveries.first.body.raw_source).to match(/BodyText/)
ポート番号の使用状態を確認する
【lsof】オープンしているファイルを一覧表示する
# オープンしているファイル情報
$ lsof | grep -e COMMAND -e sample.txt
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
vim 35093 kinosuke01 3u REG 1,7 4096 4183268 /Users/kinosuke01/Desktop/.sample.txt.swp
# オープンしているネットワークソケット情報
# -i:#{PORT}でポート番号指定できる
$lsof -i:80
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
ruby 35433 kinosuke01 10u IPv6 0x66e79b6671111eb3 0t0 TCP *:http (LISTEN)
ruby 35433 kinosuke01 11u IPv4 0x66e79b666ac622e3 0t0 TCP *:http (LISTEN)
【netstat】ネットワーク状態を確認する
# -a 現在のすべての接続を表示する # -n 出力をIPアドレスなど数値のみに抑制する $netstat -an | grep "*.80" tcp4 0 0 *.80 *.* LISTEN tcp6 0 0 *.80 *.* LISTEN
docker-composeでdocker内にログインする
ずっとdockerコマンドでやってたので。
## サービス一覧を表示
docker-compose ps --service
## サービス名を指定してログイン
docker-compose exec #{service_name} /bin/bash
ついでに
# リスタートする
docker-compose restart #{service_name}
MySQLの運用で使うコマンドメモ
忘れていたので逐次メモしていく。
## テーブル定義の確認
show create table #{table_name};
## インデックスの確認
show index from #{table_name};
## 発行されているSQLのリスト
show processlist;
## 発行中のSQLを強制終了
KILL #{processlist_id};
## ストレージエンジンの確認
show engines;
## テーブルごとの情報一覧表示
show table status;
Specified key was too long; max key length is 1024 bytes
Rails×MySQL環境下でindex作成時にエラーしてmigrateが止まった。 indexの最大幅を超えてたみたい。
Specified key was too long; max key length is 1024 bytes
migrationファイルを修正して対応した。以下メモ。
VARCHAR(255)
https://dev.mysql.com/doc/refman/5.6/ja/string-type-overview.html MySQL は、文字カラム定義の長さ指定を文字単位で解釈します。これは、CHAR、VARCHAR、および TEXT 型に適応されます。 → VARCHAR(255)は、255文字まで入る。(255byteではない)
RailsMigrationFileではlimit値で長さ指定ができる
change_column :table_name, :col_name, :string, {
null: false, default: '', limit: 80 # limitで指定すればいい
}
文字コード別: 1文字あたりのバイト数
UTF-8: 最大3バイト
utf8mb4(UTF-8に補助文字を足したもの): 最大4バイト
EUC: 最大3バイト
https://dev.mysql.com/doc/refman/5.6/ja/charset-unicode-utf8mb4.html
https://software.fujitsu.com/jp/manual/manualfiles/m150018/b1ws1136/04z200/b1136-i-03-04.html
参考
MySQLのロックについてメモ
PostgreSQLばっかり使ってて、 MySQLのロックについて理解があやふやだったのでメモしておく。
ストレージエンジン
- MyISAM
- InnoDB
14.11.1 オンライン DDL の概要
https://dev.mysql.com/doc/refman/5.6/ja/innodb-create-index-overview.html
MySQL 5.5以上, MySQL 5.1 InnoDB plugin では、craete index を高速化した。 DMLをロックせずにセカンダリインデックスの作成が可能になった。 MySQL5.6以上から他のいくつかのALTER TABLEも、DMLをロックせずにオンラインで実行できるようになった。
高速インデックス作成
https://dev.mysql.com/doc/refman/5.6/ja/glossary.html#glos_fast_index_creation
