2022-05-14

最近の MySQL の Internal Temporary Table ステータスとチューニング観点まとめ (version 8.0.28 版)

以下のエントリーでは、最近の MySQL の Internal Temporary Table の動作についてまとめました。

mita2db.hateblo.jp

条件によっては、領域がディスク上に確保される場合があり、その場合はパフォーマンスに影響がある可能性があります。今回はMySQLのステータスから Internal Temporary Table の状況を観測し、チューニングする方法をまとめます。

performance_schema.global_status / SHOW GLOBAL STATUS

mysql> SELECT * FROM performance_schema.global_status WHERE VARIABLE_NAME IN ('Created_tmp_tables', 'Created_tmp_disk_tables') ORDER BY VARIABLE_NAME \G

*************************** 1. row ***************************
 VARIABLE_NAME: Created_tmp_disk_tables
VARIABLE_VALUE: 0
*************************** 2. row ***************************
 VARIABLE_NAME: Created_tmp_tables
VARIABLE_VALUE: 10
2 rows in set (0.00 sec)

Created_tmp_tables

Internal Temporary Table が作成された回数
Internal Temporary Table のストレージエンジンや、その領域が確保される場所（メモリ or ディスク）に関係なく、Internal Temporary Table が使われるたびにインクリメントされる
Internal Temporary Table の利用が避けられないケースや、Internal Temporary Table を使う実行計画を選択したほうが高速な場合もある
- Created_tmp_tables の多寡だけではチューニングの余地があるかどうかは判断できない

Created_tmp_disk_tables

InnoDB ストレージエンジンで Internal Temporary Table が作成された回数
Temp Table エンジンが利用されたケースでは、インクリメントされない
(メモリではなく) ディスク上に作成されているため、このステータス値がどんどん増加している場合は、チューニングの余地があるかもしれない
- InnoDB に落ちないよう、tmp_table_size や temptable_max_ram を調整するとパフォーマンス改善するかも

performance_schema.memory_summary_global_by_event_name

mysql> SELECT * FROM performance_schema.memory_summary_global_by_event_name WHERE EVENT_NAME LIKE 'memory/temptable/physical%' \G
*************************** 1. row ***************************
                  EVENT_NAME: memory/temptable/physical_disk
                 COUNT_ALLOC: 0
                  COUNT_FREE: 0
   SUM_NUMBER_OF_BYTES_ALLOC: 0
    SUM_NUMBER_OF_BYTES_FREE: 0
              LOW_COUNT_USED: 0
          CURRENT_COUNT_USED: 0
             HIGH_COUNT_USED: 0
    LOW_NUMBER_OF_BYTES_USED: 0
CURRENT_NUMBER_OF_BYTES_USED: 0
   HIGH_NUMBER_OF_BYTES_USED: 0
*************************** 2. row ***************************
                  EVENT_NAME: memory/temptable/physical_ram
                 COUNT_ALLOC: 1
                  COUNT_FREE: 0
   SUM_NUMBER_OF_BYTES_ALLOC: 1048608
    SUM_NUMBER_OF_BYTES_FREE: 0
              LOW_COUNT_USED: 0
          CURRENT_COUNT_USED: 1
             HIGH_COUNT_USED: 1
    LOW_NUMBER_OF_BYTES_USED: 0
CURRENT_NUMBER_OF_BYTES_USED: 1048608
   HIGH_NUMBER_OF_BYTES_USED: 1048608
2 rows in set (0.00 sec)

Temp Table エンジンに関するステータス。

Created_tmp_tables や Created_tmp_disk_tables はシンプルに「作成された Internal Temporary Table の数」を示しますが、 memory/temptable/{physical_disk,physical_ram} はテーブル数ではなく、メモリやディスク上に領域が確保された回数やサイズを示します。単位が異なるので注意。

各ステータスの意味はオフィシャルドキュメント参照。

MySQL :: MySQL 8.0 リファレンスマニュアル :: 27.12.18.10 メモリーサマリーテーブル

memory/temptable/physical_ram
- Temp Table エンジンがメモリ上に確保した領域のステータス
- HIGH_NUMBER_OF_BYTES_USED が temptable_max_ram に近い場合、割り当てメモリが足りず、ファイル上に領域を確保しているかもしれない
  - memory/temptable/physical_disk と合わせて判断
  - ただし、一時的に足りなかっただけかもしれないので、CURRENT_NUMBER_OF_BYTES_USED を継続的に観測しておくと良い
