GA

2022/07/20

xtrabackup 8.0.29とINSTANT COLUMNと

 

TL;DR


 $ xtrabackup --version

2022-07-20T18:36:05.410933+09:00 0 [Note] [MY-011825] [Xtrabackup] recognized server arguments: --server-id=1 --log_bin=mysql-bin --log-bin-index=bin.index --innodb_buffer_pool_size=60G --innodb_file_per_table=1 --innodb_flush_method=O_DIRECT

xtrabackup version 8.0.29-22 based on MySQL server 8.0.29 Linux (x86_64) (revision id: c31e7ddcce3)

INSTANT COLUMNが無ければ、Vanilla MySQL 8.0.29のバックアップが取れる。

$ xtrabackup -S /usr/mysql/8.0.29/data/mysql.sock -uroot --backup
2022-07-20T18:39:23.707951+09:00 0 [Note] [MY-011825] [Xtrabackup] recognized server arguments: --server-id=1 --log_bin=mysql-bin --log-bin-index=bin.index --innodb_buffer_pool_size=60G --innodb_file_per_table=1 --innodb_flush_method=O_DIRECT
2022-07-20T18:39:23.708199+09:00 0 [Note] [MY-011825] [Xtrabackup] recognized client arguments: --user=root --socket=/usr/mysql/8.0.29/data/mysql.sock --user=root --backup=1
xtrabackup version 8.0.29-22 based on MySQL server 8.0.29 Linux (x86_64) (revision id: c31e7ddcce3)
220720 18:39:23  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;mysql_socket=/usr/mysql/8.0.29/data/mysql.sock' as 'root'  (using password: NO).
220720 18:39:23  version_check Connected to MySQL server
220720 18:39:23  version_check Executing a version check against the server...
220720 18:39:23  version_check Done.
2022-07-20T18:39:23.807722+09:00 0 [Note] [MY-011825] [Xtrabackup] Connecting to MySQL server host: localhost, user: root, password: not set, port: not set, socket: /usr/mysql/8.0.29/data/mysql.sock
2022-07-20T18:39:23.812076+09:00 0 [Note] [MY-011825] [Xtrabackup] Using server version 8.0.29
2022-07-20T18:39:23.815319+09:00 0 [Note] [MY-011825] [Xtrabackup] Executing LOCK INSTANCE FOR BACKUP ...
2022-07-20T18:39:23.818088+09:00 0 [Note] [MY-011825] [Xtrabackup] uses posix_fadvise().
2022-07-20T18:39:23.818165+09:00 0 [Note] [MY-011825] [Xtrabackup] cd to /usr/mysql/8.0.29/data/
2022-07-20T18:39:23.818196+09:00 0 [Note] [MY-011825] [Xtrabackup] open files limit requested 0, set to 81920
2022-07-20T18:39:23.818276+09:00 0 [Note] [MY-011825] [Xtrabackup] using the following InnoDB configuration:
2022-07-20T18:39:23.818297+09:00 0 [Note] [MY-011825] [Xtrabackup] innodb_data_home_dir = .
2022-07-20T18:39:23.818312+09:00 0 [Note] [MY-011825] [Xtrabackup] innodb_data_file_path = ibdata1:12M:autoextend

..

2022-07-20T18:39:34.555754+09:00 0 [Note] [MY-011825] [Xtrabackup] Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
2022-07-20T18:39:34.557365+09:00 0 [Note] [MY-011825] [Xtrabackup] The latest check point (for incremental): '32821155948'
2022-07-20T18:39:34.557413+09:00 0 [Note] [MY-011825] [Xtrabackup] Stopping log copying thread at LSN 32821156733
2022-07-20T18:39:34.557518+09:00 1 [Note] [MY-011825] [Xtrabackup] Starting to parse redo log at lsn = 32821155894
2022-07-20T18:39:34.557794+09:00 1 [Note] [MY-011825] [Xtrabackup] >> log scanned up to (32821156812)
2022-07-20T18:39:35.559968+09:00 0 [Note] [MY-011825] [Xtrabackup] Executing UNLOCK INSTANCE
2022-07-20T18:39:35.560323+09:00 0 [Note] [MY-011825] [Xtrabackup] All tables unlocked
2022-07-20T18:39:35.560364+09:00 0 [Note] [MY-011825] [Xtrabackup] Backup created in directory '/home/yoku0825/xtrabackup_backupfiles/'
2022-07-20T18:39:35.560378+09:00 0 [Note] [MY-011825] [Xtrabackup] MySQL binlog position: filename 'mysql-bin.000120', position '197', GTID of the last change '9079d03d-dcd3-11ec-8628-fa163f8f0da3:1-198063'
2022-07-20T18:39:35.560505+09:00 0 [Note] [MY-011825] [Xtrabackup] Writing /home/yoku0825/xtrabackup_backupfiles/backup-my.cnf
2022-07-20T18:39:35.560576+09:00 0 [Note] [MY-011825] [Xtrabackup] Done: Writing file /home/yoku0825/xtrabackup_backupfiles/backup-my.cnf
2022-07-20T18:39:35.561428+09:00 0 [Note] [MY-011825] [Xtrabackup] Writing /home/yoku0825/xtrabackup_backupfiles/xtrabackup_info
2022-07-20T18:39:35.561513+09:00 0 [Note] [MY-011825] [Xtrabackup] Done: Writing file /home/yoku0825/xtrabackup_backupfiles/xtrabackup_info
2022-07-20T18:39:36.562474+09:00 0 [Note] [MY-011825] [Xtrabackup] Transaction log of lsn (32821155948) to (32821156822) was copied.
2022-07-20T18:39:36.781992+09:00 0 [Note] [MY-011825] [Xtrabackup] completed OK!

