mita2 database life

主にMySQLに関するメモです

MySQL Orchestrator RecoveryPeriodBlockSeconds と FailureDetectionPeriodBlockMinutes の違い

Orchestrator

orchestrator は マスターの障害検知およびレプリカのマスター昇格(フェイルオーバー)を自動で行うソフトウェアです。 MySQLのマスター昇格といえば、MHAがデファクトスタンダードでしたが、MHAはメンテナンスモードになって久しい・・・ということで、最近は orchestrator が使われることが多い印象です。メルカリさんで使われたりしているようです。

github.com

アンチ フラッピング機構 RecoveryPeriodBlockSeconds

orchestrator にはアンチフラピング機構が備わってます。orchestratorにおける、フラッピングとは、フェイルオーバーが繰り返し短時間に発生する状況を指します。

障害が短期間に連続して発生する状況は、何か予期せぬ事態が起きている可能性が高いです。このような状況では、ヘタに自動的にフェイルオーバーするよりは、人間がしっかりと状況を確認し、対応するほうが好ましいでしょう。

orchestrator では一度、リカバリ(レプリカのマスター昇格)を実行したら、RecoveryPeriodBlockSeconds で指定した期間(秒)経過するまで、リカバリが発動しないように制御されています。

orchestrator avoid flapping (cascading failures causing continuous outage and elimination of resources) by introducing a block period, where on any given cluster, orchesrartor will not kick in automated recovery on an interval smaller than said period, unless cleared to do so by a human. https://github.com/openark/orchestrator/blob/master/docs/topology-recovery.md

FailureDetectionPeriodBlockMinutes

もう1つ似たようなパラメータとして、FailureDetectionPeriodBlockMinutes があります。 障害を検知したら、FailureDetectionPeriodBlockMinutes で指定した期間(分)、障害の検知を行いません*1

障害検知が行われなければ、リカバリも行われないので、「RecoveryPeriodBlockSeconds と同じやん・・・*2」と思いましたが、以下の記述で納得できました。

Detection does not always lead to recovery. There are scenarios where a recovery is undesired: https://github.com/openark/orchestrator/blob/master/docs/failure-detection.md

orchestrator では、障害の検知(FailureDetection)= リカバリ(マスター昇格)発動 とは限りません。

orchestrator は 障害検知からマスター昇格完了までの各ポイントで、任意のスクリプトを実行するためのHookが設けられています。 PreFailoverProcesses で指定したスクリプトで、エラーを返すことで、リカバリの発動を止めることができます。

フェイルオーバーに関する Hook)

* OnFailureDetectionProcesses
  * 障害検知時
* PreFailoverProcesses
  * フェイルオーバー発動前
* PostMasterFailoverProcesses
  * マスターのフェイルオーバー後
* PostFailoverProcesses
  * レプリカも含めて、全てのフェイルオーバーが完了したタイミング

--

FailureDetectionPeriodBlockMinutes の説明には、anti-spam mechanism と書いてあります。 これは、OnFailureDetectionProcesses フックで、障害通知を行っているようなケースを想定して、「アンチスパム」と表現しているのでしょう。

FailureDetectionPeriodBlockMinutes is an anti-spam mechanism that blocks orchestrator from notifying the same detection again and again and again.

まとめ

  • RecoveryPeriodBlockSeconds は短期間にフェイルオーバーが連続発生しないようにするためのパラメータ
  • 障害の検知(FailureDetection)= リカバリ(マスター昇格)発動 とは限らない
  • FailureDetectionPeriodBlockMinutes は 障害継続時 にOnFailureDetectionProcesses で指定したスクリプトを実行する間隔

僕が本当に知りたかったこと

ごく短時間のダウンではフェイルオーバーを発動させたくないんだが、Hookで作り込むしかないんだろうか(誰か教えてください)

*1:チェックする間隔はまた別のパラメータなので注意

*2:スマートスタイルの記事でも、FailureDetectionPeriodBlockMinutes がフラッピング対策と説明されているので、同じように考えてしまう人は多そう http://blog.s-style.co.jp/2018/11/2875/

