2020/06/29

performance_schema.data_locks.ENGINE_LOCK_ID is 何

TL;DR

MySQL 8.0.20のInnoDBにおいては row->lock_trx_immutable_id ":" row->lock_space ":" row->lock_page ":" row->lock_rec ":" row->lock_immutable_id らしい
- https://github.com/mysql/mysql-server/blob/mysql-8.0.20/storage/innobase/trx/trx0i_s.cc#L1134-L1170
- trx0 i_s .ccにつながってるけどperformance_schemaからもこれを呼んでる
ちなみにこのENGINE_LOCK_ID(実体は pk_pos_data_lock::m_engine_lock_id ?)を真面目に実装しているのはInnoDBだけっぽく見える
- NDBCLUSTERは読んでない

そう思って見てみると、行単位でどこのページに乗っかってるのかとか調べられたりするかなあと思ったり思わなかったり。

mysql80 18> SELECT SUBSTRING_INDEX(engine_lock_id, ':', 1) AS lock_trx_id, SUBSTRING_INDEX(SUBSTRING_INDEX(engine_lock_id, ':', 2), ':', -1) AS space_id, SUBSTRING_INDEX(SUBSTRING_INDEX(engine_lock_id, ':', 3), ':', -1) AS page_id, SUBSTRING_INDEX(SUBSTRING_INDEX(engine_lock_id, ':', 4), ':', -1) AS record_id, SUBSTRING_INDEX(SUBSTRING_INDEX(engine_lock_id, ':', 5), ':', -1) AS lock_id, lock_data FR
OM data_locks WHERE lock_type = 'RECORD';
+-----------------+----------+---------+-----------+-----------------+------------------------+
| lock_trx_id     | space_id | page_id | record_id | lock_id         | lock_data              |
+-----------------+----------+---------+-----------+-----------------+------------------------+
| 140293334355320 | 166      | 4       | 1         | 140293227750848 | supremum pseudo-record |
| 140293334355320 | 166      | 4       | 2         | 140293227750848 | 1                      |
| 140293334355320 | 166      | 4       | 3         | 140293227750848 | 2                      |
| 140293334355320 | 166      | 4       | 4         | 140293227750848 | 3                      |
| 140293334355320 | 166      | 4       | 5         | 140293227750848 | 4                      |
| 140293334355320 | 166      | 4       | 6         | 140293227750848 | 5                      |
| 140293334355320 | 166      | 4       | 7         | 140293227750848 | 6                      |
+-----------------+----------+---------+-----------+-----------------+------------------------+
7 rows in set (0.00 sec)

mysql80 19> OPTIMIZE TABLE t1; -- これで↓のspace_idが変わった

mysql80 18> SELECT SUBSTRING_INDEX(engine_lock_id, ':', 1) AS lock_trx_id, SUBSTRING_INDEX(SUBSTRING_INDEX(engine_lock_id, ':', 2), ':', -1) AS space_id, SUBSTRING_INDEX(SUBSTRING_INDEX(engine_lock_id, ':', 3), ':', -1) AS page_id, SUBSTRING_INDEX(SUBSTRING_INDEX(engine_lock_id, ':', 4), ':', -1) AS record_id, SUBSTRING_INDEX(SUBSTRING_INDEX(engine_lock_id, ':', 5), ':', -1) AS lock_id, lock_data FROM data_locks WHERE lock_type = 'RECORD';
+-----------------+----------+---------+-----------+-----------------+------------------------+
| lock_trx_id     | space_id | page_id | record_id | lock_id         | lock_data              |
+-----------------+----------+---------+-----------+-----------------+------------------------+
| 140293334355320 | 167      | 4       | 1         | 140293227750848 | supremum pseudo-record |
| 140293334355320 | 167      | 4       | 2         | 140293227750848 | 1                      |
| 140293334355320 | 167      | 4       | 3         | 140293227750848 | 2                      |
| 140293334355320 | 167      | 4       | 4         | 140293227750848 | 3                      |
| 140293334355320 | 167      | 4       | 5         | 140293227750848 | 4                      |
| 140293334355320 | 167      | 4       | 6         | 140293227750848 | 5                      |
| 140293334355320 | 167      | 4       | 7         | 140293227750848 | 6                      |
+-----------------+----------+---------+-----------+-----------------+------------------------+
7 rows in set (0.00 sec)