memory/temptable/physical_disk
- Temp Table エンジンがファイル（ディスク）上に確保した領域のステータス
- HIGH_NUMBER_OF_BYTES_USED が 0 より大きい場合、割り当てメモリが足りずファイルに溢れていたことを示す
  - temptable_max_ram を引き上げるとパフォーマンスが改善するかもしれない
- HIGH_NUMBER_OF_BYTES_USED が temptable_max_mmap に近い場合、ファイル（mmap）領域も足りず、InnoDB が使われている可能性がある
  - Created_tmp_disk_tables と合わせて判断
- こちらも、CURRENT_NUMBER_OF_BYTES_USED を継続的に観測しておくと良い

Temp Table エンジンの領域は 2^N MB で拡張されていく

Temp Table エンジンの領域は、1,2,4,8,16...MB と 2の階乗単位で追加されていきます。このため、追加する領域のサイズに必要な十分な空きメモリがない場合、たとえ、メモリ領域が空いていても、ファイルに領域が確保される場合があります（memory/temptable/physical_ram の HIGH_NUMBER_OF_BYTES_USED が temptable_max_ram に達していなくても、ファイルが利用される可能性がある）

/* Concrete implementation of Block_size_policy, a type which controls how big
 * next Block of memory is going to be allocated by TempTable allocator.
 *
 * In particular, this policy will make TempTable allocator to grow the
 * block-size at exponential rate with upper limit of ALLOCATOR_MAX_BLOCK_BYTES,
 * which is 2 ^ ALLOCATOR_MAX_BLOCK_MB_EXP.
 *
 * E.g. allocation pattern may look like the following:
 *  1 MiB,
 *  2 MiB,
 *  4 MiB,
 *  8 MiB,
 *  16 MiB,
 *  32 MiB,
 *  ...,
 *  ALLOCATOR_MAX_BLOCK_BYTES,
 *  ALLOCATOR_MAX_BLOCK_BYTES
 *
 * In cases when block size that is being requested is bigger than the one which
 * is calculated by this policy, requested block size will be returned (even if
 * it grows beyond ALLOCATOR_MAX_BLOCK_BYTES).
 * */
struct Exponential_policy {
  /** Given the current number of allocated blocks by the allocator, and number
   * of bytes actually requested by the client code, calculate the new block
   * size.
   *
   * [in] Current number of allocated blocks.
   * [in] Number of bytes requested by the client code.
   * @return New block size. */
  static size_t block_size(size_t number_of_blocks, size_t n_bytes_requested) {
    size_t block_size_hint;
    if (number_of_blocks < ALLOCATOR_MAX_BLOCK_MB_EXP) {
      block_size_hint = (1ULL << number_of_blocks) * 1_MiB;
    } else {
      block_size_hint = ALLOCATOR_MAX_BLOCK_BYTES;
    }
    return std::max(block_size_hint, Block::size_hint(n_bytes_requested));
  }
};

2022-05-04

MySQLのスロークエリログにはエラーになったクエリが含まれる？

以前、エラーになったクエリがスロークエリログに落ちるかどうか試しました。

mita2db.hateblo.jp

このときは文法エラーも含め、エラー時もスロークエリログに記録されてましたが、MySQL 8.0.29, MySQL 5.7.38 で文法エラーは除外するよう挙動が変更されたようです。改めて、挙動を確認してみます。

MySQL 8.0.29, Statements that cannot be parsed (due, for example, to syntax errors) are no longer written to the slow query log.

実行時間でフィルターされないようlong_query_time=0 を設定して試します。

log_slow_extra=ON
long_query_time=0
slow_query_log=on
slow_query_log_file=/var/log/mysqld-slow.log

文法エラー

mysql>  INSERT INTO t1 HOGEHOGE;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'HOGEHOGE' at line 1

出力されませんでした。リリースノートに書かれている通りですね。

制約違反

mysql>  INSERT INTO t.t1 VALUES(1);
ERROR 1062 (23000): Duplicate entry '1' for key 't1.PRIMARY'

出力されました。ちゃんと、Errno が出るようになってる！以前、試したときは出力されてなかったです（どのバージョンで直ったんだろ）。

