Hatena::ブログ(Diary)

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

2018-08-08

クロスアカウントのS3バケット間コピーを行うとコピー実行ユーザーが所有者になる

検証したこと


手順

$ aws s3 cp test.txt s3://az-test-src/                                                                                                                                                                              
upload: ./test.txt to s3://az-test-src/test.txt
$ aws s3 cp s3://az-test-src/test.txt s3://az-test-dst/                                           
copy: s3://az-test-src/test.txt to s3://az-test-dst/test.txt
$ aws s3api get-object-acl --bucket az-test-src --key test.txt
{
    "Owner": {
        "DisplayName": "azekatsu", ★コピー元アカウント
        "ID": "...."
    },
    "Grants": [
        {
            "Grantee": {
                "Type": "CanonicalUser",
                "DisplayName": "azekatsu",
                "ID": "..."
            },
            "Permission": "FULL_CONTROL"
        }
    ]
}

https://docs.aws.amazon.com/ja_jp/cli/latest/reference/s3api/put-object-acl.html を見ても所有者の変更はできなさそう。

$ aws s3api put-object-acl --bucket az-test-dst --key test.txt --acl bucket-owner-full-control
$ aws s3api get-object-acl --bucket az-test-dst --key test.txt
{
    "Owner": {
        "DisplayName": "azekatsu",
        "ID": "..."
    },
    "Grants": [
        {
            "Grantee": {
                "Type": "CanonicalUser",
                "DisplayName": "azekatsu",
                "ID": "..."
            },
            "Permission": "FULL_CONTROL"
        },
        {
            "Grantee": {
                "Type": "CanonicalUser",
                "DisplayName": "yoheia", ★コピー先アカウント(バケット所有者)にフルコントロール権限が付与されている
                "ID": "..."
            },
            "Permission": "FULL_CONTROL"
        }
    ]
}

関連


参考

2018-07-31

PostgreSQL 10 の宣言的パーティションについて

特徴

  • CREATE TABLE 文でパーティショニングが構築可能に
  • ALTER TABLE 文で、子テーブルの追加/除去が可能
  • 階層的なパーティショニングも可能
  • 子テーブルに外部サーバのテーブル(FDW)を使用可能
  • INSERT の高速化
  • テーブル継承の機能を使用して実装されている

制約

参考

2018-07-30

sysbench で MySQL にカスタムクエリを同時多重実行して一時ファイルを大量に使ってみる

インストール

curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.rpm.sh | sudo bash
sudo yum -y install sysbench mysql

準備

  • 初期データロード
$ sysbench /usr/share/sysbench/oltp_read_write.lua \
 --db-driver=mysql \
 --table-size=100000 \
 --mysql-host=aurora01.*********.ap-northeast-1.rds.amazonaws.com \
 --mysql-user=awsuser \
 --mysql-password=********* \
 --mysql-db=mydb \
 --db-ps-mode=disable \
 prepare
function event(thread_id)
        db_query("select c.* from (select a.* from mydb.sbtest1 a cross join sbtest1 b) as c order by c.pad")
end

負荷をかける

  • 負荷をかける
$ sysbench /usr/share/sysbench/select_sort.lua \
 --db-driver=mysql \
 --mysql-db=mydb \
 --mysql-host=aurora01.*********.ap-northeast-1.rds.amazonaws.com \
 --mysql-user=awsuser \
 --mysql-password=********* \
 --time=300 \
 --db-ps-mode=disable \
 --threads=30 \
 run
  • 実行結果
sysbench 1.0.15 (using bundled LuaJIT 2.1.0-beta2)

Running the test with following options:
Number of threads: 30
Initializing random number generator from current time


Initializing worker threads...

Threads started!