↓i_sほにゃららのくせにp_sから呼ばれている図

(gdb) bt
+bt
#0  trx_i_s_create_lock_id (row=row@entry=0x7f4918052850,
    lock_id=lock_id@entry=0x7f4918052970 "139951965544648:1248:139951844968944", lock_id_size=lock_id_size@entry=105)
    at /home/yoku0825/mysql-8.0.20/storage/innobase/trx/trx0i_s.cc:1154
#1  0x000000000205a373 in print_record_lock_id (lock=lock@entry=0x7f4914013dd0, heap_no=heap_no@entry=2,
    lock_id=lock_id@entry=0x7f4918052970 "139951965544648:1248:139951844968944", lock_id_size=lock_id_size@entry=105)
    at /home/yoku0825/mysql-8.0.20/storage/innobase/handler/p_s.cc:519
#2  0x000000000205ac5a in Innodb_data_lock_iterator::scan_trx (this=this@entry=0x7f48c400ff40,
    container=container@entry=0x7f48c42ca828, with_lock_data=with_lock_data@entry=true, trx=trx@entry=0x7f491b3120c8,
    with_filter=with_filter@entry=false, filter_lock_immutable_id=filter_lock_immutable_id@entry=0, filter_heap_id=0)
    at /home/yoku0825/mysql-8.0.20/storage/innobase/handler/p_s.cc:804
#3  0x000000000205ae8f in Innodb_data_lock_iterator::scan_trx_list (this=this@entry=0x7f48c400ff40,
    container=container@entry=0x7f48c42ca828, with_lock_data=with_lock_data@entry=true, read_write=read_write@entry=true,
    trx_list=<optimized out>) at /home/yoku0825/mysql-8.0.20/storage/innobase/handler/p_s.cc:684
#4  0x000000000205b76a in scan (with_lock_data=true, container=0x7f48c42ca828, this=0x7f48c400ff40)
    at /home/yoku0825/mysql-8.0.20/storage/innobase/handler/p_s.cc:593
#5  Innodb_data_lock_iterator::scan (this=0x7f48c400ff40, container=0x7f48c42ca828, with_lock_data=<optimized out>)
    at /home/yoku0825/mysql-8.0.20/storage/innobase/handler/p_s.cc:572
#6  0x000000000249252b in table_data_locks::rnd_next() () at /home/yoku0825/mysql-8.0.20/storage/perfschema/table_data_locks.cc:191
#7  0x000000000243fc6e in ha_perfschema::rnd_next (this=0x7f48c42bf838, buf=0x7f48c42d8e38 "\377\377")
    at /home/yoku0825/mysql-8.0.20/storage/perfschema/ha_perfschema.cc:1641
#8  0x00000000010df114 in handler::ha_rnd_next (this=0x7f48c42bf838, buf=0x7f48c42d8e38 "\377\377")
    at /home/yoku0825/mysql-8.0.20/sql/handler.cc:2966
#9  0x0000000000e0d72d in TableScanIterator::Read (this=0x7f48c42d8390) at /home/yoku0825/mysql-8.0.20/sql/row_iterator.h:275
#10 0x0000000000f702b3 in SELECT_LEX_UNIT::ExecuteIteratorQuery(THD*) () at /home/yoku0825/mysql-8.0.20/sql/sql_union.cc:1183
#11 0x0000000000f7047c in SELECT_LEX_UNIT::execute(THD*) () at /home/yoku0825/mysql-8.0.20/sql/sql_union.cc:1235
#12 0x0000000000f0245b in Sql_cmd_dml::execute_inner(THD*) () at /home/yoku0825/mysql-8.0.20/sql/sql_select.cc:945
#13 0x0000000000f0c449 in Sql_cmd_dml::execute(THD*) () at /home/yoku0825/mysql-8.0.20/sql/sql_select.cc:725
#14 0x0000000000eb6435 in mysql_execute_command(THD*, bool) () at /home/yoku0825/mysql-8.0.20/sql/sql_parse.cc:4489
#15 0x0000000000eb8378 in mysql_parse (thd=thd@entry=0x7f48c4010140, parser_state=parser_state@entry=0x7f49180544d0)
    at /home/yoku0825/mysql-8.0.20/sql/sql_parse.cc:5306
