Hatena::ブログ(Diary)

ablog このページをアンテナに追加 RSSフィード

2017-09-22

Oracle Database から Aurora MySQL互換 に DMS でデータをロードする

Oracle Database から Aurora with MySQL Compatibility にテーブルのデータを AWS Database Migration Service (DMS) でフルロードしてみたメモ。


ロード後のソースDBとターゲットDBのデータ比較

SQL> select * from test_timestamp;

COL_TS                          COL_TS6                         COL_TS9
------------------------------- ------------------------------- -------------------------------
20-SEP-17 01.23.39.000000000 AM 20-SEP-17 01.23.39.000000000 AM 20-SEP-17 01.23.39.000000000 AM
20-SEP-17 01.23.54.502295000 AM 20-SEP-17 01.23.54.502295000 AM 20-SEP-17 01.23.54.502295000 AM
  • ターゲットDB(Aurora with MySQL Compatibility)
mysql> select * from TEST_TIMESTAMP;
+----------------------------+----------------------------+-------------------------------+
| COL_TS                     | COL_TS6                    | COL_TS9                       |
+----------------------------+----------------------------+-------------------------------+
| 2017-09-20 01:23:39.000000 | 2017-09-20 01:23:39.000000 | 2017-09-20 01:23:39.000000000 |
| 2017-09-20 01:23:54.502295 | 2017-09-20 01:23:54.502295 | 2017-09-20 01:23:54.502295000 |
+----------------------------+----------------------------+-------------------------------+
2 rows in set (0.01 sec)

テーブル定義の比較

SQL> info test_timestampTABLE: TEST_TIMESTAMP
	 LAST ANALYZED:2017-09-20 02:00:03.0
	 ROWS         :2
	 SAMPLE SIZE  :2
	 INMEMORY     :DISABLED
	 COMMENTS     :

Columns
NAME         DATA TYPE      NULL  DEFAULT    COMMENTS
 COL_TS      TIMESTAMP(6)   Yes
 COL_TS6     TIMESTAMP(6)   Yes
 COL_TS9     TIMESTAMP(9)   Yes
  • ターゲットDB(Aurora with MySQL Compatibility)
mysql> desc TEST_TIMESTAMP;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| COL_TS  | datetime(6) | YES  |     | NULL    |       |
| COL_TS6 | datetime(6) | YES  |     | NULL    |       |
| COL_TS9 | varchar(37) | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

手順

create table test_timestamp(col_ts timestamp, col_ts6 timestamp(6), col_ts9 timestamp(9));
insert into test_timestamp values(sysdate, sysdate, sysdate);
insert into test_timestamp values(systimestamp, systimestamp, systimestamp);
commit;
create table `test_timestamp` (
  `col_ts` datetime(6) default null,
  `col_ts6` datetime(6) default null,
  `col_ts9` varchar(37) default null
) engine=innodb default charset=utf8;
  • DMS のレプリケーションインスタンス、ソースDBとターゲットDBへのエンドポイントを作成する。
  • DMS のレプリケーションタスクの定義(タスクはマネジメントコンソールで作成)
