Hatena::ブログ(Diary)

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

2017-12-11

RDS PostgreSQL で大量にセッションを張った場合のメモリ使用量を調べる

RDS PostgreSQL に pgbench で 2000 セッション張って負荷をかけてみたところ、12.5GBほどあった空きメモリを使い尽くして、2GB以上スワップした。

プロセス自体のメモリ、ワーク領域、ページテーブル(PTE)などで1セッションあたり、6MB以上は使っている計算になる。


検証

pgbench を以下で2つ同時に実行して、負荷をかけた。

% pgbench -r -c 1000 -j 1000 -t 100000 -U awsuser -h pg-m4xlarge-master-6.******.ap-northeast-1.rds.amazonaws.com -d mydb

結果

AWSマネジメントコンソールの CloudWatch メトリックスで確認してみた。

f:id:yohei-a:20171211190130p:image:w640

f:id:yohei-a:20171211190125p:image:w640

f:id:yohei-a:20171211190121p:image:w640


環境

  • db.m4.xlarge*1
    • vCPU: 4
    • メモリ: 16GB

参考

ローカルメモリ量 = プロセスのスタック領域
                  + 一時テーブルを使用するデータベースセッションで消費されるメモリ
                  + ソートやハッシュテーブル操作を行うデータベースセッションで消費されるメモリ
                  + 保守操作で消費されるメモリ
                  + プロセス毎に消費される基礎メモリ
                  + データアクセスの準備のために消費されるメモリ
プロセス毎に消費される基礎メモリ
  =  3メガバイト × (max_connections + autovacuum_max_workers + 9)
H.1 FUJITSU Enterprise Postgresで使用するメモリの見積り式

2017-12-09

Redshift 検証メモ

まとめ

  • 「SVV_TABLE_INFO.TBL_ROWS」 で削除対象としてマークされた行も含めた行数を確認できる
  • 「SVV_TABLE_INFO.TBL_ROWS」 - 「select count(*) from テーブル名」が削除済としてマークされた行数
    • STV_TBL_PERM、SVV_DISKUSAGE も同様に削除済としてマークされた行数を含んだ行数を返す
  • VACCUME すると削除された行が解放され、「SVV_TABLE_INFO.TBL_ROWS」と「select count(*) from テーブル名」が一致する

データロード後

  • テーブルを作成する
CREATE TABLE customer (
  c_custkey     	integer        not null,
  c_name        	varchar(25)    not null,
  c_address     	varchar(25)    not null,
  c_city        	varchar(10)    not null,
  c_nation      	varchar(15)    not null,
  c_region      	varchar(12)    not null,
  c_phone       	varchar(15)    not null,
  c_mktsegment      varchar(10)    not null)
diststyle all;
  • ロードする
mydb=# copy customer from 's3://awssampledbuswest2/ssbgz/customer' 
credentials 'aws_access_key_id=<Your-Access-Key-ID>;aws_secret_access_key=<Your-Secret-Access-Key>' 
gzip compupdate off region 'us-west-2';

INFO:  Load into table 'customer' completed, 3000000 record(s) loaded successfully.
COPY
  • テーブルの情報を確認する
mydb=# \i table_info.sql
 schema |   table   | tableid |  distkey  |  skew  | sortkey | #sks |   rows    | mbytes | enc |        pct_enc        | pct_of_total | pct_stats_off | pct_unsorted
--------+-----------+---------+-----------+--------+---------+------+-----------+--------+-----+-----------------------+--------------+---------------+--------------
 public | customer  |  139204 | EVEN      | 1.0000 |         |    0 |   3000000 |    188 | Y   | 52.941176470588235200 |         0.04 |          0.00 |
 (1 rows)
  • テーブルの行数を確認する
mydb=# select count(*) from customer;
  count
---------
 3000000
(1 row)

mydb=# select * from SVV_TABLE_INFO where "table" = 'customer';
 database | schema | table_id |  table   | encoded | diststyle | sortkey1 | max_varchar | sortkey1_enc | sortkey_num | size | pct_used | empty | unsorted | stats_off | tbl_rows | skew_sortkey1 | skew_rows
----------+--------+----------+----------+---------+-----------+----------+-------------+--------------+-------------+------+----------+-------+----------+-----------+----------+---------------+-----------
 mydb     | public |   139204 | customer | Y       | EVEN      |          |          25 |              |           0 |  188 |   0.0492 |     0 |          |      0.00 |  3000000 |               |
(1 row)
  • スライス毎の行数を確認する
mydb=# select * from stv_tbl_perm where name = 'customer' order by slice;
 slice |   id   |                                   name                                   |  rows   | sorted_rows | temp | db_id  | insert_pristine | delete_pristine | backup