# Time: 2022-05-04T08:10:47.083268Z
# User@Host: root[root] @ localhost []  Id:    14
# Query_time: 0.000293  Lock_time: 0.000002 Rows_sent: 0  Rows_examined: 0 Thread_id: 14 Errno: 1062 Killed: 0 Bytes_received: 0 Bytes_sent: 53 Read_first: 0 Read_last: 0 Read_key: 0 Read_next: 0 Read_prev: 0 Read_rnd: 0 Read_rnd_next: 0 Sort_merge_passes: 0 Sort_range_count: 0 Sort_rows: 0 Sort_scan_count: 0 Created_tmp_disk_tables: 0 Created_tmp_tables: 0 Start: 2022-05-04T08:10:47.082975Z End: 2022-05-04T08:10:47.083268Z
SET timestamp=1651651847;
INSERT INTO t.t1 VALUES(1);

ロックタイムアウト

出力されました。こちらも、Errno が正しく出力されるようになってます。

# Time: 2022-05-04T08:22:02.224408Z
# User@Host: root[root] @ localhost []  Id:    19
# Query_time: 50.333232  Lock_time: 50.331265 Rows_sent: 0  Rows_examined: 0 Thread_id: 19 Errno: 1205 Killed: 0 Bytes_received: 0 Bytes_sent: 67 Read_first: 0 Read_last: 0 Read_key: 1 Read_next: 0 Read_prev: 0 Read_rnd: 0 Read_rnd_next: 0 Sort_merge_passes: 0 Sort_range_count: 0 Sort_rows: 0 Sort_scan_count: 0 Created_tmp_disk_tables: 0 Created_tmp_tables: 0 Start: 2022-05-04T08:21:11.891176Z End: 2022-05-04T08:22:02.224408Z
SET timestamp=1651652471;
UPDATE t.t1 SET pk = 100 WHERE pk = 1;

中断

mysql> SELECT SLEEP(10);
^C^C -- query aborted
+-----------+
| SLEEP(10) |
+-----------+
|         1 |
+-----------+
1 row in set (1.21 sec)

出力されました。Errno は 0 です。

# Time: 2022-05-04T08:13:53.342742Z
# User@Host: root[root] @ localhost []  Id:    15
# Query_time: 1.211280  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 1 Thread_id: 15 Errno: 0 Killed: 0 Bytes_received: 0 Bytes_sent: 57 Read_first: 0 Read_last: 0 Read_key: 0 Read_next: 0 Read_prev: 0 Read_rnd: 0 Read_rnd_next: 0 Sort_merge_passes: 0 Sort_range_count: 0 Sort_rows: 0 Sort_scan_count: 0 Created_tmp_disk_tables: 0 Created_tmp_tables: 0 Start: 2022-05-04T08:13:52.131462Z End: 2022-05-04T08:13:53.342742Z
SET timestamp=1651652032;
SELECT SLEEP(10);

2022-04-22

最近の MySQL の Internal Temporary Table 動作まとめ (version 8.0.28 版)

MySQL

8.0 のGA以降、Internal Temporary Table まわりは改良が重ねられきました・・・ネットの記事では既に古い内容になってしまっているものもあり、改めて最新の挙動を確認して整理してみました*1。

なお、MySQL 8.0 の Internal Temporary Table の動作は @kentarokitagawa さんの以下の記事も詳しいです。こちらの記事を読んでから本エントリーを読むとより理解しやすいでしょう。

gihyo.jp

Internal Temporary Table

MySQL は、ORDER BY や UNION を含むクエリを処理するため、内部的に一時的なテーブル（Internal Temporary Table) を作成します。 Internal Temporary Table はクエリの実行が終わると、自動的に削除されます。

Internal Temporary Table のストレージエンジンは、Temp Table エンジンもしくは、InnoDB です。どちらのストレージエンジンが利用されるかは、後述の条件によって動的に決まります。

TempTable エンジン

TempTable エンジンは MySQL 8.0 で登場した Internal Temporary Table 専用のストレージエンジンです。 Internal Temporary Table を効率よく処理するための工夫がされています。

割り当て可能なメモリがある限りは、メモリ上に領域を確保し、足りない分はファイル（ディスク）上に領域を拡張します。つまり、１つの Internal Temporary Table がメモリとファイル両方にまたがって、確保され得るのです。

メモリ上に確保した領域にファイル上に確保した領域を足さなければなりません。そのため、ファイルをメモリのように扱うために、mmap を用いて実装されています。賢いですねぇ。