#16 0x0000000000eba4d5 in dispatch_command(THD*, COM_DATA const*, enum_server_command) ()
    at /home/yoku0825/mysql-8.0.20/sql/sql_parse.cc:1776
#17 0x0000000000ebb314 in do_command (thd=thd@entry=0x7f48c4010140) at /home/yoku0825/mysql-8.0.20/sql/sql_parse.cc:1274
#18 0x0000000000fccc70 in handle_connection (arg=arg@entry=0x757af30)
    at /home/yoku0825/mysql-8.0.20/sql/conn_handler/connection_handler_per_thread.cc:302
#19 0x000000000244315c in pfs_spawn_thread (arg=0x55f8070) at /home/yoku0825/mysql-8.0.20/storage/perfschema/pfs.cc:2854
#20 0x00007f49276caea5 in start_thread (arg=0x7f4918055700) at pthread_create.c:307
#21 0x00007f49258538dd in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:111
(gdb) frame 14
+frame 14
#14 0x0000000000eb6435 in mysql_execute_command(THD*, bool) () at /home/yoku0825/mysql-8.0.20/sql/sql_parse.cc:4489
4489          res = lex->m_sql_cmd->execute(thd);
(gdb) p thd->m_query_string
+p thd->m_query_string
$3 = {
  str = 0x7f48c42d4608 "SELECT * FROM performance_schema.data_locks",
  length = 43
}

2020/06/26

最近のMySQLにテンポラリーなファイル/ディレクトリを指定するオプションが多い気がする件

TL;DR

innodb_temp_data_file_path
- ibtmp1のパス、「5.7および8.0.12まで」と「8.0.13とそれ以降」でibtmp1の用途がちょっと変わっている
  - 日々の覚書: MySQL 5.7.6以降では暗黙のテンポラリーテーブルがあふれると死ぬ
  - 日々の覚書: MySQL 8.0.13とそれ以降ではibtmp1は肥大化しない（あるいは、 /var/lib/mysql/#innodb_temp ディレクトリの正体）
innodb_temp_tablespaces_dir
- 8.0.13とそれ以降。InnoDBなテンポラリーテーブルのデータが入る *.ibt 置き場のディレクトリ。デフォルトでは #innodb_temp ディレクトリ。
  - 日々の覚書: MySQL 8.0.13とそれ以降ではibtmp1は肥大化しない（あるいは、 /var/lib/mysql/#innodb_temp ディレクトリの正体）
innodb_tmpdir
- ALTER TABLEがテーブルコピーで処理される時にそのテンポラリーファイルを入れるディレクトリ
  - ALTER TABLE中に #sql-なんちゃら.ibd みたいなのが見えるアレが入る場所
slave_load_tmpdir
- binlog_format= STATEMENTの時にLOAD DATA (LOCAL) INFILE がレプリケートされてきた時に使うディレクトリ。 MLなんちゃら みたいなファイルが入るやつ
- binlog_format <> STATEMENTの時は行のデータがそのままバイナリログに入るので使わないやつ
tmpdir
- もはやtmpdirっぽいのがいっぱいありすぎてコード斜め読みした感じ以下が入りそう。
  - binlog_cache_sizeをあふれたやつ
  - sort_buffer_sizeをあふれたやつ
  - join_buffer_sizeをあふれたやつ
  - InnoDB以外のテンポラリーテーブル
    - ↑に入るような気もするけど、MMAPed-fileに落ちた時のTempTableストレージエンジン

mysql80 9> SELECT @@version;
+-----------+
| @@version |
+-----------+
| 8.0.20    |
+-----------+
1 row in set (0.00 sec)