MySQL 論理削除に関する個人的見解まとめ

技術顧問や講演の場で、論理削除について見解を聞かれる場面がよくあります。アプリケーション開発者の方にとって、身近なデータベースの疑問なんでしょうね。

しっかり言語化できてなかったので、ブログに書いておきます。

論理削除をどう考えるかは、諸派あるんだろうと思ってます。 自分の意見が正しいと言うつもりはありませんし、求めらる要件や環境で結論が変わることもあると思います。

論理削除とは(おさらい)

以下のように削除日付(deleted_at)のカラムを設けて、レコードの有効・無効を管理する手法を指します。

id name deleted_at
1 aaaaaa NULL
2 bbbbbb 2021-01-12 10:00:00

論理「削除」と呼びますが、実際は、非表示フラグやレコードの状態遷移を表していたり。よくよく考えると「削除」でないパターンも含まれています。逆に、DELETE して完全にデータを削除することを「物理削除」と呼びます。

誤操作の取り消しが、論理削除を採用する代表的な目的でしょう。

誤操作の保険としてはバックアップもありますが、バックアップは、データベース全体の時間軸を戻すことを意図したものです。 特定のレコードに絞って時間軸を戻すのには適していません。

論理削除のデメリットと言われているもの

バグの温床になりやすい

WHERE 句に deleted_at IS NULL を付けまくらないといけないというやつですね。deleted_at IS NULL を付け忘れると削除済みデータが見えてしまうと。

パフォーマンスへの影響

本来必要のないレコードが保存されることで、そのぶんオーバーヘッドになる。

ぼくの見解

論理削除をそれほど、強いアンチパターンだとは考えてません。他のアンチパターンと比較すると問題になる確率も低いように思います。 自分の周囲では論理削除が問題になっているケースをほとんど見たことがないです。

もちろん物理削除のほうがデータがクリーンで好ましいと思います。ただ、論理削除を撲滅するために、大きな労力を割く必要は感じないです。

パフォーマンス影響については、論理削除の有無より他の要素に左右されるケースがほとんどだと思います。

むしろ、現場では、貯め続けていると、そのうち明らかに問題になるデータ量なのに、ノーガードで運用されているケース に出くわすほうが多いです。論理削除云々を気にする以前に、データ量の見積もりと、適切なデータストアの選定をしっかりしてほしい。。。*1

*1:あと、物理削除バッチの作りが悪くて、爆発することも多い

MySQL GISのSRID 0(直行座標)を理解する

MySQLで20年ほど遊んでいますが・・・知らないことも、まだまだあります。 GIS(Geographic Information System、地理・空間情報) はそのうちの1つです。ということで、ほとんど触れたことのない、GISを入門してみます。

なお、このエントリーは MySQL 8.0 を前提としています。

デフォルトのSRIDは0(直行座標)

デフォルトは SRID 0です。 SRID0 は直行座標(Cartesian plane=デカルト座標)です。実際の地球ではなく、数学の授業で出てきたようなXY座標上の地点を表します。

SRID 0 represents an infinite flat Cartesian plane with no units assigned to its axes.

https://dev.mysql.com/doc/refman/8.0/en/spatial-function-argument-handling.html

ST_Distance 関数を使って、直行座標であることを確認してみます。

原点(0,0) から (10, 0) の距離は、10です。

mysql> SET @a = ST_GeomFromText('POINT(0 0)', 0);
Query OK, 0 rows affected (0.00 sec)

mysql> SET @b = ST_GeomFromText('POINT(0 10)', 0);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT ST_Distance(@a, @b);
+---------------------+
| ST_Distance(@a, @b) |
+---------------------+
|                  10 |
+---------------------+
1 row in set (0.00 sec)

原点(0,0) から (10, 10) の距離は、14.142135.. (ルート2 * 10)です。

mysql> SET @c = ST_GeomFromText('POINT(10 10)', 0);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT ST_Distance(@a, @c);
+---------------------+
| ST_Distance(@a, @c) |
+---------------------+
|  14.142135623730951 |
+---------------------+
1 row in set (0.00 sec)

