MySQL OPTIMIZE TABLE

Amazon RDS for MySQL が予想以上のストレージを使用するときのディスクストレージを最適化

テーブル用の領域の一部は実際に使用されない場合でも割り当てられます。innodb_file_per_table が有効化されている場合 (デフォルトでは有効化)、OPTIMIZE TABLE を使ってその容量を解放できます。OPTIMIZE TABLE は InnoDB、MyISAM、ARCHIVE テーブルに対して機能し、Amazon RDS は OPTIMIZE TABLE コマンドを受け入れますが、Amazon RDS は ALTER TABLE…FORCE を実行します。これが発生すると、「テーブルが最適化をサポートしていません。代わりに、再作成 + 分析を実行します」のような警告メッセージが表示されます。 詳細については、OPTIMIZE TABLE に関する MySQL ドキュメントを参照してください。

👆でリンクされている公式ドキュメントは下記。

MySQL :: MySQL 5.7 Reference Manual :: 13.7.2.4 OPTIMIZE TABLE Statement

OPTIMIZE TABLE は短期間しかテーブルをロックしない

英語より日本語の説明のほうが丁寧なのでこちらから引用する。

MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.7.2.4 OPTIMIZE TABLE 構文

Mysql 5.6.17 より前は、OPTIMIZE TABLE はオンライン DDL (ALGORITHM=INPLACE) を使用しません。その結果、OPTIMIZE TABLE が実行中のテーブルに対しては (つまり、そのテーブルのロック中は) 並列 DML (INSERT、UPDATE、DELETE) が許可されません。また、主キーに現れる順序でキーが挿入されるため、セカンダリインデックスはそれほど効率的に作成されません。

5.6.17 の時点では、OPTIMIZE TABLE は、InnoDB の通常のテーブルとパーティション化されたテーブルの両方に対してオンライン DDL (ALGORITHM=INPLACE) を使用します。OPTIMIZE TABLE によってトリガーされ、ALTER TABLE … FORCE の下で実行されるテーブル再構築は現在、オンライン DDL (ALGORITHM=INPLACE) を使用して実行され、短期間しかテーブルをロックしないため、並列 DML 操作のためのダウンタイムが短縮されます。

ポイントは新しいMySQLバージョンであればオンライン DDLが効くので「短期間しかテーブルをロックしない」ので「ダウンタイムが短縮」されていることだ。

こう思ったっす

ロック状態の確認

MySQL 5.7と8.0でロック状態を確認する（sys.innodb_lock_waitsビュー） - Qiita

ロックの状態を確認したいときのクエリ。

mysql> SELECT * FROM  sys.innodb_lock_waits\G
*************************** 1. row ***************************
                wait_started: 2018-01-07 13:09:32
                    wait_age: 00:00:02
               wait_age_secs: 2
                locked_table: `lock_test`.`lock_test`
                locked_index: PRIMARY
                 locked_type: RECORD
              waiting_trx_id: 92210
         waiting_trx_started: 2018-01-07 13:07:49
             waiting_trx_age: 00:01:45
     waiting_trx_rows_locked: 2
   waiting_trx_rows_modified: 0
                 waiting_pid: 6
               waiting_query: UPDATE lock_test.lock_test SET val1=6 WHERE id=6
             waiting_lock_id: 92210:112:3:7
           waiting_lock_mode: X
             blocking_trx_id: 92209
                blocking_pid: 4
              blocking_query: NULL
            blocking_lock_id: 92209:112:3:7
          blocking_lock_mode: X
        blocking_trx_started: 2018-01-07 13:04:45
            blocking_trx_age: 00:04:49
    blocking_trx_rows_locked: 1
  blocking_trx_rows_modified: 1
     sql_kill_blocking_query: KILL QUERY 4
sql_kill_blocking_connection: KILL 4
1 row in set, 3 warnings (0.00 sec)