ファイル上に確保する領域の最大サイズは temptable_max_mmap パラメータで設定します。

このように、メモリとファイルを使ったハイブリッドな構成である旨は、公式マニュアルの以下の記述からも読み取ることができます。

dev.mysql.com

Data is never moved between RAM and temporary files, within RAM, or between temporary files. (一度 RAM上に格納したデータは、ファイルには書き出されない)

New data is stored in RAM if space becomes available within the limit defined by temptable_max_ram. Otherwise, new data is stored in temporary files. (新たなデータは、temptable_max_ram に収まっていれば、RAM上に格納される。そうでなければ、ファイルに格納される。)

If space becomes available in RAM after some of the data for a table is written to temporary files, it is possible for the remaining table data to be stored in RAM. (いくつかのデータをファイルに書いたのち、RAMに空き領域ができた場合、残りのデータはRAM上に格納される。)

InnoDB

以下で説明する特定の条件に該当した場合、Internal Temporary Table では InnoDB が使われます。 InnoDB が使われると、全てディスク上に確保されるため、パフォーマンス面ではマイナスです。

InnoDB で作られた、Internal Temporary Table はクエリ実行中に #innodb_tempディレクトリにファイルとして見えます。

$ sudo ls -ahl /var/lib/mysql/#innodb_temp
total 4.1G
drwxr-x---. 2 mysql mysql  187 Apr 19 17:07 .
drwxr-x--x. 8 mysql mysql 4.0K Apr 19 17:07 ..
-rw-r-----  1 mysql mysql 4.1G Apr 22 12:46 temp_10.ibt
-rw-r-----  1 mysql mysql  80K Apr 19 17:07 temp_1.ibt
-rw-r-----  1 mysql mysql  80K Apr 19 17:07 temp_2.ibt
-rw-r-----  1 mysql mysql  80K Apr 19 17:07 temp_3.ibt
-rw-r-----  1 mysql mysql  80K Apr 19 17:07 temp_4.ibt
-rw-r-----  1 mysql mysql  80K Apr 19 17:07 temp_5.ibt
-rw-r-----  1 mysql mysql  80K Apr 19 17:07 temp_6.ibt
-rw-r-----  1 mysql mysql  80K Apr 19 17:07 temp_7.ibt
-rw-r-----  1 mysql mysql  80K Apr 19 17:07 temp_8.ibt
-rw-r-----  1 mysql mysql  80K Apr 19 17:07 temp_9.ibt

まとめ

MySQL 8.0.28 時点での挙動：

tmp_table_size の制限にひっかかると、InnoDB になる
temptable_max_ram の制限を超えると、ファイル上に領域が確保され始める
さらに、temptable_max_mmap で指定したサイズも超えると、InnoDB になる

パフォーマンスを意識するなら、tmp_table_size や temptable_max_ram を引き上げて、可能な限りメモリ上で処理されるようにすると良いでしょう。

*1:version 8.0.28 時点では、今回記載した挙動ですが、今後のバージョンでまた挙動が変わってるかもしれません

2022-03-25

MySQL アメリカのサマータイムが恒久化されたらやることになる作業

MySQL

サマータイムが終わらない？

アメリカでサマータイムを恒久化する動きがあるようです。どれぐらい現実化する可能性があるのかわかりませんが、仮に、決定された場合、どのような作業が必要になるのか調べておきます。

下院を通過してバイデン大統領が署名すると、2023年の春に夏時間になったらずっとそのまま、秋になっても標準時には戻らなくなります。

news.yahoo.co.jp

Linuxのタイムゾーンの管理

日本でも（結局、実現はしませんでしたが）「東京オリンピックの期間はサマータイムを採用しよう」という話があったように、時刻の定義は変遷します。言い換えると、あるタイムゾーンにおける、「UTCからの時差」がタイミングによって変わるということです。

各タイムゾーンにおける時刻の定義の履歴は zoneinfo として定義されています。この定義があるので、タイムゾーン間の時刻の変換が正しくできるわけですね。

https://www.iana.org/time-zones

zoneinfo は CentOS であれば、tzdata パッケージで管理されています。

MySQL のタイムゾーンの管理

MySQL でタイムゾーンを扱う場合、あらかじめ、mysql_tzinfo_to_sql コマンドで zoneinfo をSQLに変換し、DBにロードしておく必要があります。