SRID 4326を指定すると、地球上の緯度・経度になりST_Distanceの結果(距離)は SRID0 とは大きく異なる値になりました。

mysql> SET @g1 = ST_GeomFromText('POINT(0 0)', 4326);
Query OK, 0 rows affected (0.00 sec)

mysql> SET @g2 = ST_GeomFromText('POINT(10 10)', 4326);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT ST_Distance(@g1, @g2);
+-----------------------+
| ST_Distance(@g1, @g2) |
+-----------------------+
|    1565092.2768577514 |
+-----------------------+
1 row in set (0.00 sec)

SRID0は地球上の緯度・経度ではないので、経度としてはありえない座標(経度は180度まで)でも指定できます。

-- ref) https://sakaik.hateblo.jp/entry/20191229/mysql_gis_axis_order_srid_0
mysql> SET @d = ST_GeomFromText('POINT(190 10)', 0);
Query OK, 0 rows affected (0.00 sec)

-- SRID を指定して、地球上の地点を表すとエラー
mysql> SET @d = ST_GeomFromText('POINT(10 190)', 4326);
ERROR 3616 (22S02): Longitude 190.000000 is out of range in function st_geomfromtext. It must be within (-180.000000, 180.000000].

ところで、ST_Distance 関数では距離を表す単位を変更できるそうです。デフォルトの単位はメートルです。

next4us-ti.hatenablog.com

SRID0 で 単位を指定してみます。抽象的な座標には単位が存在しないため、単位が変換できない旨のエラーが出ますね。

mysql> SELECT ST_Distance(@a, @c, 'centimetre');
ERROR 3882 (SU001): The geometry passed to function st_distance is in SRID 0, which doesn't specify a length unit. Can't convert to 'centimetre'.

あわせて読みたい

sakaik さんのGIS関連のエントリーが非常に参考になります

sakaik.hateblo.jp

MySQL 1レコード 8000バイトの壁を確認するSQL ROW_FORMAT=DYNAMIC 編

InnoDBのレコード長の限界

InnoDBのデフォルトのページサイズは、16Kです。この場合、最大レコード長は約8000バイトです。マニュアルで「約8000バイト」と曖昧な言い回しになっているのは、データの保存に使える領域以外にメタデータを保存する領域が必要だからでしょう。

可変長カラム (VARBINARY、VARCHAR、BLOB、および TEXT) を除き、行の最大長はデータベースページの半分より少し短くなります。つまり、デフォルトページサイズの 16K バイトでは、行の最大長が約 8000 バイトになります。

https://dev.mysql.com/doc/refman/5.6/ja/column-count-limit.html

また、「可変長カラム (VARBINARY、VARCHAR、BLOB、および TEXT) を除き」は可変長カラムはオーバーフローページに分割して保存されることを指し示しています。 可変長カラムは複数のページに分割して保存されるため、可変長カラムを含むテーブルでは、8000バイト以上保存できます。

-- 固定長のCHARだと、250 x 33 = 8250 バイトでエラー
mysql> CREATE TABLE table_with_char
(pk int primary key,
   c1 CHAR(250) NOT NULL,
   c2 CHAR(250) NOT NULL,
   c3 CHAR(250) NOT NULL,
:
   c33 CHAR(250) NOT NULL
) ROW_FORMAT=DYNAMIC CHARACTER SET 'latin1';
ERROR 1118 (42000): Row size too large (> 8126). Changing some columns to TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline.

-- varchar にすれば、一行8000バイトの壁を越えられる
mysql> CREATE TABLE table_with_varchar
(pk int primary key,
   c1 VARCHAR(250) NOT NULL,
   c2 VARCHAR(250) NOT NULL,
   c3 VARCHAR(250) NOT NULL,
:
   c33 VARCHAR(250) NOT NULL
) ROW_FORMAT=DYNAMIC CHARACTER SET 'latin1';
Query OK, 0 rows affected (0.02 sec)

では、可変長カラムであれば、いくらでも保存できるかというとそうではありません。 可変長カラムであっても、ポインタ分の 20バイト 消費します。