$ aws dms describe-replication-tasks --filters Name=replication-task-id,Values=test-timestamp
{
    "ReplicationTasks": [
        {
            "SourceEndpointArn": "arn:aws:dms:ap-northeast-1:***************:endpoint:...",
            "ReplicationTaskIdentifier": "test-timestamp",
            "ReplicationInstanceArn": "arn:aws:dms:ap-northeast-1:***************:rep:...",
            "TableMappings": "{\"rules\":[{\"rule-type\":\"selection\",\"rule-id\":\"1\",\"rule-name\":\"1\",\"object-locator\":{\"schema-name\":\"AWSUSER\",\"table-name\":\"TEST_TIMESTAMP\"},\"rule-action\":\"include\"},{\"rule-type\":\"transformation\",\"rule-id\":\"2\",\"rule-name\":\"2\",\"rule-target\":\"schema\",\"object-locator\":{\"schema-name\":\"AWSUSER\"},\"rule-action\":\"rename\",\"value\":\"mydb\"}]}",
            "ReplicationTaskStartDate": 1506052627.915,
            "ReplicationTaskStats": {
                "TablesLoading": 0,
                "TablesQueued": 0,
                "TablesErrored": 0,
                "FullLoadProgressPercent": 100,
                "TablesLoaded": 1,
                "ElapsedTimeMillis": 1413
            },
            "Status": "stopped",
            "ReplicationTaskArn": "arn:aws:dms:ap-northeast-1:***************:task:...",
            "StopReason": "Stop Reason FULL_LOAD_ONLY_FINISHED",
            "ReplicationTaskCreationDate": 1506052582.138,
            "MigrationType": "full-load",
            "TargetEndpointArn": "arn:aws:dms:ap-northeast-1:***************:endpoint:...",
            "ReplicationTaskSettings": "{\"TargetMetadata\":{\"TargetSchema\":\"\",\"SupportLobs\":false,\"FullLobMode\":false,\"LobChunkSize\":64,\"LimitedSizeLobMode\":true,\"LobMaxSize\":32,\"LoadMaxFileSize\":0,\"ParallelLoadThreads\":0,\"ParallelLoadBufferSize\":0,\"BatchApplyEnabled\":false},\"FullLoadSettings\":{\"TargetTablePrepMode\":\"DO_NOTHING\",\"CreatePkAfterFullLoad\":false,\"StopTaskCachedChangesApplied\":false,\"StopTaskCachedChangesNotApplied\":false,\"MaxFullLoadSubTasks\":8,\"TransactionConsistencyTimeout\":600,\"CommitRate\":10000},\"Logging\":{\"EnableLogging\":true,\"LogComponents\":[{\"Id\":\"SOURCE_UNLOAD\",\"Severity\":\"LOGGER_SEVERITY_DEFAULT\"},{\"Id\":\"SOURCE_CAPTURE\",\"Severity\":\"LOGGER_SEVERITY_DEFAULT\"},{\"Id\":\"TARGET_LOAD\",\"Severity\":\"LOGGER_SEVERITY_DEFAULT\"},{\"Id\":\"TARGET_APPLY\",\"Severity\":\"LOGGER_SEVERITY_DEFAULT\"},{\"Id\":\"TASK_MANAGER\",\"Severity\":\"LOGGER_SEVERITY_DEFAULT\"}],\"CloudWatchLogGroup\":\"dms-tasks-replication-instance-1\",\"CloudWatchLogStream\":\"dms-task-R32JDUMJAF4ADCBYRWYGMN6PIE\"},\"ControlTablesSettings\":{\"historyTimeslotInMinutes\":5,\"ControlSchema\":\"\",\"HistoryTimeslotInMinutes\":5,\"HistoryTableEnabled\":false,\"SuspendedTablesTableEnabled\":false,\"StatusTableEnabled\":false},\"StreamBufferSettings\":{\"StreamBufferCount\":3,\"StreamBufferSizeInMB\":8,\"CtrlStreamBufferSizeInMB\":5},\"ChangeProcessingDdlHandlingPolicy\":{\"HandleSourceTableDropped\":true,\"HandleSourceTableTruncated\":true,\"HandleSourceTableAltered\":true},\"ErrorBehavior\":{\"DataErrorPolicy\":\"LOG_ERROR\",\"DataTruncationErrorPolicy\":\"LOG_ERROR\",\"DataErrorEscalationPolicy\":\"SUSPEND_TABLE\",\"DataErrorEscalationCount\":0,\"TableErrorPolicy\":\"SUSPEND_TABLE\",\"TableErrorEscalationPolicy\":\"STOP_TASK\",\"TableErrorEscalationCount\":0,\"RecoverableErrorCount\":-1,\"RecoverableErrorInterval\":5,\"RecoverableErrorThrottling\":true,\"RecoverableErrorThrottlingMax\":1800,\"ApplyErrorDeletePolicy\":\"IGNORE_RECORD\",\"ApplyErrorInsertPolicy\":\"LOG_ERROR\",\"ApplyErrorUpdatePolicy\":\"LOG_ERROR\",\"ApplyErrorEscalationPolicy\":\"LOG_ERROR\",\"ApplyErrorEscalationCount\":0,\"ApplyErrorFailOnTruncationDdl\":false,\"FullLoadIgnoreConflicts\":true,\"FailOnTransactionConsistencyBreached\":false,\"FailOnNoTablesCaptured\":false},\"ChangeProcessingTuning\":{\"BatchApplyPreserveTransaction\":true,\"BatchApplyTimeoutMin\":1,\"BatchApplyTimeoutMax\":30,\"BatchApplyMemoryLimit\":500,\"BatchSplitSize\":0,\"MinTransactionSize\":1000,\"CommitTimeout\":1,\"MemoryLimitTotal\":1024,\"MemoryKeepTime\":60,\"StatementCacheSize\":50}}"
        }
    ]
}
  • レプリケーションタスク実行
$ aws dms start-replication-task --replication-task-arn arn:aws:dms:ap-northeast-1:***:task:*** --start-replication-task-type reload-target