$  mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql

タイムゾーンの定義を変更する

フィジーで 2021〜2022 年にかけての夏時間の適用が停止される事態があったようです。これを使ってタイムゾーンの更新のテストしてみます。ちなみに、南太平洋の国なので、12月が夏時間になってます。

この夏時間の変更は、tzdata-2021d で取り込まれました。 tzdata-2020a には反映されていません。このデータだと、2021年の時刻も他の年と同じ時刻になってます。

$ rpm -qa | grep tzdata
tzdata-2020a-1.el7.noarch

$ date --date='TZ="Pacific/Fiji" 2020-12-25 14:00:00'
2020年 12月 25日 金曜日 10:00:00 JST
$ date --date='TZ="Pacific/Fiji" 2021-12-25 14:00:00'
2021年 12月 25日 土曜日 10:00:00 JST
$ date --date='TZ="Pacific/Fiji" 2022-12-25 14:00:00'
2022年 12月 25日 日曜日 10:00:00 JST
$ date --date='TZ="Pacific/Fiji" 2023-12-25 14:00:00'
2023年 12月 25日 月曜日 10:00:00 JST

tzdata をアップデートします。

$ sudo yum update tzdata
$ rpm -qa | grep tzdata
tzdata-2022a-1.el7.noarch

新しい tzdata-2022a の定義であれば、2021年だけ夏時間が適用されなくなっていることが確認できました。