-------+--------+--------------------------------------------------------------------------+---------+-------------+------+--------+-----------------+-----------------+--------
     0 | 139204 | customer                                                                 | 1500000 |           0 |    0 | 100153 |               0 |               1 |      1
     1 | 139204 | customer                                                                 | 1500000 |           0 |    0 | 100153 |               0 |               1 |      1
  6411 | 139204 | customer                                                                 |       0 |           0 |    0 | 100153 |               3 |               0 |      1
(3 rows)
  • スライス毎のブロック数を確認する
mydb=# select name, slice, count(*) as mb  from svv_diskusage
where name = 'customer'
group by name, slice;
                                   name                                   | slice | mb
--------------------------------------------------------------------------+-------+----
 customer                                                                 |     1 | 94
 customer                                                                 |     0 | 94
(2 rows)

DELETE後

  • 削除する
mydb=# delete from customer;
DELETE 3000000
mydb=# commit;
COMMIT
  • テーブル件数を確認する
mydb=# select count(*) from customer;
 count
-------
     0
(1 row)

mydb=# select * from SVV_TABLE_INFO where "table" = 'customer';
 database | schema | table_id |  table   | encoded | diststyle | sortkey1 | max_varchar | sortkey1_enc | sortkey_num | size | pct_used | empty | unsorted | stats_off | tbl_rows | skew_sortkey1 | skew_rows
----------+--------+----------+----------+---------+-----------+----------+-------------+--------------+-------------+------+----------+-------+----------+-----------+----------+---------------+-----------
 mydb     | public |   139204 | customer | Y       | EVEN      |          |          25 |              |           0 |  188 |   0.0492 |     0 |          |      0.00 |  3000000 |               |
(1 row)
  • スライス毎の行数を確認する
mydb=# select * from stv_tbl_perm where name = 'customer' order by slice;
 slice |   id   |                                   name                                   |  rows   | sorted_rows | temp | db_id  | insert_pristine | delete_pristine | backup
-------+--------+--------------------------------------------------------------------------+---------+-------------+------+--------+-----------------+-----------------+--------
     0 | 139204 | customer                                                                 | 1500000 |           0 |    0 | 100153 |               0 |               1 |      1
     1 | 139204 | customer                                                                 | 1500000 |           0 |    0 | 100153 |               0 |               1 |      1
  6411 | 139204 | customer                                                                 |       0 |           0 |    0 | 100153 |               3 |               0 |      1
(3 rows)
  • スライス毎のブロック数を確認する
mydb=# select name, slice, count(*) as mb  from svv_diskusage
where name = 'customer'
group by name, slice;
                                   name                                   | slice | mb
--------------------------------------------------------------------------+-------+----
 customer                                                                 |     1 | 94
 customer                                                                 |     0 | 94
(2 rows)

ANALYZE後

  • ANALYZE する
mydb=# set analyze_threshold_percent to 0;
SET
mydb=# analyze customer all columns;
ANALYZE
  • テーブル件数を確認する
mydb=# select count(*) from customer;
 count
-------
     0
(1 row)

mydb=# select * from SVV_TABLE_INFO where "table" = 'customer';
 database | schema | table_id |  table   | encoded | diststyle | sortkey1 | max_varchar | sortkey1_enc | sortkey_num | size | pct_used | empty | unsorted | stats_off | tbl_rows | skew_sortkey1 | skew_rows
----------+--------+----------+----------+---------+-----------+----------+-------------+--------------+-------------+------+----------+-------+----------+-----------+----------+---------------+-----------
 mydb     | public |   139204 | customer | Y       | EVEN      |          |          25 |              |           0 |  188 |   0.0492 |     0 |          |      0.00 |  3000000 |               |
(1 row)
  • スライス毎の行数を確認する
mydb=# select name, slice, sum(rows) from stv_tbl_perm
where name = 'customer'
group by name, slice;
                                   name                                   | slice |   sum
--------------------------------------------------------------------------+-------+---------
 customer                                                                 |  6411 |       0
 customer                                                                 |     0 | 1500000
 customer                                                                 |     1 | 1500000
(3 rows)
  • スライス毎のブロック数を確認する
mydb=# select name, slice, count(*) as mb  from svv_diskusage
where name = 'customer'
group by name, slice;
                                   name                                   | slice | mb
--------------------------------------------------------------------------+-------+----
 customer                                                                 |     1 | 94
 customer                                                                 |     0 | 94
(2 rows)

VACUUM後

  • VACUUM する
mydb=# vacuum full customer;
VACUUM
  • テーブル件数を確認する
mydb=# select count(*) from customer;
 count
-------
     0
(1 row)

