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を参考にさせて頂きました。この記事のSQLは ROW_FORMAT=COMPACT
用のSQLです。
ROW_FORMAT=DYNAMIC
用に上記の可変長カラムのオーバーフローページを考慮して微修正しました。
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.cc
の dict_index_too_big_for_tree
でCREATE 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 で実行計画を詳細に表示してみます。
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 のエントリーです。
問題を再現させるためにバイナリログが必要
MySQL は OSS です。誰でも無料で自由に使うことができます。
一方、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 */
適当な文字に置換するだけであれば、非常に簡単です。しかし、それではデバッグには向かないでしょう。
デバッグに必要な要素を可能な限り保持しつつ、マスクする必要があります。
ツールを作ってみた
バイナリログをマスキングするスクリプトを書いてみました。
これが、
### 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を使い続けられます。
本番環境のMySQLを超越アップグレード成功した話
@tecklさんのオンプレからRDSへ移行+バージョンアップを成功させた話。 非常に難易度の高い移行作業を、すごい工夫で成功させた話でした・・・いろんな試行錯誤があったんだろうなと感じさせるお話でした。LTではなく、ぜひ、別の機会に詳細を聞きたいお話でした。
この話の流れで、「パーティションってDROP PARTITIONしたい時ぐらいしか使いどころないよねぇ(その要件がないなら使わない方が良い)」っていう話をしました。
.@mita2 & .@yoku0825 「月単位でDROP PARTITION以外はあんまりパーティショニングお勧めする気は出ない…」 合意できた(・∀・)人(・∀・) #mysql_jp
— yoku0825 (@yoku0825) 2020年12月11日
いまは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/cs
は Case [In]sensitive
の略で、as
は Acent Sensiteve
、ks
は Katakana 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 さんです!
- 2015 qiita.com
- 2016 qiita.com
- 2017 mita2db.hateblo.jp
- 2018 mita2db.hateblo.jp
- 2019 mita2db.hateblo.jp
参考書籍
MySQL ヒストグラムはバックアップされるか
MySQL 8.0 でヒストグラム統計が追加されました。従来、MySQLはデータが均等に分布していると仮定し、実行計画を組み立てていました。 ヒストグラムを使えば正確なデータの分布に基づいて、より最適な実行計画が選択されるようになります。
ヒストグラムは 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)
mysqldump
や mysqlsh
で論理バックアップを取得した場合、どうなるんでしょうか?
- mysqldump
ヒストグラムの定義自体はバックアップされるようです(当たり前)。 取得したヒストグラム統計はバックアップをインポートするときに再度、取得しなおされます。
$ mysqldump -uroot t t | grep HISTOGRAM /*!80002 ANALYZE TABLE `t` UPDATE HISTOGRAM ON `val` WITH 100 BUCKETS */;
- mysqlsh util.dumpTable
MySQL Shell もちゃんと対応してますね。JSONにhistograms
が含まれています。
$ 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
は、vmstat
や ps
といった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 STATUS
か SHOW 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
の結果から原因を見つけることができるでしょう。