$ date --date='TZ="Pacific/Fiji" 2020-12-25 14:00:00'
2020年 12月 25日 金曜日 10:00:00 JST
$ date --date='TZ="Pacific/Fiji" 2021-12-25 14:00:00'
2021年 12月 25日 土曜日 11:00:00 JST # 2021年だけ夏時間が停止されてる
$ date --date='TZ="Pacific/Fiji" 2022-12-25 14:00:00'
2022年 12月 25日 日曜日 10:00:00 JST
[$ date --date='TZ="Pacific/Fiji" 2023-12-25 14:00:00'
2023年 12月 25日 月曜日 10:00:00 JST

これをMySQLにも反映します。タイムゾーンの情報はキャッシュされているようで、DBにロードしたあと再起動が必要でした。

$ mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql
$ sudo systemctl restart mysqld

--- 2021
mysql> SELECT CONVERT_TZ('2021-12-25 10:00:00', 'Asia/Tokyo', 'Pacific/Fiji');
+-----------------------------------------------------------------+
| CONVERT_TZ('2021-12-25 10:00:00', 'Asia/Tokyo', 'Pacific/Fiji') |
+-----------------------------------------------------------------+
| 2021-12-25 13:00:00                                             |
+-----------------------------------------------------------------+
1 row in set (0.00 sec)

--- 2022
mysql> SELECT CONVERT_TZ('2022-12-25 10:00:00', 'Asia/Tokyo', 'Pacific/Fiji');
+-----------------------------------------------------------------+
| CONVERT_TZ('2022-12-25 10:00:00', 'Asia/Tokyo', 'Pacific/Fiji') |
+-----------------------------------------------------------------+
| 2022-12-25 14:00:00                                             |
+-----------------------------------------------------------------+
1 row in set (0.00 sec)

まとめ

タイムゾーンの定義が変わったら、tzdata をアップデートして、mysql_tzinfo_to_sql でロードしなおす

2022-03-20

MySQL collation_server を変えたつもりが変わってなかった話

MySQL

character_set_server と collation_server

これらのパラメータは、データベースを作成する際に、何もキャラクタセットや照合順序を明示的に指定しなかった場合に、採用されるキャラクタセット・照合順序です（ちなみにテーブル作成時に何も指定しなかった場合は、テーブルはスキーマのキャラクタセットや照合順序を引き継く）。

mysql> show global variables like 'character_set_server';
+----------------------+---------+
| Variable_name        | Value   |
+----------------------+---------+
| character_set_server | utf8mb4 |
+----------------------+---------+
1 row in set (0.00 sec)

mysql> show global variables like 'collation_server';
+------------------+--------------------+
| Variable_name    | Value              |
+------------------+--------------------+
| collation_server | utf8mb4_general_ci |
+------------------+--------------------+
1 row in set (0.01 sec)

-- キャラクタセット・照合順序を指定しない
mysql> create database d1;
Query OK, 1 row affected (0.01 sec)


-- character_set_server と collation_server の値が引き継がれてる
mysql> show create database d1 \G
*************************** 1. row ***************************
       Database: d1
Create Database: CREATE DATABASE `d1` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci */ /*!80016 DEFAULT ENCRYPTION='N' */
1 row in set (0.00 sec)

collation_server を変えたつもりが変わってなかった...

character_set_server → collation_server の順番でSET GLOBAL 指定すれば問題ありませんが、

mysql> SET GLOBAL character_set_server=utf8mb4, collation_server=utf8mb4_bin;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GLOBAL VARIABLES LIKE 'collation_server';
+------------------+-------------+
| Variable_name    | Value       |
+------------------+-------------+
| collation_server | utf8mb4_bin |
+------------------+-------------+
1 row in set (0.00 sec)

逆の順番だと、collation_server の変更がなかったことになってしまいます。

mysql> SET GLOBAL collation_server=utf8mb4_bin, character_set_server=utf8mb4;
Query OK, 0 rows affected (0.00 sec)

-- utf8mb4_bin になってない
mysql> SHOW GLOBAL VARIABLES LIKE 'collation_server';
+------------------+--------------------+
| Variable_name    | Value              |
+------------------+--------------------+
| collation_server | utf8mb4_0900_ai_ci |
+------------------+--------------------+
1 row in set (0.00 sec)

character_set_server を変えると、collation_server が連動して、各キャラクタセットのデフォルトの照合順序にリセットされるみたい。キャラクタセットと照合順序は使える組み合わせが決まっているため、collation_server が character_set_server の値に連動する仕様なんでしょう。

「character_set_server を今と同じ値に SET GLOBAL しても何も起きないよね〜」と思って雑なスクリプトを書いてたりすると、意図せず character_set_server がリセットされる。

mysql> SHOW GLOBAL VARIABLES LIKE 'collation_server';
+------------------+-------------+
| Variable_name    | Value       |
+------------------+-------------+
| collation_server | utf8mb4_bin |
+------------------+-------------+
1 row in set (0.00 sec)

mysql> SET GLOBAL character_set_server=@@character_set_server;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GLOBAL VARIABLES LIKE 'collation_server';
+------------------+--------------------+
| Variable_name    | Value              |
+------------------+--------------------+
| collation_server | utf8mb4_0900_ai_ci |
+------------------+--------------------+
1 row in set (0.00 sec)

まとめ

character_set_server を変えると、collation_server が連動して、各キャラクタセットのデフォルトの照合順序にリセットされるよ！

2022-02-20

MySQL 8.0.28 で performance_schema に CPU_TIME が入った

MySQL

MySQL 8.0.28 から performance_schema に CPU_TIME を記録できるようになりました。

これまでも実行時間は performance_schema から確認できていました。ただ、実行時間が長いだけでは、そのクエリが必ずしもCPUに負荷をかけているクエリとは言いきれませんでした。今回の機能追加で、CPUに負荷をかけているクエリがバシっと performance_schema から確認できるようになったのは嬉しいですね(欲を言うと、CPU_TIME をスロークエリログにも出してほしいなぁ)。

デフォルトではOFFになっています。以下の設定を my.cnf に記述し、有効化します。

performance-schema-consumer-events-statements-cpu=ON

sys.statement_analysis にも cpu_latency が追加され、レスポンスタイムに占めるCPU時間が確認できます。この例では total_time = cpu_latency になってます。テーブルが小さく、ほとんどIOの発生しないクエリだったため、このような結果になってます。

mysql> select * from sys.statement_analysis limit 1\G
*************************** 1. row ***************************
            query: SELECT SUM ( `salary` ) FROM `salaries`
               db: employees
        full_scan: *
       exec_count: 5
        err_count: 0
       warn_count: 0
    total_latency: 2.12 s
      max_latency: 523.29 ms
      avg_latency: 424.49 ms
     lock_latency: 17.00 us
      cpu_latency: 2.12 s
<snip>
1 row in set (0.00 sec)

total_letency != cpu_latency となりそうな、IOバウンドなクエリで試してみます*1。 7.14 min のうち、cpu_latency が 15.80 s と IOに時間がかかったことが、しっかり読み取れました。

mysql> SELECT * FROM sbtest.sbtest1 WHERE c = 'X';
Empty set (7 min 5.33 sec)

mysql> select * from  sys.statement_analysis LIMIT 1\G
*************************** 1. row ***************************
            query: SELECT * FROM `sbtest` . `sbtest1` WHERE `c` = ?
               db: NULL
        full_scan: *
       exec_count: 2
        err_count: 1
       warn_count: 0
    total_latency: 7.14 min
      max_latency: 7.09 min
      avg_latency: 3.57 min
     lock_latency: 19.00 us
      cpu_latency: 15.80 s
<snip>

実装

CPU時間は clock_gettime() を利用して取得してる。

mysys/my_rdtsc.cc

/**
  THREAD_CPU timer.
  Expressed in nanoseconds.
*/
ulonglong my_timer_thread_cpu(void) {
#if defined(HAVE_CLOCK_GETTIME) && defined(CLOCK_THREAD_CPUTIME_ID)
  {
    struct timespec tp;
    clock_gettime(CLOCK_THREAD_CPUTIME_ID, &tp);
    return (ulonglong)tp.tv_sec * 1000000000 + (ulonglong)tp.tv_nsec;
  }

*1:差が顕著に出るようにVMのIO帯域に制限かけました

2022-01-28

MySQL Illegal mix of collations エラーが出る・出ないまとめ

MySQL

Illegal mix of collations

Illegal mix of collations エラーは異なる照合順序で結合や比較を行った場合に発生するエラーです。

mysql> SELECT CONCAT(_utf8mb4 'A' COLLATE utf8mb4_bin, _utf8mb4 'B' COLLATE utf8mb4_unicode_ci);
ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_bin,EXPLICIT) and (utf8mb4_unicode_ci,EXPLICIT) for operation 'concat'

mysql> SELECT _utf8mb4 'A' COLLATE utf8mb4_bin = _utf8mb4 'B' COLLATE utf8mb4_unicode_ci;
ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_bin,EXPLICIT) and (utf8mb4_unicode_ci,EXPLICIT) for operation '='