mysql80 9> SELECT variable_name, variable_value FROM performance_schema.global_variables WHERE variable_name LIKE '%tmp%' OR variable_name LIKE '%temp%';
+---------------------------------+-----------------------+
| variable_name                   | variable_value        |
+---------------------------------+-----------------------+
| avoid_temporal_upgrade          | OFF                   |
| default_tmp_storage_engine      | InnoDB                |
| innodb_temp_data_file_path      | ibtmp1:12M:autoextend |
| innodb_temp_tablespaces_dir     | ./#innodb_temp/       |
| innodb_tmpdir                   |                       |
| internal_tmp_mem_storage_engine | TempTable             |
| show_old_temporals              | OFF                   |
| slave_load_tmpdir               | /tmp                  |
| temptable_max_ram               | 1073741824            |
| temptable_use_mmap              | ON                    |
| tmp_table_size                  | 16777216              |
| tmpdir                          | /tmp                  |
+---------------------------------+-----------------------+
12 rows in set (0.00 sec)

mysql57 6> SELECT @@version;
+------------+
| @@version  |
+------------+
| 5.7.30-log |
+------------+
1 row in set (0.00 sec)

mysql57 6> SELECT variable_name, variable_value FROM performance_schema.global_variables WHERE variable_name LIKE '%tmp%' OR variable_name LIKE '%temp%';
+----------------------------------+-----------------------+
| variable_name                    | variable_value        |
+----------------------------------+-----------------------+
| avoid_temporal_upgrade           | OFF                   |
| default_tmp_storage_engine       | InnoDB                |
| innodb_temp_data_file_path       | ibtmp1:12M:autoextend |
| innodb_tmpdir                    |                       |
| internal_tmp_disk_storage_engine | InnoDB                |
| max_tmp_tables                   | 32                    |
| show_old_temporals               | OFF                   |
| slave_load_tmpdir                | /tmp                  |
| tmp_table_size                   | 16777216              |
| tmpdir                           | /tmp                  |
+----------------------------------+-----------------------+
10 rows in set (0.00 sec)

5.6は performance_schema ではなくて information_schema

mysql56> SELECT @@version;
+------------+
| @@version  |
+------------+
| 5.6.48-log |
+------------+
1 row in set (0.00 sec)

mysql56> SELECT variable_name, variable_value FROM information_schema.global_variables WHERE variable_name LIKE '%tmp%' OR variable_name LIKE '%temp%';
+----------------------------+----------------+
| variable_name              | variable_value |
+----------------------------+----------------+
| INNODB_TMPDIR              |                |
| SHOW_OLD_TEMPORALS         | OFF            |
| TMPDIR                     | /tmp           |
| MAX_TMP_TABLES             | 32             |
| DEFAULT_TMP_STORAGE_ENGINE | InnoDB         |
| AVOID_TEMPORAL_UPGRADE     | OFF            |
| TMP_TABLE_SIZE             | 16777216       |
| SLAVE_LOAD_TMPDIR          | /tmp           |
+----------------------------+----------------+
8 rows in set (0.00 sec)

2020/06/23

utf8mb3なマスターに絵文字を突っ込んだ時にバイナリログってどうなるんだっけ

TL;DR

マスター上で、3バイトUTF-8なカラムとコネクションのcharsetの対応(いずれもsql_mode = ‘STRICT_TRANS_TABLES’ でない )

	SET NAMES utf8	SET NAMES utf8mb4
utf8なカラム	絵文字から後ろが切れる	絵文字が ‘?’ になる
utf8mb4なカラム	絵文字が ‘????’ になる	絵文字が入る

マスターが3バイトutf8でスレーブが4バイトutf8(utf8mb4)の場合と↑の対応

	ROW	STATEMENT
マスターでは後ろが切れた	スレーブでも後ろが切れる	スレーブでは絵文字が入る
マスターでは ‘?’ になった	スレーブでも ‘?’ になる	スレーブでは絵文字が入る

「後ろが全部切れる」のが「’?’ に変換される」のはまだインパクト少なそう（絵文字を入れたら後ろが全部切れるのを前提にしているエンドユーザーはきっといない）
binlog_format=ROW の方が安牌
- マスター切り替え前後でエンドユーザーに見える値は変わらない
- スレーブの CONVERT TO CHARSET utf8mb4 後すぐにマスターを切り替えるなら binlog_format= STATEMENT にしたくなる気持ちはわからなくもない(が、やりたくない)

