パーティションほとんどわからんので触ってみた
パーティショニングしたテーブルの確認方法
MySQL触ってて、実は仕事上では一度も触ったことがなかったパーティショニング。
(だって、色んな人が使えないって言ってたんだもん)
だけど、意外と世の中使ってる人は多いので今後のためにもパーティショニングしたテーブルの確認方法を色々見てみました。
まずはパーティションテーブル作る
ドキュメントからテーブルを拝借しました。
パーティションタイプはレンジ、リスト、ハッシュ、キーの4つがあるが、今回はハッシュにしました。 (特に意味はないです。適当に選んだだけです)
mysql [localhost:8020] {admin} (test) > CREATE TABLE ti ( -> id INT, -> amount DECIMAL(7 , 2 ), -> tr_date DATE -> ) ENGINE=INNODB PARTITION BY HASH (MONTH(tr_date)) PARTITIONS 6; Query OK, 0 rows affected (0.09 sec)
適当に何件か入れてみる
mysql [localhost:8020] {admin} (test) > INSERT INTO `test`.`ti` -> (`id`, -> `amount`, -> `tr_date`) -> VALUES -> (1, -> 100, -> '2020-01-01'); Query OK, 1 row affected (0.02 sec) mysql [localhost:8020] {admin} (test) > INSERT INTO `test`.`ti` -> (`id`, -> `amount`, -> `tr_date`) -> VALUES -> (1, -> 101, -> '2020-01-02'); Query OK, 1 row affected (0.01 sec) mysql [localhost:8020] {admin} (test) > INSERT INTO `test`.`ti` -> (`id`, -> `amount`, -> `tr_date`) -> VALUES -> (2, -> 200, -> '2020-02-02'); Query OK, 1 row affected (0.01 sec) mysql [localhost:8020] {admin} (test) > INSERT INTO `test`.`ti` -> (`id`, -> `amount`, -> `tr_date`) -> VALUES -> (2, -> 201, -> '2020-02-01'); Query OK, 1 row affected (0.00 sec) mysql [localhost:8020] {admin} (test) > INSERT INTO `test`.`ti` -> (`id`, -> `amount`, -> `tr_date`) -> VALUES -> (3, -> 301, -> '2020-03-01'); Query OK, 1 row affected (0.00 sec) mysql [localhost:8020] {admin} (test) > INSERT INTO `test`.`ti` -> (`id`, -> `amount`, -> `tr_date`) -> VALUES -> (4, -> 400, -> '2020-04-01'); Query OK, 1 row affected (0.00 sec) mysql [localhost:8020] {admin} (test) > INSERT INTO `test`.`ti` -> (`id`, -> `amount`, -> `tr_date`) -> VALUES -> (5, -> 500, -> '2020-05-01'); Query OK, 1 row affected (0.00 sec) mysql [localhost:8020] {admin} (test) > INSERT INTO `test`.`ti` -> (`id`, -> `amount`, -> `tr_date`) -> VALUES -> (6, -> 600, -> '2020-06-01'); Query OK, 1 row affected (0.01 sec)
6件ほど入れてみました。
パーティションテーブルの確認
SHOW CREATE TABLE
SHOW CREATE TABLEではパーティション化されたテーブルの作成に使用されたパーティショニング句を表示します。
mysql [localhost:8020] {admin} (test) > show create table `test`.`ti`\G *************************** 1. row *************************** Table: ti Create Table: CREATE TABLE `ti` ( `id` int DEFAULT NULL, `amount` decimal(7,2) DEFAULT NULL, `tr_date` date DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!50100 PARTITION BY HASH (month(`tr_date`)) PARTITIONS 6 */ 1 row in set (0.00 sec)
SHOW TABLE STATUS
SHOW TABLE STATUSではテーブルがパーティション化されているかどうかを判別します。
mysql [localhost:8020] {admin} (test) > show table status like 'ti'\G *************************** 1. row *************************** Name: ti Engine: InnoDB Version: 10 Row_format: Dynamic Rows: 8 Avg_row_length: 12288 Data_length: 98304 Max_data_length: 0 Index_length: 0 Data_free: 0 Auto_increment: NULL Create_time: 2020-06-19 21:31:01 Update_time: 2020-06-19 21:34:08 Check_time: NULL Collation: utf8mb4_0900_ai_ci Checksum: NULL Create_options: partitioned Comment: 1 row in set (0.01 sec)
Create_optionsがpartitionedとなっているので、パーティション化されているテーブルです、と。
まあ、ここではただそれだけなんですねw
information_schema.partitions
I_Sのpartitionsビューでも確認できます。 ここではパーティションごとに行が作られますので、先のshow create tableよりも詳細な情報が得られます。
mysql [localhost:8020] {admin} (test) > select * from information_schema.partitions where table_schema = 'test' and table_name = 'ti'\G *************************** 1. row *************************** TABLE_CATALOG: def TABLE_SCHEMA: test TABLE_NAME: ti PARTITION_NAME: p0 SUBPARTITION_NAME: NULL PARTITION_ORDINAL_POSITION: 1 SUBPARTITION_ORDINAL_POSITION: NULL PARTITION_METHOD: HASH SUBPARTITION_METHOD: NULL PARTITION_EXPRESSION: month(`tr_date`) SUBPARTITION_EXPRESSION: NULL PARTITION_DESCRIPTION: NULL TABLE_ROWS: 1 AVG_ROW_LENGTH: 16384 DATA_LENGTH: 16384 MAX_DATA_LENGTH: 0 INDEX_LENGTH: 0 DATA_FREE: 0 CREATE_TIME: 2020-06-19 21:31:01 UPDATE_TIME: 2020-06-19 21:34:08 CHECK_TIME: NULL CHECKSUM: NULL PARTITION_COMMENT: NODEGROUP: default TABLESPACE_NAME: NULL *************************** 2. row *************************** TABLE_CATALOG: def TABLE_SCHEMA: test TABLE_NAME: ti PARTITION_NAME: p1 SUBPARTITION_NAME: NULL PARTITION_ORDINAL_POSITION: 2 SUBPARTITION_ORDINAL_POSITION: NULL PARTITION_METHOD: HASH SUBPARTITION_METHOD: NULL PARTITION_EXPRESSION: month(`tr_date`) SUBPARTITION_EXPRESSION: NULL PARTITION_DESCRIPTION: NULL TABLE_ROWS: 2 AVG_ROW_LENGTH: 8192 DATA_LENGTH: 16384 MAX_DATA_LENGTH: 0 INDEX_LENGTH: 0 DATA_FREE: 0 CREATE_TIME: 2020-06-19 21:31:01 UPDATE_TIME: 2020-06-19 21:32:58 CHECK_TIME: NULL CHECKSUM: NULL PARTITION_COMMENT: NODEGROUP: default TABLESPACE_NAME: NULL *************************** 3. row *************************** TABLE_CATALOG: def TABLE_SCHEMA: test TABLE_NAME: ti PARTITION_NAME: p2 SUBPARTITION_NAME: NULL PARTITION_ORDINAL_POSITION: 3 SUBPARTITION_ORDINAL_POSITION: NULL PARTITION_METHOD: HASH SUBPARTITION_METHOD: NULL PARTITION_EXPRESSION: month(`tr_date`) SUBPARTITION_EXPRESSION: NULL PARTITION_DESCRIPTION: NULL TABLE_ROWS: 2 AVG_ROW_LENGTH: 8192 DATA_LENGTH: 16384 MAX_DATA_LENGTH: 0 INDEX_LENGTH: 0 DATA_FREE: 0 CREATE_TIME: 2020-06-19 21:31:01 UPDATE_TIME: 2020-06-19 21:33:21 CHECK_TIME: NULL CHECKSUM: NULL PARTITION_COMMENT: NODEGROUP: default TABLESPACE_NAME: NULL *************************** 4. row *************************** TABLE_CATALOG: def TABLE_SCHEMA: test TABLE_NAME: ti PARTITION_NAME: p3 SUBPARTITION_NAME: NULL PARTITION_ORDINAL_POSITION: 4 SUBPARTITION_ORDINAL_POSITION: NULL PARTITION_METHOD: HASH SUBPARTITION_METHOD: NULL PARTITION_EXPRESSION: month(`tr_date`) SUBPARTITION_EXPRESSION: NULL PARTITION_DESCRIPTION: NULL TABLE_ROWS: 1 AVG_ROW_LENGTH: 16384 DATA_LENGTH: 16384 MAX_DATA_LENGTH: 0 INDEX_LENGTH: 0 DATA_FREE: 0 CREATE_TIME: 2020-06-19 21:31:01 UPDATE_TIME: 2020-06-19 21:33:31 CHECK_TIME: NULL CHECKSUM: NULL PARTITION_COMMENT: NODEGROUP: default TABLESPACE_NAME: NULL *************************** 5. row *************************** TABLE_CATALOG: def TABLE_SCHEMA: test TABLE_NAME: ti PARTITION_NAME: p4 SUBPARTITION_NAME: NULL PARTITION_ORDINAL_POSITION: 5 SUBPARTITION_ORDINAL_POSITION: NULL PARTITION_METHOD: HASH SUBPARTITION_METHOD: NULL PARTITION_EXPRESSION: month(`tr_date`) SUBPARTITION_EXPRESSION: NULL PARTITION_DESCRIPTION: NULL TABLE_ROWS: 1 AVG_ROW_LENGTH: 16384 DATA_LENGTH: 16384 MAX_DATA_LENGTH: 0 INDEX_LENGTH: 0 DATA_FREE: 0 CREATE_TIME: 2020-06-19 21:31:01 UPDATE_TIME: 2020-06-19 21:33:45 CHECK_TIME: NULL CHECKSUM: NULL PARTITION_COMMENT: NODEGROUP: default TABLESPACE_NAME: NULL *************************** 6. row *************************** TABLE_CATALOG: def TABLE_SCHEMA: test TABLE_NAME: ti PARTITION_NAME: p5 SUBPARTITION_NAME: NULL PARTITION_ORDINAL_POSITION: 6 SUBPARTITION_ORDINAL_POSITION: NULL PARTITION_METHOD: HASH SUBPARTITION_METHOD: NULL PARTITION_EXPRESSION: month(`tr_date`) SUBPARTITION_EXPRESSION: NULL PARTITION_DESCRIPTION: NULL TABLE_ROWS: 1 AVG_ROW_LENGTH: 16384 DATA_LENGTH: 16384 MAX_DATA_LENGTH: 0 INDEX_LENGTH: 0 DATA_FREE: 0 CREATE_TIME: 2020-06-19 21:31:01 UPDATE_TIME: 2020-06-19 21:33:55 CHECK_TIME: NULL CHECKSUM: NULL PARTITION_COMMENT: NODEGROUP: default TABLESPACE_NAME: NULL 6 rows in set (0.00 sec)
パーティションのタイプもわかりますし、最後にデータが更新された時間やどのパーティションに何件入ってるかなどもわかりますね。
EXPLAIN
EXPLAINで指定された SELECT によってどのパーティションが使用されているかを確認することができます。
mysql [localhost:8020] {admin} (test) > EXPLAIN select * from `test`.`ti`\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: ti partitions: p0,p1,p2,p3,p4,p5 type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 8 filtered: 100.00 Extra: NULL 1 row in set, 1 warning (0.02 sec)
partitions列に作られたパーティションが表示されていますね。
mysql [localhost:8020] {admin} (test) > EXPLAIN select * from `test`.`ti` where tr_date = '2020-01-01'; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: ti partitions: p1 type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 2 filtered: 50.00 Extra: Using where 1 row in set, 1 warning (0.01 sec)
WHERE句に条件を入れると、使用されたパーティションだけが表示されました。
ALTER TABLE ...COALESCE PARTITION
昔レンジパーティションの記事を読んだときにalter table drop partitionってあったな、と思ったらドキュメントに
DROP PARTITION can be used to drop one or more RANGE or LIST partitions. This statement cannot be used with HASH or KEY partitions; instead, use COALESCE PARTITION (see later in this section).
(DROP PARTITIONはRANGE または LIST パーティションでのみ使え、HASH または KEY パーティションに使用することはできません。HASH または KEY パーティションの場合は代わりに COALESCE PARTITION を使用します。)
とあったのでALTER TABLE ...COALESCE PARTITIONでパーティションを減らしました。
ちなみにCOALESCE PARTITION で指定する数字は減らすパーティション数なのでご注意を。
試しにやってみます
mysql [localhost:8020] {admin} (test) > alter table ti drop partition p0; ERROR 1512 (HY000): DROP PARTITION can only be used on RANGE/LIST partitions mysql [localhost:8020] {admin} (test) > ALTER TABLE ti COALESCE PARTITION 4; Query OK, 0 rows affected (0.13 sec) Records: 0 Duplicates: 0 Warnings: 0
さて、確認してみましょう。
SHOW CREATE TABLE
mysql [localhost:8020] {admin} (test) > show create table `test`.`ti`\G *************************** 1. row *************************** Table: ti Create Table: CREATE TABLE `ti` ( `id` int DEFAULT NULL, `amount` decimal(7,2) DEFAULT NULL, `tr_date` date DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!50100 PARTITION BY HASH (month(`tr_date`)) PARTITIONS 2 */ 1 row in set (0.00 sec)
4つ減らしたので2になってます。
SHOW TABLE STATUS
まあ、これは変わりませんねw
mysql [localhost:8020] {admin} (test) > show table status like 'ti'\G *************************** 1. row *************************** Name: ti Engine: InnoDB Version: 10 Row_format: Dynamic Rows: 8 Avg_row_length: 12288 Data_length: 98304 Max_data_length: 0 Index_length: 0 Data_free: 0 Auto_increment: NULL Create_time: 2020-06-19 22:27:58 Update_time: 2020-06-19 21:34:08 Check_time: NULL Collation: utf8mb4_0900_ai_ci Checksum: NULL Create_options: partitioned Comment: 1 row in set (0.00 sec)
information_schema.partitions
mysql [localhost:8020] {admin} (test) > select * from information_schema.partitions where table_schema = 'test' and table_name = 'ti'\G *************************** 1. row *************************** TABLE_CATALOG: def TABLE_SCHEMA: test TABLE_NAME: ti PARTITION_NAME: p0 SUBPARTITION_NAME: NULL PARTITION_ORDINAL_POSITION: 1 SUBPARTITION_ORDINAL_POSITION: NULL PARTITION_METHOD: HASH SUBPARTITION_METHOD: NULL PARTITION_EXPRESSION: month(`tr_date`) SUBPARTITION_EXPRESSION: NULL PARTITION_DESCRIPTION: NULL TABLE_ROWS: 0 AVG_ROW_LENGTH: 0 DATA_LENGTH: 16384 MAX_DATA_LENGTH: 0 INDEX_LENGTH: 0 DATA_FREE: 0 CREATE_TIME: 2020-06-19 22:27:58 UPDATE_TIME: NULL CHECK_TIME: NULL CHECKSUM: NULL PARTITION_COMMENT: NODEGROUP: default TABLESPACE_NAME: NULL *************************** 2. row *************************** TABLE_CATALOG: def TABLE_SCHEMA: test TABLE_NAME: ti PARTITION_NAME: p1 SUBPARTITION_NAME: NULL PARTITION_ORDINAL_POSITION: 2 SUBPARTITION_ORDINAL_POSITION: NULL PARTITION_METHOD: HASH SUBPARTITION_METHOD: NULL PARTITION_EXPRESSION: month(`tr_date`) SUBPARTITION_EXPRESSION: NULL PARTITION_DESCRIPTION: NULL TABLE_ROWS: 0 AVG_ROW_LENGTH: 0 DATA_LENGTH: 16384 MAX_DATA_LENGTH: 0 INDEX_LENGTH: 0 DATA_FREE: 0 CREATE_TIME: 2020-06-19 22:27:58 UPDATE_TIME: NULL CHECK_TIME: NULL CHECKSUM: NULL PARTITION_COMMENT: NODEGROUP: default TABLESPACE_NAME: NULL 2 rows in set (0.00 sec)
TABLE_ROWSが0になってますね・・・
ドキュメントには
The data contained in the last number partitions will be merged into the remaining partitions. In this case, partitions 4 and 5 will be merged into the first 4 partitions (the partitions numbered 0, 1, 2, and 3).
とあるんで、これらに入ると思ったんですがふっとんだんでしょうか?
mysql [localhost:8020] {admin} (test) > select * from ti; +------+--------+------------+ | id | amount | tr_date | +------+--------+------------+ | 6 | 600.00 | 2020-06-01 | | 2 | 200.00 | 2020-02-02 | | 2 | 201.00 | 2020-02-01 | | 4 | 400.00 | 2020-04-01 | | 1 | 100.00 | 2020-01-01 | | 1 | 101.00 | 2020-01-02 | | 3 | 301.00 | 2020-03-01 | | 5 | 500.00 | 2020-05-01 | +------+--------+------------+ 8 rows in set (0.00 sec)
ありますねえ…。
この後、再起動したり、データ入れてみたりしましたが、今まで入ってた分はTABLE_ROWSに戻らず。
EXPLAIN
(root@localhost) [test]> EXPLAIN select * from `test`.`ti`\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: ti partitions: p0,p1 type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 1 filtered: 100.00 Extra: NULL 1 row in set, 1 warning (0.02 sec)
partitionsはp0,p1 だけになってますね。
こちらもrowsが1(試しに後から入れた1件)のみ…。
どうしても行数を復活させたい
というわけで試すことにしました。
ALTER TABLE ... REBUILD PARTITION;
ALTER TABLE ... REBUILD PARTITIONでパーティションを再構築してみます。
ドキュメントでは
Rebuilds the partition; this has the same effect as dropping all records stored in the partition, then reinserting them. This can be useful for purposes of defragmentation.
(パーティションに格納されているすべてのレコードを削除してからそれらを再度挿入することと同じ効果があります。これはデフラグに役立つことがあります。)
とあるので、再構築すれば読めるようになるのでは?
(root@localhost) [test]> alter table ti rebuild partition p0,p1; Query OK, 0 rows affected (0.10 sec) Records: 0 Duplicates: 0 Warnings: 0
(root@localhost) [test]> select * from information_schema.partitions where table_schema = 'test' and table_name = 'ti'\G *************************** 1. row *************************** TABLE_CATALOG: def TABLE_SCHEMA: test TABLE_NAME: ti PARTITION_NAME: p1 SUBPARTITION_NAME: NULL PARTITION_ORDINAL_POSITION: 2 SUBPARTITION_ORDINAL_POSITION: NULL PARTITION_METHOD: HASH SUBPARTITION_METHOD: NULL PARTITION_EXPRESSION: month(`tr_date`) SUBPARTITION_EXPRESSION: NULL PARTITION_DESCRIPTION: NULL TABLE_ROWS: 0 AVG_ROW_LENGTH: 0 DATA_LENGTH: 16384 MAX_DATA_LENGTH: 0 INDEX_LENGTH: 0 DATA_FREE: 0 CREATE_TIME: 2020-06-20 08:47:54 UPDATE_TIME: NULL CHECK_TIME: NULL CHECKSUM: NULL PARTITION_COMMENT: NODEGROUP: default TABLESPACE_NAME: NULL *************************** 2. row *************************** TABLE_CATALOG: def TABLE_SCHEMA: test TABLE_NAME: ti PARTITION_NAME: p0 SUBPARTITION_NAME: NULL PARTITION_ORDINAL_POSITION: 1 SUBPARTITION_ORDINAL_POSITION: NULL PARTITION_METHOD: HASH SUBPARTITION_METHOD: NULL PARTITION_EXPRESSION: month(`tr_date`) SUBPARTITION_EXPRESSION: NULL PARTITION_DESCRIPTION: NULL TABLE_ROWS: 0 AVG_ROW_LENGTH: 0 DATA_LENGTH: 16384 MAX_DATA_LENGTH: 0 INDEX_LENGTH: 0 DATA_FREE: 0 CREATE_TIME: 2020-06-20 08:47:54 UPDATE_TIME: NULL CHECK_TIME: NULL CHECKSUM: NULL PARTITION_COMMENT: NODEGROUP: default TABLESPACE_NAME: NULL 2 rows in set (0.01 sec)
残念、再構築では入りませんでした。
ALTER TABLE ... OPTIMIZE PARTITION
次にパーティションの最適化をやってみます。
ドキュメントでは
ALTER TABLE ... OPTIMIZE PARTITION を使用して、未使用領域を解放したりパーティションデータファイルをデフラグしたりできます。 (パーティションから多数の行を削除した場合、または可変長行を持つ (つまり、VARCHAR、BLOB、または TEXT カラムを持つ) パーティション化されたテーブルに多くの変更を行なった場合は、 ALTER TABLE ... OPTIMIZE PARTITION を使用して、未使用領域を解放したりパーティションデータファイルをデフラグしたりできます。)
とあるので、少しイメージと違うような気持ちもしますが試してみましょう。
(root@localhost) [test]> ALTER TABLE ti OPTIMIZE PARTITION p0, p1; +---------+----------+----------+---------------------------------------------------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +---------+----------+----------+---------------------------------------------------------------------------------------------+ | test.ti | optimize | note | Table does not support optimize on partitions. All partitions will be rebuilt and analyzed. | | test.ti | optimize | status | OK | +---------+----------+----------+---------------------------------------------------------------------------------------------+ 2 rows in set (0.08 sec)
1パーティションに対してはできないよ!という注意が見えますが、ちゃんと全パーティションを選択しているのでOKとなってます。
(root@localhost) [test]> select * from information_schema.partitions where table_schema = 'test' and table_name = 'ti'\G *************************** 1. row *************************** TABLE_CATALOG: def TABLE_SCHEMA: test TABLE_NAME: ti PARTITION_NAME: p1 SUBPARTITION_NAME: NULL PARTITION_ORDINAL_POSITION: 2 SUBPARTITION_ORDINAL_POSITION: NULL PARTITION_METHOD: HASH SUBPARTITION_METHOD: NULL PARTITION_EXPRESSION: month(`tr_date`) SUBPARTITION_EXPRESSION: NULL PARTITION_DESCRIPTION: NULL TABLE_ROWS: 5 AVG_ROW_LENGTH: 3276 DATA_LENGTH: 16384 MAX_DATA_LENGTH: 0 INDEX_LENGTH: 0 DATA_FREE: 0 CREATE_TIME: 2020-06-20 08:48:16 UPDATE_TIME: NULL CHECK_TIME: NULL CHECKSUM: NULL PARTITION_COMMENT: NODEGROUP: default TABLESPACE_NAME: NULL *************************** 2. row *************************** TABLE_CATALOG: def TABLE_SCHEMA: test TABLE_NAME: ti PARTITION_NAME: p0 SUBPARTITION_NAME: NULL PARTITION_ORDINAL_POSITION: 1 SUBPARTITION_ORDINAL_POSITION: NULL PARTITION_METHOD: HASH SUBPARTITION_METHOD: NULL PARTITION_EXPRESSION: month(`tr_date`) SUBPARTITION_EXPRESSION: NULL PARTITION_DESCRIPTION: NULL TABLE_ROWS: 4 AVG_ROW_LENGTH: 4096 DATA_LENGTH: 16384 MAX_DATA_LENGTH: 0 INDEX_LENGTH: 0 DATA_FREE: 0 CREATE_TIME: 2020-06-20 08:48:16 UPDATE_TIME: NULL CHECK_TIME: NULL CHECKSUM: NULL PARTITION_COMMENT: NODEGROUP: default TABLESPACE_NAME: NULL 2 rows in set (0.00 sec)
おお、TABLE_ROWSが見事に見えるようになりました。
正解はOPTIMIZE PARTITIONだったようです。
(root@localhost) [test]> EXPLAIN select * from `test`.`ti`\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: ti partitions: p0,p1 type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 9 filtered: 100.00 Extra: NULL 1 row in set, 1 warning (0.00 sec)
EXPLAINでもrowsが 9に見えてます。
最後に
今日はパーティションと少し戯れてみました。
パーティションの数変更した後に確認しようとしたときに行数が狂っちゃう(0になっちゃう)のはイケてないと思うんだけど、直し方はありました。
(バグ登録されてるのかな?あとで見てみよう)
まだ触りたてなので、他の3つのパーティションタイプやそれぞれでのALTER TABLE ... ANALYZE PARTITION、CHECK PARTITION、REPAIR PARTITION、REMOVE PARTITIONINGあたりも触ってないのでそのへんも気が向いたら手を出していきます。
(でも、そもそも使うとしたらみなさんレンジやリストのタイプなのかしら?)
あ、あと、MySQL5.6ではEXPLAINではなくて、EXPLAIN PARTITIONSなのでお間違えなく
(root@127.0.0.1) [test]> EXPLAIN PARTITIONS select * from ti\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: ti partitions: p0,p1,p2,p3,p4,p5 type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 6 Extra: NULL 1 row in set (0.00 sec)
参考
【ポエムというか愚痴】おれはソネットをやめるぞ!ジョジョーーーーーッ‼
全くMySQLに関係ない、でも、きっかけは家で仕事するのに不都合だったから起こった身の上の話
光を超越する!
オトコは悩んでいた。
家は東京のとある集合住宅、150以上の世帯が住むマンション。
その家の光回線が遅いのである。
回線速度を測ると、1Gbpsをうたっているはずの光回線がその100分の1も出ないのである。
ひどいときには1000分の1になるときもあった。
オトコの仕事はサポート業務。
お客さんがトラブルを解消したい場合や、知見を得たいというときにそれを補助する仕事だ。
トラブルの時はログファイル等を添付していることもある。
あるバージョンのアプリケーションで問題が発生しているといったときはそのバージョンのアプリケーションをダウンロードして検証しなければならない。
オトコはそれまで家で仕事をしたことが無かった。
仕事は会社でするもの。
家でするのはあくまで緊急時、特に深夜のみだった。
しかし、コロナウイルスが家で仕事を強要した。
もちろん、それはオトコだけでなく、オトコの妻も、マンションに住む全ての社会人がそうなった。
そして、子供たちも学校や幼稚園・保育園に行けず、家にいることになった。
彼らは何をしただろう。
恐らく、オトコの家と同じく子供はインターネット経由で動画を楽しみ、親はリモートワークでインターネットを使ったのだろう。
同じ時間帯にそれらがマンションの半分、いや3分の1の世帯で起こったとしたら…。
マンションは築40年を超える物件だ。
今流行りの1Gbpsの光回線を入れることはできないらしい。
光と言っても100Mbpsが最大の貧弱な回線だ。
そこに一斉にインターネットを使う状況ができれば、それはそうなるのは明らかである。
オトコは決断した。
光をやめて、無線にしよう!
携帯のほうが速度も出ている。
速度の出ない光を使い続けるくらいなら無線でその数倍~数十倍の速度でやったほうが仕事がはかどるハズだ!
プロバイダーはそのままに、光コラボからWiMAXへ
無駄にダラダラと文章を書いたけど、要するにコロナのせいでマンションの有線がクッソ遅すぎて仕事にならないから無線に切り替えようと考えたんです。
そしてその際、プロバイダーとして使ってたSo-netにWiMAXの取り扱いがあったので、光回線を解約してWiMAXの契約をポチリました。
光回線は月末で廃線になる予定なので、それまでは使い、WiMAXとの並行運用で速度検証を行ったりしました。
WiMAXは3日後くらいに来たので、1週間くらい並行稼働してましたが、光が1Mbps~10Mbpsなのに対し、WiMAXが10Mbps~80Mbpsと約10倍の速度が出ていて、これなら自宅でも多少快適になるだろうと喜んでいました。
解約料が7500円取られるらしいけど、それくらいなら我慢しよう、そう思ってました。
ところが、
突然WiMAXがつながらなくなる
子供が動画を見ていたところ、急にインターネットが使えなくなりました。
時間は19時頃。
「何事だ?」
電源を入れなおしても、リセットしても通信ができません。
機器の故障か?と思いましたが、直接その機器にはアクセスできるようなのでそうではない模様。
「まさか!?」
So-netの契約を見に行きました。
退会処理が完了しました(5月末日付け退会)
いやいやいや、どういうこと???
確認を取ろうと思いましたが、コロナの状況下でプロバイダーのサポート業務も18時までとなっていました。
過去のメールを見ても解約になってるのは光回線のみ。WiMAXの文字はありません。
ただ、光回線のメールに不穏な文字が。
「退会した場合にもこのメールは送られます」
至急確認したいし、明日仕事どうすれば?
いや、日中ならまだしも、そっちの定時後にやられたら確認のしようが無いじゃないか…。
自分一人ならテザリングでなんとかしのげますが、嫁も家にいる日、時間帯があるので至急復旧が必要です。
会社には無理やり1日休みをもらい、朝から確認と、最悪別契約を取りに行く準備を前日に行いました。
翌朝9時からプロバイダーに確認
翌朝、朝9時にPCの前に座り、So-netのサポートに問い合わせました。
サポートの人と話すことで、以下のようなことが分かりました。
- WiMAXの契約前に光回線を解約している。
- 自分は光回線の「オプション」としてWiMAXを契約していた。(本人は単にマイページがあるので、「サービスを利用中の場合」のところから契約をした)
- 光回線が解約された時、退会も同時に行われた。
- WiMAXはオプション扱いなので同時に解約された。
- コース変更というものがあったが、それに気づいてなかった
- 退会されたのでWiMAXの復旧は不可(少なくとも1日以内では)。
- WiMAXの解約料も当然発生。
光回線を主回線とし、WiMAXはオプションで従という関係なのは100歩譲ってわかるとしても、
主回線の解約後に従のオプション回線が契約できて、主が解約・退会になったから従も併せて解約…
って常識で考えたらおかしいって思わないかな?
せめてコース変更を自動で切り替えてくれればこんなこと起こらなかったのに。
まあ、自分もサポート業務やってるので、サポートの人に言ってもかわいそうなんで、もめずに解約料の話も許容して引き下がりましたよ。
(でも、納得は行ってないから書くけどね!)
1週間程度しか使ってないもの解約料と登録手数料に22000円、そして解約した光の7500円かかって、その他諸々で3万超えてます。
そして、急遽回線引かなければいけなくなったんで、お店が開いてて今日SIMもらえるってなると限られてて、結局WiMAXしか選択肢なかったし。
かつ、SIMだけっていうのが無くてルーターも買わなきゃで、また出費(約2万)…。
合計5万程度余計に払うことに。
復旧作業に4万強使いました。
それでもポジティブに考えてみる
無駄にお金をドブに捨ててるので、何を考えてみても焼け石に水なのですが、それでも無理やりひねり出すと、
- 公式で契約したことで携帯代がこれから毎月300円安くなる(手続きもやってもらった)
- ルーターをHOME用と持ち歩き用両方手に入れた
- 明日は仕事ができる!(急遽休んでごめんなさい)
- 体調悪かったので、時間も十分にあるので病院に行ける(これから行く)
- So-netは契約のシステムがザルだったのを忘れてた自分が悪い(そういえばそうだった)
- 子供が大きくなったら、このマンションは貸して、田舎に引っ越そうという気持ちが固まった
- 全てコロナのせい
全てコロナが悪いんですよ!
早く正常な世の中に戻ってほしいですね。
MySQL8.0.20で追加・変更されたパラメータを眺める
はじめに
MySQL8.0.20で追加・変更されたパラメータをなんとなく眺めてみました。
パラメータの説明については基本的にはドキュメントからの引用です。
変更されたパラメータ一覧
例によってとみたさんのパラメータ比較ツールをお借りしました。
Parameter | 8.0.20 | 説明 |
---|---|---|
binlog-transaction-compression | FALSE | バイナリログファイルに書き込まれるトランザクションの圧縮を有効にする |
binlog-transaction_compression_level-zstd | 3 | バイナリログファイルの圧縮レベルを設定 |
innodb_doublewrite_batch_size | 0 | バッチで書き込むダブルライトバッファページの数 |
innodb_doublewrite_dir | (No default value) | ダブルライトバッファのファイルディレクトリ |
innodb_doublewrite_files | 0 | ダブルライトファイルの数 |
innodb_doublewrite_pages | 0 | バッチ書き込みのスレッドあたりの二重書き込みページの最大数 |
mysqlx_deflate_default_compression_level | 3 | サーバが Xプロトコル接続で Deflate アルゴリズムに使用する既定の圧縮レベル |
mysqlx_deflate_max_client_compression_level | 5 | サーバが X プロトコル接続上の Deflate アルゴリズムに対して許可する最大の圧縮レベル |
mysqlx_lz4_default_compression_level | 2 | サーバが X プロトコル接続で LZ4 アルゴリズムに使用する既定の圧縮レベル |
mysqlx_lz4_max_client_compression_level | 8 | サーバが X プロトコル接続上の LZ4 アルゴリズムに対して許可する最大の圧縮レベル |
mysqlx_zstd_default_compression_level | 3 | X プロトコル接続でサーバーが zstd アルゴリズムに使用するデフォルトの圧縮レベル |
mysqlx_zstd_max_client_compression_level | 11 | サーバが X プロトコル接続上の zstd アルゴリズムに対して許可する最大の圧縮レベル |
performance_schema_error_size | (number of server error codes) | インスツルメント化されたサーバーエラーコードの数 |
バイナリログトランザクション圧縮
MySQL8.0.20からbinlogが圧縮できます。
これについてはけんつさんが検証してくれてますので、非常に参考になります。
パラメータについては以下の通り
binlog_transaction_compression
- このサーバーのバイナリ ログ ファイルに書き込まれるトランザクションの圧縮を有効にします。
- デフォルト値は OFF 。
- 圧縮に使用する zstd アルゴリズムのレベルを設定するには、binlog_transaction_compression_level_zstd システム変数を使用します。
- バイナリ・ログ・トランザクション圧縮が有効な場合、トランザクション・ペイロードは圧縮され、単一のイベント (Transaction_payload_event) としてバイナリ・ログ・ファイルに書き込まれます。
- 圧縮されたトランザクション ペイロードは、レプリケーション スレーブ、他のグループ レプリケーション グループ メンバ、または mysqlbinlog のようなクライアントにレプリケーション ストリームで送信されている間、圧縮された状態のままでリレー ログに書き込まれます。
- したがって、バイナリログのトランザクション圧縮は、トランザクションの発信者と受信者の両方(およびそれらのバックアップのための)ストレージスペースを節約し、トランザクションがサーバインスタンス間で送信されるときにネットワーク帯域幅を節約します。
- binlog_transaction_compression=ON が直接効果を発揮するためには、サーバー上でバイナリロギングが有効になっている必要があります。
- MySQLサーバインスタンスがバイナリログを持たない場合、MySQL 8.0.20からのリリースであれば、binlog_transaction_compressionの値に関係なく、圧縮されたトランザクションペイロードを受信、処理、表示することができます。
- そのようなサーバインスタンスによって受信された圧縮されたトランザクションペイロードは、その圧縮された状態でリレーログに書き込まれるので、レプリケーショントポロジ内の他のサーバによって実行された圧縮から間接的に恩恵を受けます。
- このシステム変数は、トランザクションのコンテキスト内では変更できない。
- このシステム変数のセッション値の設定は制限された操作です。セッションユーザーは制限されたセッション変数を設定するのに十分な権限を持っていなければならない。
- セクション5.1.9.1「システム変数の特権」を参照してください。
- 圧縮されるイベントと圧縮されないイベントの詳細や、トランザクション圧縮が使用されているときの動作の変化など、バイナリログトランザクション圧縮の詳細については、5.4.4.5項 「バイナリログトランザクション圧縮」を参照してください。
もしCPUはヒマしてるけど、大量にデータ更新してて、ネットワークがネックなMySQL環境があればONにして、次の圧縮レベルを調整すると良いかもしれませんね。
binlog_transaction_compression_level_zstd
- このサーバのバイナリログトランザクション圧縮の圧縮レベルを設定します。
- 値は、圧縮努力を決定する整数で、1 (最も低い) から 22 (最も高い) までの値を指定します。
- このシステム変数を指定しない場合、圧縮レベルは3に設定されます。
- 圧縮レベルが高くなると、データ圧縮率が高くなり、トランザクション・ペイロードに必要なストレージ・スペースとネットワーク帯域幅が削減されます。
- しかし、データ圧縮に必要な労力も増加し、時間と発信元サーバのCPUおよびメモリリソースが必要となる。圧縮努力の増加は、データ圧縮率の増加と直線的な関係はありません。
- このシステム変数は、トランザクションのコンテキスト内では変更できません。
- このシステム変数のセッション値の設定は制限された操作です。
- セッションユーザは、制限されたセッション変数を設定するのに十分な権限を持っていなければならない。
- セクション5.1.9.1 「システム変数の特権」を参照してください。
けんつさんの記事から読むに、デフォルトの3でも半分くらいになるみたいなので、22とかどうなっちゃうの?とw
ダブルライトバッファの改良
- 8.0.20以降、doublewriteバッファストレージ領域をInnoDBシステムテーブルスペースではなく、別途専用ファイルに分けることができるようになりました。
- これにより書き込みレイテンシが減少するため、スループットを向上させることができ、ダブルライトバッファページの配置に関して柔軟性が提供されます。
- 8.0.20では、ダブルライトバッファの構成用にパラメータが追加されました。
ドキュメントを読む限りはinnodb_doublewrite_dir以外はあまり変更する必要は無さそうですが、書き込み量やファイルサイズからデフォルト値だと…という場合に触ってみる可能性があるかも、でしょうか。
ダブルライトバッファについて
念のため、ダブルライトバッファについて軽く触れておくと、
- InnoDBではデータファイル更新前にダブルライトバッファと呼ばれる領域(ファイル)にデータを書き込む。
- マシンクラッシュでデータページが壊れていても、ダブルライトバッファからリカバリされる。
- ダブルライトバッファが壊れていた場合は、そのデータは破棄。
- ダブルライトバッファは連続した領域なので、書き込みのオーバーヘッドはデータファイルより少ない
- とはいえ、書き込み量、利用領域が増えるので不要な場合は無効にできる
innodb_doublewrite_batch_size
- バッチで書き込むダブルライトバッファページの数を定義します。
- 基本的にはデフォルト値のままで問題ないはずですが、パフォーマンスチューニングしたい場合には調整するパラメータ。
innodb_doublewrite_dir
- ダブルライトバッファのファイルディレクトリを定義します。
- ディレクトリが指定されていない場合、ディレクトリに二重書き込みファイルが作成されます。
- 指定されていない場合は、innodb_data_home_dirのデフォルトでデータディレクトリが作成されます。
- スキーマ名との競合を避けるために、指定されたディレクトリ名の前にハッシュ記号「#」が自動的に付加されます。
- 理想的には、doublewriteディレクトリは、利用可能な最速のストレージメディアに配置する必要があります。
innodb_doublewrite_files
- ダブルライトファイルの数を定義します。
- デフォルトでは、バッファープールインスタンスごとに2つの二重書き込みファイルが作成されます。
- 少なくとも、2つの二重書き込みファイルがあります。
- doublewriteファイルの最大数は、バッファープールインスタンスの数の2倍です。(バッファー・プール・インスタンスの数はinnodb_buffer_pool_instances 変数によって制御されます。)
- 二重書き込みファイル名の形式は次のとおりです 。
#ib_16384_0.dblwr #ib_16384_1.dblwr
- 基本的にはデフォルト値のままで問題ないはずですが、パフォーマンスチューニングしたい場合には調整するパラメータ。
innodb_doublewrite_pages
- バッチ書き込みのスレッドあたりの二重書き込みページの最大数を定義します。
- 値が指定されていない場合は、innodb_doublewrite_pagesはinnodb_write_io_threadsの値に設定されます。
- 基本的にはデフォルト値のままで問題ないはずですが、パフォーマンスチューニングしたい場合には調整するパラメータ。
Xプロトコル接続を介して送信されるメッセージの圧縮
MySQL 8.0.19以降、Xプロトコル接続を介して送信されるメッセージの圧縮がサポートされました。
サーバーとクライアントが使用する圧縮アルゴリズムに同意した場合、接続を圧縮でき、デフォルトでは、Deflate、LZ4、およびzstd圧縮アルゴリズムを許可します。
ところが、MySQL 8.0.19では、サーバー側で圧縮レベルがデフォルト設定化されていて、クライアントはこれを調整することができません。
それがMySQL 8.0.20以降、クライアントはXプロトコル接続のケイパビリティ ネゴシエーション(※)中に特定の圧縮レベルを要求できるようになったとのこと。
一方でアルゴリズムごとに最大圧縮レベルを設定できるようになっていて、サーバーが忙しい時に、クライアントが高圧縮レベルを要求しても、最大圧縮レベル以上の負荷をかけることを防ぎます。
ちなみに、この圧縮レベルの要求はMySQL Shell経由でのみ行われるため、現時点ではMySQLクライアントやその他のConnector系はサポートされていないようです。(20.5.5項 「X プラグインによる接続圧縮」より)
ちなみに、ケイパビリティ ネゴシエーション(capability negotiation)っていうのがよくわからんな、と思って調べたらドキュメントにありました。
ケイパビリティ ネゴシエーション(capability negotiation)
14.2.1.3 Capability Negotiation
要するに、サーバーとクライアントでやり取りするのに色んなもの使うし、使えますよ、と。(例えば、サーバーのバージョンや認証情報(認証方式)とか)
mysqlx_deflate_default_compression_level
- サーバが X プロトコル接続で Deflate アルゴリズムに使用する既定の圧縮レベル。
- レベルは、1 (最も低い圧縮率) から 9 (最も高い圧縮率) までの整数で指定。
- このレベルは、クライアントがケイパビリティ ネゴシエーション中に圧縮レベルを要求しない場合に使用されます。
- このシステム変数を指定しない場合、サーバーはレベル3を既定値として使用します。
- 詳細については、20.5.5項 「X プラグインによる接続圧縮」を参照してください。
mysqlx_deflate_max_client_compression_level
- サーバが X プロトコル接続上の Deflate アルゴリズムに対して許可する最大の圧縮レベル。
- 範囲は、このアルゴリズムの既定の圧縮レベルと同じ(1から9)です。
- クライアントがこれより高い圧縮レベルを要求した場合、サーバはここで設定したレベルを使用します。
- このシステム変数を指定しない場合、サーバは最大圧縮レベルを 5 に設定します。
Deflateとは
Deflate(デフレート)とはLZ77とハフマン符号化を組み合わせた可逆データ圧縮アルゴリズム。フィル・カッツが開発した圧縮ツールPKZIPのバージョン2で使われていた。ZIPやgzipなどで使われている。1996年5月に RFC 1951 としてドキュメント化された。ヘッダーやフッターをつけた zlib (RFC 1950) 形式や gzip (RFC 1952) 形式とともに使われる事が多い。 Deflateアルゴリズムが利用されているソフトウェアの一例を挙げる。
zlib ZIP GZIP 7z Portable Network Graphics (PNG) また、ほとんどのプログラミング言語で利用できる。以下はその一例。
Java - Deflater クラスで nowrap を有効にすることにより素の deflate が扱え、別途 zlib 形式や gzip 形式のヘッターやフッターの付いた物も扱える。 Perl PHP Python Ruby C#、VB.NET等の.NET Framework 2.0以降対応言語 - DeflateStream クラスで素の deflate もしくは GZipStream クラスで gzip 形式。 Apache HTTP ServerなどのWebサーバでも圧縮通信を zlib 形式で Deflate を使って実装している。 (Wikipediaより)
というわけで普段からよくお世話になってる可逆圧縮方式のあいつらはDeflateだったわけでした。
mysqlx_lz4_default_compression_level
- サーバが X プロトコル接続で LZ4 アルゴリズムに使用する既定の圧縮レベル。
- レベルは、0 (最も低い圧縮率) から 16 (最も高い圧縮率) までの整数で指定します。
- このレベルは、クライアントがケイパビリティ ネゴシエーション中に圧縮レベルを要求しない場合に使用されます。
- このシステム変数を指定しない場合、サーバーはレベル2を既定値として使用します。
- 詳細については、20.5.5項 「X プラグインによる接続圧縮」を参照してください。
LZ4とは
LZ4 は圧縮と展開の速さに焦点を当てた可逆圧縮アルゴリズムである。バイト指向の圧縮方法であるLZ77ファミリーに属する。 (Wikipediaより)
mysqlx_lz4_max_client_compression_level
- サーバが X プロトコル接続上の LZ4 アルゴリズムに対して許可する最大の圧縮レベル。
- 範囲は、このアルゴリズムの既定の圧縮レベルと同じです。
- クライアントがこれより高い圧縮レベルを要求した場合、サーバはここで設定したレベルを使用します。
- このシステム変数を指定しない場合、サーバは最大圧縮レベルを 8 に設定します。
mysqlx_zstd_default_compression_level
- Xプロトコル接続でサーバーが zstd アルゴリズムに使用するデフォルトの圧縮レベル。
- 1.4.0 以降の zstd ライブラリのバージョンでは、1 から 22 までの正の値 (最高の圧縮率)、または負の値 (圧縮率が徐々に低くなることを表す) を設定できます。
- 0 の値は 1 の値に変換されます。
- zstd ライブラリの以前のバージョンでは、3 の値しか指定できません。
- このレベルは、ケイパビリティ・ネゴシエーション中にクライアントが圧縮レベルを要求しない場合に使用されます。
- このシステム変数を指定しない場合、サーバーはレベル3をデフォルトとして使用します。
- 詳細については、20.5.5項 「X プラグインによる接続圧縮」を参照してください。
Zstandard (Zstd)
Zstandard (Zstd) は、2015年からFacebookに所属しているYann Colletによって開発された可逆圧縮アルゴリズムである。またCで書かれた前述のアルゴリズムのリファレンス実装の名前でもある。 Zstandardは現在主流であるDeflate (ZIP, gzip) アルゴリズムによるものと遜色ない圧縮を、より高速に行えるように設計されている。 Zstandardは大きな検索窓の辞書式圧縮アルゴリズム (LZ77) とエントロピー符号化を併用しており、エントロピー符号化ステージで有限状態エントロピー(FSE)のtANS(英語版) あるいはハフマン符号化を使用している。Zstandardの実装で特徴的なのはエントロピー復号化時に逆方向から読み取ることである。 (Wikipediaより)
mysqlx_zstd_max_client_compression_level
- サーバが X プロトコル接続上の zstd アルゴリズムに対して許可する最大の圧縮レベル。
- 範囲は、このアルゴリズムの既定の圧縮レベルと同じです。
- クライアントがこれより高い圧縮レベルを要求した場合、サーバはここで設定したレベルを使用します。
- このシステム変数を指定しない場合、サーバは最大圧縮レベルを 11 に設定します。
performance_schema_error_size
- インスツルメント化されたサーバーエラーコードの数。
- デフォルト値はサーバーエラーコードの実際の数なので、サーバーエラーコードが増えたことで変更となりました。
- 値は0から最大値まで設定できますが、使用目的は、デフォルト(すべてのエラーを計測する)または0(エラーなしを計測する)に設定することです。
- エラー情報は要約テーブルに集約されます。セクション26.12.18.11「エラーサマリーテーブル」を参照してください。
まとめ
- MySQL8.0.20では圧縮形の機能追加が二つあったことがパラメータから見て取れました。
- ダブルライトバッファ系パラメータはinnodb_doublewrite_dir以外はあまり変更する必要は無さそうです。
- innodb_doublewrite_dirを高速なディスクに設定すると良さそうです。
さいごに
- 和訳部分についてはドキュメントをDeepLやGoogle翻訳を使って訳したものを一部引用してます。
- 日本語がおかしい部分は自分で訳しましたが、合ってなかったらごめんなさい。
MySQLでのテーブルコピー(InnoDBとMyISAM)
MySQLでのテーブルコピーを考える
MySQLでテーブルコピー(同一インスタンス内とか別インスタンスとかあるけど)って意外とテストとかちょっとしたリカバリ作業用にしたくなりますよね。
というわけで、InnoDBとMyISAM関連だけですが書き出してみました。
コピーパターン
僕がパッと思いつく限りだとこの辺ですが、後から追記する可能性あり〼。
- Mysql Enterprise Backup(MEB)
- mysqldump/mysqlpump
- INSERT...SELECT
- CREATE TABLE...SELECT
- OSファイルコピー
- トランスポータブルテーブルスペース(TTS)
- MEB + TTS
- IMPORT TABLE
- その他
MEB(InnoDB,MyISAM)
MySQL Enterprise Backup と呼ばれる商用の製品です。
以下、ドキュメントからの転記です
- 実行中の MySQL データベース (InnoDB および MyISAM テーブルを含む) を、データベースの整合性のあるスナップショットを生成しながら、操作の中断を最小限に抑えてバックアップできます。
- MySQL Enterprise Backup が InnoDB テーブルをコピーしている間は、InnoDB テーブルと MyISAM テーブルの両方に対する読み取りと書き込みを続行できます。
- MyISAM およびその他の InnoDB 以外のテーブルのコピー中は、これらのテーブルに対する (書き込みではなく) 読み取りが許可されます。
- さらに、MySQL Enterprise Backup では、圧縮バックアップファイルを作成したり、InnoDB テーブルのサブセットをバックアップしたりすることもできます。
- MySQL のバイナリログと組み合わせると、ポイントインタイムリカバリを実行できます。
ユーザーガイドはこちらですが、EE版を契約した人には使わない手は無い代物だと思います。
mysqldump/mysqlpump(InnoDB,MyISAM)
皆さんご存じmysqldumpとあまりご存じじゃないmysqlpumpですw
mysqlpumpだけの機能だとか、mysqldumpとの違いなんかは既に僕ら大好きyoku0825さんがまとめてくださってるので多くは語らず。
あえて言うなら、mysqlpumpはMySQL5.7以降で使いましょう。
そして、mysqldumpと比べると新しい機能なのでまだまだ枯れてない部分もありそうです。
こういう話もありますので、最新を使いましょうね!
INSERT...SELECT(InnoDB,MyISAM)
create tableして、INSERT...SELECTする、こちらもおなじみのやり方ですね。
create tableして、csv,tsv形式のファイルを作ってload dataコマンドやmysqlimportコマンドという方法なら並列化できていいかもしれません。
CREATE TABLE...SELECT(InnoDB,MyISAM)
CREATE TABLE...SELECTもちょっとしたコピーテーブル作るのには便利ですよね。
でも、インデックスや制約を自動で作らないので、以下のような書き方でインデックスや制約は追加してあげましょう。
mysql> CREATE TABLE t2 (PRIMARY KEY (a),INDEX (b),foreign key f1 (a) references t1(a)) SELECT * FROM t1;
AUTO_INCREMENTあたりも保持しないので気を付けましょう。
OSファイルコピー(InnoDB,MyISAM)
これは2パターンある。MySQLを停止するパターンと停止しないで行うパターンです。
停止パターン
ドキュメントに例があります。
Not停止パターン
停止パターンとほぼ同じですがshutdownをしないので、メモリやらバイナリログにデータが残ってる可能性があるのでフラッシュやsync作業が必要かも。
https://dev.mysql.com/doc/refman/8.0/en/flush.html
トランスポータブルテーブルスペース(InnoDB)
ドキュメントの手順だと別のサーバーへのコピーとなっているが、同じインスタンス内の別スキーマでもOK。
注意点としては外部キーは持てないので、必要なら後から適用する必要があるところ。
MEB + TTS(InnoDB)
MEBのオプションとして先ほど挙げたTTSがバックアップとして使えるというものです。
詳しく知りたい方はドキュメントを参照してください。
IMPORT TABLE(MyISAM)
ここまではMySQL5.6以降であれば使えました。(え?5.5?もう僕は知らない子ですねぇ…)
そして、MySQL8.0では"IMPORT TABLE"というのができました。
まあ、MySQL8.0ではMyISAM自体非推奨であり、Perconaのブログでは
”MySQL 8.0: The end of MyISAM”
なんてタイトルの話があったりして、今更じゃね?という気もしないではないんですが…。
IMPORT TABLEはドキュメントを読むと以下の通りです。
- MyISAMのテーブルをシリアル化された辞書情報(.sdi)メタデータファイルに含まれる情報に基づいてインポートします。
- .sdiを読むためのIMPORT TABLEコマンドにはFILE権限とテーブルを作成するためのCREATE権限が必要です。
- mysqldumpでエクスポートして、mysqlでインポートする方法よりも高速です。
参照:13.2.5 IMPORT TABLE Statement
作業手順としてはOSファイルコピーと似たような感じですが、んーーーー、使うメリットが今のところわかってません(苦笑)
その他
サードパーティ製のツールを使う方法です。
GUI、CUI色々ありますが、有名なのはPercona社のPercona XtraBackupですね。
xtrabackupのオプションの--tablesオプションを使えば対象テーブルのみをコピーしてくれるようです。
Percona XtraBackupの使い方については北川さんの記事がとても分かりやすいですが、他にも色々記事があるので使う場合はググって読んでみるといいです。
まとめ
MySQLでテーブルコピーに使えそうなものを雑に挙げてみました。
速度で行くとOSファイルコピーは最強でしょうね。ただ、権限的なものが強くなりますし、基本的には同一データベース名にコピーするものなので、開発者が気軽にできないところが難点でしょうか。
開発者が使うなら時間はかかりますが、mysqldumpやSQLでできるようなものがいいでしょう。
あとはTTSはそこまで有名じゃないですが、非常に便利だと思います。気になった方は試してみてはいかがでしょうか。
あ、そういえばロックの話全然書いてなかった…(;^_^A
パフォーマンススキーマの計器の最大登録数について
※追記しました。後述のドキュメントの話は修正されました!(^▽^)
計器(インストゥルメント)とは
計器(インストゥルメント)と呼ばれるコードを通じてMySQL(NDB Clusterを含む)はパフォーマンスに関するデータを取得しています。
取得されたデータはバッファ上に格納されます。
それがperformance_schemaデータベースのテーブル達です。
ユーザーはperformance_schemaデータベースのテーブルやsysビューを参照することでこれらの計器が収集した結果を取得することができます。
命名規則
計器(インストゥルメント)の名前は '/' 文字で区切られた一連のコンポーネントから構成されます。
この命名規則はどのような情報を取得しているかのおおよその目安になります。
名前空間がツリー上の構造を持っており、インストゥルメント名のコンポーネントは左から右に、より一般的からより具体的になります。
例えば、
wait/io/file
といった場合、ファイル I/O 操作の待機系情報を取得するコンポーネントが含まれています。
https://dev.mysql.com/doc/refman/8.0/en/performance-schema-instrument-naming.html
トップレベルインストゥルメントコンポーネント
計器(インストゥルメント)の一番大枠となる部分です。
以下、各トップレベルインストゥルメントコンポーネント下にあるものの数を8.0.20版でざっと書き出します。(performance_schema.setup_instrumentsより)
トップ | コンポーネント数 | 説明 |
---|---|---|
error | 1 | インストゥルメント化されたエラーイベント |
idle | 1 | インストゥルメント化されたアイドルイベント |
memory | 489 | インストゥルメント化されたメモリイベント |
stage | 121 | インストゥルメント化されたステージイベント |
statement | 212 | インストゥルメント化されたステートメントイベント |
transaction | 1 | インストルメント化されたトランザクションイベント |
wait | 381 | インストゥルメント化された待機イベント |
この中で言葉からイメージが湧きづらいのはstageだと思います。
でも、名前空間を見るとなんとなくわかります。
例)
stage/innodb/alter table (end) stage/innodb/alter table (flush) stage/innodb/alter table (insert) stage/innodb/alter table (log apply index) … stage/mysys/Waiting for table level lock stage/sql/After create stage/sql/altering table stage/sql/Applying batch of row changes (delete) stage/sql/Applying batch of row changes (update) stage/sql/Applying batch of row changes (write) stage/sql/Changing master stage/sql/Checking master version stage/sql/checking permissions stage/sql/cleaning up stage/sql/closing tables stage/sql/committing alter table to storage engine stage/sql/Compressing gtid_executed table stage/sql/Compressing transaction changes. stage/sql/Connecting to master stage/sql/converting HEAP to ondisk stage/sql/copy to tmp table stage/sql/creating table stage/sql/Creating tmp table … stage/sql/Waiting for acl cache lock stage/sql/Waiting for an event from Coordinator stage/sql/Waiting for backup lock stage/sql/Waiting for check constraint metadata lock stage/sql/Waiting for column statistics lock stage/sql/Waiting for commit lock stage/sql/Waiting for dependent transaction to commit
これを見るとドキュメントの言ってることもなんとなくわかります。(ドキュメントだけだと僕は最初なんのこっちゃでしたw)
ステージインストゥルメントは、形式 stage/code_area/stage_name の名前を持ちます。ここで code_area は sql や myisam などの名前で、stage_name は、Sorting result や Sending data などのステートメント処理のステージを示します。ステージは SHOW PROCESSLIST によって表示されるか、または INFORMATION_SCHEMA.PROCESSLIST テーブルに表示されるスレッドの状態に対応します。
Stage instruments have names of the form stage/code_area/stage_name, where code_area is a value such as sql or myisam, and stage_name indicates the stage of statement processing, such as Sorting result or Sending data. Stages correspond to the thread states displayed by SHOW PROCESSLIST or that are visible in the INFORMATION_SCHEMA.PROCESSLIST table.
https://dev.mysql.com/doc/refman/5.6/ja/performance-schema-instrument-naming.html
https://dev.mysql.com/doc/refman/8.0/en/performance-schema-instrument-naming.html
パフォーマンススキーマ関連のシステムパラメータ
mysql> SHOW VARIABLES LIKE 'perf%';
で確認できますが、各パラメータの詳細についてはドキュメントを参照してください。
https://dev.mysql.com/doc/refman/8.0/en/performance-schema-system-variables.html
ご存じだと思いますが、パフォーマンススキーマを有効にするにはperformance_schemaをONにするんですが、MySQL5.7以降はデフォルトONです。
最大登録数について
そしてここからが本題
performance_schema_max_xxxx_classesは計器の種類がxxxxのものの最大登録数を設定しています。
例えば、performance_schema_max_file_classesであれば ****/file/xxx といった計器が登録できる最大数という意味です。
パフォーマンススキーマの計器はバージョンを重ねるごとに増えており、計器の最大登録数もそれに併せて増えているようです。
また、計器自体は既存のもの以外にサードパーティプラグインを追加することもできるようになっており、そういったことから追加したい場合にその数に併せて最大登録数を調整できるようにしています。
https://dev.mysql.com/doc/refman/8.0/en/performance-schema-status-monitoring.html
実際にどう増えていってるか確認してみる
とみたさんの例のパラメータ比較するやつをお借りします。
https://mysql-params.tmtms.net/mysqld/?vers=5.6.47,5.7.24,5.7.25,8.0.11,8.0.12,8.0.13&diff=true
パラメータ名 | MySQL 5.6.47 |
MySQL 5.7.24 |
MySQL 5.7.25 |
MySQL 8.0.11 |
MySQL 8.0.12 |
MySQL 8.0.13 |
MySQL 8.0.19 |
---|---|---|---|---|---|---|---|
performance-schema-max-cond-classes | 80 | 80 | 80 | 80 | 80 | 100 | 100 |
performance-schema-max-file-classes | 50 | 80 | 80 | 80 | 80 | 80 | 80 |
performance-schema-max-memory-classes | - | 320 | 320 | 450 | 450 | 450 | 450 |
performance-schema-max-mutex-classes | 200 | 210 | 210 | 250 | 300 | 300 | 300 |
performance-schema-max-rwlock-classes | 40 | 40 | 50 | 60 | 60 | 60 | 60 |
performance-schema-max-stage-classes | 150 | 150 | 150 | 150 | 150 | 175 | 175 |
performance-schema-max-statement-classes | 168 | 193 | 193 | 212 | 212 | 212 | 218 |
performance-schema-max-thread-classes | 50 | 50 | 50 | 100 | 100 | 100 | 100 |
ところどころマイナーバージョンアップ時に上がってます。
ところが・・・
MySQL8.0のドキュメントを見ると・・・
あれ?一部のデフォルト値がおかしい。
マイナーバージョンで数値が上がっているのであれば、そのマイナーバージョンごとの記載があるべきなんですが・・・。
ソースも見てみる
#ifndef PFS_MAX_MUTEX_CLASS #define PFS_MAX_MUTEX_CLASS 300 #endif #ifndef PFS_MAX_RWLOCK_CLASS #define PFS_MAX_RWLOCK_CLASS 60 #endif #ifndef PFS_MAX_COND_CLASS #define PFS_MAX_COND_CLASS 100 #endif #ifndef PFS_MAX_THREAD_CLASS #define PFS_MAX_THREAD_CLASS 100 #endif #ifndef PFS_MAX_FILE_CLASS #define PFS_MAX_FILE_CLASS 80 #endif ・・・ #endif #ifndef PFS_MAX_STAGE_CLASS #define PFS_MAX_STAGE_CLASS 175 #endif ・・・ #ifndef PFS_MAX_MEMORY_CLASS #define PFS_MAX_MEMORY_CLASS 450 #endif
https://github.com/mysql/mysql-server/blob/8.0/storage/perfschema/pfs_server.h#L56-L85
あー、やっぱりドキュメントが未修正っぽい・・・。
いずれ修正されると思いますが、気になるパラメータについてはデフォルト値についてはソースや実際にインストールしたDBの初期値で確認するのが確実ですね。
追記
修正されました!
Default Value (≥ 8.0.13)という欄が追加されてます!やったね!
ようこそ…『男の世界』へ…(AUTO_INCREMENTが巻き戻るお話)
AUTO_INCREMENTが巻き戻る
今の会社に入るまで知らなかったんですが、結構有名なバグっぽいですね。
AUTO_INCREMENTで採番された番号が、再起動するとMySQL5.7以前は巻き戻る現象が起きる話です。
再現してみる
MySQL5.7と8.0にそれぞれ同じテーブルを作ってデータを入れ、最新の行を削除します。
共通
mysql> use test; Database changed mysql> CREATE TABLE `t1` ( -> `id` int(11) NOT NULL AUTO_INCREMENT, -> PRIMARY KEY (`id`) -> ) ENGINE=InnoDB; Query OK, 0 rows affected, 1 warning (0.04 sec) mysql> insert into t1 values(NULL); Query OK, 1 row affected (0.01 sec) mysql> insert into t1 values(NULL); Query OK, 1 row affected (0.00 sec) mysql> insert into t1 values(NULL); Query OK, 1 row affected (0.00 sec) mysql> select * from t1; +----+ | id | +----+ | 1 | | 2 | | 3 | +----+ 3 rows in set (0.00 sec) mysql> delete from t1 where id = 3; Query OK, 1 row affected (0.00 sec)
MySQL5.7.29の場合
mysql> show create table t1; +-------+------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+------------------------------------------------------------------------------------------------------------------------------------------+ | t1 | CREATE TABLE `t1` ( `id` int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1 | +-------+------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
AUTO_INCREMENTは4ですね。
MySQL8.0.19の場合
mysql> show create table t1; +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | t1 | CREATE TABLE `t1` ( `id` int NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci | +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
同じくAUTO_INCREMENTは4ですね。
MySQLを再起動すると・・・
MySQL5.7.29の場合
mysql> show create table t1; +-------+------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+------------------------------------------------------------------------------------------------------------------------------------------+ | t1 | CREATE TABLE `t1` ( `id` int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 | +-------+------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
AUTO_INCREMENT=3 になってる!(4から3に巻き戻ってる)
でも、information_schema.tablesで確認すると4のまま…。
MySQL8.0.19の場合
mysql> show create table t1; +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | t1 | CREATE TABLE `t1` ( `id` int NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci | +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
AUTO_INCREMENT=4 のままですね。
ドキュメントやソース
この変更についてはこの8.0のドキュメントに記載されています。
Google先生等を使ってそれっぽく訳したものをあげておきます。
MySQL 5.7 以前のバージョンでは、オートインクリメントカウンタはメインメモリにのみ保存され、ディスクには保存されません。 サーバの再起動後にオートインクリメントカウンタを初期化するために、InnoDB は AUTO_INCREMENT カラムを含むテーブルへの最初の挿入時に以下のステートメントと同等のものを実行します。
SELECT MAX(ai_col) FROM table_name FOR UPDATE;
MySQL 8.0では、この動作が変更されています。 現在のオートインクリメントカウンタの最大値は、変更されるたびに REDO ログに書き込まれ、チェックポイントごとにエンジン・プライベート・システム・テーブルに保存されます。 これらの変更により、現在のオートインクリメントカウンタの最大値は、サーバーの再起動時にも持続します。
通常のシャットダウン後のサーバー再起動時に、InnoDB は、データ辞書システムテーブルに保存されている現在の最大オートインクリメント値を使用して、 メモリ内オートインクリメントカウンタを初期化します。
REDOログに書き込んでいると言っているのがソース上だとこの辺
そのdict_table_autoinc_log()のドキュメントはここ
バグだったのが8で直ったという扱い
2003年に見つかって、約20年経って直ったんですねえ…。
5.7以前でも巻き戻らないようにしたいという人は
(5.6以降ならオンラインDDL扱いなので)ストアドプロシージャ作って、そこから値を取ればシーケンスのような挙動はできますね。
- 作るときはinformation_schema.tablesのauto_incrementカラムの値を取得して、
- alter table 文をでauto_incrementの値を変更
5.5以前だとauto increment使わずにプロシージャと専用テーブルかな?(さすがにやりすぎか)
ところでタイトルの意味は?
ようこそ…『男の世界』へ…
ジョジョの奇妙な冒険 Part7 スティール・ボール・ランのキャラクターの一人リンゴォ・ロードアゲインのセリフから。
このキャラがきっかり時を6秒『巻き戻す』能力を持っていることから引用。
ただそれだけw
ぶっちゃけ
ソースの場所とかyoku0825さんに教えてもらったんですよね。
本当にいつもありがとうございます。
参考
MySQL8でCHAR関数がドキュメントどおりになってない
MySQL8でCHAR関数がドキュメントどおりになってない
https://dev.mysql.com/doc/refman/5.6/ja/string-functions.html#function_char
を読むと、こう書いてある。
- CHAR(N,... [USING charset_name])
CHAR() は各 N 引数を整数として解釈し、それらの整数のコード値で指定された文字を構成している文字列を返します。NULL 値はスキップされます。
mysql> SELECT CHAR(77,121,83,81,'76'); -> 'MySQL' mysql> SELECT CHAR(77,77.3,'77.3'); -> 'MMM'
MySQL5.7.29の場合
mysql [localhost:5729] {msandbox} ((none)) > SELECT CHAR(77,121,83,81,'76'); +-------------------------+ | CHAR(77,121,83,81,'76') | +-------------------------+ | MySQL | +-------------------------+ 1 row in set (0.00 sec)
ドキュメント通りですね。
MySQL8.0.19の場合
mysql [localhost:8019] {msandbox} ((none)) > SELECT CHAR(77,121,83,81,'76'); +--------------------------------------------------+ | CHAR(77,121,83,81,'76') | +--------------------------------------------------+ | 0x4D7953514C | +--------------------------------------------------+ 1 row in set (0.01 sec)
あるぇ?おかしい。。。
これ、バグっぽいな
バグレポを見る。
これはわりと関係しそうだけど直接的ではないんだよなー。
ただ、ドキュメントと違うというだけで、雰囲気的には8のそれも最初からそれならアリな感じはするんだよな。
そして
atsuizoさんからコメントを頂く。
なるほど、8.0.14で検証したら大丈夫。 ということはどこかのバージョンか、環境のせいでおかしいのかも?
MySQL8.0.19の場合 by docker
mysql> SELECT CHAR(77,121,83,81,'76'); +--------------------------------------------------+ | CHAR(77,121,83,81,'76') | +--------------------------------------------------+ | 0x4D7953514C | +--------------------------------------------------+ 1 row in set (0.00 sec)
同じだ。
MySQL8.0.18の場合 by docker
mysql> SELECT CHAR(77,121,83,81,'76'); +-------------------------+ | CHAR(77,121,83,81,'76') | +-------------------------+ | MySQL | +-------------------------+ 1 row in set (0.00 sec)
あ!今までの見え方になった・・・。
おそらくバージョンのせいっぽいぞ。
バグレポ
既に木村さんの方で出してもらってたようです。ありがたや。
解決方法
やはり木村さんに教えてもらいました。
MySQL :: MySQL 8.0 Reference Manual :: 4.5.1.1 mysql Client Options
試してみます
# mysql -uroot -p --skip-binary-as-hex Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 10 Server version: 8.0.19 MySQL Community Server - GPL Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> SELECT CHAR(77,121,83,81,'76'); +-------------------------+ | CHAR(77,121,83,81,'76') | +-------------------------+ | MySQL | +-------------------------+ 1 row in set (0.00 sec)
ほんとだ、元の見え方になりました!
リリースノートを見る
8.0.19のリリースノートはこちら
When the mysql client operates in interactive mode, the --binary-as-hex option now is enabled by default.
mysqlクライアントが対話モードで動作する とき、--binary-as-hexオプションはデフォルトで有効になりました。
To disable hexadecimal notation, use --skip-binary-as-hex (Bug #24432545)
16進表記を無効にするには、--skip-binary-as-hex (バグ#24432545)を使用します
ここのことか!(分かりづらい…)
バグ対応で正しくしたのならドキュメントの方直すべきだべ…。
感想
今回は木村さんに全てやってもらって、僕は記事を挙げただけですw(木村さんありがとうございます)
そして、atsuizoさんに指摘してもらって8.0.19からの変更だと気づけましたし、そのあとのコメントの通り、これ誰が嬉しいのかよくわからない変更ですよね(苦笑)
マイナーながらもなかなかナニコレ的なものなので、こいつがいつか誰かの役に立てれば幸いです。