エラーが発生しない場合もある

照合順序が異なっていても、エラーにならないケースもあります。 latin1_swedish_ci と utf8mb4_unicode_ci と異なる照合順序で比較していますが、エラーなく実行することが出来てます。

mysql> SELECT _latin1 'A' COLLATE latin1_swedish_ci = _utf8mb4 'B' COLLATE utf8mb4_unicode_ci AS r;
+------+
| r    |
+------+
|    0 |
+------+
1 row in set (0.00 sec)

Illegal mix of collations エラーが発生する・しないまとめ

雑なコードを書いて、主な照合順序を総当たりでチェックしてみました*1。

name	sjis_japanese_ci	sjis_bin	ujis_japanese_ci	ujis_bin	utf8_general_ci	utf8_unicode_ci	utf8_bin	utf8mb4_general_ci	utf8mb4_unicode_ci	utf8mb4_bin	latin1_swedish_ci	latin1_bin	ascii_general_ci	ascii_bin
sjis_japanese_ci	-	ERR	ERR	ERR	OK	OK	OK	OK	OK	OK	ERR	ERR	ERR	ERR
sjis_bin	ERR	-	ERR	ERR	OK	OK	OK	OK	OK	OK	ERR	ERR	ERR	ERR
ujis_japanese_ci	ERR	ERR	-	ERR	OK	OK	OK	OK	OK	OK	ERR	ERR	ERR	ERR
ujis_bin	ERR	ERR	ERR	-	OK	OK	OK	OK	OK	OK	ERR	ERR	ERR	ERR
utf8_general_ci	OK	OK	OK	OK	-	ERR	ERR	OK	OK	OK	OK	OK	OK	OK
utf8_unicode_ci	OK	OK	OK	OK	ERR	-	ERR	OK	OK	OK	OK	OK	OK	OK
utf8_bin	OK	OK	OK	OK	ERR	ERR	-	OK	OK	OK	OK	OK	OK	OK
utf8mb4_general_ci	OK	OK	OK	OK	OK	OK	OK	-	ERR	ERR	OK	OK	OK	OK
utf8mb4_unicode_ci	OK	OK	OK	OK	OK	OK	OK	ERR	-	ERR	OK	OK	OK	OK
utf8mb4_bin	OK	OK	OK	OK	OK	OK	OK	ERR	ERR	-	OK	OK	OK	OK
latin1_swedish_ci	ERR	ERR	ERR	ERR	OK	OK	OK	OK	OK	OK	-	ERR	ERR	ERR
latin1_bin	ERR	ERR	ERR	ERR	OK	OK	OK	OK	OK	OK	ERR	-	ERR	ERR
ascii_general_ci	ERR	ERR	ERR	ERR	OK	OK	OK	OK	OK	OK	ERR	ERR	-	ERR
ascii_bin	ERR	ERR	ERR	ERR	OK	OK	OK	OK	OK	OK	ERR	ERR	ERR	-