以下、ログ

binlog_format = ROW

CREATE TABLE .. CHARACTER SET utf8mb3

mysql80 54> CREATE TABLE t1 (num int, val varchar(32)) CHARSET utf8mb3;
Query OK, 0 rows affected, 1 warning (0.03 sec)

mysql80 54> SHOW WARNINGS;
+---------+------+---------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                     |
+---------+------+---------------------------------------------------------------------------------------------+
| Warning | 1287 | 'utf8mb3' is deprecated and will be removed in a future release. Please use utf8mb4 instead |
+---------+------+---------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql80 54> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `num` int DEFAULT NULL,
  `val` varchar(32) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.01 sec)

SET NAMES utf8mb3

mysql80 54> INSERT INTO t1 VALUES (1, 'utf8mb3による🍣だよ');
Query OK, 1 row affected, 2 warnings (0.00 sec)

mysql80 54> SHOW WARNINGS;
+---------+------+---------------------------------------------------------------------------------+
| Level   | Code | Message                                                                         |
+---------+------+---------------------------------------------------------------------------------+
| Warning | 1300 | Invalid utf8 character string: 'F09F8D'                                         |
| Warning | 1366 | Incorrect string value: '\xF0\x9F\x8D\xA3\xE3\x81...' for column 'val' at row 1 |
+---------+------+---------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql80 54> SELECT * FROM t1;
+------+------------------+
| num  | val              |
+------+------------------+
|    1 | utf8mb3による    |
+------+------------------+
1 row in set (0.00 sec)

# at 772
#200623 13:53:33 server id 1080  end_log_pos 851 CRC32 0x9517e90e       Rows_query
# INSERT INTO t1 VALUES (1, 'utf8mb3による🍣だよ')
### INSERT INTO `d11`.`t1`
### SET
###   @1=1 /* INT meta=0 nullable=1 is_null=0 */
###   @2='utf8mb3による' /* VARSTRING(96) meta=96 nullable=1 is_null=0 */

SET NAMES utf8mb4

mysql80 54> INSERT INTO t1 VALUES (1, 'utf8mb4による🍣だよ');
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql80 54> SHOW WARNINGS;
+---------+------+---------------------------------------------------------------------------------+
| Level   | Code | Message                                                                         |
+---------+------+---------------------------------------------------------------------------------+
| Warning | 1366 | Incorrect string value: '\xF0\x9F\x8D\xA3\xE3\x81...' for column 'val' at row 1 |
+---------+------+---------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql80 54> SELECT * FROM t1;
+------+-------------------------+
| num  | val                     |
+------+-------------------------+
|    1 | utf8mb3による           |
|    1 | utf8mb4による?だよ      |
+------+-------------------------+
2 rows in set (0.00 sec)

# at 1145
#200623 13:54:54 server id 1080  end_log_pos 1224 CRC32 0x4357ac1b      Rows_query
# INSERT INTO t1 VALUES (1, 'utf8mb4による🍣だよ')
### INSERT INTO `d11`.`t1`
### SET
###   @1=1 /* INT meta=0 nullable=1 is_null=0 */
###   @2='utf8mb4による?だよ' /* VARSTRING(96) meta=96 nullable=1 is_null=0 */

CREATE TABLE .. CHARSET utf8mb4

mysql80 54> CREATE TABLE t1 (num int, val varchar(32)) CHARSET utf8mb4;
Query OK, 0 rows affected (0.04 sec)

mysql80 54> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `num` int DEFAULT NULL,
  `val` varchar(32) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

SET NAMES utf8mb3

mysql80 54> INSERT INTO t1 VALUES (1, 'utf8mb3による🍺だよ');
Query OK, 1 row affected, 2 warnings (0.01 sec)