いつの間にかxbも MY- の番号つけるようになっていたのね(何か親近感を感じる番号だ)

フツーにバックアップは取れたので、INSTANT COLUMNを足してみる。

$ mysql80 d1
mysql80 86> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `num` bigint unsigned NOT NULL AUTO_INCREMENT,
  `val` varchar(32) COLLATE utf8mb4_ja_0900_as_cs DEFAULT NULL,
  UNIQUE KEY `num` (`num`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_ja_0900_as_cs
1 row in set (0.01 sec)

mysql80 86> ALTER TABLE t1 ADD col INT;  -- ALGORITHM=INSTANTを省略してもINSTANT ADD COLUMNになる
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql80 86> SELECT * FROM information_schema.INNODB_TABLES WHERE total_row_versions > 0;
+----------+-------+------+--------+-------+------------+---------------+------------+--------------+--------------------+
| TABLE_ID | NAME  | FLAG | N_COLS | SPACE | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE | INSTANT_COLS | TOTAL_ROW_VERSIONS |
+----------+-------+------+--------+-------+------------+---------------+------------+--------------+--------------------+
|    22441 | d1/t1 |   33 |      6 | 21380 | Dynamic    |             0 | Single     |            0 |                  1 |
+----------+-------+------+--------+-------+------------+---------------+------------+--------------+--------------------+
1 row in set (0.01 sec)
$ rm -r xtrabackup_backupfiles/

$ xtrabackup -S /usr/mysql/8.0.29/data/mysql.sock -uroot --backup
2022-07-20T18:43:20.923483+09:00 0 [Note] [MY-011825] [Xtrabackup] recognized server arguments: --server-id=1 --log_bin=mysql-bin --log-bin-index=bin.index --innodb_buffer_pool_size=60G --innodb_file_per_table=1 --innodb_flush_method=O_DIRECT
2022-07-20T18:43:20.923747+09:00 0 [Note] [MY-011825] [Xtrabackup] recognized client arguments: --user=root --socket=/usr/mysql/8.0.29/data/mysql.sock --user=root --backup=1
xtrabackup version 8.0.29-22 based on MySQL server 8.0.29 Linux (x86_64) (revision id: c31e7ddcce3)
220720 18:43:21  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;mysql_socket=/usr/mysql/8.0.29/data/mysql.sock' as 'root'  (using password: NO).
220720 18:43:21  version_check Connected to MySQL server
220720 18:43:21  version_check Executing a version check against the server...
220720 18:43:21  version_check Done.
2022-07-20T18:43:21.010844+09:00 0 [Note] [MY-011825] [Xtrabackup] Connecting to MySQL server host: localhost, user: root, password: not set, port: not set, socket: /usr/mysql/8.0.29/data/mysql.sock
2022-07-20T18:43:21.015998+09:00 0 [Note] [MY-011825] [Xtrabackup] Using server version 8.0.29
2022-07-20T18:43:21.020044+09:00 0 [Note] [MY-011825] [Xtrabackup] Executing LOCK INSTANCE FOR BACKUP ...
2022-07-20T18:43:21.023131+09:00 0 [ERROR] [MY-011825] [Xtrabackup] Found tables with row versions due to INSTANT ADD/DROP columns
2022-07-20T18:43:21.023271+09:00 0 [ERROR] [MY-011825] [Xtrabackup] This feature is not stable and will cause backup corruption.
2022-07-20T18:43:21.023352+09:00 0 [ERROR] [MY-011825] [Xtrabackup] Please check https://docs.percona.com/percona-xtrabackup/8.0/em/instant.html for more details.
2022-07-20T18:43:21.023429+09:00 0 [ERROR] [MY-011825] [Xtrabackup] Tables found:
2022-07-20T18:43:21.023513+09:00 0 [ERROR] [MY-011825] [Xtrabackup] d1/t1
2022-07-20T18:43:21.023586+09:00 0 [ERROR] [MY-011825] [Xtrabackup] Please run OPTIMIZE TABLE or ALTER TABLE ALGORITHM=COPY on all listed tables to fix this issue.

あっさりわかりやすく転けた。
エラーメッセージにも書いてあるけど、INSTANT COLUMNはOPTIMIZE TABLEするとINSTANTじゃないカラムになるので、OPTIMIZE。

mysql80 89> OPTIMIZE TABLE d1.t1;
+-------+----------+----------+-------------------------------------------------------------------+
| Table | Op       | Msg_type | Msg_text                                                          |
+-------+----------+----------+-------------------------------------------------------------------+
| d1.t1 | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| d1.t1 | optimize | status   | OK                                                                |
+-------+----------+----------+-------------------------------------------------------------------+
2 rows in set (0.02 sec)

$ xtrabackup -S /usr/mysql/8.0.29/data/mysql.sock -uroot --backup

..
2022-07-20T18:44:57.129630+09:00 0 [Note] [MY-011825] [Xtrabackup] completed OK!

逆にDROP COLUMNの方。

mysql80 96> SELECT * FROM information_schema.INNODB_TABLES WHERE total_row_versions > 0;
Empty set (0.00 sec)

mysql80 96> ALTER TABLE d1.t1 DROP COLUMN col;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql80 96> SELECT * FROM information_schema.INNODB_TABLES WHERE total_row_versions > 0;
+----------+-------+------+--------+-------+------------+---------------+------------+--------------+--------------------+
| TABLE_ID | NAME  | FLAG | N_COLS | SPACE | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE | INSTANT_COLS | TOTAL_ROW_VERSIONS |
+----------+-------+------+--------+-------+------------+---------------+------------+--------------+--------------------+
|    22442 | d1/t1 |   33 |      5 | 21381 | Dynamic    |             0 | Single     |            0 |                  1 |
+----------+-------+------+--------+-------+------------+---------------+------------+--------------+--------------------+
1 row in set (0.01 sec)

$ xtrabackup -S /usr/mysql/8.0.29/data/mysql.sock -uroot --backup
2022-07-20T18:45:53.616425+09:00 0 [Note] [MY-011825] [Xtrabackup] recognized server arguments: --server-id=1 --log_bin=mysql-bin --log-bin-index=bin.index --innodb_buffer_pool_size=60G --innodb_file_per_table=1 --innodb_flush_method=O_DIRECT
2022-07-20T18:45:53.616691+09:00 0 [Note] [MY-011825] [Xtrabackup] recognized client arguments: --user=root --socket=/usr/mysql/8.0.29/data/mysql.sock --user=root --backup=1
xtrabackup version 8.0.29-22 based on MySQL server 8.0.29 Linux (x86_64) (revision id: c31e7ddcce3)
220720 18:45:53  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;mysql_socket=/usr/mysql/8.0.29/data/mysql.sock' as 'root'  (using password: NO).
220720 18:45:53  version_check Connected to MySQL server
220720 18:45:53  version_check Executing a version check against the server...
220720 18:45:53  version_check Done.
2022-07-20T18:45:53.707364+09:00 0 [Note] [MY-011825] [Xtrabackup] Connecting to MySQL server host: localhost, user: root, password: not set, port: not set, socket: /usr/mysql/8.0.29/data/mysql.sock
2022-07-20T18:45:53.712051+09:00 0 [Note] [MY-011825] [Xtrabackup] Using server version 8.0.29
2022-07-20T18:45:53.714724+09:00 0 [Note] [MY-011825] [Xtrabackup] Executing LOCK INSTANCE FOR BACKUP ...
2022-07-20T18:45:53.717792+09:00 0 [ERROR] [MY-011825] [Xtrabackup] Found tables with row versions due to INSTANT ADD/DROP columns
2022-07-20T18:45:53.717842+09:00 0 [ERROR] [MY-011825] [Xtrabackup] This feature is not stable and will cause backup corruption.
2022-07-20T18:45:53.717856+09:00 0 [ERROR] [MY-011825] [Xtrabackup] Please check https://docs.percona.com/percona-xtrabackup/8.0/em/instant.html for more details.
2022-07-20T18:45:53.717886+09:00 0 [ERROR] [MY-011825] [Xtrabackup] Tables found:
2022-07-20T18:45:53.717909+09:00 0 [ERROR] [MY-011825] [Xtrabackup] d1/t1
2022-07-20T18:45:53.717921+09:00 0 [ERROR] [MY-011825] [Xtrabackup] Please run OPTIMIZE TABLE or ALTER TABLE ALGORITHM=COPY on all listed tables to fix this issue.

同じエラーでアウト。
このエラーを吐いてるところは

https://github.com/percona/percona-xtrabackup/blob/percona-xtrabackup-8.0.29-22/storage/innobase/xtrabackup/src/backup_mysql.cc#L2364-L2401

なので、MySQL 8.0.29以上かつ SELECT NAME FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE TOTAL_ROW_VERSIONS > 0 でマッチする行があるとエラるようになっている(MySQL 8.0.28とそれ以前にはそもそも total_row_versions カラムがない)

クラッシュリカバリがおかしいのをPercona Serverでは直した、Vanillaではまだ、ってことらしいので、MySQL側でも直ってくれることを祈りつつ。

2022/07/01

mysql_use_result(サーバーサイドバッファリング) vs. history list length


TL;DR

  • トランザクション分離レベルがたとえREAD-UNCOMMITTEDだとしても、クエリの実行中はhistory list lengthは伸びていく

    • REPEATABLE-READ .. トランザクション中はundo recordをパージできない

    • READ-COMMITTED, READ-UNCOMMITTED .. クエリの実行中はundo recordをパージできない

  • ところで、 mysql_use_result はSQLの評価が終わった後に結果セットをクライアントに送信するのを遅延させる(サーバーサイドでバッファリングさせる)ので、クライアントが結果セットを全部読みだしてくれるまでは「MySQL上ではクエリ実行中扱い」

    • というわけで、history list lengthは伸びていった

history list lengthが伸びるかどうかの実験用に、mysqlslapを走らせておく。


$ mysqlslap80 --auto-generate-sql --auto-generate-sql-execute-number=100000000

REPEATABLE-READ.


mysql80 12> SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

Query OK, 0 rows affected (0.00 sec)

mysql80 12> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql80 12> SELECT * FROM d1.t1;
+-----+------+
| num | val  |
+-----+------+
|   1 | one  |
+-----+------+
1 row in set (0.00 sec)

mysql80 12> SELECT count FROM information_schema.innodb_metrics WHERE name = 'trx_rseg_history_len';
+-------+
| count |
+-------+
|  3106 |
+-------+
1 row in set (0.00 sec)

mysql80 12> COMMIT;
Query OK, 0 rows affected (0.00 sec)

mysql80 12> SELECT count FROM information_schema.innodb_metrics WHERE name = 'trx_rseg_history_len';
+-------+
| count |
+-------+
|    60 |
+-------+
1 row in set (0.00 sec)

READ-COMMITTED。
「クエリが終わるまでは伸びる」をSLEEPで表現(history_list_lengthの監視は別のターミナルでやる)

mysql80 12> SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

Query OK, 0 rows affected (0.00 sec)

mysql80 12> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql80 12> SELECT *, SLEEP(30) FROM d1.t1;

mysql80 15> SELECT count FROM information_schema.innodb_metrics WHERE name = 'trx_rseg_history_len';  -- 別ターミナル
+-------+
| count |
+-------+
|  1022 |
+-------+
1 row in set (0.00 sec)

mysql80 12> COMMIT;  -- これを待たずにSELECTが終わった時点でhistory list lengthは減る
Query OK, 0 rows affected (0.00 sec)

READ-UNCOMMITTED

mysql80 12> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
Query OK, 0 rows affected (0.00 sec)

mysql80 12> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql80 12> SELECT *, SLEEP(30) FROM d1.t1;

mysql80 15> SELECT count FROM information_schema.innodb_metrics WHERE name = 'trx_rseg_history_len';  -- READ-UNCOMMITTEDでさえ実行中は伸びる
+-------+
| count |
+-------+
|   793 |
+-------+
1 row in set (0.00 sec)

ところで、 mysql_use_result を使ったクエリで結果セットが吸い上げられていないものは、 SHOW PROCESSLIST でも Sending to client のStateでクエリが実行中のように出力される(し、実際実行中として扱われているのであろう)

(ちなみにd1.t1のサイズが小さいと、net_bufferに収まってしまって一発で全部送りきられてしまった。ある程度のサイズがないと再現しない)

$ perl -E 'my $conn= DBI->connect("dbi:mysql:d1;mysql_socket=/usr/mysql/8.0.29/data/mysql.sock", "root", ""); my $sth= $conn->prepare("SELECT * FROM d1.t1", { mysql_use_result => 1 }); $sth->execute(); while (my $row= $sth->fetchrow_arrayref()) { sleep 100; }'

| 27 | root            | localhost | d1        | Query   |    5 | Sending to client      | SELECT * FROM d1.t1      |

mysql80 15> SELECT count FROM information_schema.innodb_metrics WHERE name = 'trx_rseg_history_len';
+-------+
| count |
+-------+
|   553 |
+-------+
1 row in set (0.00 sec)

Connector/Jとかでカーソルを使っている場合はこれにあたるかも知れない。というか当たったらしい。
誰かConnector/Jで試してみてほしい。

どうしてもSELECT COUNT(*) せずに正しいレコードの数を数えたい(が、そんなに都合の良いことはなかった)

 

TL;DR

  • innochecksumを使って強引に数えてみた
  • こんなことをするくらいならおとなしくCOUNT(*) した方がマシ

SHOW TABLE STATUS や information_schema.tables は統計情報ベースなので正確な値ではない、でも COUNT(*) はしたくない、という時。

mysql> SHOW TABLE STATUS LIKE 't1'\G
*************************** 1. row ***************************
           Name: t1
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 94295380
 Avg_row_length: 37
    Data_length: 3515875328
Max_data_length: 0
   Index_length: 3868196864
      Data_free: 6291456
 Auto_increment: NULL
    Create_time: 2022-05-23 21:43:56
    Update_time: 2022-05-23 22:43:29
     Check_time: NULL
      Collation: utf8mb4_bin
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

mysql> SELECT COUNT(*) FROM t1;
+-----------+
| COUNT(*)  |
+-----------+
| 100000000 |
+-----------+
1 row in set (8.92 sec)

innochecksum ならそれができるかもなあと思った。

innochecksumは -D オプションでファイル名を指定すると、ibdファイルの中身をページ単位でドバドバ吐き出すことができる。 ( -D /dev/stdout でターミナルに吐ける)

ただしこの時、mysqldが.ibdファイルを掴んでいると↓のようなエラーになるので

$ innochecksum -D /dev/stdout t1.ibd
Error: Unable to lock file:: t1.ibd
fcntl: Resource temporarily unavailable

どうせ「その瞬間のページの状態」しか認識できないんだからと割り切ってどっかにibdファイルをコピーしてそれを掴ませるのをよく(?)やってる。

このコピーの負荷や時間を考えるとCOUNT(*)の方がたぶんマシ。

$ cp -ip t1.ibd ~
$ innochecksum -D /dev/stdout ~/t1.ibd ### コピーしたibdの方にinnochecksumをかける

Filename::/home/yoku0825/t1.ibd
==============================================================================
        PAGE_NO         |               PAGE_TYPE                       |       EXTRA INFO
==============================================================================
#::       0             |               File Space Header               |       -
#::       1             |               Insert Buffer Bitmap            |       -
#::       2             |               Inode page                      |       -
#::       3             |               SDI Index page                  |       index id=18446744073709551615, page level=0, No. of records=2, garbage=0, -
#::       4             |               Index page                      |       index id=145, page level=2, No. of records=189, garbage=0, -
#::       5             |               Index page                      |       index id=146, page level=2, No. of records=91, garbage=0, -
#::       6             |               Index page                      |       index id=147, page level=2, No. of records=91, garbage=0, -
#::       7             |               Index page                      |       index id=148, page level=2, No. of records=91, garbage=0, -
#::       8             |               Index page                      |       index id=145, page level=0, No. of records=220, garbage=7514, -
#::       9             |               Index page                      |       index id=145, page level=0, No. of records=441, garbage=0, -
#::      10             |               Index page                      |       index id=145, page level=0, No. of records=441, garbage=0, -
#::      11             |               Index page                      |       index id=145, page level=0, No. of records=441, garbage=0, -
#::      12             |               Index page                      |       index id=146, page level=0, No. of records=601, garbage=7826, -
#::      13             |               Index page                      |       index id=146, page level=0, No. of records=1203, garbage=0, -
#::      14             |               Index page                      |       index id=147, page level=0, No. of records=601, garbage=7826, -
#::      15             |               Index page                      |       index id=147, page level=0, No. of records=1203, garbage=0, -
#::      16             |               Index page                      |       index id=148, page level=0, No. of records=1202, garbage=0, -
#::      17             |               Index page                      |       index id=148, page level=0, No. of records=601, garbage=7826, -
#::      18             |               Index page                      |       index id=145, page level=0, No. of records=441, garbage=0, -
#::      19             |               Index page                      |       index id=146, page level=0, No. of records=1203, garbage=0, -
#::      20             |               Index page                      |       index id=147, page level=0, No. of records=1203, garbage=0, -
#::      21             |               Index page                      |       index id=148, page level=0, No. of records=1203, garbage=0, -

..

index id は information_schema.innodb_indexes から引ける ( innodb_tables とJOINするとテーブルで絞りやすい )

mysql> SELECT * FROM information_schema.innodb_indexes JOIN information_schema.innodb_tables USING(table_id) WHERE innodb_tables.name = 'd1/t1';
+----------+----------+---------+------+----------+---------+-------+-----------------+-------+------+--------+-------+------------+---------------+------------+--------------+
| TABLE_ID | INDEX_ID | NAME    | TYPE | N_FIELDS | PAGE_NO | SPACE | MERGE_THRESHOLD | NAME  | FLAG | N_COLS | SPACE | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE | INSTANT_COLS |
+----------+----------+---------+------+----------+---------+-------+-----------------+-------+------+--------+-------+------------+---------------+------------+--------------+
|     1061 |      145 | PRIMARY |    3 |        6 |       4 |     4 |              50 | d1/t1 |   33 |      7 |     4 | Dynamic    |             0 | Single     |            0 |
|     1061 |      146 | c2      |    0 |        2 |       5 |     4 |              50 | d1/t1 |   33 |      7 |     4 | Dynamic    |             0 | Single     |            0 |
|     1061 |      147 | c3      |    0 |        2 |       6 |     4 |              50 | d1/t1 |   33 |      7 |     4 | Dynamic    |             0 | Single     |            0 |
|     1061 |      148 | c4      |    0 |        2 |       7 |     4 |              50 | d1/t1 |   33 |      7 |     4 | Dynamic    |             0 | Single     |            0 |
+----------+----------+---------+------+----------+---------+-------+-----------------+-------+------+--------+-------+------------+---------------+------------+--------------+
4 rows in set (0.00 sec)

なんか gargage=? の値を引いたりとかいろいろしてみたけど引かない方が合ってるっぽい。 page level=0 (これがリーフノードらしい) の数だけを数えるのが正解っぽい?

$ innochecksum -D /dev/stdout ~/t1.ibd | grep 'Index page' | grep -v 'SDI' | grep 'page level=0' | perl -MData::Dumper -nlE 'BEGIN { $counter }; /index id=(\d+).*records=(\d+)/; $counter->{$1} += $2; END { print Dumper $counter }'
$VAR1 = {
          '147' => '100000000',
          '145' => '100000000',
          '148' => '100000601',
          '146' => '100000000'
        };

なんで INDEX c4(c4) だけズレるのかがわからない…。

2022/06/29

DROP TABLEがどれくらいその他のトラフィックに影響を与えるか……を、手元で測る方法

 

TL;DR

  • sysbenchでDROP用のテーブルと、その他トラフィック用のテーブルをprepareして

  • sysbench runしながらDROP TABLEするだけ


sysbenchは無ければインストール(CentOS 7.xだとepelに入ってる)


$ sudo yum install -y sysbench

ベンチ先がMySQL 8.0の場合、 default_authentication_plugin=mysql_native_password かつ、sysbench用のアカウントがmysql_native_passwordでされてないとエラーになる。


FATAL: error 2059: Authentication plugin 'caching_sha2_password' cannot be loaded: /usr/lib64/mysql/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory

FATAL: `sysbench.cmdline.call_command' function failed: /usr/share/sysbench/oltp_common.lua:83: connection creation failed

ちなみにchaching_sha2_passwordは libmysqlcliento.so に埋め込まれているので、どこを探しても caching_sha2_password.so なんてものは無いのでyum providesとかで探す必要はない。

どうしても8.0のcaching_sha2_passwordで通したい場合は最新のmysql-community-develを使ってsysbenchをビルドしなおすことになると思う。

mysql80 16> CREATE USER sysbench@localhost IDENTIFIED WITH mysql_native_password BY '';
Query OK, 0 rows affected (0.02 sec)

mysql80 16> GRANT ALL ON for_drop.* TO sysbench@localhost;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql80 16> GRANT ALL ON sysbench.* TO sysbench@localhost;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql80 21> CREATE DATABASE for_drop;
Query OK, 1 row affected (0.00 sec)

mysql80 21> CREATE DATABASE sysbench;
Query OK, 1 row affected (0.00 sec)

for_dropスキーマの方にデカめのテーブルを作る。

$ sysbench --mysql-socket=/usr/mysql/8.0.29/data/mysql.sock --mysql-user=sysbench --mysql-db=for_drop oltp_common --table-size=10000000 prepare
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)

Creating table 'sbtest1'...

Inserting 10000000 records into 'sbtest1'
Creating a secondary index on 'sbtest1'...

$ ll -h /usr/mysql/8.0.29/data/for_drop/sbtest1.ibd
-rw-r----- 1 yoku0825 yoku0825 2.3G Jun 29 15:21 /usr/mysql/8.0.29/data/for_drop/sbtest1.ibd

1000万行で2.3Gらしいので、このへんを基準(?)にDROPで試したいテーブルサイズを作る。とりあえず10倍の23GBを作っておいた。

今度はsysbenchスキーマの方に小さめのテーブルを複数作っておく。

$ sysbench --mysql-socket=/usr/mysql/8.0.29/data/mysql.sock --mysql-user=sysbench --mysql-db=sysbench oltp_common --tables=100 prepare
..

で、sysbench側にベンチをかける。
oltp_* にどんなスクリプトが用意されているかはこんな感じ。

$ ll /usr/share/sysbench/
total 132
-rwxr-xr-x 1 root root  1452 May  8  2020 bulk_insert.lua
-rw-r--r-- 1 root root 14369 May  8  2020 oltp_common.lua
-rwxr-xr-x 1 root root  1290 May  8  2020 oltp_delete.lua
-rwxr-xr-x 1 root root  2415 May  8  2020 oltp_insert.lua
-rwxr-xr-x 1 root root  1265 May  8  2020 oltp_point_select.lua
-rwxr-xr-x 1 root root  1649 May  8  2020 oltp_read_only.lua
-rwxr-xr-x 1 root root  1824 May  8  2020 oltp_read_write.lua
-rwxr-xr-x 1 root root  1118 May  8  2020 oltp_update_index.lua
-rwxr-xr-x 1 root root  1127 May  8  2020 oltp_update_non_index.lua
-rwxr-xr-x 1 root root  1440 May  8  2020 oltp_write_only.lua
-rwxr-xr-x 1 root root  1919 May  8  2020 select_random_points.lua
-rwxr-xr-x 1 root root  2118 May  8  2020 select_random_ranges.lua
drwxr-xr-x 4 root root    49 Apr 13 12:53 tests
-rwxr-xr-x 1 root root 12320 May  8  2020 tpcc_check.lua
-rwxr-xr-x 1 root root 20795 May  8  2020 tpcc_common.lua
-rwxr-xr-x 1 root root  1863 May  8  2020 tpcc.lua
-rwxr-xr-x 1 root root 28512 May  8  2020 tpcc_run.lua

oltp_read_onlyにしてみる。
--report-interval=1 にすると毎秒出力されるので便利。

$ sysbench --mysql-socket=/usr/mysql/8.0.29/data/mysql.sock --mysql-user=sysbench --mysql-db=sysbench oltp_read_only --tables=100 --report-interval=1 --threads=100 --time=1000 run

..
Threads started!

[ 1s ] thds: 100 tps: 1530.75 qps: 25282.12 (r/w/o: 22122.60/0.00/3159.52) lat (ms,95%): 132.49 err/s: 0.00 reconn/s: 0.00
[ 2s ] thds: 100 tps: 1585.97 qps: 25346.51 (r/w/o: 22180.57/0.00/3165.93) lat (ms,95%): 121.08 err/s: 0.00 reconn/s: 0.00
[ 3s ] thds: 100 tps: 1679.10 qps: 26823.60 (r/w/o: 23465.40/0.00/3358.20) lat (ms,95%): 110.66 err/s: 0.00 reconn/s: 0.00
[ 4s ] thds: 100 tps: 1655.01 qps: 26505.23 (r/w/o: 23195.20/0.00/3310.03) lat (ms,95%): 114.72 err/s: 0.00 reconn/s: 0.00
[ 5s ] thds: 100 tps: 1673.00 qps: 26725.07 (r/w/o: 23377.06/0.00/3348.01) lat (ms,95%): 112.67 err/s: 0.00 reconn/s: 0.00
[ 6s ] thds: 100 tps: 1577.97 qps: 25269.49 (r/w/o: 22112.55/0.00/3156.94) lat (ms,95%): 121.08 err/s: 0.00 reconn/s: 0.00
[ 7s ] thds: 100 tps: 1580.93 qps: 25326.88 (r/w/o: 22166.02/0.00/3160.86) lat (ms,95%): 116.80 err/s: 0.00 reconn/s: 0.00
[ 8s ] thds: 100 tps: 1593.91 qps: 25551.54 (r/w/o: 22365.73/0.00/3185.82) lat (ms,95%): 110.66 err/s: 0.00 reconn/s: 0.00
[ 9s ] thds: 100 tps: 1593.18 qps: 25472.95 (r/w/o: 22282.58/0.00/3190.37) lat (ms,95%): 99.33 err/s: 0.00 reconn/s: 0.00               <--- ここで開始
[ 10s ] thds: 100 tps: 1316.81 qps: 21059.97 (r/w/o: 18429.35/0.00/2630.62) lat (ms,95%): 99.33 err/s: 0.00 reconn/s: 0.00

..
[ 60s ] thds: 100 tps: 1890.99 qps: 30242.85 (r/w/o: 26459.87/0.00/3782.98) lat (ms,95%): 13.46 err/s: 0.00 reconn/s: 0.00
[ 61s ] thds: 100 tps: 1783.59 qps: 28572.41 (r/w/o: 25006.23/0.00/3566.18) lat (ms,95%): 14.73 err/s: 0.00 reconn/s: 0.00
[ 62s ] thds: 100 tps: 1820.78 qps: 29147.51 (r/w/o: 25503.95/0.00/3643.56) lat (ms,95%): 12.98 err/s: 0.00 reconn/s: 0.00
[ 63s ] thds: 100 tps: 1783.71 qps: 28545.30 (r/w/o: 24977.89/0.00/3567.41) lat (ms,95%): 13.95 err/s: 0.00 reconn/s: 0.00
[ 64s ] thds: 100 tps: 1943.01 qps: 31046.16 (r/w/o: 27163.14/0.00/3883.02) lat (ms,95%): 12.98 err/s: 0.00 reconn/s: 0.00
[ 65s ] thds: 100 tps: 1978.97 qps: 31682.47 (r/w/o: 27721.53/0.00/3960.93) lat (ms,95%): 12.75 err/s: 0.00 reconn/s: 0.00
[ 66s ] thds: 100 tps: 1768.98 qps: 28216.69 (r/w/o: 24683.73/0.00/3532.96) lat (ms,95%): 170.48 err/s: 0.00 reconn/s: 0.00              <-- ここで終わった
[ 67s ] thds: 100 tps: 1559.02 qps: 24944.31 (r/w/o: 21828.27/0.00/3116.04) lat (ms,95%): 121.08 err/s: 0.00 reconn/s: 0.00
[ 68s ] thds: 100 tps: 1648.94 qps: 26347.03 (r/w/o: 23046.15/0.00/3300.88) lat (ms,95%): 116.80 err/s: 0.00 reconn/s: 0.00
[ 69s ] thds: 100 tps: 1702.05 qps: 27290.79 (r/w/o: 23887.69/0.00/3403.10) lat (ms,95%): 110.66 err/s: 0.00 reconn/s: 0.00
[ 70s ] thds: 100 tps: 1708.01 qps: 27256.18 (r/w/o: 23839.16/0.00/3417.02) lat (ms,95%): 108.68 err/s: 0.00 reconn/s: 0.00

というわけで、semisyncなしでSELECTしか来てないサーバーだとデカいテーブルのDROP TABLEでも他のクエリにはほとんど影響は無さそうでした。

……というのを手元で確認するためのやり方でした。

2022/06/28

MySQLが勝手に作るファイルのパーミッションを指定する

 

TL;DR


  • UMASK を設定しないと、ファイルは 0640, ディレクトリは 0750
    • ただし auto_generate_certs で作られる証明書は 0600 (公開用のは 0644 っぽい
$ env | grep UMASK  ### からっぽ
$ /usr/mysql/8.0.29/bin/mysqld --no-defaults --initialize-insecure --datadir=/tmp/noumask
2022-06-28T03:49:08.901551Z 0 [System] [MY-013169] [Server] /usr/mysql/8.0.29/bin/mysqld (mysqld 8.0.29) initializing of server in progress as process 116145
2022-06-28T03:49:08.921448Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2022-06-28T03:49:09.938657Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2022-06-28T03:49:11.335845Z 5 [Warning] [MY-010453] [Server] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.
$ ll /tmp/noumask/
total 176568
-rw-r----- 1 yoku0825 yoku0825       56 Jun 28 12:49 auto.cnf
-rw------- 1 yoku0825 yoku0825     1676 Jun 28 12:49 ca-key.pem
-rw-r--r-- 1 yoku0825 yoku0825     1112 Jun 28 12:49 ca.pem
-rw-r--r-- 1 yoku0825 yoku0825     1112 Jun 28 12:49 client-cert.pem
-rw------- 1 yoku0825 yoku0825     1676 Jun 28 12:49 client-key.pem
-rw-r----- 1 yoku0825 yoku0825   196608 Jun 28 12:49 #ib_16384_0.dblwr
-rw-r----- 1 yoku0825 yoku0825  8585216 Jun 28 12:49 #ib_16384_1.dblwr
-rw-r----- 1 yoku0825 yoku0825     5944 Jun 28 12:49 ib_buffer_pool
-rw-r----- 1 yoku0825 yoku0825 12582912 Jun 28 12:49 ibdata1
-rw-r----- 1 yoku0825 yoku0825 50331648 Jun 28 12:49 ib_logfile0
-rw-r----- 1 yoku0825 yoku0825 50331648 Jun 28 12:49 ib_logfile1
drwxr-x--- 2 yoku0825 yoku0825        6 Jun 28 12:49 #innodb_temp
drwxr-x--- 2 yoku0825 yoku0825      143 Jun 28 12:49 mysql
-rw-r----- 1 yoku0825 yoku0825 25165824 Jun 28 12:49 mysql.ibd
drwxr-x--- 2 yoku0825 yoku0825     8192 Jun 28 12:49 performance_schema
-rw------- 1 yoku0825 yoku0825     1676 Jun 28 12:49 private_key.pem
-rw-r--r-- 1 yoku0825 yoku0825      452 Jun 28 12:49 public_key.pem
-rw-r--r-- 1 yoku0825 yoku0825     1112 Jun 28 12:49 server-cert.pem
-rw------- 1 yoku0825 yoku0825     1680 Jun 28 12:49 server-key.pem
drwxr-x--- 2 yoku0825 yoku0825       28 Jun 28 12:49 sys
-rw-r----- 1 yoku0825 yoku0825 16777216 Jun 28 12:49 undo_001
-rw-r----- 1 yoku0825 yoku0825 16777216 Jun 28 12:49 undo_002
  • UMASK=0600 を押し込むと、ファイルは 0600, ディレクトリは…… 0710 ?
    • 証明書類はUMASKの影響を受けてなさそう
$ UMASK=0600 /usr/mysql/8.0.29/bin/mysqld --no-defaults --initialize-insecure --datadir=/tmp/umask0600
2022-06-28T03:52:21.587890Z 0 [System] [MY-013169] [Server] /usr/mysql/8.0.29/bin/mysqld (mysqld 8.0.29) initializing of server in progress as p
rocess 116751
2022-06-28T03:52:21.595297Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2022-06-28T03:52:22.194497Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2022-06-28T03:52:23.877843Z 5 [Warning] [MY-010453] [Server] root@localhost is created with an empty password ! Please consider switching off th
e --initialize-insecure option.

$ ll /tmp/umask0600/
total 176568
-rw------- 1 yoku0825 yoku0825       56 Jun 28 12:52 auto.cnf
-rw------- 1 yoku0825 yoku0825     1680 Jun 28 12:52 ca-key.pem
-rw-r--r-- 1 yoku0825 yoku0825     1112 Jun 28 12:52 ca.pem
-rw-r--r-- 1 yoku0825 yoku0825     1112 Jun 28 12:52 client-cert.pem
-rw------- 1 yoku0825 yoku0825     1676 Jun 28 12:52 client-key.pem
-rw------- 1 yoku0825 yoku0825   196608 Jun 28 12:52 #ib_16384_0.dblwr
-rw------- 1 yoku0825 yoku0825  8585216 Jun 28 12:52 #ib_16384_1.dblwr
-rw------- 1 yoku0825 yoku0825     5600 Jun 28 12:52 ib_buffer_pool
-rw------- 1 yoku0825 yoku0825 12582912 Jun 28 12:52 ibdata1
-rw------- 1 yoku0825 yoku0825 50331648 Jun 28 12:52 ib_logfile0
-rw------- 1 yoku0825 yoku0825 50331648 Jun 28 12:52 ib_logfile1
drwx--x--- 2 yoku0825 yoku0825        6 Jun 28 12:52 #innodb_temp
drwx--x--- 2 yoku0825 yoku0825      143 Jun 28 12:52 mysql
-rw------- 1 yoku0825 yoku0825 25165824 Jun 28 12:52 mysql.ibd
drwx--x--- 2 yoku0825 yoku0825     8192 Jun 28 12:52 performance_schema
-rw------- 1 yoku0825 yoku0825     1676 Jun 28 12:52 private_key.pem
-rw-r--r-- 1 yoku0825 yoku0825      452 Jun 28 12:52 public_key.pem
-rw-r--r-- 1 yoku0825 yoku0825     1112 Jun 28 12:52 server-cert.pem
-rw------- 1 yoku0825 yoku0825     1680 Jun 28 12:52 server-key.pem
drwx--x--- 2 yoku0825 yoku0825       28 Jun 28 12:52 sys
-rw------- 1 yoku0825 yoku0825 16777216 Jun 28 12:52 undo_001
-rw------- 1 yoku0825 yoku0825 16777216 Jun 28 12:52 undo_002

0710 がもんにょりしたけど、ドキュメントに書いてあった。

UMASK 変数および UMASK_DIR 変数は、その名前にもかかわらず、マスクではなくモードとして使用されます。

UMASK が設定されている場合、mysqld は ($UMASK | 0600) をファイル作成のモードとして使用し、新しく作成されるファイルのモードは 0600 から 0666 の範囲になります (すべて 8 進数の値)。

UMASK_DIR が設定されている場合、mysqld は ($UMASK_DIR | 0700) をディレクトリ作成のベースモードとして使用し、次に ~(~$UMASK & 0666) との AND が取られます。そのため新しく作成されるファイルのモードは 0700 から 0777 の範囲になります (すべて 8 進数の値)。 AND 演算によってディレクトリモードから読み取り/書き込み権が削除されることがありますが、実行権が削除されることはありません。

MySQL :: MySQL 8.0 リファレンスマニュアル :: 4.9 環境変数