utf8, utf8mb4 は他のキャラクタセットとの比較が可能

mysql> SELECT _utf8mb4"A" COLLATE utf8mb4_general_ci = _sjis"A" COLLATE sjis_japanese_ci;
+----------------------------------------------------------------------------+
| _utf8mb4"A" COLLATE utf8mb4_general_ci = _sjis"A" COLLATE sjis_japanese_ci |
+----------------------------------------------------------------------------+
|                                                                          1 |
+----------------------------------------------------------------------------+
1 row in set (0.00 sec)

ただし、同じ UTF8 の他の照合順序 (例：utf8mb4_general_ci vs utf8mb4_unicode_ci) とは比較できない

mysql> SELECT _utf8mb4"A" COLLATE utf8mb4_general_ci = _utf8mb4"A" COLLATE utf8mb4_unicode_ci;
ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_general_ci,EXPLICIT) and (utf8mb4_unicode_ci,EXPLICIT) for operation '='

一方、uft8, utf8mb4 以外はキャラクタセット・照合順序が一致していないと比較できない。

mysql> SELECT _sjis"A" COLLATE sjis_bin = _sjis"A" COLLATE sjis_japanese_ci;
ERROR 1267 (HY000): Illegal mix of collations (sjis_bin,EXPLICIT) and (sjis_japanese_ci,EXPLICIT) for operation '='

という、法則のようです。

*1:version 5.7.37 で試しました

mita2 database life

主にMySQLに関するメモです

最近の MySQL の Internal Temporary Table ステータスとチューニング観点まとめ (version 8.0.28 版)

performance_schema.global_status / SHOW GLOBAL STATUS

Created_tmp_tables

Created_tmp_disk_tables

performance_schema.memory_summary_global_by_event_name

Temp Table エンジンの領域は 2^N MB で拡張されていく

MySQLのスロークエリログにはエラーになったクエリが含まれる？

文法エラー

制約違反

ロックタイムアウト

中断

最近の MySQL の Internal Temporary Table 動作まとめ (version 8.0.28 版)

Internal Temporary Table

TempTable エンジン

InnoDB

関連するパラメータと各ストレージエンジンが使われる条件

temptable_max_ram

temptable_max_mmap

tmp_table_size

まとめ

MySQL アメリカのサマータイムが恒久化されたらやることになる作業

サマータイムが終わらない？

Linuxのタイムゾーンの管理

MySQL のタイムゾーンの管理

タイムゾーンの定義を変更する

まとめ

MySQL collation_server を変えたつもりが変わってなかった話

character_set_server と collation_server

collation_server を変えたつもりが変わってなかった...

まとめ

MySQL 8.0.28 で performance_schema に CPU_TIME が入った

実装

MySQL Illegal mix of collations エラーが出る・出ないまとめ

Illegal mix of collations

エラーが発生しない場合もある

Illegal mix of collations エラーが発生する・しないまとめ

performance_schema.global_status / SHOW GLOBAL STATUS

Created_tmp_tables

Created_tmp_disk_tables

performance_schema.memory_summary_global_by_event_name

Temp Table エンジンの領域は 2N MB で拡張されていく

文法エラー

制約違反

ロックタイムアウト

中断

Internal Temporary Table

TempTable エンジン

関連するパラメータと各ストレージエンジンが使われる条件

temptable_max_ram

temptable_max_mmap

tmp_table_size

まとめ

サマータイムが終わらない？

Linuxのタイムゾーンの管理

MySQL のタイムゾーンの管理

タイムゾーンの定義を変更する

まとめ

character_set_server と collation_server

collation_server を変えたつもりが変わってなかった...

まとめ

実装

Illegal mix of collations

エラーが発生しない場合もある

Illegal mix of collations エラーが発生する・しない まとめ

Temp Table エンジンの領域は 2^N MB で拡張されていく

Illegal mix of collations エラーが発生する・しないまとめ