mysql80 54> SHOW WARNINGS;
+---------+------+---------------------------------------------------------------------------------+
| Level   | Code | Message                                                                         |
+---------+------+---------------------------------------------------------------------------------+
| Warning | 1300 | Invalid utf8 character string: 'F09F8D'                                         |
| Warning | 1366 | Incorrect string value: '\xF0\x9F\x8D\xBA\xE3\x81...' for column 'val' at row 1 |
+---------+------+---------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql80 54> SELECT * FROM t1;
+------+----------------------------+
| num  | val                        |
+------+----------------------------+
|    1 | utf8mb3による????だよ      |
+------+----------------------------+
1 row in set (0.00 sec)

# at 1948
#200623 13:57:06 server id 1080  end_log_pos 2027 CRC32 0xccae4843      Rows_query
# INSERT INTO t1 VALUES (1, 'utf8mb3による🍺だよ')
### INSERT INTO `d11`.`t1`
### SET
###   @1=1 /* INT meta=0 nullable=1 is_null=0 */
###   @2='utf8mb3による????だよ' /* VARSTRING(128) meta=128 nullable=1 is_null=0 */

SET NAMES utf8mb4

mysql80 54> INSERT INTO t1 VALUES (1, 'utf8mb4による🍺だよ');
Query OK, 1 row affected (0.01 sec)

mysql80 54> SELECT * FROM t1;
+------+----------------------------+
| num  | val                        |
+------+----------------------------+
|    1 | utf8mb3による????だよ      |
|    1 | utf8mb4による🍺だよ          |
+------+----------------------------+
2 rows in set (0.00 sec)

# at 2333
#200623 13:58:52 server id 1080  end_log_pos 2412 CRC32 0xbc81cf0a      Rows_query
# INSERT INTO t1 VALUES (1, 'utf8mb4による🍺だよ')
### INSERT INTO `d11`.`t1`
### SET
###   @1=1 /* INT meta=0 nullable=1 is_null=0 */
###   @2='utf8mb4による🍺だよ' /* VARSTRING(128) meta=128 nullable=1 is_null=0 */

binlog_format = STATEMENT

CREATE TABLE .. CHARACTER SET utf8mb3

SET NAMES utf8mb3

mysql80 57> INSERT INTO t1 VALUES (1, 'utf8mb3による🍣だよ');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql80 57> SELECT * FROM t1;
+------+------------------+
| num  | val              |
+------+------------------+
|    1 | utf8mb3による    |
+------+------------------+
1 row in set (0.00 sec)

# at 1584
#200623 14:05:34 server id 1080  end_log_pos 1714 CRC32 0xb7219e87      Query   thread_id=57    exec_time=0     error_code=0
SET TIMESTAMP=1592888734/*!*/;
INSERT INTO t1 VALUES (1, 'utf8mb3による🍣だよ')

SET NAMES utf8mb4

mysql80 57> INSERT INTO t1 VALUES (1, 'utf8mb4による🍣だよ');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql80 57> SELECT * FROM t1;
+------+-------------------------+
| num  | val                     |
+------+-------------------------+
|    1 | utf8mb3による           |
|    1 | utf8mb4による?だよ      |
+------+-------------------------+
2 rows in set (0.00 sec)

# at 1904
#200623 14:06:17 server id 1080  end_log_pos 2034 CRC32 0xae911ee8      Query   thread_id=57    exec_time=0     error_code=0
SET TIMESTAMP=1592888777/*!*/;
INSERT INTO t1 VALUES (1, 'utf8mb4による🍣だよ')

CREATE TABLE .. CHARACTER SET utf8mb4

SET NAMES utf8mb3

mysql80 57> INSERT INTO t1 VALUES (1, 'utf8mb3による🍺だよ');
Query OK, 1 row affected, 2 warnings (0.00 sec)

mysql80 57> SELECT * FROM t1;
+------+----------------------------+
| num  | val                        |
+------+----------------------------+
|    1 | utf8mb3による????だよ      |
+------+----------------------------+
1 row in set (0.00 sec)

# at 2647
#200623 14:07:40 server id 1080  end_log_pos 2777 CRC32 0xf21371cd      Query   thread_id=57    exec_time=0     error_code=0
SET TIMESTAMP=1592888860/*!*/;
INSERT INTO t1 VALUES (1, 'utf8mb3による🍺だよ')
/*!*/;

