【MySQL】テーブルの断片化を確認しよう
はじめに
自分用にテーブルが断片化してるか否かを確認する方法を書いてみました。
もし他にこんなのあるよー、って話あれば教えてください。
SHOW TABLE STATUS
- Data_freeを見ると良い
- innodb_file_per_tableオプションが有効であるべき
mysql> SHOW TABLE STATUS FROM test\G *************************** 1. row *************************** Name: t1 Engine: InnoDB Version: 10 Row_format: Dynamic Rows: 0 Avg_row_length: 0 Data_length: 98304 Max_data_length: 0 Index_length: 16384 Data_free: 0 Auto_increment: 1015 Create_time: 2020-11-19 12:38:12 Update_time: 2020-11-19 12:47:15 Check_time: NULL Collation: utf8mb4_0900_ai_ci Checksum: NULL Create_options: Comment: 1 row in set (0.01 sec)
SHOW TABLE STATUS 構文については以下のドキュメントに詳細がありますので、興味のある方は読んでみてください。
MySQL :: MySQL 5.6 Reference Manual :: 13.7.5.37 SHOW TABLE STATUS Statement
information_schema.tablesで確認
とりあえずの概算値ではあるが、100MB以上のdata_freeがあるようなテーブルの一覧を出してみる方法。
SELECT table_schema, table_name, data_free/1024/1024 AS data_free_MB FROM INFORMATION_SCHEMA.TABLES WHERE engine LIKE 'InnoDB' AND data_free > 100*1024*1024;
INFORMATION_SCHEMA TABLES テーブルについては以下のドキュメントに詳細がありますので、興味のある方は読んでみてください。
MySQL :: MySQL 5.6 リファレンスマニュアル :: 21.22 INFORMATION_SCHEMA TABLES テーブル
innodb_ruby
Ruby製のMySQL InnoDBテーブルの断片化を見ることができるツール。
インストールについてはここ参照
使い方のイメージ
$ innodb_space -f /home/takiida/git/mysql/bld/data/test/t1.ibd space-extents-illustrate
【番外編】バッファプール上のデータのフラグメンテーションの見積もり
こちらはファイルではなく、バッファプール上のデータのフラグメンテーションを見るものです。
SELECT table_name, index_name, AVG(data_size), COUNT(*) FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE WHERE table_name IS NOT NULL GROUP BY table_name , index_name HAVING COUNT(*) > 100 ORDER BY AVG(data_size) ASC LIMIT 10;
INFORMATION_SCHEMA INNODB_BUFFER_PAGE テーブルについては以下のドキュメントに詳細がありますので、興味のある方は読んでみてください。
MySQL :: MySQL 5.6 リファレンスマニュアル :: 21.29.16 INFORMATION_SCHEMA INNODB_BUFFER_PAGE テーブル
最後に
とりあえず5.6のでも問題ないから読みやすい日本語ドキュメントを選んでます。
MySQL8.0の日本語ドキュメント公開されたらリンクは差し替えようかなと思っとります。
SELECT ... FOR UPDATEとUPDATEでデッドロックが出る人へ
はじめに
最近は主に花粉症に悩まされており、目が痒くてたまりません。
また、娘の生活がガラッと変わったせいで、毎日貧乏ヒマ無しです。
そんな中、たまたま早起きできたので奮起して久々に書いてみました。
問題が起きる環境
- MySQL8.0.17以前
- transaction_isolationがREAD-COMMITTED
- WHERE句の条件が一意ではない。（フルテーブルスキャンだと発生しやすくなる）
- キーの値がたすきがけになってる
- トランザクション開始＋SELECT ...FOR UPDATE→UPDATEのようにロックを取っている
- 先に実行されたトランザクションが、たすきがけになっているキー値の若い（っていうのかな？）方のロックを取る
何が起きるかと言うと、SELECT ...FOR UPDATEのWHERE句で抽出した行に対してロックを取ってるのに、 後から別セッションで実行されたSELECT ...FOR UPDATEのせいで、先取りしたほうがデッドロックして負けるというお話。
これ、世の中のシステムで意外と起きてそうだなーと思ったが、文章にするとわかりづらいので、具体例を記載しながら説明する。
詳細
準備
１．まず、こんなテストテーブルを構築。
mysql> use test; mysql> show create table tu\G *************************** 1. row *************************** Table: tu Create Table: CREATE TABLE `tu` ( `id` int(11) NOT NULL, `a` int(11) DEFAULT NULL, `b` varchar(10) DEFAULT NULL, `c` varchar(10) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `u` (`a`,`b`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.00 sec)
２．データはこちら
mysql> INSERT INTO tu VALUSE(1,1,'a','a'),(2,2,'c','2'),(3,3,'b','3'),(4,4,'d','4'); Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM tu; +----+------+------+------+ | id | a | b | c | +----+------+------+------+ | 1 | 1 | a | a | | 2 | 2 | c | 2 | | 3 | 3 | b | 3 | | 4 | 4 | d | 4 | +----+------+------+------+ 4 rows in set (0.00 sec)
キーはPKがid、UKがa,bで作られ、データはbカラムのところでid=2と3で入れ子にしている。
（PKはaカラムとbカラムの組み合わせでもOK）
検証
１． １つ目のトランザクションでSELECT...FOR UPDATEを行う。
３行目のデータを更新対象に見据えて、SELECT...FOR UPDATE文を実行する。
mysql> BEGIN; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM tu WHERE c = '3' FOR UPDATE; +----+------+------+------+ | id | a | b | c | +----+------+------+------+ | 3 | 3 | b | 3 | +----+------+------+------+ 1 row in set (0.00 sec) mysql> EXPLAIN SELECT * FROM tu WHERE c = '3' FOR UPDATE; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | tu | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 25.00 | Using WHERE | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
ここではWHERE句の条件をインデックスを使わず、フルテーブルスキャンになるようにしている。
この時、どのようなロックが取得されているかと言うと、以下の通り。
> SELECT * FROM performance_schema.data_locks\G *************************** 1. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140229171137104:1061:140229071664344 ENGINE_TRANSACTION_ID: 3593 THREAD_ID: 47 EVENT_ID: 13 OBJECT_SCHEMA: test OBJECT_NAME: tu PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: NULL OBJECT_INSTANCE_BEGIN: 140229071664344 LOCK_TYPE: TABLE LOCK_MODE: IX LOCK_STATUS: GRANTED LOCK_DATA: NULL *************************** 2. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140229171137104:4:4:7:140229071661352 ENGINE_TRANSACTION_ID: 3593 THREAD_ID: 47 EVENT_ID: 13 OBJECT_SCHEMA: test OBJECT_NAME: tu PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: PRIMARY OBJECT_INSTANCE_BEGIN: 140229071661352 LOCK_TYPE: RECORD LOCK_MODE: X,REC_NOT_GAP LOCK_STATUS: GRANTED LOCK_DATA: 3 2 rows in set (0.00 sec)
テーブルに対し、インサートインテンションロック、対象行に対し、排他ロックがかかる。
（ちなみに、MySQL5.7以前だとperformance_schema.data_locksは無い）
２． ２つ目のトランザクションでもSELECT...FOR UPDATEを行う。
mysql> BEGIN; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM tu WHERE c = '2' FOR UPDATE;
待たされる。
この時、どのようなロックが取得されているかと言うと、以下の通り。
mysql> SELECT * FROM performance_schema.data_locks\G *************************** 1. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140229171137968:1061:140229071670264 ENGINE_TRANSACTION_ID: 3595 THREAD_ID: 48 EVENT_ID: 16 OBJECT_SCHEMA: test OBJECT_NAME: tu PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: NULL OBJECT_INSTANCE_BEGIN: 140229071670264 LOCK_TYPE: TABLE LOCK_MODE: IX LOCK_STATUS: GRANTED LOCK_DATA: NULL *************************** 2. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140229171137968:4:4:3:140229071667384 ENGINE_TRANSACTION_ID: 3595 THREAD_ID: 48 EVENT_ID: 16 OBJECT_SCHEMA: test OBJECT_NAME: tu PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: PRIMARY OBJECT_INSTANCE_BEGIN: 140229071667384 LOCK_TYPE: RECORD LOCK_MODE: X,REC_NOT_GAP LOCK_STATUS: GRANTED LOCK_DATA: 2 *************************** 3. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140229171137968:4:4:7:140229071667728 ENGINE_TRANSACTION_ID: 3595 THREAD_ID: 48 EVENT_ID: 16 OBJECT_SCHEMA: test OBJECT_NAME: tu PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: PRIMARY OBJECT_INSTANCE_BEGIN: 140229071667728 LOCK_TYPE: RECORD LOCK_MODE: X,REC_NOT_GAP LOCK_STATUS: WAITING LOCK_DATA: 3 *************************** 4. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140229171137104:1061:140229071664344 ENGINE_TRANSACTION_ID: 3593 THREAD_ID: 47 EVENT_ID: 13 OBJECT_SCHEMA: test OBJECT_NAME: tu PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: NULL OBJECT_INSTANCE_BEGIN: 140229071664344 LOCK_TYPE: TABLE LOCK_MODE: IX LOCK_STATUS: GRANTED LOCK_DATA: NULL *************************** 5. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140229171137104:4:4:7:140229071661352 ENGINE_TRANSACTION_ID: 3593 THREAD_ID: 47 EVENT_ID: 13 OBJECT_SCHEMA: test OBJECT_NAME: tu PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: PRIMARY OBJECT_INSTANCE_BEGIN: 140229071661352 LOCK_TYPE: RECORD LOCK_MODE: X,REC_NOT_GAP LOCK_STATUS: GRANTED LOCK_DATA: 3 5 rows in set (0.00 sec)
上の３つが２つ目のトランザクションによるロック。
"3. row" のロックが LOCK_STATUS: WAITING となっているのでこれにより待たされていることがわかる。
これだけを見ると、１つ目のトランザクションでは正常に後続のUPDATE文が通りそうなもんであるが、しかし・・・
３． １つ目のトランザクションでUPDATEが失敗する
mysql> UPDATE tu SET c='c2' WHERE c = '3'; ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
ちなみに上記のデッドロック、もし２つ目のトランザクションの条件（where c = '2'）を先に実行した場合はデッドロックが発生しない。
原因
リリースノートにあるコレ
InnoDB: A deadlock was possible when a transaction tries to upgrade a record lock to a next key lock. (Bug #23755664, Bug #82127)
解決方法
3つあって
- MySQL8.0.18以降にする
- transaction_isolationをREPEATABLE-READにする
- 更新対象を一意にする
根治するならバージョンアップ一択なんですが、バージョンいきなりやれって言われてもツライと思うし、
transaction_isolation変えるなんて、それもまたシステムへの影響多いだろうから、
まずは更新対象をできるだけ絞る、可能なら一意にするってところから始めてくれればかと。
もし、EXPLAIN取ってtypeがALLなんて出てたら、それを潰すところからですね。
インデックス張りましょう。抽出対象を絞れるようにしましょう。
最後に
そういえば、２月１５日で勤続１年になりました。
チームのメンバー、イルカさん関係者の皆さん、外部のイルカ大好きな皆様、そしてお客様に恵まれ、非常にやりがいもあり成長させていただき、充実した一年でした。
まだまだ力不足でご迷惑をおかけすることもあるかもしれませんが、今後ともよろしくお願いします。
参照情報
適応ハッシュインデックスについて
はじめに
こないだのAdvent Calendar用の記事で適応ハッシュインデックス（AHI）について少し書いたんだけど、今日はそもそもそれってどうやって使ったり見たりするの？って話です。
適応ハッシュインデックスとは？（おさらい）
- InnoDBのインデックスはクラスタ化されていて、主キーのリーフノードにデータが一緒に格納されている。
- そのため、主キーによる検索は高速だが、セカンダリインデックスを用いた検索では、セカンダリインデックスから条件に合致するインデックスエントリの主キーの値を取得し、改めて主キーを検索するという2段階の検索が生じるため、やや遅い。
- それに対し、バッファプール上にハッシュテーブルを動的に作成し、主キーの検索を効率化させるようにしたのが適応ハッシュインデックス。
詳しい説明については、下記のマニュアル参照
MySQL :: MySQL 5.6 リファレンスマニュアル :: 14.2.13.6 適応型ハッシュインデックス
有効化するには？
以下のシステム変数により、オンラインでON/OFFの切り替えが可能。
MySQL :: MySQL 5.6 リファレンスマニュアル :: 14.12 InnoDB の起動オプションおよびシステム変数
デフォルトは?
ONです。
SET_VARヒント使える？（8.0の話）
SET_VARヒント→ MySQL :: MySQL 8.0 Reference Manual :: 8.9.3 Optimizer Hints 参照
モニタリングを有効化するには
innodb_monitor_enableにmodule_adaptive_hashを設定する。
mysql> SET GLOBAL innodb_monitor_enable='module_adaptive_hash';
MySQL :: MySQL 5.6 リファレンスマニュアル :: 14.14.6 InnoDB INFORMATION_SCHEMA メトリックテーブル
AHIをクエリで確認する
AHIを使っているかどうか
以下のようなクエリで確認することが可能
mysql> SELECT name, count FROM information_schema.INNODB_METRICS WHERE name LIKE '%adaptive%';
どの程度の適応ハッシュインデックスがバッファプールに存在するか
以下のようなクエリで確認することが可能
mysql> SELECT SUM(IS_HASHED = 'YES') HASHED_PAGES, COUNT(1) TOTAL_PAGES FROM information_schema.INNODB_BUFFER_PAGE;
但し、上記クエリは実行に負荷が高いので、本番環境で実行するのはおススメできない。（検証環境でやろう） また、出力された結果は、ハッシュそのもののサイズではなく、これはハッシュが作成されたページ数であるためあくまで参考情報。
INNODB_BUFFER_PAGE テーブルについては以下のドキュメント参照。 MySQL :: MySQL 5.6 リファレンスマニュアル :: 21.29.16 INFORMATION_SCHEMA INNODB_BUFFER_PAGE テーブル
ヒット率
以下のようなクエリで確認することが可能
mysql> SELECT ahi_hits, ahi_miss, 100 * ahi_hits / ahi_miss AS ahi_hit_pct > FROM (SELECT > (SELECT count FROM information_schema.INNODB_METRICS WHERE NAME = 'adaptive_hash_searches') AS ahi_hits, > (SELECT count FROM information_schema.INNODB_METRICS WHERE NAME = 'adaptive_hash_searches_btree') AS ahi_miss) t;
とはいえ、有効にしたとしてもワークロード上、必ず早くなるというわけではないため、切り替えを行う場合は確認が必要。
INNODB_METRICS テーブルについては以下のドキュメント参照。 MySQL :: MySQL 5.6 リファレンスマニュアル :: 21.29.19 INFORMATION_SCHEMA INNODB_METRICS テーブル
その他
B ツリーインデックスとハッシュインデックスの比較
MySQL :: MySQL 5.6 リファレンスマニュアル :: 8.3.8 B ツリーインデックスとハッシュインデックスの比較
を読もう。
おわりに
これも自分用のメモみたいなもんなんで殴り書きレベルです。
なんか新しく気づいたことあれば追記します。
読みにくかったらごめんなさい。
MyISAMってなんだろう？
はじめに
最近、MyISAMのこと調べる機会が多かったので、今までの人生でMyISAMテーブルを触ることなんてほぼ0だったし、MySQL的には今更な感じもするけどとりあえず書き出してみる。
(殴り書きに近い。あとで調べるときにとっかかりになればと思ってる)
なので、後日付け足していける感じの自分用のメモ扱い。
基本的にドキュメントは5.6以降大きな変更無いので、5.6ベースのもののリンクを貼っとく。
MyISAMとは
MySQL :: MySQL 5.6 リファレンスマニュアル :: 15.2 MyISAM ストレージエンジン
- MySQL5.5以降はInnoDBがデフォルトのストレージエンジン
MyISAM 起動オプション
MySQL :: MySQL 5.6 リファレンスマニュアル :: 15.2.1 MyISAM 起動オプション
キーに必要な容量
インデックスファイルのサイズは、キーを (key_length+4)/0.67 と計算し、すべてのキーに対してその値を合計して概算できます。 これは、すべてのキーがソート順に挿入され、テーブル内のキーが圧縮されていなときの最悪なケースです。
MyISAM テーブルでは、テーブルの作成時に PACK_KEYS=1 テーブルオプションを指定することで、数値のプリフィクスを圧縮することもできます。
MySQL :: MySQL 5.6 リファレンスマニュアル :: 15.2.2 キーに必要な容量
MyISAM テーブルのストレージフォーマット
以下の３つがある
- 静的 (固定長) テーブル
- 動的テーブル
- 圧縮テーブル
MySQL :: MySQL 5.6 リファレンスマニュアル :: 15.2.3 MyISAM テーブルのストレージフォーマット
OPTIMIZE TABLEで再編成
MyISAM テーブルの場合、OPTIMIZE TABLE は次のように機能します。 * テーブルが行を削除または分割した場合は、そのテーブルを修復します。 * インデックスページがソートされていない場合は、それをソートします。 * テーブルの統計が最新でない (そのため、インデックスのソートによって修復を実行できない) 場合は、それを更新します。
MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.7.2.4 OPTIMIZE TABLE 構文
myisamchk -r でも可。
MyISAM テーブルの破損が起きるとき
MySQL :: MySQL 5.6 リファレンスマニュアル :: 15.2.4.1 MyISAM テーブルの破損
適切に閉じられなかったテーブルの問題
MySQL :: MySQL 5.6 リファレンスマニュアル :: 15.2.4.2 適切に閉じられなかったテーブルの問題
MyISAM テーブルの保守とクラッシュリカバリ
MySQL :: MySQL 5.6 リファレンスマニュアル :: 7.6 MyISAM テーブルの保守とクラッシュリカバリ
MyISAM テーブルの保守は、myisamchk が実行するものと似た操作を実行する SQL ステートメントを使用しても実行できます。 * MyISAM テーブルをチェックするには、CHECK TABLE を使用します。 * MyISAM テーブルを修復するには、REPAIR TABLE を使用します。 * MyISAM テーブルを最適化するには、OPTIMIZE TABLE を使用します。 * MyISAM テーブルを分析するには、ANALYZE TABLE を使用します。
MyISAMテーブルの修復
REPAIR TABLE を実行する。
myisamchk --recover tbl_nameも可
MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.7.2.5 REPAIR TABLE 構文
myisamchk — MyISAM テーブルメンテナンスユーティリティー
その他MyISAM関連のユーティリティプログラム
myisamlog — MyISAM ログファイルの内容の表示
myisampack — 圧縮された読み取り専用の MyISAM テーブルの生成
mysqlhotcopy — データベースバックアッププログラム
バックアップ(というかテーブルコピーメイン)
自分の過去の記事↓
MySQLでのテーブルコピー（InnoDBとMyISAM） - 41から始めました
mysqldumpでのバックアップの場合
--lock-tablesまたは -lを使ってダンプするすべてのテーブルをダンプ前にロック（READ LOCK）する。
MySQL :: MySQL 5.6 リファレンスマニュアル :: 4.5.4 mysqldump — データベースバックアッププログラム
DELETE構文
MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.2.2 DELETE 構文
MyISAMテーブルの項目を参照
DELETE ステートメントの速度
MySQL :: MySQL 5.6 リファレンスマニュアル :: 8.2.2.3 DELETE ステートメントの速度
MyISAM テーブル内の個々の行を削除するために必要な時間は、インデックスの数に正確に比例します。 行をもっと速く削除するには、key_buffer_size システム変数を増やして、キーキャッシュのサイズを大きくできます。
MyISAM テーブルからすべての行を削除するには、TRUNCATE TABLE tbl_name の方が DELETE FROM tbl_name より速くなります。 切り捨て操作はトランザクションセーフではありません。 アクティブなトランザクションやアクティブなテーブルロックの途中で試みるとエラーが発生します。
MyISAMからInnoDBへ
MySQL :: MySQL 5.6 リファレンスマニュアル :: 14.6.4 MyISAM から InnoDB へのテーブルの変換
漢(オトコ)のコンピュータ道: MyISAMからInnoDBへ切り替えるときの注意点
大きな違いはロッキングとデータの堅牢性。
MyISAMはテーブルロック、原子性の非担保による壊れやすさ。
MyISAMの最適化
MySQL :: MySQL 5.6 リファレンスマニュアル :: 8.6 MyISAM テーブルの最適化
パーティション関連の話
MySQL :: MySQL 5.6 リファレンスマニュアル :: 19.6 パーティショニングの制約と制限
パラメータ一覧
MySQL :: MySQL 5.6 リファレンスマニュアル :: 5.1.4 サーバーシステム変数
key_buffer_size
MySQL :: MySQL 5.6 リファレンスマニュアル :: 5.1.4 サーバーシステム変数
値を増やすことで、すべての読み取りおよび複数の書き込みのインデックス処理を改善できます。 システムの主な機能が MyISAM ストレージエンジンを使用して MySQL を実行する場合、マシンの合計メモリーの 25% がこの変数の許容可能な値です。 ただし、値を大きくし過ぎると (マシンの合計メモリーの 50% 超)、システムのページングが始まってきわめて低速になることがあります。 これは MySQL がデータ読み取りのためのファイルシステムキャッシュの実行をオペレーティングシステムに依存しているためで、ファイルシステムキャッシュのためにいくらかの空きを残しておく必要があります。 また、MyISAM に追加して使用するほかのストレージエンジンのメモリー要件も考慮します。
MyISAMテーブルの最大サイズ
MySQL :: MySQL 5.6 リファレンスマニュアル :: D.10.3 テーブルサイズの制限
これ調べてみたら、可変長カラムを含む場合のみっぽい。
カラムが固定長オンリーの場合はこの限界サイズを超えて、可変長カラムの場合の約カラム長倍になるという。
詳しく知りたい人はmi_create.ccあたりを読んでみるといいです。
GTIDとMyISAM
混ぜるな危険。
クラッシュ時はソースとレプリカの整合性が崩れてレプリケーションが停止する。
MySQL :: MySQL 5.6 リファレンスマニュアル :: 17.1.3.4 GTID ベースレプリケーションの制約
GTID を使用する場合、MyISAM などの非トランザクションストレージエンジンを使用するテーブルへの更新は、InnoDB などのトランザクションストレージエンジンを使用するテーブルへの更新と同じステートメントまたはトランザクションで実行できません。 この制約は、非トランザクションストレージエンジンを使用するテーブルへの更新とトランザクションストレージエンジンを使用するテーブルへの更新が、同じトランザクション内に混在していると、複数の GTID が同じトランザクションに割り当てられる可能性があるためです。
統計情報について
MyISAMはインデックスのファイルサイズと格納されているレコード数を管理しているだけ→実質的には統計情報と呼べる情報は管理してない。
なぜならMyISAMには、対象のインデックスエントリがそのインデックス内で何番目のレコードかということを即座に返す機能があるため、インデックスの統計情報を維持する必要がないから。
内部一時テーブルストレージエンジン
MySQL5.6だと以下の通り
MySQL :: MySQL 5.6 リファレンスマニュアル :: 8.4.4 MySQL が内部一時テーブルを使用する仕組み
場合により、サーバーはクエリーの処理中に内部一時テーブルを作成します。 それらのテーブルは、メモリー内に保持して、MEMORY ストレージエンジンによって処理したり、ディスク上に格納して、MyISAM ストレージエンジンによって処理したりできます。 サーバーは最初にインメモリーテーブルとして内部で一時テーブルを作成し、それが大きくなりすぎた場合に、それをディスク上テーブルに変換することがあります。
MySQL5.7ではサーバーがディスク上の内部一時テーブルを管理するために使用するストレージエンジンとしてMyISAMがパラメータ（internal_tmp_disk_storage_engine）で指定可能。
しかし、このパラメータ、8.0.16で削除される。理由はInnoDBに固定となったため。
一方で、internal_tmp_mem_storage_engineというパラメータもできる。こちらは名前がややこしいが別物かつエンジンはTempTable or MEMORY)
MySQL :: MySQL 8.0 Reference Manual :: 5.1.8 Server System Variables
おわりに
他にもMyISAMってこんな特徴、問題あるよ！って話あれば教えてください。
TRUNCATE TABLEがバッファプールを読んでいる
本エントリはMySQL Advent Calendar 2020 の13日目です。
今日はちょっとだけMySQL8.0にアップグレードしとくと良いよ、というネタです。
TRUNCATEよりもDROPとCREATEのほうが性能が良いか？
はい、MySQL5.7以前まではDROPと&CREATE のほうがTRUNCATE TABLEよりも高速なんです。
なぜならこんなバグが5.7以前にはあるからです。
要約すると、MySQL 5.7以前のInnoDBでは、TRUNCATE TABLEを実行した際、ファイルを再作成する以外にも、バッファプールのスキャンが発生するというものです。
ただし、適応型ハッシュインデックスがOFFの場合には、スキャンは生じません。
このバグ、MySQL8.0で改善されました。
どういうときに困るのか？
大きなバッファプールを持っていて、TRUNCATE対象のテーブル情報がそこに乗っているとき。
テーブルの再作成＋バッファープールのスキャンが走るので、テーブルがでかければでかいほど遅くなるってわけです。
この令和の時代、MySQL Serverでさすがにinnodb_buffer_pool_size=128MBのデフォルトサイズでいつまでも運用してる会社さんはそうそういないでしょう。
皆さん最低100GBのメモリくらいは積んでますよね？
ですから、皆さん全員困ってると思ってですね・・・おや、誰か来たようだ。
対応方法
- TRUNCATE TABLEをやめて、DROP TABLE＋CREATE TABLEにする。
- innodb_adaptive_hash_indexをOFFにして、適応型ハッシュインデックスを無効にする。
- MySQL8.0にアップグレードする。
バッファプールを小さくすることは他の処理への影響もあるのでやらないこと。（もちろん、OMMKillerが発生するような設定の場合は別）
innodb_adaptive_hash_indexをOFFにするのは動的に行えます。
なんでMySQL8.0では直ったの？
MySQL 8.0では、内部的にTRUNCATEテーブルがDROPとCREATEを連続して行うのと等価の操作に置き換えられ、バッファプールを読まなくなったからです。
https://dev.mysql.com/doc/refman/8.0/en/truncate-table.html
In MySQL 5.7 and earlier, on a system with a large buffer pool and innodb_adaptive_hash_index enabled, a TRUNCATE TABLE operation could cause a temporary drop in system performance due to an LRU scan that occurred when removing the table's adaptive hash index entries (Bug #68184). The remapping of TRUNCATE TABLE to DROP TABLE and CREATE TABLE in MySQL 8.0 avoids the problematic LRU scan.
ところで適応型ハッシュインデックス（AHI）って何？
一部のインデックス値のアクセス頻度が高い場合、バッファプール上にハッシュテーブルを動的に作成し、主キーの検索を効率化させるInnoDBエンジンの機能です。
更には、セカンダリインデックスによる検索はその過程で主キーによる等価比較が行われますが、AHIは主キーの検索を効率化させているので、セカンダリインデックスによる検索が多いワークロードだと検索効率が上がる可能性も大いにあります。
一方で、これが複数の並列結合などの負荷が高いワークロードではロックが競合の原因となったり、LIKE 演算子と % ワイルドカードを使用したクエリは恩恵が得られないといったことがあり、5.6以降のアーキテクチャ改善によりこれがONになっているメリットは過去のバージョンと比べると徐々に減っています。
とはいえ、デフォルトはONであり、ONとなっていることで高速化している可能性もあるので、知らずに使っていた人もOFFにする場合は事前に検証が必要な機能です。
（これ、地味にパフォーマンスに影響する意外と重要な機能ですよね）
詳しくはドキュメントをご確認下さい。
AHIでおまけ
Perconaのピーター・ツァイツェフさんがAHI関連で面白い記事を2本載せているので参考に載せておきます。
一つは古いですが、MySQL5.7.11の段階で色々なデータで検証したものです。 www.percona.com データが非常に多くなった場合、sysbenchで検証した結果としては効率が落ちてはいるものの、MySQLのドキュメントにある通り、ワークロードに応じて使える場合もあるのでちゃんと確認してON/OFFしようねという内容になっています。
もう一つはAuroraでReaderノードでは無効なAHIをWriterノードで有効にしたら同じクエリがWriterでは高速だったという話です。 www.percona.com
今現在、Auroraでどのような設定になっているのか知らないのですが、innodb_adaptive_hash_indexがデフォルトでは無効だったらしく、かつReaderノードではAHIを有効化できなかったが、Writerノードでは変更できたので変えてみたら高速になったので注意してねとのこと。
(そういえば、AuroraがMySQL8.0をComing soon的な話を聞きましたが、クエリキャッシュはどうするんだろう？)
明日は？
明日はupdraftさんの
今日は、DBで日付にはDATE型？VARCHAR型どっちの日。 - 今日はなにの日。
です。
お楽しみに！
MySQL ShellのダンプとMDSへのインポート
はじめに
今回はずっと触りたかったMySQL Shellのダンプをやりつつ、 最後は前回作ったMDS相手にインポートしてみようかと思います。
MDSにインポートするためにはociクライアントの設定や、オブジェクトストレージが必要なので、それらを途中で設定していきます。
準備
1. 手元のdbdeployerで作成したMySQL8.0.21の環境に、公式が出しているテスト用データベースたちを適当に入れていきます。
自分はgit cloneで取得しましたが、zip形式でもダウンロードは可能です。
$ git clone https://github.com/datacharmer/test_db.git
2. MySQL Shellももちろんインストールしておいてください
自分のところの環境はUbuntuとOCI側はCentOSですので、以下のとおりです。
Ubuntu
$ sudo dpkg -i /PATH/version-specific-package-name.deb -- 今時点だとmysql-apt-config_0.8.15-1_all.debが最新の様子 $ sudo apt-get update $ sudo apt-get install mysql-shell
CentOS(前回やりましたが、一応転記)
$ sudo yum install https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm $ sudo yum install mysql-shell
3. 1で取得したスキーマ達をローカルの8.0.21にインポート
$ cd test_db/ $ mysql -uroot -p -h127.0.0.1 -P8021 < employees.sql $ mysql -uroot -p -h127.0.0.1 -P8021 < test_employees_md5.sql $ mysql -uroot -p -h127.0.0.1 -P8021 < test_employees_sha.sql $ mysql -uroot -p -h127.0.0.1 -P8021 < sakila/sakila-mv-schema.sql $ mysql -uroot -p -h127.0.0.1 -P8021 < sakila/sakila-mv-data.sql $ $ # 作成したスキーマを確認(testはdbdeployerで自動で作っている) $ mysql -uroot -p -h127.0.0.1 -P8021 (root@127.0.0.1) [(none)]> show databases; +--------------------+ | Database | +--------------------+ | employees | | information_schema | | mysql | | performance_schema | | sakila | | sys | | test | +--------------------+ 7 rows in set (0.00 sec)
MySQL ShellのdumpInstanceを使っていく
ダンプ取得で使えるものとしてdumpInstanceとdumpSchemasがあるけど、その差はスキーマ（データベース）単位でやるか否かだけの話。
dumpInstanceのほうが自分はよく使いそうだし、2つの差も引数としてスキーマ指定が絡むだけっぽいのでdumpInstanceを触っていく
dumpInstanceを試す
ダンプユーティリティのオプションについては公式ドキュメントにあるのでそれを参照するのだが、事例が少ない。
書き方で戸惑うかもしれないので試してみて実行できた書き方を以下並べてみる。
第１引数が出力先のディレクトリ名になります。（/tmp/dumpdirとか）
上書きしようとするとエラーになるので、
excludeSchemas
指定したスキーマをダンプから除外します。
例）testスキーマを除外
MySQL 127.0.0.1:8021 ssl JS > util.dumpInstance("/tmp/dumpdir",{excludeSchemas:["test"]})
Acquiring global read lock
All transactions have been started
Locking instance for backup
Global read lock has been released
Writing global DDL files
Writing users DDL
Preparing data dump for table `sakila`.`actor`
Writing DDL for schema `sakila`
Writing DDL for view `sakila`.`customer_list`
Writing DDL for view `sakila`.`actor_info`
Writing DDL for view `sakila`.`film_list`
Data dump for table `sakila`.`actor` will be chunked using column `actor_id`
Preparing data dump for table `sakila`.`address`
Data dump for table `sakila`.`address` will be chunked using column `address_id`
Preparing data dump for table `sakila`.`category`
Data dump for table `sakila`.`category` will be chunked using column `category_id`
Preparing data dump for table `sakila`.`city`
Data dump for table `sakila`.`city` will be chunked using column `city_id`
Preparing data dump for table `sakila`.`country`
Data dump for table `sakila`.`country` will be chunked using column `country_id`
Preparing data dump for table `sakila`.`customer`
Data dump for table `sakila`.`customer` will be chunked using column `customer_id`
Preparing data dump for table `sakila`.`film`
Data dump for table `sakila`.`film` will be chunked using column `film_id`
Preparing data dump for table `sakila`.`film_actor`
Data dump for table `sakila`.`film_actor` will be chunked using column `actor_id`
Preparing data dump for table `sakila`.`film_category`
Data dump for table `sakila`.`film_category` will be chunked using column `film_id`
Preparing data dump for table `sakila`.`film_text`
Writing DDL for view `sakila`.`nicer_but_slower_film_list`
Data dump for table `sakila`.`film_text` will be chunked using column `film_id`
Preparing data dump for table `sakila`.`inventory`
Data dump for table `sakila`.`inventory` will be chunked using column `inventory_id`
Preparing data dump for table `sakila`.`language`
Writing DDL for view `sakila`.`sales_by_film_category`
Data dump for table `sakila`.`language` will be chunked using column `language_id`
Preparing data dump for table `sakila`.`payment`
Writing DDL for view `sakila`.`sales_by_store`
Writing DDL for view `sakila`.`staff_list`
Data dump for table `sakila`.`payment` will be chunked using column `payment_id`
Preparing data dump for table `sakila`.`rental`
Data dump for table `sakila`.`rental` will be chunked using column `rental_id`
Preparing data dump for table `sakila`.`staff`
Writing DDL for table `sakila`.`actor`
Data dump for table `sakila`.`staff` will be chunked using column `staff_id`
Preparing data dump for table `sakila`.`store`
Data dump for table `sakila`.`store` will be chunked using column `store_id`
Preparing data dump for table `employees`.`departments`
Data dump for table `employees`.`departments` will be chunked using column `dept_no`
Preparing data dump for table `employees`.`dept_emp`
Data dump for table `employees`.`dept_emp` will be chunked using column `emp_no`
Preparing data dump for table `employees`.`dept_manager`
Writing DDL for table `sakila`.`address`
Data dump for table `employees`.`dept_manager` will be chunked using column `emp_no`
Preparing data dump for table `employees`.`employees`
Writing DDL for table `sakila`.`category`
Data dump for table `employees`.`employees` will be chunked using column `emp_no`
Preparing data dump for table `employees`.`salaries`
Data dump for table `employees`.`salaries` will be chunked using column `emp_no`
Preparing data dump for table `employees`.`titles`
Data dump for table `employees`.`titles` will be chunked using column `emp_no`
Running data dump using 4 threads.
NOTE: Progress information uses estimated values and may not be accurate.
Writing DDL for table `sakila`.`city`
Writing DDL for table `sakila`.`country`
Writing DDL for table `sakila`.`customer`
Writing DDL for table `sakila`.`film`
Writing DDL for table `sakila`.`film_actor`
Writing DDL for table `sakila`.`film_category`
Writing DDL for table `sakila`.`film_text`
Writing DDL for table `sakila`.`inventory`
Writing DDL for table `sakila`.`language`
Writing DDL for table `sakila`.`payment`
Writing DDL for table `sakila`.`rental`
Writing DDL for table `sakila`.`staff`
Writing DDL for table `sakila`.`store`
Writing DDL for schema `employees`
Writing DDL for view `employees`.`current_dept_emp`
Writing DDL for view `employees`.`dept_emp_latest_date`
Writing DDL for table `employees`.`departments`
Writing DDL for table `employees`.`dept_emp`
Writing DDL for table `employees`.`dept_manager`
Writing DDL for table `employees`.`employees`
Writing DDL for table `employees`.`salaries`
Writing DDL for table `employees`.`titles`
Data dump for table `sakila`.`actor` will be written to 1 file
Data dump for table `sakila`.`category` will be written to 1 file
Data dump for table `sakila`.`address` will be written to 1 file
Data dump for table `sakila`.`country` will be written to 1 file
Data dump for table `sakila`.`customer` will be written to 1 file
Data dump for table `sakila`.`city` will be written to 1 file
Data dump for table `sakila`.`film_actor` will be written to 1 file
Data dump for table `sakila`.`film` will be written to 1 file
Data dump for table `sakila`.`film_category` will be written to 1 file
Data dump for table `sakila`.`film_text` will be written to 1 file
Data dump for table `sakila`.`language` will be written to 1 file
Data dump for table `sakila`.`payment` will be written to 1 file
Data dump for table `sakila`.`inventory` will be written to 1 file
Data dump for table `sakila`.`rental` will be written to 1 file
Data dump for table `sakila`.`store` will be written to 1 file
Data dump for table `sakila`.`staff` will be written to 1 file
Data dump for table `employees`.`departments` will be written to 1 file
Data dump for table `employees`.`dept_manager` will be written to 1 file
Data dump for table `employees`.`dept_emp` will be written to 1 file
Data dump for table `employees`.`employees` will be written to 1 file
Data dump for table `employees`.`salaries` will be written to 1 file
Data dump for table `employees`.`titles` will be written to 1 file
1 thds dumping - 100% (3.97M rows / ~3.93M rows), 733.12K rows/s, 26.80 MB/s uncompressed, 7.02 MB/s compressed
Duration: 00:00:05s
Schemas dumped: 2
Tables dumped: 22
Uncompressed data size: 144.53 MB
Compressed data size: 37.75 MB
Compression ratio: 3.8
Rows written: 3966288
Bytes written: 37.75 MB
Average uncompressed throughput: 26.31 MB/s
Average compressed throughput: 6.87 MB/s
確かにtestスキーマに対してはバックアップを取得していないですね。
最初だけは出力結果を上記のように表示しましたが、長いんであとは省略。
基本コマンド・オプション実行例だけだしておきます。
excludeTables
任意のテーブルをダンプから除外します。
DDLだけとかデータだけとかそういうオプションはありません、とのこと。
ちなみにmysql.apply_status、 mysql.general_log、 mysql.schema、とmysql.slow_log tablesあたりのデータだけは勝手に除外されるようです。
例）test.test02テーブルを除外
MySQL 127.0.0.1:8021 ssl JS > util.dumpInstance("/tmp/dumpdir2",{excludeTables:["test.test02"]})
ユーザ情報を除外
ユーザ関連の情報を除外します。これはcreate userやrole,grantとかそういったものを移行先に持っていかないようにするやつですね。
MySQL 127.0.0.1:8021 ssl JS > util.dumpInstance("/tmp/dumpdir3",{users:false})
メタ定義のみ
いわゆるmysqldumpでいう--no-dataですね。DDLだけ取得するやつです。
MySQL 127.0.0.1:8021 ssl JS > util.dumpInstance("/tmp/dumpdir4",{ddlOnly:true})
データオンリー
いわゆるmysqldumpでいう--no-create-infoですね。データだけ取得するやつです。
MySQL 127.0.0.1:8021 ssl JS > util.dumpInstance("/tmp/dumpdir5",{dataOnly:true})
ドライラン
要するに実際に作りはしないけど、やったらどうなるかな？ってやつ。
試しに既に作成済のダンプディレクトリに出力しようとした→ドライランなのにエラー（なかなか便利そう）
MySQL 127.0.0.1:8021 ssl JS > util.dumpInstance("/tmp/dumpdir5",{excludeSchemas:["test"],dryRun:true})
Util.dumpInstance: Cannot proceed with the dump, '/tmp/dumpdir5' already exists at the target location /tmp/dumpdir5. (ArgumentError)
testスキーマ抜きでやってみる→実際は出力されない（OK）
MySQL 127.0.0.1:8021 ssl JS > util.dumpInstance("/tmp/dumpdir6",{excludeSchemas:["test"],dryRun:true})
/tmp$ ll 合計 92 drwxrwxrwt 20 root root 4096 10月 4 06:29 ./ drwxr-xr-x 26 root root 4096 9月 23 09:59 ../ drwxr-x--- 2 (user) (user) 4096 10月 4 06:21 dumpdir5/ srwxrwxrwx 1 (user) (user) 0 10月 4 06:11 mysql_sandbox8021.sock= ...
/tmp/dumpdir6がないですね。
mdsに持っていけるかチェック
エラーのオンパレードの模様…
MySQL 127.0.0.1:8021 ssl JS > util.dumpInstance("/tmp/dumpdir6",{excludeSchemas:["test"],dryRun:true, ocimds: true})
Checking for compatibility with MySQL Database Service 8.0.21
ERROR: User root@localhost is granted restricted privileges: RELOAD, FILE, SUPER, BINLOG_ADMIN, SET_USER_ID
ERROR: User R_DO_IT_ALL@% is granted restricted privileges: RELOAD, FILE, SUPER, BINLOG_ADMIN, SET_USER_ID
ERROR: User R_POWERFUL@% is granted restricted privileges: RELOAD, FILE, SUPER, BINLOG_ADMIN, SET_USER_ID
NOTE: Database sakila had unsupported ENCRYPTION option commented out
ERROR: Function sakila.get_customer_balance - definition uses DEFINER clause set to user `root`@`localhost` which can only be executed by this user or a user with SET_USER_ID or SUPER privileges
ERROR: Function sakila.inventory_held_by_customer - definition uses DEFINER clause set to user `root`@`localhost` which can only be executed by this user or a user with SET_USER_ID or SUPER privileges
ERROR: Function sakila.inventory_in_stock - definition uses DEFINER clause set to user `root`@`localhost` which can only be executed by this user or a user with SET_USER_ID or SUPER privileges
ERROR: Procedure sakila.film_in_stock - definition uses DEFINER clause set to user `root`@`localhost` which can only be executed by this user or a user with SET_USER_ID or SUPER privileges
ERROR: Procedure sakila.film_not_in_stock - definition uses DEFINER clause set to user `root`@`localhost` which can only be executed by this user or a user with SET_USER_ID or SUPER privileges
ERROR: Procedure sakila.rewards_report - definition uses DEFINER clause set to user `root`@`localhost` which can only be executed by this user or a user with SET_USER_ID or SUPER privileges
NOTE: Database employees had unsupported ENCRYPTION option commented out
ERROR: Trigger sakila.customer_create_date - definition uses DEFINER clause set to user `root`@`localhost` which can only be executed by this user or a user with SET_USER_ID or SUPER privileges
ERROR: Trigger sakila.ins_film - definition uses DEFINER clause set to user `root`@`localhost` which can only be executed by this user or a user with SET_USER_ID or SUPER privileges
ERROR: Trigger sakila.upd_film - definition uses DEFINER clause set to user `root`@`localhost` which can only be executed by this user or a user with SET_USER_ID or SUPER privileges
ERROR: Trigger sakila.del_film - definition uses DEFINER clause set to user `root`@`localhost` which can only be executed by this user or a user with SET_USER_ID or SUPER privileges
ERROR: Trigger sakila.payment_date - definition uses DEFINER clause set to user `root`@`localhost` which can only be executed by this user or a user with SET_USER_ID or SUPER privileges
ERROR: Trigger sakila.rental_date - definition uses DEFINER clause set to user `root`@`localhost` which can only be executed by this user or a user with SET_USER_ID or SUPER privileges
ERROR: View sakila.actor_info - definition uses DEFINER clause set to user `root`@`localhost` which can only be executed by this user or a user with SET_USER_ID or SUPER privileges
ERROR: View sakila.customer_list - definition uses DEFINER clause set to user `root`@`localhost` which can only be executed by this user or a user with SET_USER_ID or SUPER privileges
ERROR: View sakila.film_list - definition uses DEFINER clause set to user `root`@`localhost` which can only be executed by this user or a user with SET_USER_ID or SUPER privileges
ERROR: View sakila.nicer_but_slower_film_list - definition uses DEFINER clause set to user `root`@`localhost` which can only be executed by this user or a user with SET_USER_ID or SUPER privileges
ERROR: View sakila.sales_by_film_category - definition uses DEFINER clause set to user `root`@`localhost` which can only be executed by this user or a user with SET_USER_ID or SUPER privileges
ERROR: View sakila.sales_by_store - definition uses DEFINER clause set to user `root`@`localhost` which can only be executed by this user or a user with SET_USER_ID or SUPER privileges
ERROR: View sakila.staff_list - definition uses DEFINER clause set to user `root`@`localhost` which can only be executed by this user or a user with SET_USER_ID or SUPER privileges
ERROR: View employees.current_dept_emp - definition uses DEFINER clause set to user `root`@`localhost` which can only be executed by this user or a user with SET_USER_ID or SUPER privileges
ERROR: View employees.dept_emp_latest_date - definition uses DEFINER clause set to user `root`@`localhost` which can only be executed by this user or a user with SET_USER_ID or SUPER privileges
Compatibility issues with MySQL Database Service 8.0.21 were found. Please use the 'compatibility' option to apply compatibility adaptations to the dumped DDL.
Util.dumpInstance: Compatibility issues were found (RuntimeError)
どうやら今のところMDSに持っていくためには変換が必要＝compatibilityっていうオプションが必要な模様
compatibilityを使う
MySQL 127.0.0.1:8021 ssl JS > util.dumpInstance("/tmp/dumpdir6",{excludeSchemas:["test"],dryRun:true,ocimds:true,compatibility:["strip_definers", "strip_restricted_grants"]})
Checking for compatibility with MySQL Database Service 8.0.21
NOTE: User root@localhost had restricted privileges (RELOAD, FILE, SUPER, BINLOG_ADMIN, SET_USER_ID) removed
NOTE: User R_DO_IT_ALL@% had restricted privileges (RELOAD, FILE, SUPER, BINLOG_ADMIN, SET_USER_ID) removed
NOTE: User R_POWERFUL@% had restricted privileges (RELOAD, FILE, SUPER, BINLOG_ADMIN, SET_USER_ID) removed
NOTE: Database sakila had unsupported ENCRYPTION option commented out
NOTE: Function sakila.get_customer_balance had definer clause removed and SQL SECURITY characteristic set to INVOKER
NOTE: Function sakila.inventory_held_by_customer had definer clause removed and SQL SECURITY characteristic set to INVOKER
NOTE: Function sakila.inventory_in_stock had definer clause removed and SQL SECURITY characteristic set to INVOKER
NOTE: Procedure sakila.film_in_stock had definer clause removed and SQL SECURITY characteristic set to INVOKER
NOTE: Procedure sakila.film_not_in_stock had definer clause removed and SQL SECURITY characteristic set to INVOKER
NOTE: Procedure sakila.rewards_report had definer clause removed and SQL SECURITY characteristic set to INVOKER
NOTE: Database employees had unsupported ENCRYPTION option commented out
NOTE: Trigger sakila.customer_create_date had definer clause removed
NOTE: Trigger sakila.ins_film had definer clause removed
NOTE: Trigger sakila.upd_film had definer clause removed
NOTE: Trigger sakila.del_film had definer clause removed
NOTE: Trigger sakila.payment_date had definer clause removed
NOTE: Trigger sakila.rental_date had definer clause removed
NOTE: View sakila.actor_info had definer clause removed
NOTE: View sakila.customer_list had definer clause removed and SQL SECURITY characteristic set to INVOKER
NOTE: View sakila.film_list had definer clause removed and SQL SECURITY characteristic set to INVOKER
NOTE: View sakila.nicer_but_slower_film_list had definer clause removed and SQL SECURITY characteristic set to INVOKER
NOTE: View sakila.sales_by_film_category had definer clause removed and SQL SECURITY characteristic set to INVOKER
NOTE: View sakila.sales_by_store had definer clause removed and SQL SECURITY characteristic set to INVOKER
NOTE: View sakila.staff_list had definer clause removed and SQL SECURITY characteristic set to INVOKER
NOTE: View employees.current_dept_emp had definer clause removed and SQL SECURITY characteristic set to INVOKER
NOTE: View employees.dept_emp_latest_date had definer clause removed and SQL SECURITY characteristic set to INVOKER
Compatibility issues with MySQL Database Service 8.0.21 were found and repaired. Please review the changes made before loading them.
...
チェック作業が事前に入ってる。
パフォーマンスの調整したい人に
並列数２スレッド、ダンプ中のデータ読み取りスループットのスレッドあたりの1秒あたりの最大バイト数を10MBに抑えるような場合
MySQL 127.0.0.1:8021 ssl JS > util.dumpInstance("/tmp/dumpdir7",threads:2,maxRate:"10M"})
他にも
まだオプションはあるんだけど、あんまり使わなそうなやつは今回省略w
知りたい方はドキュメントを熟読して僕に教えてください。
MDSに直接入れたい
ローカルにあるダンプ結果をOCIだとどうやって上げるんだろう？
というので、調べたところ、オブジェクトストレージというものがありました。（AWSで言うところのS3かな）
これ作って、そこにアップしたらええんやな？
いや、待てよ。直接MySQL Shellでそこにアップできない？
できるんです！くうぅ〜〜〜！！
準備作業
オブジェクトストレージ（正確にはバケット）を作りましょう。(作り方はドキュメントをご参照ください)
OCI クライアントをインストールしましょう。（これもインストールの仕方はドキュメントを参照してください）
とはいえ、OCI クライアントはちょっとハマったので簡単にメモを残しておきます
インストール
$ sudo bash -c "$(curl -L https://raw.githubusercontent.com/oracle/oci-cli/master/scripts/install/install.sh)" --accept-all-defaults
oci cliのconfig作成
$ oci setup config
こんな感じのやり取りがある
This command provides a walkthrough of creating a valid CLI config file.
The following links explain where to find the information required by this
script:
User API Signing Key, OCID and Tenancy OCID:
https://docs.cloud.oracle.com/Content/API/Concepts/apisigningkey.htm#Other
Region:
https://docs.cloud.oracle.com/Content/General/Concepts/regions.htm
General config documentation:
https://docs.cloud.oracle.com/Content/API/Concepts/sdkconfig.htm
Enter a location for your config [/home/(user)/.oci/config]:
Config file: /home/(user)/.oci/config already exists. Do you want add a profile here? (If no, you will be prompted to overwrite the file) [Y/n]: n
File: /home/(user)/.oci/config already exists. Do you want to overwrite (Removes existing profiles!!!)? [y/N]: y
Enter a user OCID: ocid1.user.oc1..aaaaaaaaxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Enter a tenancy OCID: ocid1.tenancy.oc1..aaaaaaaavyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy
Enter a region (e.g. ap-chiyoda-1, ap-chuncheon-1, ap-hyderabad-1, ap-melbourne-1, ap-mumbai-1, ap-osaka-1, ap-seoul-1, ap-sydney-1, ap-tokyo-1, ca-montreal-1, ca-toronto-1, eu-amsterdam-1, eu-frankfurt-1, eu-zurich-1, me-jeddah-1, sa-saopaulo-1, uk-gov-cardiff-1, uk-gov-london-1, uk-london-1, us-ashburn-1, us-gov-ashburn-1, us-gov-chicago-1, us-gov-phoenix-1, us-langley-1, us-luke-1, us-phoenix-1, us-sanjose-1): ap-tokyo-1
Do you want to generate a new API Signing RSA key pair? (If you decline you will be asked to supply the path to an existing key.) [Y/n]: Y
Enter a directory for your keys to be created [/home/(user)/.oci]:
Enter a name for your key [oci_api_key]:
Public key written to: /home/(user)/.oci/oci_api_key_public.pem
Enter a passphrase for your private key (empty for no passphrase):
Private key written to: /home/(user)/.oci/oci_api_key.pem
Fingerprint: 1e:2b:f5:83:93:93:9e:03:85:05:66:7f:b1:f8:ac:ca
Config written to /home/(user)/.oci/config
If you haven't already uploaded your API Signing public key through the
console, follow the instructions on the page linked below in the section
'How to upload the public key':
https://docs.cloud.oracle.com/Content/API/Concepts/apisigningkey.htm#How2
公開鍵をアップロードする
このドキュメントを見ながらユーザー＞公開キーの追加で公開鍵を登録します。
公開鍵はこんな感じで見れるかと。
cat /home/(user)/.oci/oci_api_key_public.pem
トークンの検証
oci iam region list
を実行して、以下のように出たらOK
{
"data": [
{
"key": "AMS",
"name": "eu-amsterdam-1"
},
{
"key": "BOM",
"name": "ap-mumbai-1"
},
{
"key": "DXB",
"name": "me-dubai-1"
},
...
{
"key": "YYZ",
"name": "ca-toronto-1"
},
{
"key": "ZRH",
"name": "eu-zurich-1"
}
]
}
さっそくOCIへ吐き出してみる
osBucketNameとosNamespace
OCIのオブジェクトストレージのバケットに出力するにはこの２つのオプションが必要。
osBucketNameは文字通り、オブジェクトストレージのバケット名、osNamespaceでバケットの名前空間を指定します。
MySQL 127.0.0.1:8021 ssl JS > util.dumpInstance("testbackup",{ocimds:true,compatibility:["strip_definers", "strip_restricted_grants"],"osBucketName": "bucket-hogehoge", "osNamespace": "xxxxxxx"})
osNamespaceは以下のコマンドで取得するか、オブジェクトストレージ>バケットの詳細>ネームスペースで確認するといいです。
oci os ns get
MDSにインポート
事前準備
OCIにインスタンスを一つ立てて、そっちにもOCI クライアントをインストールしましょう。
インポート
そしてMySQL Shellで以下のように実行すればインポートできます
MySQL xxxxxx:3306 ssl JS >util.loadDump("testbackup", {osBucketName: "bucket-hogehoge", osNamespace: "xxxxxxx"})
並列度はインスタンスのCPUパワー次第で上げたら良いんじゃないかなと思いますが、僕はお試しなんでデフォルトのままでやりました。
インポートのほうのパラメータはあんまり調べませんでしたが、パフォーマンスの設定以外は基本エクスポートでやっちゃう人が多いんじゃないかな？と思って今回は手を抜きましたｗ
さいごに
今回はMySQL ShellとOCI両方ちょっとずつ学ぶことができました。
インポートは若干駆け足気味ですが、まあそんなに難しくないかと思って。
MySQL Shellのダンプが使えるのは8.0.21限定ですし、MDSもまだまだ検証段階って人も多いと思います。
そんな段階だからこそ、触っていざというときにどっちも使えるようになっておけば、きっと将来役に立つこと間違い無しです（？）
MySQL Database Serviceを触るまでのお話
はじめに
MDS（MySQL Database Service）が東京リージョンにも来ました！
(^O^) <祝！
というわけで、朝4時に起こされたこともあり、時間もできたんで久々にOCIを触ってみたんですが、作りたいものが簡単に作れて楽しかったです。
ただ、公式チュートリアルにMDS関連がまだ無いこと、MDS系だとlefredさんのブログとかOracleのセミナー資料しか手順でわかりやすい情報が無いのでまだまだ敷居が微妙に高い可能性もあり、初見の人がどうするの？っていうのをざっくり流れで書いてみました。
でも、細かいところは前述の公式のチュートや記事を参考にしてね。
やりたいこと
要するに、１台どっからでもインターネット上から繋げられるようなVM立てて、そのマシン経由でMySQLに繋げられるイメージ。
マイPC--->インターネット--->OCI領域[VM(Linux)--->MySQL]
作り方(超ざっくり)
１．アカウント作成
まずはなにはともあれアカウントを作る
通常、ここからOracle Cloud Freeでアカウントが作れる。
30日間有効な$300のクレジットがもらえて、MySQL DBシステムは有料なのでそこから支払われる。
と言っても、普段Auroraとか触ってる人からすると安すぎて笑うくらい。
多くの人は30日じゃ使い切れないんじゃないかな。
また、Oracle主催のセミナーとか参加すると更に+$200で合計$500のクレジットとかになることがある（それ用のURLを教えてもらえる）。
そっちからだと通常はクレカ登録があるのだが、無いので楽ちんでもある。
注意点
アカウント作成時の注意点は以下の２つ
- クラウド・アカウント名を忘れない
- ホーム・リージョンを日本東部（東京）にしよう
クラウド・アカウント名はログイン時に使う。
ログイン時に使うのはクラウド・アカウント名、メールアドレス、パスワードの３つ
ホーム・リージョンはせっかく日本でMDS使えるようになったー！ってなったのに、アメリカにするのもどうなん？ってことで。
まあ、アッシュバーンは空いてるなんて話もあるんで、それでも良いって人は止めませんがｗ
２．ログイン
チュートリアル読みながらログインしてみてください。
ちなみにクラウド・アカウント名はテナンシー名とも言われるので、どっかで出てきたら脳内変換してやってください。
クラウド・アカウントの領域を内部で区画わけするコンパートメントって概念もあるけど、個人で使う分には無視でいいかも。
整理整頓好きな人は使ってみるのもいいかも（自分は一回作って、ちょっと触って削除した）
３．VCNの作成
２同様チュートリアル読みながら作成してみてください。
チュートリアルにもありますが、VCNウィザード使ったほうが楽です。VCN名考えることくらいしかやることありませんｗ
パブリック・サブネットもプライベート・サブネットも作ってくれます。
プライベート・サブネットの更新でエラーが出ることがありますが、プロビジョニングの再試行で普通に作れます。
４．インスタンスの作成
チュートリアルを参考に作成してみてください。
ここに用意されているOSのイメージがデフォルトでは使え、OWindows Server以外はAlways Free対象です。
とりあえず、自分はCentOS7とOracle Linux7.8の2つを作ってみました。
シェイプ、ネットワークおよびストレージ・オプションの表示を開くと先程作ったVCNのパブリックサブネットからIPがデフォルトで割り振られているのがわかります。
接続については
- SSHキー・ペアの生成
- 公開キー・ファイルの選択
- 公開キーの貼付け
がありますが、自分は公開キーを貼り付けてます。（どうせ使うPC決まってるし）
作成されたパブリックIPを使ってsshでつなぎます（以下イメージです）
接続例
$ ssh opc@xxx.xxx.xxx.xxx The authenticity of host 'xxx.xxx.xxx.xxx (xxx.xxx.xxx.xxx)' can't be established. ECDSA key fingerprint is SHA256:pKpvubZvr4MeMfHVHMzRJxMhRlIdpJqimTdiPD64qLk. Are you sure you want to continue connecting (yes/no)? yes Warning: Permanently added 'xxx.xxx.xxx.xxx' (ECDSA) to the list of known hosts. [opc@tkyk7704vm-c7 ~]$
５．MySQLクライアント等を入れとく
mysqlshやmysqlクライアントでDBにつなごうと思っているので、それらをインストールしておきます。
$ sudo yum install https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm $ sudo yum install mysql-shell $ sudo yum install mysql-community-client
６．プライベート・サブネットのイングレス・ルールにMySQLポート追加
パブリックIPがこれから作るMySQL DBシステムに接続できるよう、先にプライベート・サブネットのイングレス・ルールにMySQLポートの3306と33060を追加しておきます。
この作業はMySQL DBシステム作ったあとでも問題ありません。
ポート番号もMySQL DBシステムを別の番号で作るのであれば臨機応変に変えてください。
ネットワーキング＞仮想クラウド・ネットワーク＞作ったVCN＞プライベート・サブネット＞プライベート・サブネットのセキュリティ・リスト という感じでクリックしていくと、先程作ったVCNに紐づくプライベート・サブネットのイングレス・ルールの追加画面が表示されます。
ここでイングレス・ルールの追加ボタンを押して、パブリック・サブネットをソースCIDRに、宛先ポート範囲に3306,33060を入れて追加ボタンを押せばOKです。
(ちなみにプライベート・サブネットの他のルールは消しておくのが吉です。無駄な穴は消しておきましょう)
山崎さんのスライドでもこの辺の話は記載されてますので参考にしてください。
７．MySQL DBシステムの作成
MySQL＞DBシステムをクリックすると作成画面に入ります。
MySQL DBシステムの作成ボタンを押して作ります。
作り方や機能の詳細についてはここからダウンロードできる資料のほうが詳しいので僕は手抜きで説明しませんｗ
出来上がるまで結構かかります。10分〜15分くらいかかることも。コーヒーでも淹れてゆっくり飲みながら待つのが吉です。
８．DBへ接続
MySQLクライアントやMySQL Shellで接続してみてください。
mysql -u(DBユーザ名) -p -h(DBのエンドポイント名) mysqlsh (DBユーザ名)@(DBのエンドポイント名)
ちゃんと入れてるのに MySQL Error 2003 (HY000): Can't connect to MySQL server が出るよーって人はイングレス・ルールを見直してみてください （入れるのはプライベート・サブネットのほうですよ！）
９．ローカルからDBを見てみたい？
そういう人はlefredさんのブログを読むと言いです。
SSHトンネルやWorkbenchでつなぐ方法があります。
（一つ前の記事にMySQL Router使うなんてなかなかトリッキーなのもありますが・・・）
コスト
一番小さいサイズとはいえ、数時間放置してて、おっといけね！と見てみたが、
安ぅ〜〜〜！
コストの見積もり、ここでできるみたいだけど、一番小さいやつ、デフォルトだと4400円くらいなんで、$300とか、ましてや$500とか最初にも書いたけどやっぱり使い切れない気がする。
サイズ上げて、ディスクも増やしてガンガン遊んでみるかー！（家族が時間をクレナイケド・・・）
さいごに
１時間もあればこれらの作業ができます。
しかも一回VCNやインスタンスを設定してしまえば、更に時間は短縮されるので、今からVM環境作ったりするよりは圧倒的に早いです。
自分も今日作った環境でこれから色々と遊んでいこうと思います。
追記
日本語のチュートリアルが無いってだけで、ちゃんとドキュメントにはある。
（これを朝見つけておきたかったなー）
