コピペコードで快適生活

明日使えるソースを自分のために

MySQL予習メモ (2020年12月版)

MySQLとしばらく密にお付き合いすることになりそうなので、色々と予習したことメモ。

InnoDBのバッファプール

  • 取得処理では、バッファプールにデータがあればそれ返す。なければ、テーブルスペースから取得してプールして、それを返す。
  • 更新系では、プールにデータを書き込む→ログに書き込む→非同期でログファイルの内容がテーブルスペースに書き込まれる。
  • テーブルスペースに反映前のログファイルがあるときに、該当データを取得しようとすると、最初にテーブルスペースに書き込まれる。(SELECTだけなのに書き込みが発生する)
  • バッファプールにはページ単位で書き込みがされる。
  • 物理メモリの75%とかよく言われるらしい。
  • ページとは、InnoDBでバッファプールに読み書きする最小単位。1行以上のデータが入る。
  • InnoDBでは行の最大サイズはページサイズの半分以下に制限される。
  • リンク

MyISAM

  • メモリに書き込みして、ディスクへのフラッシュをOS任せにしている。なのでクラッシュしたらフラッシュ前のデータは揮発してしまう。
  • トランザクションがなくその分のオーバーヘッドが発生しないため、高速に動作する。
  • テーブル単位でロックするため、並行書き込みに優れない。

レプリケーション

  • マスターで発行された更新処理を同じ順番でスレーブで実行することで、データの複製を行なっている。
  • 実質非同期でデータは複製されている。
  • マスターからスレーブに送られるのはバイナリーログ
  • バイナリーログの形式
    • SBR: SQLステートメント単位(1SQL 文) 単純なSQL。マスターにあってスレーブにないデータがあっても空振りするだけでエラー出ない。
    • RBR: 行単位。更新前と更新後のデータが含まれる。マスターにあってスレーブにないデータがあるとエラーする。5.1から導入。5.7からデフォルト。
  • pt-checksum / pt-table-sync
    • マスタースレーブのデータのズレを検出、修正する。SBRとRBRそれぞれで生成されたチェックサムを比較することでズレを検出する。SBRはスレーブで、RBRはマスターでチェックサムが生成される。
  • リンク

バックアップ

こんなのあったんだ

パフォーマンスチューニング

  • Optimizerは、実行計画の決定をする。
  • Executer
    • 実行計画の通りにHandlerを叩く
    • Handlerからの結果をもとに残処理を行う。
    • この残処理をさせてしまうのが重たいので、なるべくExecuterに処理をさせないようにする。
    • キーワード
      • Using where Executerがフィルターしてる
      • Using filesort Executerがソートしている
  • Handlerは、DBエンジンの抽象化レイヤー
  • インデックス
    • 検索時はひとつのテーブルにつき一つのインデックスしか使えない。
    • USE INDEXで使用するインデックスを指定できる。
    • MySQLはB+木でインデックス作っている。
  • 設定値
    • innodb_buffer_pool_size
    • innodb_log_buffer_size テーブルスペースに書き込まれるまで、ログファイルはここにいる。
  • リンク

ネクスキーロックとは

Percona

  • Percona
  • MySQLのプロ集団企業
  • ツールも色々出してる

SHOWステートメントいろいろ

SHOW GLOBAL STATUS
SHOW INNODB STATUS
SHOW MASTER STATUS
SHOW SLAVE STATUS

Rsils+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;

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

参考

https://hit.hateblo.jp/entry/mysql/indexes/maxlength

MySQLのロックについてメモ

PostgreSQLばっかり使ってて、 MySQLのロックについて理解があやふやだったのでメモしておく。

ストレージエンジン

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