mydb=# select * from SVV_TABLE_INFO where "table" = 'customer';
 database | schema | table_id |  table   | encoded | diststyle | sortkey1 | max_varchar | sortkey1_enc | sortkey_num | size | pct_used | empty | unsorted | stats_off | tbl_rows | skew_sortkey1 | skew_rows
----------+--------+----------+----------+---------+-----------+----------+-------------+--------------+-------------+------+----------+-------+----------+-----------+----------+---------------+-----------
 mydb     | public |   139204 | customer | Y       | EVEN      |          |          25 |              |           0 |   22 |   0.0057 |     0 |          |           |        0 |               |
(1 row)
  • スライス毎の行数を確認する
mydb=# select * from stv_tbl_perm where name = 'customer' order by slice;
 slice |   id   |                                   name                                   | rows | sorted_rows | temp | db_id  | insert_pristine | delete_pristine | backup
-------+--------+--------------------------------------------------------------------------+------+-------------+------+--------+-----------------+-----------------+--------
     0 | 139204 | customer                                                                 |    0 |           0 |    0 | 100153 |               0 |               1 |      1
     1 | 139204 | customer                                                                 |    0 |           0 |    0 | 100153 |               0 |               1 |      1
  6411 | 139204 | customer                                                                 |    0 |           0 |    0 | 100153 |               3 |               0 |      1
(3 rows)
  • スライス毎のブロック数を確認する
mydb=# select name, slice, count(*) as mb  from svv_diskusage
where name = 'customer'
group by name, slice;
                                   name                                   | slice | mb
--------------------------------------------------------------------------+-------+----
 customer                                                                 |     1 | 11
 customer                                                                 |     0 | 11
(2 rows)

参考

SVV_TABLE_INFO

データベーステーブルに関する概要情報を表示します。ビューではシステムテーブルが絞り込まれ、ユーザー定義テーブルのみが表示されます。

SVV_TABLE_INFO ビューを使用して、クエリのパフォーマンスに影響する可能性のあるテーブル設計の問題を診断し、対応できます。これには、圧縮エンコード分散キー、ソートスタイル、データ分散スキュー、テーブルサイズ、および統計情報が含まれます。SVV_TABLE_INFO ビューは、空のテーブルの情報を返しません。

SVV_TABLE_INFO ビューには、STV_BLOCKLIST、STV_PARTITIONS、STV_TBL_PERM、および STV_SLICES システムテーブルと、PG_DATABASE、PG_ATTRIBUTE、PG_CLASS、PG_NAMESPACE、および PG_TYPE カタログテーブルからの概要情報が表示されます。

テーブルの列

列名データ型説明
tbl_rowsnumeric(38,0)テーブル内の合計行数。この値には、削除対象としてマークされ、まだバキューム処理されていない列が含まれます。
SVV_TABLE_INFO - Amazon Redshift

STV_TBL_PERM

STV_TBL_PERM テーブルには、現在のセッション用にユーザーが作成した一時テーブルを含め、Amazon Redshift の永続テーブルに関する情報が表示されます。STV_TBL_PERM には、すべてのデータベース内のすべてのテーブルに関する情報が含まれます。

このテーブルは、クエリの処理中にシステムが作成する一時的なデータベーステーブルの情報を表示する STV_TBL_TRANS とは異なります。

テーブルの列

列名データ型説明
sliceintegerテーブルに割り当てられたノードスライス。
namecharacter(72)テーブル名。
rowsbigintスライス内のデータ行数。
sorted_rowsbigintディスク上でソート済みの、スライス内の行数。この数が ROWS の数と異なる場合は、テーブルに vacuum を実行して行をソートし直してください。
STV_TBL_PERM - Amazon Redshift

SVV_DISKUSAGE

Amazon Redshift は STV_TBL_PERM テーブルと STV_BLOCKLIST テーブルを結合して、SVV_DISKUSAGE システムビューを作成します。SVV_DISKUSAGE ビューにはデータベーステーブルに対するデータ割り当てに関する情報が含まれます。

次の例で示されているように集計クエリを SVV_DISKUSAGE と一緒に使用すると、データベースあたり、テーブルあたり、スライスあたり、列あたりに割り当てられたディスクブロックの数が算出されます。各データブロックのサイズは 1 MB です。または STV_PARTITIONS を使用して、ディスク利用に関する概要を見ることができます。

テーブルの列

列名データ型説明
db_idintegerデータベース ID。
namecharacter(72)テーブル名。
sliceintegerテーブルに割り当てられたデータスライス。

SVV_DISKUSAGE には割り当て済みディスクブロックにつき 1 つの行が含まれるため、すべての行を選択するクエリを実行すると非常に多数の行が返される可能性があります。SVV_DISKUSAGE を使用した集計クエリのみを使用することをお勧めします。