SET NAMES utf8mb4

mysql80 57> INSERT INTO t1 VALUES (1, 'utf8mb4による🍺だよ');
Query OK, 1 row affected (0.01 sec)

mysql80 57> SELECT * FROM t1;
+------+----------------------------+
| num  | val                        |
+------+----------------------------+
|    1 | utf8mb3による????だよ      |
|    1 | utf8mb4による🍺だよ          |
+------+----------------------------+
2 rows in set (0.00 sec)

# at 2967
#200623 14:08:25 server id 1080  end_log_pos 3097 CRC32 0x29b3d896      Query   thread_id=57    exec_time=0     error_code=0
SET TIMESTAMP=1592888905/*!*/;
INSERT INTO t1 VALUES (1, 'utf8mb4による🍺だよ')
/*!*/;

2020/06/19

MySQL 8.0.13とそれ以降ではibtmp1は肥大化しない（あるいは、 /var/lib/mysql/#innodb_temp ディレクトリの正体）

TL;DR

MySQL :: MySQL 8.0 Reference Manual :: 15.6.3.5 Temporary Tablespaces
MySQL 8.0.13とそれ以降ではテンポラリーテーブルの実データ格納に「セッション単位のテンポラリーテーブルスペース」が使われるようになった
- セッションが終われば領域が解放されるので、ibtmp1のように「mysqldを再起動しないとDisk Fullから復帰できない」ことがなくなった
- この「セッション単位のテンポラリーテーブルスペース」の格納ディレクトリが datadir/#innodb_temp ディレクトリ

PoC

### ダミーデータを1000万行ほど
$ perl -MDigest::MD5 -E 'for (my $n= 1; $n <= 10000000 ; $n++) { printf("%d\t%s\n", $n, Digest::MD5::md5_hex($n)) }' > /tmp/md5

$ ll -h /tmp/md5
-rw-r--r-- 1 yoku0825 yoku0825 390M Jun 18 22:42 /tmp/md5

$ mysql -h172.17.0.2 --local-infile -uroot
mysql> SET GLOBAL local_infile= 1;
mysql> CREATE TABLE t1 (num serial, val varchar(32));
mysql> LOAD DATA LOCAL INFILE '/tmp/md5' INTO TABLE t1;

### InnoDBに落ちるようにTempTableを使わせない
mysql> SET SESSION internal_tmp_mem_storage_engine = Memory;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT SUBSTR(val, 1, 10) AS v, COUNT(*) AS c FROM t1 LEFT JOIN (SELECT DISTINCT num /2 AS num FROM t1) AS tt1 USING(num) WHERE tt1.num IS NULL GROUP BY v ORDER BY c DESC, v ASC; -- なんでも良いけどとにかくでかそうな暗黙のテンポラリーテーブルを発生させる
..
4999985 rows in set (3 min 0.64 sec)

8.0.12の挙動

$ ll -h /var/lib/mysql/ibtmp1
-rw-r----- 1 mysql mysql 1.1G Jun 19 05:31 /var/lib/mysql/ibtmp1

無事（？）太ってらっしゃる。

8.0.13の挙動

$ ll -h /var/lib/mysql/ibtmp1
-rw-r----- 1 mysql mysql 12M Jun 19 05:33 /var/lib/mysql/ibtmp1

全然太ってない。代わりに、

$ ll -h /var/lib/mysql/#innodb_temp/
total 1.1G
-rw-r----- 1 mysql mysql  80K Jun 19 05:33 temp_1.ibt
-rw-r----- 1 mysql mysql 1.1G Jun 19 05:39 temp_10.ibt
-rw-r----- 1 mysql mysql  80K Jun 19 05:33 temp_2.ibt
-rw-r----- 1 mysql mysql  80K Jun 19 05:33 temp_3.ibt
-rw-r----- 1 mysql mysql  80K Jun 19 05:33 temp_4.ibt
-rw-r----- 1 mysql mysql  80K Jun 19 05:33 temp_5.ibt
-rw-r----- 1 mysql mysql  80K Jun 19 05:33 temp_6.ibt
-rw-r----- 1 mysql mysql  80K Jun 19 05:33 temp_7.ibt
-rw-r----- 1 mysql mysql  80K Jun 19 05:33 temp_8.ibt
-rw-r----- 1 mysql mysql  80K Jun 19 05:33 temp_9.ibt

