AND OR  

このページでは主にMySQL関連のメモを書いています。

公式リファレンス
MySQL公式リファレンスマニュアル

パラメータチューニング

MySQLのバッファには以下の2種類のタイプがある。パラメータチューニングの際にはこの2つのタイプの違いを意識しないといけない。

  • グローバルバッファ (mysqld内部で1つだけ確保される)
  • スレッドバッファ (スレッド毎に確保される)

スレッドバッファに多くのメモリを割り当てすぎるとコネクションが増えた途端すぐにメモリ不足に。物理メモリ以上のサイズを割り当ててしまうと、スワップが発生して逆にパフォーマンスが落ちる。

innodb_buffer_pool_size (グローバル)InnoDBのデータやインデックスを保持するために使用するメモリバッファのサイズ。グローバルなのでたくさん割り当てると良い。
innodb_additional_mem_pool_size (グローバル)InnoDBの内部データなどを保持するために使用するメモリプールのサイズ。エラーログに警告が出たら増やせばいい程度。
innodb_log_buffer_size (グローバル)InnoDBの更新ログを記録するために使用するメモリバッファのサイズ。大きなトランザクションがある場合はこれを大きくするとディスクI/Oを減らせるが実際はそんなに必要ない。
sort_buffer_size (スレッド)ORDER BYやGROUP BYの時に使用されるメモリバッファのサイズ。スレッドバッファなので大きくしすぎてメモリ不足にならないように。
read_rnd_buffer_size (スレッド)ソート後にレコードを読むときに使用されるメモリバッファのサイズ。これも ORDER BY や GROPY BY を行う場合のパフォーマンスに影響する。
join_buffer_size (スレッド)インデックスを用いないテーブル結合時に使用されるメモリバッファのサイズ。
read_buffer_size (スレッド)インデックスを用いないテーブルスキャン時に使用されるメモリバッファのサイズ。
key_buffer_size (グローバル)MyISAMのキー(インデックス)を保持するために使用するメモリバッファのサイズ。グローバルなのでたくさん割り当てると良い。ただし、MyISAMを利用してない場合は他のパラメータにメモリを回す。
myisam_sort_buffer_size (スレッド)MyISAMでソート時(REPAIR TABLE, CREATE INDEX, ALTER INDEX)に使用するメモリバッファのサイズ。ORDER BY や GROUP BY を行う場合のパフォーマンスに影響する。

その他

innodb_log_file_sizeInnoDBの更新ログを記録するディスク上のファイルサイズ。
1MB<innodb_buffer_pool_size/innodb_log_files_in_groupの間で設定する。

MySQL5.5ではデフォルトストレージエンジンはInnoDBとなっている。

データベースのダンプ

データベースサーバのホスト移行時など、頻繁に使う訳ではないのでオプションなどはすぐ忘れてしまうのでメモ。

オプションメモ

  • single-transaction : テーブルをロックしないでバックアップ。InnoDBでは常に指定すること。
  • master-data : レプリケーション用データをダンプする。CHANGE MASTER TO ~ のクエリがダンプに追加される。
  • routines : Storead Procedure/Function を作成するための CREATE PROCEDURE および CREATE FUNCTION 文が含まれる。プロシージャや関数がある場合はこのオプションを付けるのを忘れずに。

レプリケーション

MySQLのレプリケーション関連のメモ。

  • 環境
    CentOS 5.9 (x86_64)
    MySQL 5.5.x, InnoDB
  • レプリケーションの流れ
    マスタ側の更新系のクエリが、マスタのバイナリログに記録される。
    ↓
    スレーブのI/OスレッドがマスタのBinlog Dumpスレッドに接続し、
    マスタのBinlog Dumpスレッドはバイナリログの内容を送信する。
    ↓
    スレーブのI/Oスレッドは、受け取ったマスタのバイナリログをリレーログに保存。
    ↓
    スレーブのSQLスレッドがリレーログからクエリを読み取って実行。
  • レプリケーションユーザの作成
    レプリケーション専用ユーザなので権限は REPLICATION SLAVE のみ与えておけばよい。
  • レプリケーション設定
    • マスター側
      ## /etc/my.cnf
      server-id = 1  ## サーバID
      log-bin = mysql-bin  ## バイナリログ名
      relay-log = relay-log  ## リレーログ名
      expire_logs_days = 30  ## ログの保存期間
      binlog_format = 0  ## バイナリログのフォーマット (0:MIXED, 1:, 2:)
      また、レプリケーションにできる限りの耐用性と一貫性を期待する場合はマスターの設定ファイルに sync_binlog=1 と innodb_flush_log_at_trx_commit=1 を設定しておく。
    • スレーブ側
      server-id = 2
      フェイルオーバーをサポートするにはスレーブ側でもバイナリログを書き出しておく必要がある。その際は設定ファイルに log-slave-updates を設定する。
  • マスターのスナップショットを取得
    データの取得には mysqldump コマンドを利用する。--master-data を指定するとダンプファイルにレプリケーション位置を指定するSQLも出力される。また、InnoDBの場合は --single-transaction も指定しておこう。
  • スレーブにマスターデータをロード
    スレーブにデータベースを作成してからダンプしたマスターデータをロードする。
  • レプリケーションの開始
  • その他
    リレーログはバイナリログと同じ形式なので、mysqlbinlog で読み取ることができる。リレーログは必要がなくなると(そのイベントがすべて実行されたら)、SQLスレッドによって自動的に削除される。

レプリケーションのセットアップ方法
レプリケーション FAQ
mysqldump — データベースバックアッププログラム

alter table

  • メモ
    1800万レコードの外部キー制約のあるテーブルのカラムの型変更(int -> bitint)に約10分程度かかった。
    • 環境:
      MySQL 5.6.22
      Intel Xeon E5-2630L v2 2.40GHz / 64GB RAM

MariaDBについて

CentOS 7.x以降はデフォルトのデータベースサーバはMySQLではなくMariaDBになっている。