ROW_FORMAT=DYNAMIC または ROW_FORMAT=COMPRESSED で作成されたテーブルでは、カラムの長さおよび行全体の長さによっては、BLOB、TEXT、または VARCHAR カラムの値が完全にオフページに格納される場合もあります。オフページに格納されるカラムでは、クラスタ化されたインデックスのレコードに、オーバーフローページへの 20 バイトのポインタのみがカラムごとに 1 つずつ含まれます。

https://dev.mysql.com/doc/refman/5.6/ja/innodb-compression-internals.html

また、TEXT/BLOB 型では、実際のデータの長さによっては、20バイトではなく、40バイト消費するケースもあります。

ROW_FORMAT=DYNAMIC または ROW_FORMAT=COMPRESSED で作成されたテーブルでは、40 バイト以下の TEXT および BLOB カラムは、常にインラインに格納されます。

https://dev.mysql.com/doc/refman/5.6/ja/innodb-compression-internals.html

ROW_FORMAT=DYNAMIC で 8000バイトの壁を確認するSQL

さて、上記をふまえて、各テーブルのレコード長(オーバーフローページ分を除く)を表示するSQLを考えてみます。

こちらの記事のSQLを参考にさせて頂きました。この記事のSQLROW_FORMAT=COMPACT 用のSQLです。 ROW_FORMAT=DYNAMIC 用に上記の可変長カラムのオーバーフローページを考慮して微修正しました。

qiita.com

SELECT
  TABLE_SCHEMA,
  TABLE_NAME,
SUM(
    CASE
      WHEN DATA_TYPE = 'tinyint' then 1
      WHEN DATA_TYPE = 'smallint' then 2
      WHEN DATA_TYPE = 'mediumint' then 3
      WHEN DATA_TYPE = 'int' then 4
      WHEN DATA_TYPE = 'bigint' then 8

      WHEN DATA_TYPE = 'float' then 4
      WHEN DATA_TYPE = 'double' then 8

      WHEN DATA_TYPE = 'decimal' then 0
          + FLOOR((NUMERIC_PRECISION - NUMERIC_SCALE) / 9) * 4
          + CEIL((NUMERIC_PRECISION - NUMERIC_SCALE) % 9 / 2)
          + FLOOR((NUMERIC_SCALE) / 9) * 4
          + CEIL((NUMERIC_SCALE) % 9 / 2)

      WHEN DATA_TYPE = 'char' then CHARACTER_OCTET_LENGTH
      WHEN DATA_TYPE = 'binary' then CHARACTER_OCTET_LENGTH

      -- varchar/varbinary ではポインタ分 20BYTE
      WHEN DATA_TYPE = 'varchar' then 20
      WHEN DATA_TYPE = 'varbinary' then 20

      -- text/blob では 40BYTE
      WHEN DATA_TYPE REGEXP '^(tiny|medium|long)?text$' then 40
      WHEN DATA_TYPE REGEXP '^(tiny|medium|long)?blob$' then 40

      WHEN DATA_TYPE = 'datetime' then 8
      WHEN DATA_TYPE = 'date' then 3
      WHEN DATA_TYPE = 'time' then 3
      WHEN DATA_TYPE = 'year' then 1
      WHEN DATA_TYPE = 'timestamp' then 4

      WHEN DATA_TYPE = 'enum' then IF((CHARACTER_OCTET_LENGTH / CHARACTER_MAXIMUM_LENGTH) < 256, 1, 2)

      ELSE NULL
    END
  ) as SIZE
FROM information_schema.COLUMNS 
WHERE TABLE_SCHEMA NOT IN ('mysql', 'information_schema', 'performance_schema') 
GROUP BY TABLE_SCHEMA, TABLE_NAME ORDER BY TABLE_SCHEMA, TABLE_NAME;

実行結果例)