FATAL: mysql_store_result() returned error 3 (Error writing file '/rdsdbdata/tmp/MYz8AJRr' (Errcode: 28 - No space left on device))
FATAL: `thread_run' function failed: /usr/share/sysbench/select_sort.lua:2: db_query() failed
FATAL: mysql_store_result() returned error 3 (Error writing file '/rdsdbdata/tmp/MY4EuUqw' (Errcode: 28 - No space left on device))
FATAL: `thread_run' function failed: /usr/share/sysbench/select_sort.lua:2: db_query() failed
(中略)
FATAL: mysql_store_result() returned error 3 (Error writing file '/rdsdbdata/tmp/MYoI30Js' (Errcode: 28 - No space left on device))
FATAL: `thread_run' function failed: /usr/share/sysbench/select_sort.lua:2: db_query() failed
FATAL: mysql_store_result() returned error 3 (Error writing file '/rdsdbdata/tmp/MYgWJgNX' (Errcode: 28 - No space left on device))

性能統計情報を確認する

  • performance_schema.events_statements_current を確認する
mysql> select thread_id, sql_text, sort_range, sort_rows, sort_scan,created_tmp_disk_tables, created_tmp_tables from performance_schema.events_statements_current where sql_text = 'select c.* from (select a.* from mydb.sbtest1 a cross join sbtest1 b) as c order by c.pad';
+-----------+-------------------------------------------------------------------------------------------+------------+-----------+-----------+-------------------------+--------------------+
| thread_id | sql_text                                                                                  | sort_range | sort_rows | sort_scan | created_tmp_disk_tables | created_tmp_tables |
+-----------+-------------------------------------------------------------------------------------------+------------+-----------+-----------+-------------------------+--------------------+
|       533 | select c.* from (select a.* from mydb.sbtest1 a cross join sbtest1 b) as c order by c.pad |          0 |         0 |         0 |                       1 |                  1 |
|       534 | select c.* from (select a.* from mydb.sbtest1 a cross join sbtest1 b) as c order by c.pad |          0 |         0 |         0 |                       1 |                  1 |
|       535 | select c.* from (select a.* from mydb.sbtest1 a cross join sbtest1 b) as c order by c.pad |          0 |         0 |         0 |                       1 |                  1 |
|       536 | select c.* from (select a.* from mydb.sbtest1 a cross join sbtest1 b) as c order by c.pad |          0 |         0 |         0 |                       1 |                  1 |
|       537 | select c.* from (select a.* from mydb.sbtest1 a cross join sbtest1 b) as c order by c.pad |          0 |         0 |         0 |                       1 |                  1 |
|       538 | select c.* from (select a.* from mydb.sbtest1 a cross join sbtest1 b) as c order by c.pad |          0 |         0 |         0 |                       1 |                  1 |
|       539 | select c.* from (select a.* from mydb.sbtest1 a cross join sbtest1 b) as c order by c.pad |          0 |         0 |         0 |                       1 |                  1 |
|       540 | select c.* from (select a.* from mydb.sbtest1 a cross join sbtest1 b) as c order by c.pad |          0 |         0 |         0 |                       1 |                  1 |
|       541 | select c.* from (select a.* from mydb.sbtest1 a cross join sbtest1 b) as c order by c.pad |          0 |         0 |         0 |                       1 |                  1 |
|       542 | select c.* from (select a.* from mydb.sbtest1 a cross join sbtest1 b) as c order by c.pad |          0 |         0 |         0 |                       1 |                  1 |
|       543 | select c.* from (select a.* from mydb.sbtest1 a cross join sbtest1 b) as c order by c.pad |          0 |         0 |         0 |                       1 |                  1 |
|       544 | select c.* from (select a.* from mydb.sbtest1 a cross join sbtest1 b) as c order by c.pad |          0 |         0 |         0 |                       1 |                  1 |
|       545 | select c.* from (select a.* from mydb.sbtest1 a cross join sbtest1 b) as c order by c.pad |          0 |         0 |         0 |                       1 |                  1 |
|       546 | select c.* from (select a.* from mydb.sbtest1 a cross join sbtest1 b) as c order by c.pad |          0 |         0 |         0 |                       1 |                  1 |
|       547 | select c.* from (select a.* from mydb.sbtest1 a cross join sbtest1 b) as c order by c.pad |          0 |         0 |         0 |                       1 |                  1 |
|       548 | select c.* from (select a.* from mydb.sbtest1 a cross join sbtest1 b) as c order by c.pad |          0 |         0 |         0 |                       1 |                  1 |
|       549 | select c.* from (select a.* from mydb.sbtest1 a cross join sbtest1 b) as c order by c.pad |          0 |         0 |         0 |                       1 |                  1 |
|       550 | select c.* from (select a.* from mydb.sbtest1 a cross join sbtest1 b) as c order by c.pad |          0 |         0 |         0 |                       1 |                  1 |
|       551 | select c.* from (select a.* from mydb.sbtest1 a cross join sbtest1 b) as c order by c.pad |          0 |         0 |         0 |                       1 |                  1 |
|       552 | select c.* from (select a.* from mydb.sbtest1 a cross join sbtest1 b) as c order by c.pad |          0 |         0 |         0 |                       1 |                  1 |
|       553 | select c.* from (select a.* from mydb.sbtest1 a cross join sbtest1 b) as c order by c.pad |          0 |         0 |         0 |                       1 |                  1 |
|       554 | select c.* from (select a.* from mydb.sbtest1 a cross join sbtest1 b) as c order by c.pad |          0 |         0 |         0 |                       1 |                  1 |
|       555 | select c.* from (select a.* from mydb.sbtest1 a cross join sbtest1 b) as c order by c.pad |          0 |         0 |         0 |                       1 |                  1 |
|       556 | select c.* from (select a.* from mydb.sbtest1 a cross join sbtest1 b) as c order by c.pad |          0 |         0 |         0 |                       1 |                  1 |
|       557 | select c.* from (select a.* from mydb.sbtest1 a cross join sbtest1 b) as c order by c.pad |          0 |         0 |         0 |                       1 |                  1 |
|       558 | select c.* from (select a.* from mydb.sbtest1 a cross join sbtest1 b) as c order by c.pad |          0 |         0 |         0 |                       1 |                  1 |
|       559 | select c.* from (select a.* from mydb.sbtest1 a cross join sbtest1 b) as c order by c.pad |          0 |         0 |         0 |                       1 |                  1 |
|       560 | select c.* from (select a.* from mydb.sbtest1 a cross join sbtest1 b) as c order by c.pad |          0 |         0 |         0 |                       1 |                  1 |
|       561 | select c.* from (select a.* from mydb.sbtest1 a cross join sbtest1 b) as c order by c.pad |          0 |         0 |         0 |                       1 |                  1 |
|       562 | select c.* from (select a.* from mydb.sbtest1 a cross join sbtest1 b) as c order by c.pad |          0 |         0 |         0 |                       1 |                  1 |
+-----------+-------------------------------------------------------------------------------------------+------------+-----------+-----------+-------------------------+--------------------+
30 rows in set (0.42 sec)

参考

MySQLトラブルシューティング

MySQLトラブルシューティング

2018-07-21

Amazon Redshift でロックを保持しているセッションとロック待ちしているセッションを確認する方法

Redshift でロックを保持しているセッションとロック待ちしているセッションlock_wait.sql で確認すると便利。マネジメントコンソールで Redshift の「クエリ」タブではロックを保持しているクエリは表示されるがロック待ちしているクエリは表示されない。


準備

  • テーブルを作成してレコードを insert する。
CREATE TABLE testtab1(id int primary key, name varchar(32));
INSERT INTO testtab1 (id, name) VALUES(1, 'foo');
INSERT INTO testtab1 (id, name) VALUES(2, 'bar');

検証

BEGIN;
UPDATE testtab1 SET name='fff' WHERE id=1;
UPDATE testtab1 SET name='bbb' WHERE id=2;

確認

  • ロックを保持しているセッションと待たされているセッションを確認する。
    • pid: 75517 は granted が True なのでロックを保持していて、pid:79568 は False なので待たされている。
    • 一行目の pid: 75517 は num_blocking から1セッションを待たせていて、pidlist から pid:79568 を待たされていることが分かる。
    • 三行目の pid:79568 は waiting から 2239秒(37分)待たされていることが分かる。
# \i lock_wait.sql
  xid   |  pid  | username | dbname | relation | schemaname | objectname |         mode          | granted | obj_type |         txn_start          | block_sec | block_min | block_hr | waiting | max_sec_blocking | num_blocking | pidlist
--------+-------+----------+--------+----------+------------+------------+-----------------------+---------+----------+----------------------------+-----------+-----------+----------+---------+------------------+--------------+---------
 863579 | 75517 | awsuser  | mydb   |   170038 | public     | testtab1   | ShareRowExclusiveLock | True    | relation | 2018-07-21 14:17:07.367098 |   2307.24 |     38.45 |     0.64 |         |          2239.24 |            1 | 79568★待たされているセッションの pid
 863579 | 75517 | awsuser  | mydb   |   170038 | public     | testtab1   | AccessShareLock       | True    | relation | 2018-07-21 14:17:07.367098 |   2307.24 |     38.45 |     0.64 |         |                  |              |
 863608 | 79568 | awsuser  | mydb   |   170038 | public     | testtab1   | ShareRowExclusiveLock | False   | relation | 2018-07-21 14:18:11.388639 |           |           |          | 2239.24★待たされている秒数 |                  |              |
(3 rows)
/**********************************************************************************************
Purpose: Return instances of table filter for all or a given table in the past 7 days
Columns:
pid: 				Process/Session Id
username:			User name
dbname:				Database
relation:			Object id
schemaname: 		Schema
objectname:			Object Name
mode:				Lock Mode (AcessShareLock, AccessExclusiveLock, etc)
granted:			Granted (True or False)
obj_type:			Type of Object
txn_start:			Start Time of the transaction that asked for the lock
block_sec:			Seconds Holding the Lock
block_min:  		Minutes Holding the lock
block_hr: 			Hours Holding the lock
waiting:			Seconds waiting for the lock	
max_sec_blocking:	Peak of seconds blocking other sessions
num_blocking:		Number of sessions blocked by this lock
pidlist:			List of Sessions being blocked by this lock

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


参考

2018-07-19

macOS で AWS CLI をアップデートする

  • 現在のバージョンを確認する
% aws --version
aws-cli/1.11.74 Python/2.7.10 Darwin/16.7.0 botocore/1.5.37
% sudo pip install --upgrade awscli
(中略)
Successfully installed PyYAML-3.13 awscli-1.15.61 botocore-1.10.60 colorama-0.3.9 docutils-0.14 futures-3.2.0 jmespath-0.9.3 pyasn1-0.4.3 python-dateutil-2.7.3 rsa-3.4.2 s3transfer-0.1.13 six-1.11.0

参考