参考

2017-09-15

MySQL で NOT NULL 制約のある列に複数行インサートするとその型のデフォルト値が入る

MySQLSQL モードが STRICT モードでない場合、NOT NULL 制約のある列に複数行インサートするとその型のデフォルト値(0とか空文字)が入る(1行インサートだとエラーで入らない)。


検証結果

  • Amazon Aurora with MySQL Compatibility に接続する
$ mysql -h ******.******.ap-northeast-1.rds.amazonaws.com -u awsuser -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 53
Server version: 5.6.10 MySQL Community Server (GPL)

Copyright (c) 2000, 2017, 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> use mydb;
Database changed
mysql> create table `not_null_test` (
    ->   `id` int(10) unsigned not null,
    ->   `int_col` int(10) unsigned not null,
    ->   `char_col` char(10)  not null,
    ->   `ts_col` timestamp not null,
    ->   primary key (`id`)
    -> ) engine=innodb default charset=utf8;
Query OK, 0 rows affected (0.05 sec)
  • SQL モードは設定されていない
mysql> select @@global.sql_mode;
+-------------------+
| @@global.sql_mode |
+-------------------+
|                   |
+-------------------+
1 row in set (0.02 sec)
  • 1行インサートはエラーになる
mysql> insert into not_null_test (id, int_col, char_col, ts_col) values (1, null, null, null);
ERROR 1048 (23000): Column 'int_col' cannot be null
  • 複数行インサートは成功する