+--------------+--------------------+------+
| TABLE_SCHEMA | TABLE_NAME         | SIZE |
+--------------+--------------------+------+
| test         | col1017            | 1020 |
| test         | t2                 |   44 |
| test         | table_with_varchar |  664 |
| test         | text_x_197         | 7884 |
| test         | text_x_200         | 7884 |
| test         | text_x_null197     | 7884 |
| test         | var1               |   20 |
+--------------+--------------------+------+
7 rows in set (0.03 sec)

ソースコード

MySQL 5.6 では storage/innobase/dict/dict0dict.ccdict_index_too_big_for_treeCREATE TABLE 時のレコード長のチェックがされてました。 rec_max_size の値を追いかけると、各型が何バイトで計算されるかわかりました。

/****************************************************************//**
If a record of this index might not fit on a single B-tree page,
return TRUE.
@return TRUE if the index record could become too big */
static
ibool
dict_index_too_big_for_tree(
/*========================*/
    const dict_table_t* table,      /*!< in: table */
    const dict_index_t* new_index)  /*!< in: index */
{
    ulint   zip_size;
    ulint   comp;
    ulint   i;
    /* maximum possible storage size of a record */
    ulint   rec_max_size;
    /* maximum allowed size of a record on a leaf page */
    ulint   page_rec_max;
    /* maximum allowed size of a node pointer record */
    ulint   page_ptr_max;

<snip>


add_field_size:
        rec_max_size += field_max_size;

        /* Check the size limit on leaf pages. */
        if (UNIV_UNLIKELY(rec_max_size >= page_rec_max)) {

            return(TRUE);
        }

        /* Check the size limit on non-leaf pages.  Records
        stored in non-leaf B-tree pages consist of the unique
        columns of the record (the key columns of the B-tree)
        and a node pointer field.  When we have processed the
        unique columns, rec_max_size equals the size of the
        node pointer record minus the node pointer column. */
        if (i + 1 == dict_index_get_n_unique_in_tree(new_index)
            && rec_max_size + REC_NODE_PTR_SIZE >= page_ptr_max) {

            return(TRUE);
        }

MySQL Index dive の動きを観測してみた

Index dive とは

range スキャンの場合に、オプティマイザーがその範囲に含まれる行数を正確に見積もるための仕組みです。 インデックスダイブは特に値の分布が偏っているデータに対して、効果を発揮します。

eq_range_index_dive_limit パラメータ

インデックスダイブは、正確な行見積もりを提供しますが、式内の比較値の数が増えるほど、オプティマイザの行見積もりの生成に時間がかかるようになります。 インデックス統計の使用は、インデックスダイブより正確ではありませんが、大きな値リストの場合に、行見積もりが高速になります。

https://dev.mysql.com/doc/refman/5.6/ja/range-optimization.html

OR条件の数や IN句に指定された条件の数 が多ければ多いほど、インデックスダイブのコストが高くなるとされています。

大量のOR/IN条件に対してはインデックスダイブを回避するよう、 eq_range_index_dive_limit パラメータで制限がされています。 ORやINの数が eq_range_index_dive_limit - 1 以上になると、インデックスダイブが無効化されます(代わりに統計情報から見積もりを取得します)。 eq_range_index_dive_limit=0 の場合は、上限なしです。常にインデックスダイブが有効となります。

MySQL 8.0 の eq_range_index_dive_limit の デフォルト値は 200 です。

Index dive の効果を確認してみる

テストデータは以下のような分布のデータを用います。pk_p1 は 1〜100万まで連番で値が入っています。 pk_p1 が 1〜10 のレコードだけ、それぞれ 10万行あり、残りは1行です。効果をわかりやすくするため、非常に分布が偏ったデータで試します。

mysql> SELECT pk_p1, count(*) FROM t GROUP BY pk_p1 LIMIT 15 ;
+-------+----------+
| pk_p1 | count(*) |
+-------+----------+
|     1 |   100000 |
|     2 |   100000 |
|     3 |   100000 |
|     4 |   100000 |
|     5 |   100000 |
|     6 |   100000 |
|     7 |   100000 |
|     8 |   100000 |
|     9 |   100000 |
|    10 |   100000 |
|    11 |        1 |
|    12 |        1 |
|    13 |        1 |
|    14 |        1 |
|    15 |        1 |
+-------+----------+
15 rows in set (0.15 sec)


mysql> SHOW CREATE TABLE t \G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `pk_p1` int NOT NULL,
  `pk_p2` int NOT NULL,
  `c1` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  PRIMARY KEY (`pk_p1`,`pk_p2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
1 row in set (0.00 sec)

このクエリの見積もりをインデックスダイブあり・なしで比較してみます。実際のレコード数は100万件です。 オプティマイザーが 100万件に近い値を出していれば、正確な見積もりができていると言えます。

mysql> SELECT count(*) FROM t WHERE pk_p1 in (1,2,3,4,5,6,7,8,9,10) ;
+----------+
| count(*) |
+----------+
|  1000000 |
+----------+
1 row in set (0.18 sec)

Index dive なし

eq_range_index_dive_limit を小さくして、インデックスダイブを発動しないようにします。 rows が 10 と、100万行から大きく外れた見積もりになっています。

mysql> SET eq_range_index_dive_limit=1;
Query OK, 0 rows affected (0.00 sec)

mysql> EXPLAIN SELECT count(*) FROM t WHERE pk_p1 in (1,2,3,4,5,6,7,8,9,10) \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t
   partitions: NULL
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 10
     filtered: 100.00
        Extra: Using where; Using index
1 row in set, 1 warning (0.00 sec)

Index dive あり

eq_range_index_dive_limit を大きくして、インデックスダイブを発動できるようにします。 rows が 1992788 と、インデックスダイブなしよりかなり近い見積もりになることが確認できました。

mysql> SET eq_range_index_dive_limit=100;
Query OK, 0 rows affected (0.00 sec)

mysql> EXPLAIN SELECT count(*) FROM t WHERE pk_p1 in (1,2,3,4,5,6,7,8,9,10) \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t
   partitions: NULL
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 1992788
     filtered: 100.00
        Extra: Using where; Using index
1 row in set, 1 warning (0.00 sec)

Optimizer Trace で Index dive の有無を確認する

EXPLAIN 結果には インデックスダイブの有無は直接表現されないようです。 FORMAT=JSONとしても、インデックスダイブが行われてたかを示すフィールドは見つかりません。

mysql> EXPLAIN FORMAT=JSON SELECT count(*) FROM t WHERE pk_p1 IN (1,2,3,4,5,6,7,8,9,10)  \G
*************************** 1. row ***************************
EXPLAIN: {
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "399006.02"
    },
    "table": {
      "table_name": "t",
      "access_type": "range",
      "possible_keys": [
        "PRIMARY"
      ],
      "key": "PRIMARY",
      "used_key_parts": [
        "pk_p1"
      ],
      "key_length": "4",
      "rows_examined_per_scan": 1992788,
      "rows_produced_per_join": 1992788,
      "filtered": "100.00",
      "using_index": true,
      "cost_info": {
        "read_cost": "199727.23",
        "eval_cost": "199278.80",
        "prefix_cost": "399006.03",
        "data_read_per_join": "1G"
      },
      "used_columns": [
        "pk_p1"
      ],
      "attached_condition": "(`test`.`t`.`pk_p1` in (1,2,3,4,5,6,7,8,9,10))"
    }
  }
}
1 row in set, 1 warning (0.00 sec)

Optimizer Trace で実行計画を詳細に表示してみます。

nippondanji.blogspot.com

range_scan_alternatives の中に、index_dives_for_eq_ranges でインデックスダイブの有無が確認できました!

mysql> SET optimizer_trace="enabled=on";
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT count(*) FROM t WHERE pk_p1 IN (1,2,3,4,5,6,7,8,9,10)  \G
*************************** 1. row ***************************
count(*): 1000000
1 row in set (0.18 sec)

mysql> SELECT * FROM information_schema.optimizer_trace\G
*************************** 1. row ***************************
                            QUERY: SELECT count(*) FROM t WHERE pk_p1 IN (1,2,3,4,5,6,7,8,9,10)
                            TRACE: {
<snip>
                  "analyzing_range_alternatives": {
                    "range_scan_alternatives": [
                      {
                        "index": "PRIMARY",
                        "ranges": [
                          "1 <= pk_p1 <= 1",
                          "2 <= pk_p1 <= 2",
                          "3 <= pk_p1 <= 3",
                          "4 <= pk_p1 <= 4",
                          "5 <= pk_p1 <= 5",
                          "6 <= pk_p1 <= 6",
                          "7 <= pk_p1 <= 7",
                          "8 <= pk_p1 <= 8",
                          "9 <= pk_p1 <= 9",
                          "10 <= pk_p1 <= 10"
                        ],
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": true,
                        "using_mrr": false,
                        "index_only": true,
                        "rows": 1992788,
                        "cost": 199727,
                        "chosen": true
                      }
                    ],
<snip>
}
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0
          INSUFFICIENT_PRIVILEGES: 0
1 row in set (0.04 sec)

次回予告

次回は、インデックスダイブのオーバーヘッドの計測にチャレンジしてみたいと思います。

MySQL バイナリログをマスキングするツールを作ってみた

このエントリーは MySQL Advent Calendar 2020 の 12/20 のエントリーです。

問題を再現させるためにバイナリログが必要

MySQLOSS です。誰でも無料で自由に使うことができます。

一方、Oracle や Percona といったデータベースを専門とする会社にお願いして、有償サポートを受けることも可能です。有償サポートはその道のプロフェッショナルの方が、問題解決にあたってくれるわけですが、彼らも魔法使いではありません。 問題の再現性がなければ、有効な回答がもらえないことがあります。

問題の再現性を高めるために、必要になってくるのがバイナリログです。MySQL のバイナリログにはデータベースに対する更新履歴が記録されています。 バイナリログをサポートに提出することで、より問題を詳細に解析してもらうことが可能になります。

しかし、バイナリログにはデータが含まれていますから、契約を結んでいるサポート先とはいえ、外に出せない場合も多いです。そこで、バイナリログに含まれるデータのマスキングについて考えてみました。

バイナリログのフォーマット

バイナリログは名前のとおり、バイナリ形式で書かれています。そのままでは人間が読めません。まず、人間が扱えるよう、mysqlbinlog コマンドでバイナリログ形式からテキスト形式へ変換します。

$ sudo mysqlbinlog -vv --base64-output=DECODE-ROWS   /var/lib/mysql/mysqld-bin.000002 

バイナリログのフォーマットは非常にシンプルです。更新内容が @数字=値 の形式で記録されています (ROWフォーマット前提)。

mysql> INSERT INTO test.t (c1, c2, c3), ('THIS IS SECRET STRING', 1000, now());
#201213  5:45:26 server id 1  end_log_pos 819 CRC32 0x2388d887  Write_rows: table id 109 flags: STMT_END_F
### INSERT INTO `test`.`t`
### SET
###   @1=1 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @2='THIS IS SECRET STRING' /* VARSTRING(255) meta=255 nullable=0 is_null=0 */
###   @3=1000 /* INT meta=0 nullable=1 is_null=0 */
###   @4='2020-12-13 05:45:26' /* DATETIME(0) meta=0 nullable=1 is_null=0 */

適当な文字に置換するだけであれば、非常に簡単です。しかし、それではデバッグには向かないでしょう。

デバッグに必要な要素を可能な限り保持しつつ、マスクする必要があります。

ツールを作ってみた

バイナリログをマスキングするスクリプトを書いてみました。

github.com

これが、

### INSERT INTO `test`.`t`
### SET
###   @1=1 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @2='THIS IS SECRET STRING' /* VARSTRING(255) meta=255 nullable=0 is_null=0 */
###   @3=1000 /* INT meta=0 nullable=1 is_null=0 */
###   @4='2020-12-13 05:45:26' /* DATETIME(0) meta=0 nullable=1 is_null=0 */

こなります↓

$ sudo mysqlbinlog -vv --base64-output=DECODE-ROWS   /var/lib/mysql/mysqld-bin.000002 | ./mita2-binlog-mask.py --preserve=test.t.1
<snip>
### INSERT INTO `test`.`t`
### SET
###   @1=1 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @2='EFIA7R01TBPE9ADWOYH' /* VARSTRING(255) meta=255 nullable=0 is_null=0 */
###   @3=2140268343 /* INT meta=0 nullable=1 is_null=0 */
###   @4='2020-12-25 21:17:29' /* DATETIME(0) meta=0 nullable=1 is_null=0 */

以下のような特徴があります。

  • マスキングの対象外とするカラムの指定が出来る

--preserve でどのテーブルの何番目のカラムをマスキングの対象外にするか指定します。 ロックにまつわる問題の場合は、どの行に更新があったかが重要になってきます。そのようなときに、主キーだけマスキングの対象外にすることを想定しています。

  • 文字列長を維持してマスクする

文字列型は文字列長を維持してランダムな文字列にマスクします。

  • 日付型はシフトする

日付型も完全にランダムに置き換えるのではなく、スクリプト開始時にランダムな秒数を決め、すべてのレコードでそのランダム秒数ぶん同じようにシフトするとしました。 レコードどうしの日付の相対値を維持します。

例えば、10:10 のレコードと 10:15 のレコードがあった場合、10:40 (+30秒) と 10:45 (こちらも+30秒)というような値に置換します。

もっとやれそうなことは、ありそうですが、日曜大工ではここまで・・・

MySQL Advent Calendar 2020

以上、日曜大工の紹介でした。明日の Advent Calendar は hamtsu47 さんです。

日本MySQLユーザ会 望年LT大会2020に参加してきた

先週、MyNA(日本MySQLユーザ会) 忘年LT大会 2020に参加して、飲みながらLTをネタにワイワイしてきました。

自分からは、ここ最近取り組んでいた、MySQL Shell のバックアップ機能のバグについてLTしました。 実際にどうやってデバッグしたか*1 など、ワイワイしました。

www2.slideshare.net

AUTO_INCREMENT vs UUID

@hmatsu47 さんが、「データベースを遅くするための8つの方法」で話題になっていた、Right Growing Index の件について、MySQL でのベンチマーク結果を共有してくれました。この記事ではシーケンス(AUTO_INCREMENT)は使うべきではないと述べられているのですが、私の経験では、MySQL の AUTO_INCREMENTで Right Growing Index の問題に遭遇したことがありません(逆に、Oracle RACでは、この記事に書かれていることは、ありそうだなと思います)。

この点を @hmatsu47 さんがベンチマークでも裏付けてくれました。安心して、AUTO_INCREMENTを使い続けられます。

speakerdeck.com

本番環境のMySQLを超越アップグレード成功した話

@tecklさんのオンプレからRDSへ移行+バージョンアップを成功させた話。 非常に難易度の高い移行作業を、すごい工夫で成功させた話でした・・・いろんな試行錯誤があったんだろうなと感じさせるお話でした。LTではなく、ぜひ、別の機会に詳細を聞きたいお話でした。

この話の流れで、「パーティションってDROP PARTITIONしたい時ぐらいしか使いどころないよねぇ(その要件がないなら使わない方が良い)」っていう話をしました。

いまは2006年! 書籍『超極める!MySQL』が出ます!

@sakaik さんが、今が2006年の体で話すLT。 坂井さんが「今はーーー、あ、今は2006年だから、今って言っちゃいけないw」と一人で乗りツッコミしてる感じで発表してたのが面白かったです(笑) sakaik.hateblo.jp

ハイパフォーマンスレプリケーションの新アイデア

@tom__bo さん

セミ同期レプリケーションで、Durability を犠牲にしてパフォーマンス向上を狙うアイデアの発表。 セミ同期レプリケーションであれば、レプリカ側に必ずデータが同期されているので、マスターの Durability(耐久性)は犠牲(sync_binlog = off, innodb_flush_log_at_trx_commit = 0)にしても良いよねと。

まとめ

楽しかったです!@sakaik さん企画ありがとうございましたm(__)m

*1:地道に printf でクエリを出して、それを手打ちして、再現性を確かめました