mita2 database life

主にMySQLに関するメモです

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 でクエリを出して、それを手打ちして、再現性を確かめました

MySQL 8.0 でも utf8mb4_general_ci を使い続けたい僕らは

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

照合順序(COLLATION)とは

照合順序は文字列の比較やソート順のルールのことです。各キャラクタセットごとに照合順序が定義されています。

-- SHOW COLLATIONS で一覧が見れる
mysql> SHOW COLLATIONS;
+----------------------------+----------+-----+---------+----------+---------+---------------+
| Collation                  | Charset  | Id  | Default | Compiled | Sortlen | Pad_attribute |
+----------------------------+----------+-----+---------+----------+---------+---------------+
| armscii8_bin               | armscii8 |  64 |         | Yes      |       1 | PAD SPACE     |
| armscii8_general_ci        | armscii8 |  32 | Yes     | Yes      |       1 | PAD SPACE     |
| ascii_bin                  | ascii    |  65 |         | Yes      |       1 | PAD SPACE     |
| ascii_general_ci           | ascii    |  11 | Yes     | Yes      |       1 | PAD SPACE     |
| big5_bin                   | big5     |  84 |         | Yes      |       1 | PAD SPACE     |
| big5_chinese_ci            | big5     |   1 | Yes     | Yes      |       1 | PAD SPACE     |
| binary                     | binary   |  63 | Yes     | Yes      |       1 | NO PAD        |
<snip>

MySQL 8.0 で、utf8mb4 の照合順序が増えました。以下の表で太字にしたものが、新規に追加されたものです。各文字列が、同一と扱われる場合は、○としています。 ci/csCase [In]sensitive の略で、asAcent SensiteveksKatakana Sensitive の略です。

COLLATION A、a はは、ぱぱ はは、ハハ びょういん、びよういん 🍣、🍺 +、+
utf8mb4_bin × × × × × ×
utf8mb4_0900_bin × × × × × ×
utf8mb4_unicode_ci
utf8mb4_general_ci × × × ×
utf8mb4_unicode_520_ci ×
utf8mb4_0900_ai_ci ×
utf8mb4_0900_as_ci × ×
utf8mb4_ja_0900_as_cs × × × ×
utf8mb4_ja_0900_as_cs_ks × × × × ×

アルファベットの大文字・小文字を区別しない要件で、どれが選ばれそうか・・・ utf8mb4_0900_as_ci は「びょういん」「びよういん」が同一と扱われてしまい、いまいちに感じます。

そもそも、日本語の文字列比較やソート結果を網羅的に精査するのは現実的に可能なんでしょうか(上記の表以外にも考えないといけない、パターンがありそうです)。日本語には異字体・長音記号・漢数字・・・ちょっと思いつくだけでも、扱いに悩みそうな要素が多くあります。

絵文字が区別できないとは言え、utf8mb4_general_ci にはずっと使ってきた実績と安心があります。 MySQL 8.0 でも utf8mb4_general_ci を 引き続き使うケースが多いのではないでしょうか。

MySQL 8.0 で utf8mb4_general_ci を使うときの注意点

ALTER TABLE CONVERT TO 時に COLLATION の指定が必要

MySQL 8.0 で utf8mb4 のデフォルトの照合順序が utf8mb4_general_ci から utf8mb4_0900_as_ci に変更になりました。

あわせて、従来の3バイトUTF8、utf8(mb3) は deprecated になっています。 utf8mb4 に変換するときに COLLATE を明示的に指定しないと、utf8_general_ci から utf8mb4_0900_ai_ci へとテーブルのデフォルト照合順序になってしまいます。

mysql> SELECT * FROM utf8t WHERE c1 = "ぱぱ";
Empty set (0.00 sec)

mysql> ALTER TABLE utf8t CONVERT TO CHARACTER SET 'utf8mb4';
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 「ぱぱ」で「はは」がヒットしてしまう
mysql> SELECT * FROM utf8t WHERE c1 = "ぱぱ";
+----+--------+
| pk | c1     |
+----+--------+
|  1 | はは   |
+----+--------+
1 row in set (0.00 sec)

mysql> SHOW CREATE TABLE utf8t \G
*************************** 1. row ***************************
       Table: utf8t