mysql> insert into not_null_test (id, int_col, char_col, ts_col) values (1, null, null, null), (2, null, null, null);
Query OK, 2 rows affected, 4 warnings (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 4
mysql> select * from not_null_test;
+----+---------+----------+---------------------+
| id | int_col | char_col | ts_col              |
+----+---------+----------+---------------------+
|  1 |       0 |          | 2017-09-15 06:54:07 |
|  2 |       0 |          | 2017-09-15 06:54:07 |
+----+---------+----------+---------------------+
2 rows in set (0.02 sec)
  • SQLモードを STRICT_ALL_TABLES にする
mysql> set sql_mode='strict_all_tables';
Query OK, 0 rows affected (0.03 sec)
  • 1行インサートは失敗する
mysql> insert into not_null_test (id, int_col, char_col, ts_col) values (1, null, null, null);
ERROR 1048 (23000): Column 'int_col' cannot be null
  • 複数行インサートも失敗する
mysql> insert into not_null_test (id, int_col, char_col, ts_col) values (1, null, null, null), (2, null, null, null);
ERROR 1048 (23000): Column 'int_col' cannot be nul

参考

単列インサートの場合はNOT NULLが指定されたカラムにNULL値が挿入されるとそのクエリはエラーとなって失敗するが、複数列インサートの場合は警告(warning)を発するものの、クエリは正常に受け付けられる。

その際、NULL値が指定された各カラムにはそれぞれのカラムのデータ型の暗黙的なデフォルト値が挿入される。(数値型なら0、文字列型なら空文字''、etc…)

MySQLにおけるNOT NULLカラムへのインサート時の挙動 - Sojiro’s Blog

NOT NULL として宣言されているカラムへの NULL の挿入。複数行の INSERT ステートメントまたは INSERT INTO ... SELECT ステートメントの場合、このカラムは、そのカラムデータ型の暗黙のデフォルト値に設定されます。これは、数値型では 0、文字列型では空の文字列 ('')、および日付と時間型では「0」の値です。サーバーは SELECT からの結果セットを検査して、それが単一行を返すかどうかを確認しないため、INSERT INTO ... SELECT ステートメントは複数行の挿入と同じ方法で処理されます。(単一行の INSERT の場合は、NULL が NOT NULL カラムに挿入されても警告は発生しません。代わりに、このステートメントがエラーで失敗します。)

MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.2.5 INSERT 構文

明示的な DEFAULT 句のない NOT NULL カラムに対するデータエントリでは、INSERT または REPLACE ステートメントにカラムの値を含まれていない場合、または UPDATE ステートメントがカラムを NULL に設定する場合、MySQL はその時点で有効な SQL モードに従ってカラムを処理します。

(中略)

セクション5.1.7「サーバー SQL モード」を参照してください。

所定のテーブルに対して、SHOW CREATE TABLE ステートメントを使用すると、どのカラムに明示的な DEFAULT 句があるかを確認できます。

暗黙的なデフォルトは次のように定義されます。

MySQL :: MySQL 5.6 リファレンスマニュアル :: 11.6 データ型デフォルト値

厳密モードは、MySQL が INSERT や UPDATE などのデータ変更ステートメントで無効な値または欠落した値を処理する方法を制御します。値はいくつかの理由で無効になることがあります。たとえば、カラムに対して正しくないデータ型を持っていたり、範囲外であったりすることがあります。値の欠落が発生するのは、挿入される新しい行の非 NULL カラムに値が含まれておらず、そのカラムに明示的な DEFAULT 句が定義されていない場合です。(NULL カラムの場合、値が欠落しているときは NULL が挿入されます。)

厳密モードが有効でない場合、MySQL は無効または欠落した値に対して調整された値を挿入し、警告を生成します (セクション13.7.5.41「SHOW WARNINGS 構文」を参照してください)。厳密モードでは、INSERT IGNORE または UPDATE IGNORE を使用すると、この動作を実行できます。

データを変更しない SELECT などのステートメントの場合、厳密モードでは無効な値はエラーでなく警告を生成します。

厳密モードは、外部キー制約が検査されるかどうかに影響されません。foreign_key_checks を検査に使用できます。(セクション5.1.4「サーバーシステム変数」を参照してください。)

MySQL :: MySQL 5.6 リファレンスマニュアル :: 5.1.7 サーバー SQL モード

2017-07-24

AWS DMS(Database Migration Service) で特定の時点以降のトランザクションをレプリケーションする

DMS(Database Migration Service)では RDBMS のREDO(トランザクション)ログを読んで Amazon RDS にテーブルのデータをレプリケーションする CDC(Change Data Capture) 機能*1があるが、特定の時点以降のデータをレプリケーションしたい場合は、AWSマネジメントコンソールからは "Custom CDC start time"、AWS CLI からは “--cdc-start-time” でタイムスタンプを指定することができる。

使用するオプション操作
Custom CDC start timeこのパラメータは、データ変更のレプリケーション専用に設定されたタスクに関係します。これにより、変更ストリームのどこから変更を探し始めるかを AWS DMS に指示します。
ステップ 6: 移行タスクの作成 - AWS Database Migration Service

--cdc-start-time (timestamp)

The start time for the Change Data Capture (CDC) operation.

create-replication-task — AWS CLI 1.11.170 Command Reference

補足

RDBMS は内部的に時刻と対応するカウンターを持っていて、Oracle Database では SCN(System Change Number)、PostgreSQL や MySQL では LSN(Log Sequence Number)と呼ぶ。

*1:Attunity Replicate や Oracle GoldenGate のようなもの

2016-10-04

InnoDB の Double Write の話

"Partial page writes is when page write request submited to OS completes only partially. "

MySQL のストレージエンジン InnoDB は partial page writes を防ぐ為に double write という機能がある。page は Oracle Database でいう block。Partial page writes は Oracle Database で言うブロック破損。ユーザープロセスがI/Oシステムコールを発行して、カーネルモードにスイッチ後、以下のレイヤーで一部しか書けてなかったというケース。Oracle Database でソフトウェア的に partial page writes を検知する仕組みは DB_ULTRA_SAFE パラメータのような機能だけど、書込み時は検知できない。Oracle Database on Oracle Linux + ストレージでユーザー空間、カーネル空間、ストレージまで、フルスタックで書込み時に partial page writes を検知するのが T10 DIF/DIX。

| サイオスOSS | サイオステクノロジー

2012-03-10

InnoDB Deep Talk #1 に行ってきた

InnoDB Deep Talk #1 に行ってきた。

久しぶりの六本木ヒルズ、おしゃれですねー

13時くらいに着いたので、六本木ランチを堪能して、

f:id:yohei-a:20120311013533j:image:w360

GREE さんへ GO!

登録するの遅すぎてQRコードが出なかったけど、

@yoheia いらしてくださーい!!

「いま」を見つけよう

ということで、無事参加できました。id:ichii386 さんありがとうございました!


で、はじまりはじまり


木下さん(DB改造屋雑記

「特に話すことなんてないんですけど」とはじまり、InnoDB ってこうやっとけばいいですよねと、で話すことないので、InnoDB の歴史について話ますって、そこから木下さんの過去の開発の歴史が、facebook のために XtraBackup を作られたとか、高速化のためにいろいろしていたら InnoDB 本体にとりこまれていたとか。

途中で木下さんって最近転職されて話題になっていた DB改造屋雑記: 転職等、状況のご報告 の方だということに気付きました。最初簡単そうに見せかけて、Deep なところにガンと入っていく感じ結構好きです。資料が後で公開されるものと思って、資料に書いていることは全然メモとってなかった。。。

メモしていたのは、

  • InnoDB の rw_lock は Oracle Database でいう latch にあたるぽい
  • Ken Jacobs が ORACLE を去って(ry
  • http://bugs.mysql.com/bug.php?id=29560
  • 2008年あたりに rw_lock が GCC Atomic Builtins に変更されたとか
  • チェンジングバッファは内部バグが原因でなんとかとか
  • JOIN は NESTED LOOPS のみで、最近 HASH JOIN が実装されたらしい
  • Eclipse の CDT はよい
  • バッファプールを分けるのは最後の手段(どなたかからの質問に対して)

など。

印象的だったのはマルチコアで並列処理ができるよう mutex などの排他制御の改良を重ねてきた歴史。


平塚さん

あの id:sh2 (@sh2nd) さん。twitter やブログではやりとりさせていただいたことはあるものの、お会いするのは初めて。

発表資料はコチラ → MySQL SQLオプティマイザのコスト計算アルゴリズム - SH2の日記

以下はメモ。

  • InnoDB のページは Oracle Database でいうデータブロックのことのようだ
  • P.10 の SQL のコスト計算式。バッファプールにキャッシュされることを加味してなかったから、read_time を足しているか。read_time は Oracle Database でいう Physical Reads かなとおもった。Logical か Physical かわからないが、キャッシュされることを加味してなかったということで、Physical Reads なのかなと。どちらでもいいけど、要するにブロック読み込み量をいっているんだろうと思った。
  • 平塚さんは Basic Quality Contributer。ある一定期間に10個くらい妥当な(?)バグ報告をすると、Basic Quality Contributer というフラグが立ち、その後バグ報告をすると優先的に見てもらえるらしい。「この人は信用できる」フラグみたいなもののようだ。
  • P.25 の計算式 は delete を考慮しているから?
  • 4000くらい足している → ランダムアクセスのコストを考慮しているか?

資料の最後で宿題が出題されていました。


瀬島さん(sejima | GREE Engineers' Blog

GREE に転職される前はオンラインゲームのインフラを担当されていたとか。ディクショナリの話をされていました。

資料が公開されると思って全然メモしてませんでした。。。


そして、LT

Fusion-io の長谷川さん

no title でフォーマットされたフラッシュメモリを参加者全員にいただきました。ありがとうございました。Atomic Write の話が気になった。もう少し詳しく聞きたかったが気が付いたら帰られてしまっていた(ToT) 世界初の話だったらしい。

f:id:yohei-a:20120311235949j:image:w360


moriyoshitさん(www

会場で、ずっとコード書いているぽい人がいるなと思っていたら、@moriyoshit さんだった。以前拝見したときより、シャープな感じにならているような気がした。@moriyoshit さんだと気付かなかった。

InnokoDB https://launchpad.net/innokodb

「いま」を見つけよう

猪子さんのツイートを表示するストレートエンジン?をその場でコーディングして完成。すげー

cmake のあたりのオプションででちょっとはまったとか。


斯波さん

Spider という MySQL のストレートエンジンを開発されているとのこと。

途中で、以前、Shibuya.pm#12 で NoSQL vs. NoKVS ライトニングディスカッションをされていた斯波さんだということを思い出した。

id:kamipo さんがパーティション・プルーニングが効かない(特にノードをまたがる)クエリを実行させないようにできないかといった質問というか要望を話されていた。


木村明治さん(キムラデービーブログ

twitter や OraTweet ではよくお世話になっているものの、お会いするのははじめて。

オプティマイザのことをわかりやすく説明された後に、以下の3冊を紹介されていました。

Webエンジニアのための データベース技術[実践]入門 (Software Design plus)

Webエンジニアのための データベース技術[実践]入門 (Software Design plus)

MySQL Cluster構築・運用バイブル ?仕組みからわかる基礎と実践のノウハウ

MySQL Cluster構築・運用バイブル ?仕組みからわかる基礎と実践のノウハウ


myfinderさん(まいんだーのはてなブログ

次回の My SQL Casual の紹介をされていました。Oracle Aoyama Center で開催されるらしいので、都合が合えば行きたいです。


塩原さん(Trying Database

JPOUG のボードメンバー。OOW Unconference の紹介をされていました。アグレッシブな方だと思いました。あの行動力は見習いたいです。


おもしろい勉強会でした。次は MyISAM Deep Talk #1 になるのか InnoDB Deep Talk #2 になるのかわかりませんが、また参加したいと思います。あと、MySQLInnoDB をちょっと触ってみたいと思います。