#innodb_temp ディレクトリの.ibtファイルが太っている。
od -c とかで見るとちゃんとデータが入っていて、コイツがテンポラリーテーブルの実体なのだということが見て取れた。

ただしコイツがibtmp1と違うのは、「セッションがクリアされれば.ibtファイルが消える」こと（クエリーが終了すれば、ではない。接続を切らないとダメ）

mysql> quit
Bye

$ ll -h /var/lib/mysql/#innodb_temp/
total 224K
-rw-r----- 1 mysql mysql 80K Jun 19 05:33 temp_1.ibt
-rw-r----- 1 mysql mysql 80K Jun 19 05:41 temp_10.ibt
-rw-r----- 1 mysql mysql 80K Jun 19 05:33 temp_2.ibt
-rw-r----- 1 mysql mysql 80K Jun 19 05:33 temp_3.ibt
-rw-r----- 1 mysql mysql 80K Jun 19 05:33 temp_4.ibt
-rw-r----- 1 mysql mysql 80K Jun 19 05:33 temp_5.ibt
-rw-r----- 1 mysql mysql 80K Jun 19 05:33 temp_6.ibt
-rw-r----- 1 mysql mysql 80K Jun 19 05:33 temp_7.ibt
-rw-r----- 1 mysql mysql 80K Jun 19 05:33 temp_8.ibt
-rw-r----- 1 mysql mysql 80K Jun 19 05:33 temp_9.ibt

やった！すごい！これでInnoDBのテンポラリーテーブルがあふれても安心だ！（）

想定Q&A

Q. デカいテンポラリーテーブル作ってたセッションが切れた途端、数ギガバイトがファイルシステムから消されるって怖くない？
A. 暗黙のテンポラリーテーブルがMyISAMの時だってそうだったから、それで問題なければ問題ないんじゃなかろうか

Q. 10個しかないの？ 11スレッド以上で同時に使おうとするとどうなるの？
A. temp_11以降の.ibtファイルがどんどん増える。ドキュメント的には40万(400 thousand)まで行けるとか書いてある

Q. コネクションプールを使っているので、セッションがクリアされないんですが
A. ストレージがあふれたらAPサーバーを再起動だ！

Q. 逆に、今までは512MBを超えたらエラーにするとか頭打ちができたけど、今後はサイズによる頭打ちが出来なくなる？
A. 今のところできなさそう。瞬間的に天井まで行かなきゃいけないMyISAM時代に逆戻り…？

登録: 投稿 ( Atom )

GA

2020/06/29

performance_schema.data_locks.ENGINE_LOCK_ID is 何

TL;DR

2020/06/26

最近のMySQLにテンポラリーなファイル/ディレクトリを指定するオプションが多い気がする件

TL;DR

2020/06/23

utf8mb3なマスターに絵文字を突っ込んだ時にバイナリログってどうなるんだっけ

TL;DR

binlog_format = ROW

CREATE TABLE .. CHARACTER SET utf8mb3

SET NAMES utf8mb3

SET NAMES utf8mb4

CREATE TABLE .. CHARSET utf8mb4

SET NAMES utf8mb3

SET NAMES utf8mb4

binlog_format = STATEMENT

CREATE TABLE .. CHARACTER SET utf8mb3

SET NAMES utf8mb3

SET NAMES utf8mb4

CREATE TABLE .. CHARACTER SET utf8mb4

SET NAMES utf8mb3

SET NAMES utf8mb4

2020/06/19

MySQL 8.0.13とそれ以降ではibtmp1は肥大化しない（あるいは、 /var/lib/mysql/#innodb_temp ディレクトリの正体）

TL;DR

PoC

8.0.12の挙動

8.0.13の挙動

想定Q&A