SVV_DISKUSAGE - Amazon Redshift

関連

Redshift のテーブル一覧とサイズを確認する

Amazon Redshift のテーブル一覧とサイズを AWS Labs の table_info.sql で確認する。

% git clone https://github.com/awslabs/amazon-redshift-utils.git
% cd src/AdminScripts
% psql "host=rs-spectrum01.******.us-east-1.redshift.amazonaws.com user=awsuser dbname=mydb port=5439"
Password:
psql (9.6.2, server 8.0.2)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: on)
Type "help" for help.

mydb=# \i table_info.sql
 schema |  table   | tableid | distkey |  skew  | sortkey | #sks |  rows   | mbytes | enc |        pct_enc        | pct_of_total | pct_stats_off | pct_unsorted
--------+----------+---------+---------+--------+---------+------+---------+--------+-----+-----------------------+--------------+---------------+--------------
 public | customer |  139204 | EVEN    | 1.0000 |         |    0 | 3000000 |    188 | Y   | 52.941176470588235200 |         0.04 |          0.00 |
 public | dwdate   |  139206 | EVEN    | 1.0000 |         |    0 |    2556 |     40 | Y   | 69.230769230769230700 |         0.01 |          0.00 |
(2 rows)

mydb=# select name, count(*) as MB  from svv_diskusage
group by name
order by name;

                                   name                                   |  mb
--------------------------------------------------------------------------+-------
 customer                                                                 |   188
 dwdate                                                                   |    40
 lineorder                                                                | 34280
 part2                                                                    |    74
(4 rows)

mydb=# select name, slice, count(*) as MB  from svv_diskusage
group by name, slice
order by name, slice;

                                   name                                   | slice |  mb
--------------------------------------------------------------------------+-------+-------
 customer                                                                 |     0 |    94
 customer                                                                 |     1 |    94
 dwdate                                                                   |     0 |    20
 dwdate                                                                   |     1 |    20
 lineorder                                                                |     0 | 17139
 lineorder                                                                |     1 | 17141
 part2                                                                    |     0 |    37
 part2                                                                    |     1 |    37
(8 rows)

参考

2017-12-04

DMS Full Load 検証メモ

AWS Database Migration Service (DMS) の Full Load (Oracle Database -> MySQL) の検証メモ。

  • Oracle Database(ソース) の TIMESTAMP WITH TIME ZONE 型の列は DMS で MySQL(ターゲット) にロードすると varchar(37) 型の列に変換される*1
    • ソースが TIMESTAMP(3) ... でも TIMESTAMP(6) ... でも精度に関係なく、ターゲットでは9桁(ナノ秒)の varchar(37) 列が作成される。
    • ソースの TIMESTAMP WITH TIME ZONE が9桁未満の場合は末尾が0埋めされてロードされる。
      • 例)2017-12-04 08:05:19.112684000 +09:00
  • ターゲットの MySQL に予めテーブルchar(36) で作成しておいて TRUNCATE モードの場合も、TIMESTAMP(3) ... でも TIMESTAMP(6) ... でも精度に関係なく9桁(ナノ秒)まで末尾を0埋めしてロードされる。

ケース1

ソースの Oracle Database にテーブルを作ってレコードを挿入する
sqlplus awsuser/passowrd@orcl.******.ap-northeast-1.rds.amazonaws.com:1521/ORCL
alter session set NLS_TIMESTAMP_TZ_FORMAT='YYYY-MM-DD HH24:MI:SS.FF TZR';
create table timestamp_tz_test1 (
	col1 timestamp(6) with time zone,
	col2 timestamp(6) with time zone
);
insert into timestamp_tz_test1 (col1, col2) values(current_timestamp, current_timestamp);
insert into timestamp_tz_test1 (col1, col2) values(current_timestamp, current_timestamp);
insert into timestamp_tz_test1 (col1, col2) values(current_timestamp, current_timestamp);
commit;
set linesize 200
col COL1 for a36
col COL2 for a36
select * from timestamp_tz_test1;