Create Table: CREATE TABLE `utf8t` (
  `pk` bigint unsigned NOT NULL AUTO_INCREMENT,
  `c1` varchar(255) DEFAULT NULL,
  UNIQUE KEY `pk` (`pk`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

このように、COLLATE を指定してALTERする必要があります。

mysql> ALTER TABLE utf8t CONVERT TO CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_general_ci';
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>  SHOW CREATE TABLE utf8t \G
*************************** 1. row ***************************
       Table: utf8t
Create Table: CREATE TABLE `utf8t` (
  `pk` bigint unsigned NOT NULL AUTO_INCREMENT,
  `c1` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  UNIQUE KEY `pk` (`pk`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
1 row in set (0.00 sec)

SET NAMES では COLLATE の指定が必要

同様に、SET NAMES で照合順序を明示的に指定していない場合、MySQL 8.0 からは utf8mb4_0900_as_ci が使われてしまいます。

# MySQL 8.0 以降は utf8mb4_0900_as_ci が使われる
mysql> SET NAMES utf8mb4;

# MySQL 8.0 以降は 明示的に utf8mb4_general_ci を指定する必要がある。
mysql> SET NAMES utf8mb4 COLLATE utf8mb4_general_ci;

過去のMySQL Advent Calendar

気付けば、MySQL Advent Calendar を 6年も続けてました・・・。時が経つのは早い・・・。 明日は、lhfukamachi さんです!

参考書籍

MySQL ヒストグラムはバックアップされるか

MySQL 8.0 でヒストグラム統計が追加されました。従来、MySQLはデータが均等に分布していると仮定し、実行計画を組み立てていました。 ヒストグラムを使えば正確なデータの分布に基づいて、より最適な実行計画が選択されるようになります。

yakst.com

ヒストグラムANALIZE TABLE 〜 UPDATE HISTOGRAM で追加します。そして、information_schema.column_statistics を参照すると、ヒストグラムが追加されていることが確認できます。

mysql> ANALYZE TABLE t UPDATE HISTOGRAM ON val;
+-------+-----------+----------+------------------------------------------------+
| Table | Op        | Msg_type | Msg_text                                       |
+-------+-----------+----------+------------------------------------------------+
| t.t   | histogram | status   | Histogram statistics created for column 'val'. |
+-------+-----------+----------+------------------------------------------------+
1 row in set (0.06 sec)

mysql> SELECT * FROM  information_schema.column_statistics \G
*************************** 1. row ***************************
SCHEMA_NAME: t
 TABLE_NAME: t
COLUMN_NAME: val
  HISTOGRAM: {"buckets": [[93, 139, 0.009009009009009009, 3], [153, 326, 0.021021021021021023, 4],〜
1 row in set (0.00 sec)

ヒストグラム を追加しても、CREATE TABLE文 には、変化がないようです。

mysql> SHOW CREATE TABLE t \G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `pk` bigint unsigned NOT NULL AUTO_INCREMENT,
  `val` int DEFAULT NULL,
  UNIQUE KEY `pk` (`pk`)
) ENGINE=InnoDB AUTO_INCREMENT=334 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
1 row in set (0.00 sec)

mysqldumpmysqlsh で論理バックアップを取得した場合、どうなるんでしょうか?

  • mysqldump

ヒストグラムの定義自体はバックアップされるようです(当たり前)。 取得したヒストグラム統計はバックアップをインポートするときに再度、取得しなおされます。

$ mysqldump -uroot t t | grep HISTOGRAM
/*!80002 ANALYZE TABLE `t` UPDATE HISTOGRAM ON `val` WITH 100 BUCKETS */;
  • mysqlsh util.dumpTable

MySQL Shell もちゃんと対応してますね。JSONhistograms が含まれています。

$ cat t@t.json
{
    "options": {
        "schema": "t",
        "table": "t",
        "columns": [
            "pk",
            "val"
        ],
        "primaryIndex": "",
        "compression": "zstd",
        "defaultCharacterSet": "utf8mb4",
        "fieldsTerminatedBy": "\t",
        "fieldsEnclosedBy": "",
        "fieldsOptionallyEnclosed": false,
        "fieldsEscapedBy": "\\",
        "linesTerminatedBy": "\n"
    },
    "triggers": [],
    "histograms": [
        {
            "column": "val",
            "buckets": 100
        }
    ],
    "includesData": true,
    "includesDdl": true,
    "extension": "tsv.zst",
    "chunking": true
}

MySQL 8.0 より前の mysqldump を使うとヒストグラムは移植されない

ヒストグラムMySQL 8.0 の機能なので、MySQL 5.7 の mysqldump で 8.0のテーブルをダンプするとヒストグラムの定義は抜け落ちてしまいます。

$ mysqldump --version
mysqldump  Ver 10.13 Distrib 5.7.31, for Linux (x86_64)

$ mysqldump -ubk -pPassword -h mysql80 t t | grep HISTO
(何も出力されない)

来週はMySQLユーザ会会

来週 11/25(水) は MyNA会ですね!楽しみです。 mysql.connpass.com

MySQL Lock wait timeout exceeded が発生している現場を押さえる

pt-stalk は、vmstatpsといったOSから見える情報や SHOW PROCESSLIST SHOW GLOBAL STATUSといったDBの情報など、MySQLのトラブルシュートに必要な情報をごっそり収集するツールです。

$ sudo ls -l /var/lib/pt-stalk
total 1428
-rw-r--r--. 1 root root  13260 11月 19 06:15 2020_11_19_06_15_10-df
-rw-r--r--. 1 root root    121 11月 19 06:15 2020_11_19_06_15_10-disk-space
-rw-r--r--. 1 root root   5730 11月 19 06:15 2020_11_19_06_15_10-diskstats
-rw-r--r--. 1 root root     22 11月 19 06:15 2020_11_19_06_15_10-hostname
-rw-r--r--. 1 root root   4714 11月 19 06:15 2020_11_19_06_15_10-innodbstatus1
-rw-r--r--. 1 root root   4715 11月 19 06:15 2020_11_19_06_15_10-innodbstatus2
-rw-r--r--. 1 root root  65640 11月 19 06:15 2020_11_19_06_15_10-interrupts
-rw-r--r--. 1 root root  15624 11月 19 06:15 2020_11_19_06_15_10-lock-waits
-rw-r--r--. 1 root root    845 11月 19 06:15 2020_11_19_06_15_10-log_error
-rw-r--r--. 1 root root   2724 11月 19 06:15 2020_11_19_06_15_10-lsof
-rw-r--r--. 1 root root  39780 11月 19 06:15 2020_11_19_06_15_10-meminfo
-rw-r--r--. 1 root root    121 11月 19 06:15 2020_11_19_06_15_10-mutex-status1
-rw-r--r--. 1 root root    121 11月 19 06:15 2020_11_19_06_15_10-mutex-status2
-rw-r--r--. 1 root root  22145 11月 19 06:15 2020_11_19_06_15_10-mysqladmin
-rw-r--r--. 1 root root  30210 11月 19 06:15 2020_11_19_06_15_10-netstat
-rw-r--r--. 1 root root  42810 11月 19 06:15 2020_11_19_06_15_10-netstat_s
-rw-r--r--. 1 root root   3052 11月 19 06:15 2020_11_19_06_15_10-opentables1
-rw-r--r--. 1 root root   3052 11月 19 06:15 2020_11_19_06_15_10-opentables2
-rw-r--r--. 1 root root   8439 11月 19 06:15 2020_11_19_06_15_10-output
-rw-r--r--. 1 root root  10483 11月 19 06:15 2020_11_19_06_15_10-pmap
-rw-r--r--. 1 root root  20060 11月 19 06:15 2020_11_19_06_15_10-processlist
-rw-r--r--. 1 root root  39388 11月 19 06:15 2020_11_19_06_15_10-procstat
-rw-r--r--. 1 root root  73146 11月 19 06:15 2020_11_19_06_15_10-procvmstat
-rw-r--r--. 1 root root  10055 11月 19 06:15 2020_11_19_06_15_10-ps
-rw-r--r--. 1 root root 332400 11月 19 06:15 2020_11_19_06_15_10-slabinfo
-rw-r--r--. 1 root root    660 11月 19 06:15 2020_11_19_06_15_10-slave-status
-rw-r--r--. 1 root root  32799 11月 19 06:15 2020_11_19_06_15_10-sysctl
-rw-r--r--. 1 root root   9648 11月 19 06:15 2020_11_19_06_15_10-top
-rw-r--r--. 1 root root  72480 11月 19 06:15 2020_11_19_06_15_10-transactions
-rw-r--r--. 1 root root    381 11月 19 06:15 2020_11_19_06_15_10-trigger
-rw-r--r--. 1 root root  12789 11月 19 06:15 2020_11_19_06_15_10-variables
-rw-r--r--. 1 root root   2785 11月 19 06:15 2020_11_19_06_15_10-vmstat
-rw-r--r--. 1 root root    327 11月 19 06:15 2020_11_19_06_15_10-vmstat-overall

データベースを定期的に監視し、指定したトリガーが発動したタイミングで、情報収集を行います。 事前に pt-stalk を仕掛けておくことで、障害の現場を押さえることができます。

例)実行状態のコネクションが100を超えたら何かが起きてるので情報を取る

$ pt-stalk --function status --variable Threads_running --threshold=100

SHOW GLOBAL STATUSSHOW PROCCESSLIST の結果をトリガーの条件とすることができます。 それぞれ、--function status--function processlist と指定します。

--function status では ステータスの値が閾値を超えたら、--function processlist では条件を満たすプロセス(接続)数が閾値を超えたら情報収集が発動します。

カスタムトリガーを設定してロック待ち発生現場を押さえる

--functionシェルスクリプトを指定することで、トリガー条件をカスタマイズできます。シェルスクリプトで定義した trg_plugin 関数がpt-stalkから定期的に呼び出されます。

ロック待ちの時間をトリガーの条件としてみます。

$ cat locktime.sh
trg_plugin() {
   mysql  $EXT_ARGV -N -B -e "SELECT IFNULL((SELECT MAX(now() - trx_wait_started) FROM information_schema.INNODB_TRX), 0) AS wt"
}

locktime.sh が出力する数値が --threshold を超えたら、情報収集が発動します。この例では、3秒以上ロックで待たされているクエリが発生したら、情報収集が走ります。

$ sudo pt-stalk --function locktime.sh --threshold=3

ロック待ちが発生している瞬間に、必ずしもロック待ちの原因となったクエリが実行状態とは限りませんが(未コミットのトランザクションがロックを持っている場合がある)、何度か繰り返し情報を集めていれば pt-stalk の結果から原因を見つけることができるでしょう。