COL1				     COL2
------------------------------------ ------------------------------------
2017-12-04 08:05:19.112684 +09:00    2017-12-04 08:05:19.112684 +09:00
2017-12-04 08:05:19.130025 +09:00    2017-12-04 08:05:19.130025 +09:00
2017-12-04 08:05:19.152363 +09:00    2017-12-04 08:05:19.152363 +09:00
ターゲットの Aurora(MySQL互換) にテーブルを作成する
mysql -h aurora01.******.ap-northeast-1.rds.amazonaws.com -u awsuser -p
use mydb;
create table `TIMESTAMP_TZ_TEST1` (
	col1 char(36),
	col2 char(33)
);
DMS のタスクで Full Load する。
Task nameAn identifier for your Task	timestamp-tz-test1
Task ARNThis ARN is the stable way uniquely identify your Replication Task when calling DMS APIs	arn:aws:dms:ap-northeast-1:******
StatusThe current computed status of the task. Note that this is a computed value and may not match the raw status from the service API	starting
Migration typeHow should this task migrate data	Full Load
Replication instanceReplication instance	replication-instance-1
Source endpointSource endpoint	prodendpoint
Target endpointTarget endpoint	testendpoint
Mapping methodThis is a json document that details how source tables are mapped to the target	{
	"rules": [
		{
			"rule-type": "selection",
			"rule-id": "1",
			"rule-name": "1",
			"object-locator": {
				"schema-name": "AWSUSER",
				"table-name": "TIMESTAMP_TZ_TEST1"
			},
			"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"
		}
	]
}
Task settingsThe task settings JSON allows you to apply custom settings to you task.	{"TargetMetadata":{"TargetSchema":"","SupportLobs":false,"FullLobMode":false,"LobChunkSize":64,"LimitedSizeLobMode":true,"LobMaxSize":32,"LoadMaxFileSize":0,"ParallelLoadThreads":0,"ParallelLoadBufferSize":0,"BatchApplyEnabled":false},"FullLoadSettings":{"TargetTablePrepMode":"TRUNCATE_BEFORE_LOAD","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-*******"},"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},"ValidationSettings":{"EnableValidation":true,"ValidationMode":"ROW_LEVEL","ThreadCount":5}}
ターゲットの Aurora(MySQL互換) を確認する
select * from TIMESTAMP_TZ_TEST1;
+--------------------------------------+-----------------------------------+
| col1                                 | col2                              |
+--------------------------------------+-----------------------------------+
| 2017-12-04 08:05:19.112684000 +09:00 | 2017-12-04 08:05:19.112684000 +09★ | 桁数を減らすと Time Zone の表記が欠ける
| 2017-12-04 08:05:19.130025000 +09:00 | 2017-12-04 08:05:19.130025000 +09 |
| 2017-12-04 08:05:19.152363000 +09:00 | 2017-12-04 08:05:19.152363000 +09 |
+--------------------------------------+-----------------------------------+
3 rows in set (0.01 sec)
pager less -S
select * from mysql.general_log where user_host like '%awsuser%' and event_time > date_add(now(), interval -1 day); 

...

| 2017-12-03 23:11:55 | awsuser[awsuser] @  [172.30.0.38] |      1852 | 1880231669 | Query        | TRUNCATE TABLE `mydb`.`TIMESTAMP_TZ_TEST1` ★ truncate
| 2017-12-03 23:11:55 | awsuser[awsuser] @  [172.30.0.38] |      1852 | 1880231669 | Query        | set @@sql_select_limit=1
| 2017-12-03 23:11:55 | awsuser[awsuser] @  [172.30.0.38] |      1852 | 1880231669 | Query        | SELECT COUNT(*) FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'mydb'
| 2017-12-03 23:11:55 | awsuser[awsuser] @  [172.30.0.38] |      1852 | 1880231669 | Query        | set @@sql_select_limit=DEFAULT
| 2017-12-03 23:11:55 | awsuser[awsuser] @  [172.30.0.38] |      1852 | 1880231669 | Query        | SELECT COUNT(*) FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'mydb'
| 2017-12-03 23:11:55 | awsuser[awsuser] @  [172.30.0.38] |      1852 | 1880231669 | Query        | set @@sql_select_limit=1
| 2017-12-03 23:11:55 | awsuser[awsuser] @  [172.30.0.38] |      1852 | 1880231669 | Query        | SELECT count(*) FROM information_schema.tables WHERE table_schema='mydb' and table_name='TIMESTAMP_TZ_TES
| 2017-12-03 23:11:55 | awsuser[awsuser] @  [172.30.0.38] |      1852 | 1880231669 | Query        | set @@sql_select_limit=DEFAULT
| 2017-12-03 23:11:55 | awsuser[awsuser] @  [172.30.0.38] |      1852 | 1880231669 | Query        | SELECT count(*) FROM information_schema.tables WHERE table_schema='mydb' and table_name='TIMESTAMP_TZ_TES
| 2017-12-03 23:11:55 | awsuser[awsuser] @  [172.30.0.38] |      1856 | 1880231669 | Query        | SELECT * FROM `mydb`.`TIMESTAMP_TZ_TEST1` WHERE 1=0
| 2017-12-03 23:11:55 | awsuser[awsuser] @  [172.30.0.38] |      1856 | 1880231669 | Query        | set @@sql_select_limit=1
| 2017-12-03 23:11:55 | awsuser[awsuser] @  [172.30.0.38] |      1856 | 1880231669 | Query        | SHOW FULL COLUMNS FROM `mydb`.`TIMESTAMP_TZ_TEST1` where `Field` = "col1"
| 2017-12-03 23:11:55 | awsuser[awsuser] @  [172.30.0.38] |      1856 | 1880231669 | Query        | set @@sql_select_limit=DEFAULT
| 2017-12-03 23:11:55 | awsuser[awsuser] @  [172.30.0.38] |      1856 | 1880231669 | Query        | SHOW FULL COLUMNS FROM `mydb`.`TIMESTAMP_TZ_TEST1` where `Field` = "col1"
| 2017-12-03 23:11:55 | awsuser[awsuser] @  [172.30.0.38] |      1856 | 1880231669 | Query        | set @@sql_select_limit=1
| 2017-12-03 23:11:55 | awsuser[awsuser] @  [172.30.0.38] |      1856 | 1880231669 | Query        | SHOW FULL COLUMNS FROM `mydb`.`TIMESTAMP_TZ_TEST1` where `Field` = "col2"
| 2017-12-03 23:11:55 | awsuser[awsuser] @  [172.30.0.38] |      1856 | 1880231669 | Query        | set @@sql_select_limit=DEFAULT
| 2017-12-03 23:11:55 | awsuser[awsuser] @  [172.30.0.38] |      1856 | 1880231669 | Query        | SHOW FULL COLUMNS FROM `mydb`.`TIMESTAMP_TZ_TEST1` where `Field` = "col2"
| 2017-12-03 23:11:55 | awsuser[awsuser] @  [172.30.0.38] |      1856 | 1880231669 | Query        | SHOW KEYS FROM `mydb`.`TIMESTAMP_TZ_TEST1`
| 2017-12-03 23:11:55 | awsuser[awsuser] @  [172.30.0.38] |      1856 | 1880231669 | Query        | SHOW KEYS FROM `mydb`.`TIMESTAMP_TZ_TEST1`
| 2017-12-03 23:11:55 | awsuser[awsuser] @  [172.30.0.38] |      1856 | 1880231669 | Query        | SHOW KEYS FROM `mydb`.`TIMESTAMP_TZ_TEST1`
| 2017-12-03 23:11:55 | awsuser[awsuser] @  [172.30.0.38] |      1852 | 1880231669 | Query        | SET AUTOCOMMIT=0
| 2017-12-03 23:11:55 | awsuser[awsuser] @  [172.30.0.38] |      1852 | 1880231669 | Query        | load data local infile "/rdsdbdata/data/tasks/RFI7ZS7TYDMJIWYHLMY5AUXEV4/data_files/1/LOAD00000001.csv" i ★データロード
| 2017-12-03 23:11:55 | awsuser[awsuser] @  [172.30.0.38] |      1852 | 1880231669 | Query        | COMMIT
| 2017-12-03 23:11:55 | awsuser[awsuser] @  [172.30.0.38] |      1852 | 1880231669 | Query        | SET AUTOCOMMIT=1

ケース2

ソースの Oracle Database にテーブルを作ってレコードを挿入する
sqlplus awsuser/password@orcl.******.ap-northeast-1.rds.amazonaws.com:1521/ORCL
alter session set NLS_TIMESTAMP_TZ_FORMAT='YYYY-MM-DD HH24:MI:SS.FF TZR';
create table timestamp_tz_test2 (
	col1 timestamp(3) with time zone,
	col2 timestamp(6) with time zone,
	col3 timestamp(9) with time zone
);
insert into timestamp_tz_test2 (col1, col2, col3) values(current_timestamp, current_timestamp, current_timestamp);
insert into timestamp_tz_test2 (col1, col2, col3) values(current_timestamp, current_timestamp, current_timestamp);
insert into timestamp_tz_test2 (col1, col2, col3) values(current_timestamp, current_timestamp, current_timestamp);
commit;
set linesize 200
col col1 for a36
col col2 for a36
col col3 for a36
select * from timestamp_tz_test2;
COL1				     COL2				  COL3
------------------------------------ ------------------------------------ ------------------------------------
2017-12-04 09:15:16.378 +09:00	     2017-12-04 09:15:16.377774 +09:00	  2017-12-04 09:15:16.377774000 +09:00
2017-12-04 09:15:16.394 +09:00	     2017-12-04 09:15:16.393758 +09:00	  2017-12-04 09:15:16.393758000 +09:00
2017-12-04 09:15:16.406 +09:00	     2017-12-04 09:15:16.405861 +09:00	  2017-12-04 09:15:16.405861000 +09:00
DMS で Full Load
Task nameAn identifier for your Task	timestamp-tz-test2
Task ARNThis ARN is the stable way uniquely identify your Replication Task when calling DMS APIs	arn:aws:dms:ap-northeast-1:******
StatusThe current computed status of the task. Note that this is a computed value and may not match the raw status from the service API	stopped
Migration typeHow should this task migrate data	Full Load
Replication instanceReplication instance	replication-instance-1
Source endpointSource endpoint	prodendpoint
Target endpointTarget endpoint	testendpoint
Mapping methodThis is a json document that details how source tables are mapped to the target
{
	"rules": [
		{
			"rule-type": "selection",
			"rule-id": "1",
			"rule-name": "1",
			"object-locator": {
				"schema-name": "AWSUSER",
				"table-name": "TIMESTAMP_TZ_TEST2"
			},
			"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"
		}
	]
}
Task settingsThe task settings JSON allows you to apply custom settings to you task.	{"TargetMetadata":{"TargetSchema":"","SupportLobs":false,"FullLobMode":false,"LobChunkSize":64,"LimitedSizeLobMode":true,"LobMaxSize":32,"LoadMaxFileSize":0,"ParallelLoadThreads":0,"ParallelLoadBufferSize":0,"BatchApplyEnabled":false},"FullLoadSettings":{"TargetTablePrepMode":"DROP_AND_CREATE","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-BQG6YVNJVPXA3VIRCANSZXKNP4"},"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},"ValidationSettings":{"EnableValidation":true,"ValidationMode":"ROW_LEVEL","ThreadCount":5}}
ターゲットの Aurora(MySQL互換) を確認する
mysql -h aurora01.******.ap-northeast-1.rds.amazonaws.com -u awsuser -p
desc TIMESTAMP_TZ_TEST2;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| COL1  | varchar(37)★ | YES  |     | NULL    |       | DMS にテーブル作成も任せるとソースの Oracle Database の timestamp(39) with time zone は MySQL(Aurora) では全て varchar(37) の列として作成される
| COL2  | varchar(37) | YES  |     | NULL    |       |
| COL3  | varchar(37) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
select * from TIMESTAMP_TZ_TEST2;
+--------------------------------------+--------------------------------------+--------------------------------------+
| COL1                                 | COL2                                 | COL3                                 |
+--------------------------------------+--------------------------------------+--------------------------------------+
| 2017-12-04 09:15:16.378000000 +09:00 | 2017-12-04 09:15:16.377774000 +09:00 | 2017-12-04 09:15:16.377774000 +09:00 |
| 2017-12-04 09:15:16.394000000 +09:00 | 2017-12-04 09:15:16.393758000 +09:00 | 2017-12-04 09:15:16.393758000 +09:00 |
| 2017-12-04 09:15:16.406000000 +09:00 | 2017-12-04 09:15:16.405861000 +09:00 | 2017-12-04 09:15:16.405861000 +09:00 |
+--------------------------------------+--------------------------------------+--------------------------------------+
3 rows in set (0.02 sec)
★データは9桁(ナノ秒)で末尾が0埋めされる。

pager less -S
select * from mysql.general_log where user_host like '%awsuser%' and event_time > date_add(now(), interval -1 day); 

| 2017-12-04 00:18:09 | awsuser[awsuser] @  [172.30.0.38] |      1881 | 1880231669 | Query        | CREATE TABLE `mydb`.`TIMESTAMP_TZ_TEST2` (  `COL1` VARCHAR(37), `COL2` VARCHAR(37), `COL3` VARCHAR(37) ) ★ テーブル作成
| 2017-12-04 00:18:09 | awsuser[awsuser] @  [172.30.0.38] |      1887 | 1880231669 | Query        | SELECT * FROM `mydb`.`TIMESTAMP_TZ_TEST2` WHERE 1=0
| 2017-12-04 00:18:09 | awsuser[awsuser] @  [172.30.0.38] |      1887 | 1880231669 | Query        | set @@sql_select_limit=1
| 2017-12-04 00:18:09 | awsuser[awsuser] @  [172.30.0.38] |      1887 | 1880231669 | Query        | SHOW FULL COLUMNS FROM `mydb`.`TIMESTAMP_TZ_TEST2` where `Field` = "COL1"
| 2017-12-04 00:18:09 | awsuser[awsuser] @  [172.30.0.38] |      1887 | 1880231669 | Query        | set @@sql_select_limit=DEFAULT
| 2017-12-04 00:18:09 | awsuser[awsuser] @  [172.30.0.38] |      1887 | 1880231669 | Query        | SHOW FULL COLUMNS FROM `mydb`.`TIMESTAMP_TZ_TEST2` where `Field` = "COL1"
| 2017-12-04 00:18:09 | awsuser[awsuser] @  [172.30.0.38] |      1887 | 1880231669 | Query        | set @@sql_select_limit=1
| 2017-12-04 00:18:09 | awsuser[awsuser] @  [172.30.0.38] |      1887 | 1880231669 | Query        | SHOW FULL COLUMNS FROM `mydb`.`TIMESTAMP_TZ_TEST2` where `Field` = "COL2"
| 2017-12-04 00:18:09 | awsuser[awsuser] @  [172.30.0.38] |      1887 | 1880231669 | Query        | set @@sql_select_limit=DEFAULT
| 2017-12-04 00:18:09 | awsuser[awsuser] @  [172.30.0.38] |      1887 | 1880231669 | Query        | SHOW FULL COLUMNS FROM `mydb`.`TIMESTAMP_TZ_TEST2` where `Field` = "COL2"
| 2017-12-04 00:18:09 | awsuser[awsuser] @  [172.30.0.38] |      1887 | 1880231669 | Query        | set @@sql_select_limit=1
| 2017-12-04 00:18:09 | awsuser[awsuser] @  [172.30.0.38] |      1887 | 1880231669 | Query        | SHOW FULL COLUMNS FROM `mydb`.`TIMESTAMP_TZ_TEST2` where `Field` = "COL3"
| 2017-12-04 00:18:09 | awsuser[awsuser] @  [172.30.0.38] |      1887 | 1880231669 | Query        | set @@sql_select_limit=DEFAULT
| 2017-12-04 00:18:09 | awsuser[awsuser] @  [172.30.0.38] |      1887 | 1880231669 | Query        | SHOW FULL COLUMNS FROM `mydb`.`TIMESTAMP_TZ_TEST2` where `Field` = "COL3"
| 2017-12-04 00:18:09 | awsuser[awsuser] @  [172.30.0.38] |      1887 | 1880231669 | Query        | SHOW KEYS FROM `mydb`.`TIMESTAMP_TZ_TEST2`
| 2017-12-04 00:18:09 | awsuser[awsuser] @  [172.30.0.38] |      1887 | 1880231669 | Query        | SHOW KEYS FROM `mydb`.`TIMESTAMP_TZ_TEST2`
| 2017-12-04 00:18:09 | awsuser[awsuser] @  [172.30.0.38] |      1887 | 1880231669 | Query        | SHOW KEYS FROM `mydb`.`TIMESTAMP_TZ_TEST2`
| 2017-12-04 00:18:09 | awsuser[awsuser] @  [172.30.0.38] |      1881 | 1880231669 | Query        | SET AUTOCOMMIT=0
| 2017-12-04 00:18:09 | awsuser[awsuser] @  [172.30.0.38] |      1881 | 1880231669 | Query        | load data local infile "/rdsdbdata/data/tasks/BQG6YVNJVPXA3VIRCANSZXKNP4/data_files/1/LOAD00000001.csv" i ★データロード
| 2017-12-04 00:18:09 | awsuser[awsuser] @  [172.30.0.38] |      1881 | 1880231669 | Query        | COMMIT
| 2017-12-04 00:18:09 | awsuser[awsuser] @  [172.30.0.38] |      1881 | 1880231669 | Query        | SET AUTOCOMMIT=1

*1:create table も DMS に任せた場合

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)

手順

  • Oracle Database にテーブルを作成して、データを投入する。
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;
  • Aurora with MySQL Compatibility にテーブルを作成する。
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 モードに従ってカラムを処理します。

  • 厳密な SQL モードを有効にした場合、トランザクションテーブルに対してエラーが発生し、ステートメントがロールバックされます。非トランザクションテーブルではエラーが起きるが、これが複数行ステートメントの 2 行目以降の行に対するエラーの場合、先行する行が挿入されています。
  • 厳密モードが有効でない場合、MySQL はカラムデータ型の暗黙的なデフォルト値にカラムを設定します。

(中略)

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

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

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

  • 数値型のデフォルトは 0 です。ただし、例外として AUTO_INCREMENT 属性で宣言された整数型または浮動小数点型のデフォルトは、そのシーケンスの次の値になります。
  • TIMESTAMP 以外の日付と時間型のデフォルトには、「ゼロ」値が適切です。explicit_defaults_for_timestamp システム変数が有効な場合、これは TIMESTAMP にも当てはまります (セクション5.1.4「サーバーシステム変数」を参照してください)。それ以外の場合、テーブルの最初の TIMESTAMP カラムのデフォルト値は現在の日付と時間になります。セクション11.3「日付と時間型」を参照してください。
  • ENUM ではない文字列型のデフォルト値は空の文字列です。ENUM のデフォルトは、最初の列挙値です。
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 モード