Hatena::ブログ(Diary)

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

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-17

Amazon Redshift で awslabs の amazon-redshift-utils/AdminViews を一括作成する

awslabs/amazon-redshift-utils の AdminViews を一括作成する手順をメモ。


インストール

$ sudo yum -y install git
$ sudo yum -y install postgresql

GitHub からスクリプトを入手する

$ git clone https://github.com/awslabs/amazon-redshift-utils.git

ビューを作成する

$ cd amazon-redshift-utils/src/AdminViews
$ ls -tr v_*.sql|perl -nle 'print qq/\\i $_/' > create_all_views.sql
  • Redshift にスーパーユーザーで接続する
$ psql "host=ds28xl4n.********.us-west-2.redshift.amazonaws.com user=awsuser dbname=mydb port=5439"
# create schema admin authorization awsuser;
  • ビューを一括作成する
# \i create_all_views.sql

2018-07-16

Amazon Redshift に重いクエリを同時多重実行しながら性能情報を見る

Redshift に負荷をかける

EC2gitpsql、pgbench をインストールする
$ sudo yum -y install postgresql96
$ sudo yum -y install postgresql96-contrib
$ sudo yum -y install git
Redshift で負荷かけの準備を行う
  • EC2 から Redshift にスーパーユーザーで接続する
$ psql "host=ds28xl4n.******.us-west-2.redshift.amazonaws.com user=awsuser dbname=mydb port=5439"
  • ベンチマーク用のグループとユーザーを作成する
    • キューの同時実行数を超えると情報取得クエリもキューで待たされるため、負荷かけ実行ユーザーを別に作成してキューを分ける
# create group bench;
# create user benchuser password '********' in group bench;
# ALTER USER benchuser SET enable_result_cache_for_session = off;
# \q
$ psql "host=ds28xl4n.******.us-west-2.redshift.amazonaws.com user=benchuser dbname=mydb port=5439"
  • テーブルを作成する
# CREATE TABLE lineorder 
(
  lo_orderkey          INTEGER NOT NULL,
  lo_linenumber        INTEGER NOT NULL,
  lo_custkey           INTEGER NOT NULL,
  lo_partkey           INTEGER NOT NULL,
  lo_suppkey           INTEGER NOT NULL,
  lo_orderdate         INTEGER NOT NULL,
  lo_orderpriority     VARCHAR(15) NOT NULL,
  lo_shippriority      VARCHAR(1) NOT NULL,
  lo_quantity          INTEGER NOT NULL,
  lo_extendedprice     INTEGER NOT NULL,
  lo_ordertotalprice   INTEGER NOT NULL,
  lo_discount          INTEGER NOT NULL,
  lo_revenue           INTEGER NOT NULL,
  lo_supplycost        INTEGER NOT NULL,
  lo_tax               INTEGER NOT NULL,
  lo_commitdate        INTEGER NOT NULL,
  lo_shipmode          VARCHAR(10) NOT NULL
);
  • データをロードする
# copy lineorder from 's3://awssampledbuswest2/ssbgz/lineorder' 
credentials 'aws_access_key_id=<Your-Access-Key-ID>;aws_secret_access_key=<Your-Secret-Access-Key>'
gzip compupdate off region 'us-west-2';
# \q
Redshift に pgbench で負荷をかける
  • lineorder.sql を作成する
$ cat << EOF > lineorder.sql
select count(a.*) from lineorder a, lineorder b;
EOF
  • pgbench で lineorder.sql を10並列で合計50回実行する
$ pgbench -r -c 10 -j 10 -n -t 50 -f lineorder.sql -U awsuser -h ds28xl4n.******.us-west-2.redshift.amazonaws.com -d mydb -p 5439
  • running_queues.sql で実行中のクエリのPIDを確認して cancel すると実行中のクエリが終了して、キュー待ちしていたクエリが実行される様子を見る事ができる。
# cancel 44143;

性能情報の見え方

マネジメントコンソール
  • マネジメントコンソールの「クエリ」タブにはキュー待ちのクエリは表示されない。
  • マネジメントコンソールの「クエリ」タブの「実行時間」はキュー待ち時間を含む時間、「開始時刻」はクエリが発行された時刻(キュー待ちが終わって実行フェーズに変わった時刻ではなくクエリが発行された時刻)が表示される。

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

システムテーブル・システムビュー
  • スーパーユーザーでログインする(全ユーザーの情報を見るため)
$ psql "host=ds28xl4n.******.us-west-2.redshift.amazonaws.com user=awsuser dbname=mydb port=5439"
  • running_queues.sql でキュー待ちと実行中のクエリを確認する
    • state が Queue だとキュー待ち、Run だと実行中。q_sec がキュー待ち時間(秒)、exe_sec が実行時間(秒)。
    • CPU時間(cpu_sec)が exe_sec より長いことがあるのはクエリマルチプロセッサで並列実行された時間の合計のためと思われる。
    • read_mb はディスク読込み量、spill_mb はディスク書込み量。
ds28xl4n-r awsuser 00:10 =# \i running_queues.sql
   user    |  pid  |  xid   | query | q | slt |        start        | state | q_sec | exe_sec | cpu_sec | read_mb | spill_mb | ret_rows |    nl_rows     |                                            sql                                             | alert
-----------+-------+--------+-------+---+-----+---------------------+-------+-------+---------+---------+---------+----------+----------+----------------+--------------------------------------------------------------------------------------------+-------
 benchuser | 34229 | 762470 | 89556 | 6 |   1 | 2018-07-16 21:39:43 | Run   |     0 |    9074 |  267373 |     128 |          |          | 37471050468695 | select count(a.*) from lineorder a, lineorder b;                                           | NL
 benchuser | 34230 | 762472 | 89557 | 6 |   1 | 2018-07-16 21:39:43 | Run   |     0 |    9074 |  253425 |     128 |          |          | 35415740526019 | select count(a.*) from lineorder a, lineorder b;                                           | NL
 benchuser | 34231 | 762474 | 89558 | 6 |   1 | 2018-07-16 21:39:43 | Run   |     0 |    9074 |  274471 |     128 |          |          | 38407248173760 | select count(a.*) from lineorder a, lineorder b;                                           | NL
 benchuser | 34228 | 762476 | 89559 | 6 |   1 | 2018-07-16 21:39:43 | Run   |     0 |    9074 |  255883 |     128 |          |          | 35736062097462 | select count(a.*) from lineorder a, lineorder b;                                           | NL
 benchuser | 34232 | 762478 | 89560 | 6 |   1 | 2018-07-16 21:39:43 | Run   |     0 |    9074 |  250486 |     128 |          |          | 34941414838224 | select count(a.*) from lineorder a, lineorder b;                                           | NL
 benchuser | 34233 | 762480 | 89561 | 6 |   1 | 2018-07-16 21:39:43 | Queue |  9074 |       0 |         |         |          |          |                | select count(a.*) from lineorder a, lineorder b;                                           |
 benchuser | 34235 | 762482 | 89562 | 6 |   1 | 2018-07-16 21:39:43 | Queue |  9074 |       0 |         |         |          |          |                | select count(a.*) from lineorder a, lineorder b;                                           |
 benchuser | 34234 | 762484 | 89563 | 6 |   1 | 2018-07-16 21:39:43 | Queue |  9074 |       0 |         |         |          |          |                | select count(a.*) from lineorder a, lineorder b;                                           |
 benchuser | 34236 | 762486 | 89564 | 6 |   1 | 2018-07-16 21:39:43 | Queue |  9074 |       0 |         |         |          |          |                | select count(a.*) from lineorder a, lineorder b;                                           |
 benchuser | 34237 | 762487 | 89565 | 6 |   1 | 2018-07-16 21:39:43 | Queue |  9074 |       0 |         |         |          |          |                | select count(a.*) from lineorder a, lineorder b;                                           |
 awsuser   | 34063 | 765301 | 90186 | 7 |   1 | 2018-07-17 00:10:57 | Run   |     0 |       0 |       0 |       0 |          |          |                | /***************************************************************************************** |
(11 rows)

Time: 1024.246 ms
  • lock_wait.sql でロック待ち状況を確認する
    • objectname が対象オブジェクト、mode がロックモード、txn_start がトランザクション開始時刻。
    • block_sec、block_min、block_hr はロックを保持している秒、分、時間。
    • waiting はロック待ち時間(秒)。
    • num_blockingは待たされているセッション数、pidlist は待たされているセッションの pid。
ds28xl4n-r awsuser 21:42 =# \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
--------+-------+-----------+--------+----------+------------+------------+-----------------+---------+----------+----------------------------+-----------+-----------+----------+---------+------------------+--------------+---------
 762484 | 34234 | benchuser | mydb   |   153654 | public     | lineorder  | AccessShareLock | True    | relation | 2018-07-16 21:39:43.911033 |    197.83 |       3.3 |     0.05 |         |                  |              |
 762478 | 34232 | benchuser | mydb   |   153654 | public     | lineorder  | AccessShareLock | True    | relation | 2018-07-16 21:39:43.905794 |    197.83 |       3.3 |     0.05 |         |                  |              |
 762482 | 34235 | benchuser | mydb   |   153654 | public     | lineorder  | AccessShareLock | True    | relation | 2018-07-16 21:39:43.909771 |    197.83 |       3.3 |     0.05 |         |                  |              |
 762474 | 34231 | benchuser | mydb   |   153654 | public     | lineorder  | AccessShareLock | True    | relation | 2018-07-16 21:39:43.90356  |    197.83 |       3.3 |     0.05 |         |                  |              |
 762476 | 34228 | benchuser | mydb   |   153654 | public     | lineorder  | AccessShareLock | True    | relation | 2018-07-16 21:39:43.904795 |    197.83 |       3.3 |     0.05 |         |                  |              |
 762486 | 34236 | benchuser | mydb   |   153654 | public     | lineorder  | AccessShareLock | True    | relation | 2018-07-16 21:39:43.912798 |    197.83 |       3.3 |     0.05 |         |                  |              |
 762480 | 34233 | benchuser | mydb   |   153654 | public     | lineorder  | AccessShareLock | True    | relation | 2018-07-16 21:39:43.907442 |    197.83 |       3.3 |     0.05 |         |                  |              |
 762487 | 34237 | benchuser | mydb   |   153654 | public     | lineorder  | AccessShareLock | True    | relation | 2018-07-16 21:39:43.914389 |    197.83 |       3.3 |     0.05 |         |                  |              |
 762470 | 34229 | benchuser | mydb   |   153654 | public     | lineorder  | AccessShareLock | True    | relation | 2018-07-16 21:39:43.900309 |    197.83 |       3.3 |     0.05 |         |                  |              |
 762472 | 34230 | benchuser | mydb   |   153654 | public     | lineorder  | AccessShareLock | True    | relation | 2018-07-16 21:39:43.90117  |    197.83 |       3.3 |     0.05 |         |                  |              |
(10 rows)

Time: 40773.823 ms
  • top_queries.sql で実行時間の長いクエリを調べる
    • クエリテキストで集計した実行時間の長いトップ50が表示される。
    • n_qry が実行回数、min/max/avg が実行時間の最小/最大/平均で total が実行時間の合計。
    • max_query_id がクエリIDが最大のもので、last_run がクエリが最後に実行された日付。
ds28xl4n-r awsuser 00:48 =# \i top_queries.sql
  db  | n_qry |                                     qrytext                                      |   min   |   max   |   avg   |  total  | max_query_id |  last_run  | aborted |    event
------+-------+----------------------------------------------------------------------------------+---------+---------+---------+---------+--------------+------------+---------+-------------
 mydb |     1 | select count(a.*) from lineorder a, lineorder b;                                 | 1603.00 | 1603.00 | 1603.00 | 1603.00 |        80506 | 2018-07-16 |       1 | Nested Loop
 mydb |     1 | select * from STV_EXEC_STATE ORDER BY segment, step, slice;                      |  305.00 |  305.00 |  305.00 |  305.00 |        80640 | 2018-07-16 |       0 |
 mydb |     1 | copy lineorder from 's3://awssampledbuswest2/ssbgz/lineorder' credentials '' gzi |  179.00 |  179.00 |  179.00 |  179.00 |        85025 | 2018-07-16 |       0 |
 mydb |     1 | /******************************************************************************* |  100.00 |  100.00 |  100.00 |  100.00 |        89635 | 2018-07-16 |       0 | Nested Loop
 mydb |     1 | select * from lineorder;                                                         |   53.00 |   53.00 |   53.00 |   53.00 |        89549 | 2018-07-16 |       1 |
 mydb |     1 | /******************************************************************************* |   50.00 |   50.00 |   50.00 |   50.00 |        89648 | 2018-07-16 |       0 |
 mydb |     2 | /******************************************************************************* |    2.00 |   46.00 |   24.00 |   48.00 |        90184 | 2018-07-17 |       0 |
 mydb |     1 | /******************************************************************************* |   41.00 |   41.00 |   41.00 |   41.00 |        89582 | 2018-07-16 |       0 |
 mydb |     5 | select trim(u.usename) as user, s.pid, q.xid,q.query,q.service_class as "q", q.s |    1.00 |   29.00 |    6.60 |   33.00 |        87441 | 2018-07-16 |       0 |
 mydb |     1 | /******************************************************************************* |   20.00 |   20.00 |   20.00 |   20.00 |        89626 | 2018-07-16 |       0 |
 mydb |     1 | /******************************************************************************* |   20.00 |   20.00 |   20.00 |   20.00 |        89610 | 2018-07-16 |       0 |
 mydb |     1 | select * from SVL_QUERY_QUEUE_INFO;                                              |   17.00 |   17.00 |   17.00 |   17.00 |        85321 | 2018-07-16 |       0 |
 mydb |     1 | select * from SVL_QUERY_REPORT;                                                  |   17.00 |   17.00 |   17.00 |   17.00 |        82789 | 2018-07-16 |       1 |
 mydb |     1 | select trim(database) as DB, count(query) as n_qry, max(substring (qrytext,1,80) |   15.00 |   15.00 |   15.00 |   15.00 |        82847 | 2018-07-16 |       0 |
 mydb |     1 | select * from admin.v_check_wlm_query_time;                                      |   12.00 |   12.00 |   12.00 |   12.00 |        85257 | 2018-07-16 |       0 |
 mydb |     2 | SELECT w.query ,substring(q.querytxt,1,100) AS querytxt ,w.queue_start_time ,w.s |    0.00 |   11.00 |    5.50 |   11.00 |        85266 | 2018-07-16 |       0 |
 mydb |     1 | select * from SVV_QUERY_STATE;                                                   |   11.00 |   11.00 |   11.00 |   11.00 |        82820 | 2018-07-16 |       0 |
 mydb |     1 | SELECT date_trunc('hour',a.service_class_start_time) AS hour, a.service_class, b |   10.00 |   10.00 |   10.00 |   10.00 |        89658 | 2018-07-16 |       0 |
 mydb |     6 | /******************************************************************************* |    1.00 |    1.00 |    1.00 |    6.00 |        90186 | 2018-07-17 |       0 |
 mydb |     1 | SELECT *, DATEDIFF(SEC,STARTTIME,ENDTIME) AS DATEDIFF FROM STL_QUERY WHERE QUERY |    6.00 |    6.00 |    6.00 |    6.00 |        82812 | 2018-07-16 |       0 |
 mydb |     1 | SELECT s.process AS pid ,date_Trunc ('second',s.starttime) AS S_START ,datediff( |    6.00 |    6.00 |    6.00 |    6.00 |        82831 | 2018-07-16 |       0 |
 mydb |     1 | /******************************************************************************* |    6.00 |    6.00 |    6.00 |    6.00 |        89606 | 2018-07-16 |       0 |
 mydb |     2 | select * from STV_EXEC_STATE ;                                                   |    0.00 |    5.00 |    2.50 |    5.00 |        82795 | 2018-07-16 |       0 |
 mydb |     1 | select * from stl_query;                                                         |    5.00 |    5.00 |    5.00 |    5.00 |        82816 | 2018-07-16 |       0 |
 mydb |     1 | padb_fetch_sample: select * from lineorder                                       |    5.00 |    5.00 |    5.00 |    5.00 |        85039 | 2018-07-16 |       0 | Filter
 mydb |    50 | select count(1) from lineorder;                                                  |    0.00 |    2.00 |    0.08 |    4.00 |        80493 | 2018-07-16 |       0 |
 mydb |     2 | /* query showing queries which are waiting on a WLM Query Slot */ SELECT w.query |    0.00 |    1.00 |    0.50 |    1.00 |        90180 | 2018-07-17 |       0 |
 mydb |     1 | select * from STV_INFLIGHT;                                                      |    1.00 |    1.00 |    1.00 |    1.00 |        82788 | 2018-07-16 |       0 |
 mydb |     1 | SELECT TRIM(DATABASE) AS DB, w.query, SUBSTRING(q.querytxt,1,100) AS querytxt, w |    1.00 |    1.00 |    1.00 |    1.00 |        89655 | 2018-07-16 |       0 |
 mydb |     1 | /******************************************************************************* |    1.00 |    1.00 |    1.00 |    1.00 |        89597 | 2018-07-16 |       0 |
 mydb |     4 | select * from stv_wlm_query_state;                                               |    0.00 |    1.00 |    0.25 |    1.00 |        87438 | 2018-07-16 |       0 |
 mydb |     1 | select count(*) from lineorder;                                                  |    0.00 |    0.00 |    0.00 |    0.00 |        89553 | 2018-07-16 |       0 |
 mydb |     1 | /* Query showing information about sessions with currently running queries */ SE |    0.00 |    0.00 |    0.00 |    0.00 |        89592 | 2018-07-16 |       0 |
(33 rows)

Time: 531.186 ms
  • top_queries_and_cursors.sql で実行時間の長いクエリを調べる
    • クエリテキストで集計した実行時間の長いトップ50が表示される。
    • top_queries.sql と似ているが、CPU時間(cpu)、CPU使用率(cpupct)、ディスク読込み量(mb_read)、一時領域使用量(spill)など情報量が多い。
ds28xl4n-r awsuser 00:58 =# \i top_queries_and_cursors.sql
  db  | n_qry |                                                         qrytext                                                          |   max   |   avg   |  total  | queue |  cpu  | cpupct | spill | mb_read | rows_ret | max_query |  last_run  | aborted | alert
------+-------+--------------------------------------------------------------------------------------------------------------------------+---------+---------+---------+-------+-------+--------+-------+---------+----------+-----------+------------+---------+-------
 mydb |     1 | select count(a.*) from lineorder a, lineorder b;                                                                         | 1603.00 | 1603.00 | 1603.00 |     6 | 64935 |  63.29 |       |     128 |          |     80506 | 2018-07-16 |       1 | NL
 mydb |     1 | select * from STV_EXEC_STATE ORDER BY segment, step, slice;                                                              |  305.00 |  305.00 |  305.00 |       |       |        |       |         |          |     80640 | 2018-07-16 |       0 |
 mydb |     1 | copy lineorder from 's3://awssampledbuswest2/ssbgz/lineorder' credentials '' gzip compupdate off region 'us-west-2';     |  179.00 |  179.00 |  179.00 |     6 |  1809 |  14.42 |       |       0 |          |     85025 | 2018-07-16 |       0 |
 mydb |     1 | /********************************************************************************************** Purpose: Returns the hig |  100.00 |  100.00 |  100.00 |     7 |   122 |   7.24 |       |       0 |          |     89635 | 2018-07-16 |       0 | NL
 mydb |     1 | /********************************************************************************************** Purpose: Return Alerts f |   68.00 |   68.00 |   68.00 |     7 |    17 |  20.58 |       |       0 |          |     90369 | 2018-07-17 |       0 | NL
 mydb |     2 | /********************************************************************************************** WLM QMR Rule Candidates  |   50.00 |   28.50 |   57.00 |     7 |     1 |   0.05 |       |       0 |          |     90365 | 2018-07-17 |       0 |
 mydb |     1 | select * from lineorder;                                                                                                 |   53.00 |   53.00 |   53.00 |     7 |    56 |   1.74 |       |    1850 | 23561221 |     89549 | 2018-07-16 |       1 |
 mydb |     2 | /********************************************************************************************** Purpose: Returns the per |   46.00 |   24.00 |   48.00 |     7 |       |        |       |       0 |          |     90184 | 2018-07-17 |       0 |
 mydb |     1 | /********************************************************************************************** Purpose: Return instance |   41.00 |   41.00 |   41.00 |     7 |       |        |       |       0 |          |     89582 | 2018-07-16 |       0 |
 mydb |     3 | select trim(u.usename) as user, s.pid, q.xid,q.query,q.service_class as "q", q.slot_count as slt, date_trunc('second',q. |   29.00 |   10.33 |   31.00 |     6 |     1 |   0.37 |       |       0 |          |     82846 | 2018-07-16 |       0 |
 mydb |     1 | /********************************************************************************************** Purpose: Return the top  |   20.00 |   20.00 |   20.00 |     7 |       |        |       |       0 |          |     89626 | 2018-07-16 |       0 |
 mydb |     1 | /********************************************************************************************** Purpose: Return instance |   20.00 |   20.00 |   20.00 |     7 |    18 |  22.67 |       |       0 |          |     89610 | 2018-07-16 |       0 |
 mydb |     1 | select * from SVL_QUERY_QUEUE_INFO;                                                                                      |   17.00 |   17.00 |   17.00 |     7 |       |        |       |       0 |          |     85321 | 2018-07-16 |       0 |
 mydb |     1 | select * from SVL_QUERY_REPORT;                                                                                          |   17.00 |   17.00 |   17.00 |       |       |        |       |         |          |     82789 | 2018-07-16 |       1 |
 mydb |     1 | select trim(database) as DB, count(query) as n_qry, max(substring (qrytext,1,80)) as qrytext, min(run_seconds) as "min"  |   15.00 |   15.00 |   15.00 |     6 |       |        |       |       0 |          |     82847 | 2018-07-16 |       0 |
 mydb |     1 | select * from admin.v_check_wlm_query_time;                                                                              |   12.00 |   12.00 |   12.00 |       |       |        |       |         |          |     85257 | 2018-07-16 |       0 |
 mydb |     1 | select * from SVV_QUERY_STATE;                                                                                           |   11.00 |   11.00 |   11.00 |     6 |       |        |       |       0 |          |     82820 | 2018-07-16 |       0 |
 mydb |     1 | SELECT w.query ,substring(q.querytxt,1,100) AS querytxt ,w.queue_start_time ,w.service_class AS class ,w.slot_count AS s |   11.00 |   11.00 |   11.00 |     6 |       |        |       |       0 |          |     82837 | 2018-07-16 |       0 |
 mydb |     1 | SELECT date_trunc('hour',a.service_class_start_time) AS hour, a.service_class, b.condition AS service_class_condition, C |   10.00 |   10.00 |   10.00 |     7 |       |        |       |         |      202 |     89658 | 2018-07-16 |       0 |
 mydb |     1 | SELECT *, DATEDIFF(SEC,STARTTIME,ENDTIME) AS DATEDIFF FROM STL_QUERY WHERE QUERYTXT LIKE 'SELECT * FROM%' ORDER BY START |    6.00 |    6.00 |    6.00 |       |       |        |       |         |          |     82812 | 2018-07-16 |       0 |
 mydb |     6 | /********************************************************************************************** Purpose: Return the Curr |    1.00 |    1.00 |    6.00 |     7 |     1 |   0.41 |       |       0 |          |     90186 | 2018-07-17 |       0 |
 mydb |     1 | /********************************************************************************************** Purpose: Return commit q |    6.00 |    6.00 |    6.00 |     7 |       |        |       |       0 |          |     89606 | 2018-07-16 |       0 |
 mydb |     1 | SELECT s.process AS pid ,date_Trunc ('second',s.starttime) AS S_START ,datediff(minutes,s.starttime,getdate ()) AS conn_ |    6.00 |    6.00 |    6.00 |       |       |        |       |         |          |     82831 | 2018-07-16 |       0 |
 mydb |     2 | select * from STV_EXEC_STATE ;                                                                                           |    5.00 |    2.50 |    5.00 |       |       |        |       |         |          |     82795 | 2018-07-16 |       0 |
 mydb |     1 | padb_fetch_sample: select * from lineorder                                                                               |    5.00 |    5.00 |    5.00 |     6 |   136 |  98.42 |       |   25890 |          |     85039 | 2018-07-16 |       0 |
 mydb |     1 | select * from stl_query;                                                                                                 |    5.00 |    5.00 |    5.00 |       |       |        |       |         |          |     82816 | 2018-07-16 |       0 |
 mydb |    50 | select count(1) from lineorder;                                                                                          |    2.00 |    0.08 |    4.00 |     6 |     1 |  85.02 |       |      20 |          |     80493 | 2018-07-16 |       0 |
 mydb |     2 | select trim(u.usename) as user, s.pid, q.xid,q.query,q.service_class as "q", q.slot_count as slt, date_trunc('second',q. |    1.00 |    1.00 |    2.00 |     7 |       |        |       |       0 |          |     87441 | 2018-07-16 |       0 |
 mydb |     2 | /********************************************************************************************** Purpose: Return the top  |    1.00 |    1.00 |    2.00 |     7 |       |        |       |       0 |          |     90337 | 2018-07-17 |       0 |
 mydb |     1 | SELECT TRIM(DATABASE) AS DB, w.query, SUBSTRING(q.querytxt,1,100) AS querytxt, w.queue_start_time, w.service_class AS cl |    1.00 |    1.00 |    1.00 |       |       |        |       |         |          |     89655 | 2018-07-16 |       0 |
 mydb |     1 | select * from STV_INFLIGHT;                                                                                              |    1.00 |    1.00 |    1.00 |       |       |        |       |         |          |     82788 | 2018-07-16 |       0 |
 mydb |     2 | /* query showing queries which are waiting on a WLM Query Slot */ SELECT w.query ,substring(q.querytxt,1,100) AS querytx |    1.00 |    0.50 |    1.00 |     7 |     1 |   0.23 |       |       0 |          |     90180 | 2018-07-17 |       0 |
 mydb |     4 | select * from stv_wlm_query_state;                                                                                       |    1.00 |    0.25 |    1.00 |       |       |        |       |         |          |     87438 | 2018-07-16 |       0 |
 mydb |     1 | SELECT w.query ,substring(q.querytxt,1,100) AS querytxt ,w.queue_start_time ,w.service_class AS class ,w.slot_count AS s |    0.00 |    0.00 |    0.00 |     7 |     1 |   1.70 |       |       0 |          |     85266 | 2018-07-16 |       0 |
 mydb |     1 | select count(*) from lineorder;                                                                                          |    0.00 |    0.00 |    0.00 |       |       |        |       |         |          |     89553 | 2018-07-16 |       0 |
 mydb |     1 | /* Query showing information about sessions with currently running queries */ SELECT s.process AS pid ,date_Trunc ('seco |    0.00 |    0.00 |    0.00 |       |       |        |       |         |          |     89592 | 2018-07-16 |       0 |
(36 rows)

Time: 1141.186 ms
# select a.userid, a.query, a.total_queue_time, a.total_exec_time, a.exec_start_time, a.exec_end_time, substring(b.text,1,100)
from stl_wlm_query a, stl_querytext b
where a.query = b.query
order by a.total_exec_time desc
limit 10;

 userid | query | total_queue_time | total_exec_time |      exec_start_time       |       exec_end_time        |                                              substring
--------+-------+------------------+-----------------+----------------------------+----------------------------+------------------------------------------------------------------------------------------------------
    100 | 80506 |                0 |      1602831743 | 2018-07-16 18:51:06.926251 | 2018-07-16 19:17:49.757994 | select count(a.*) from lineorder a, lineorder b;
    102 | 85025 |                0 |       179448651 | 2018-07-16 20:12:48.089068 | 2018-07-16 20:15:47.537719 | copy lineorder from 's3://awssampledbuswest2/ssbgz/lineorder'\ncredentials ''\ngzip compupdate off r
    100 | 89635 |                0 |        99895758 | 2018-07-16 21:54:04.487489 | 2018-07-16 21:55:44.383247 | /**********************************************************************************************\nPur
    100 | 89635 |                0 |        99895758 | 2018-07-16 21:54:04.487489 | 2018-07-16 21:55:44.383247 |  results can be used to fine tune WLM queues which contain too many or too few slots\nresulting in W
    100 | 89635 |                0 |        99895758 | 2018-07-16 21:54:04.487489 | 2018-07-16 21:55:44.383247 |  in the WLM configuration file.\nmax_wlm_concurrency: Current actual concurrency level of the servic
    100 | 89635 |                0 |        99895758 | 2018-07-16 21:54:04.487489 | 2018-07-16 21:55:44.383247 | time.\nmax_slots_ts: Most recent time at which the max_service_class_slots occurred.\nlast_queued_ti
    100 | 89635 |                0 |        99895758 | 2018-07-16 21:54:04.487489 | 2018-07-16 21:55:44.383247 | Since generate_series is unsupported in Redshift, this uses an unelegant method to generate a dt\nse
    100 | 89635 |                0 |        99895758 | 2018-07-16 21:54:04.487489 | 2018-07-16 21:55:44.383247 | .\n- If SVL_QUERY_REPORT has < 604800 rows you may want to substitue SVL_QUERY_REPORT for another ta
    100 | 89635 |                0 |        99895758 | 2018-07-16 21:54:04.487489 | 2018-07-16 21:55:44.383247 | un after period of heaviest query activity\nHistory:\n2015-08-31 chriz-bigdata created\n************
    100 | 89635 |                0 |        99895758 | 2018-07-16 21:54:04.487489 | 2018-07-16 21:55:44.383247 | dt_series AS (select sysdate - (n * interval '1 second') as dt from (select row_number() over () as
(10 rows)

Time: 258.192 ms
# select a.userid, a.query, a.total_queue_time, a.total_exec_time, a.queue_start_time, a.queue_end_time, substring(b.text,1,100)
from stl_wlm_query a, stl_querytext b
where a.query = b.query
order by a.total_queue_time desc
limit 10;

 userid | query | total_queue_time | total_exec_time |      queue_start_time      |       queue_end_time       |                                              substring
--------+-------+------------------+-----------------+----------------------------+----------------------------+------------------------------------------------------------------------------------------------------
    100 | 80640 |        304428952 |               0 | 2018-07-16 19:20:38.746379 | 2018-07-16 19:25:43.175331 | select * from STV_EXEC_STATE ORDER BY segment, step, slice;
      1 | 79172 |                0 |          103599 | 2018-07-16 13:40:59.910911 | 2018-07-16 13:40:59.910911 | elect oid from pg_namespace where nspname='pg_internal'))
      1 | 79212 |                0 |           97704 | 2018-07-16 13:51:59.913595 | 2018-07-16 13:51:59.913595 | select count(distinct id) from pg_catalog.stv_tbl_perm where temp = false and db_id > 1 and id not i
      1 | 79220 |                0 |           17152 | 2018-07-16 13:53:00.02343  | 2018-07-16 13:53:00.02343  | select node_num,value from stv_fdisk_stats where name='blocks_allocated_total'
      1 | 79307 |                0 |           20577 | 2018-07-16 14:16:39.938937 | 2018-07-16 14:16:39.938937 | select sum(value) from stv_fdisk_stats where name = 'blocks_allocated_permanent'
      1 | 79267 |                0 |           96503 | 2018-07-16 14:05:59.917467 | 2018-07-16 14:05:59.917467 | elect oid from pg_namespace where nspname='pg_internal'))
      1 | 79259 |                0 |           94293 | 2018-07-16 14:03:11.801154 | 2018-07-16 14:03:11.801154 | elect oid from pg_namespace where nspname='pg_internal'))
      1 | 79314 |                0 |            6204 | 2018-07-16 14:17:48.023831 | 2018-07-16 14:17:48.023831 | Select * from pg_internal.redshift_auto_health_check_386769
      1 | 79493 |                0 |           16171 | 2018-07-16 15:02:00.041758 | 2018-07-16 15:02:00.041758 | select node_num,value from stv_fdisk_stats where name='blocks_allocated_total'
      1 | 79360 |                0 |            6354 | 2018-07-16 14:28:48.186254 | 2018-07-16 14:28:48.186254 | Select * from pg_internal.redshift_auto_health_check_386769
Time: 4216.293 ms

2018-07-11

chroot を使ってみる

  • 環境
$ cat /etc/issue
Amazon Linux AMI release 2018.03
Kernel \r on an \m

$ uname -r
4.14.33-51.37.amzn1.x86_64
  • 準備
$ mkdir -p change/the/dir
$ cd change/the/dir
$ cp --parents /bin/bash .
$ sudo cp --parents /bin/ls .
$ sudo cp --parents /bin/rm .
$ sudo cp -pr --parents /lib64 .
$ sudo cp -pr --parents /usr/lib64 .
  • chroot を実行
$ sudo chroot . /bin/bash
bash-4.2#
bash-4.2# pwd
/
bash-4.2# ls
bin  lib64  usr
bash-4.2# rm -fr /
rm: it is dangerous to operate recursively on '/'
rm: use --no-preserve-root to override this failsafe
bash-4.2# rm -fr --no-preserve-root /
rm: cannot remove '/': Device or resource busy
bash-4.2# ls
bash: /bin/ls: No such file or directory
bash-4.2# exit
exit
$ ls

参考

2018-07-08

AWS Fargate を使ってみる

構築手順

タスク定義
  • [Elastic Container Service]-[タスク定義]-[新しいタスク定義の作成]-[FARGATE]
    • タスク定義名: fargate-httpd-task
    • タスクロール: ecsTaskExecutionRole
    • タスク実行IAMロール: ecsTaskExecutionRole
    • タスクメモリ: 4GB
    • タスク CPU: 2vCPU
    • コンテナの定義
      • コンテナ名: fargate-httpd-container
      • イメージ: httpd
      • メモリ制限: 300MB
      • ポートマッピング: 80(tcp)
クラスター作成
  • クラスターテンプレートの選択: ネットワーキングのみ
  • クラスター名: fargate-httpd-cluster
サービス作成
  • [Elastic Container Service]-[クラスター]で "fargate-httpd-cluster" をクリック
    • サービス名: fargate-httpd-service
    • サービスタイプ: REPLICA
    • タスクの数: 8
    • 配置テンプレート: AZバランススプレッド
    • クラスター VPC: 選択する
    • サブネット: 選択する

確認する

  • [Elastic Container Service]-[クラスター]で "fargate-httpd-cluster" をクリック
  • [タスク]タブをクリックし、任意のタスクをクリックする
  • [Network]-[Public IP]をブラウザのアドレスバーに入力し、"It works!" と表示されることと確認する。

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

参考

Amazon ECS を使ってみる

構築手順

タスク定義
  • [Elastic Container Service]-[タスク定義]-[新しいタスク定義の作成]-[EC2]
    • タスク定義名:httpd-task
    • コンテナの定義
      • コンテナ名: httpd-container
      • イメージ: httpd
      • メモリ制限: ハード制限 300MB
      • ポートマッピング: 80、80
クラスター作成
  • [Elastic Container Service]-[クラスター]-[クラスターの作成]-[EC2 Linux + ネットワーキング]
    • クラスター名: httpd-cluster
    • インスタンス数: 2
    • キーペア: 既存のキーペアを設定
    • ネットワーキング
      • VPC: 既存のVPCを選択
      • サブネット: 既存のサブネットを選択
      • セキュリティグループ: 既存のセキュリティグループを選択
    • コンテナインスタンスの IAM ロール: ecsInstanceRole
サービス作成
  • [Elastic Container Service]-[クラスター]で httpd-cluster をクリック
  • サービス名: httpd-service
  • タスクの数:4
セキュリティグループの設定
  • ssh(22)、http(80) でのアクセスを許可する

テスト

  • ブラウザでアクセスし"It works!"と表示されることを確認する。
  • ssh で EC2ログインする
$ ssh -i ~/mykey.pem ec2-user@ec2-**-**-***-***.ap-northeast-1.compute.amazonaws.com

[ec2-user@ip-***-**-**-*** ~]$ docker images
REPOSITORY                TAG                 IMAGE ID            CREATED             SIZE
httpd                     latest              2a7d646dbba8        11 days ago         177MB
amazon/amazon-ecs-agent   latest              622111e45fde        8 weeks ago         29.4MB
amazon/amazon-ecs-pause   0.1.0               b875fd8f097a        8 weeks ago         963kB

[ec2-user@ip-***-**-**-*** ~]$ docker ps -a
CONTAINER ID        IMAGE                            COMMAND              CREATED             STATUS              PORTS                NAMES
256573641bb8        httpd                            "httpd-foreground"   33 minutes ago      Up 33 minutes       0.0.0.0:80->80/tcp   ecs-httpd-task-1-httpd-container-80b2d8e5a69893885300
978a01c35740        amazon/amazon-ecs-agent:latest   "/agent"             36 minutes ago      Up 36 minutes                            ecs-agent
  • Docker のバージョンを確認する
$ docker version
Client:
 Version:	17.12.1-ce
 API version:	1.35
 Go version:	go1.9.4
 Git commit:	3dfb8343b139d6342acfd9975d7f1068b5b1c3d3
 Built:	Tue Apr  3 23:37:44 2018
 OS/Arch:	linux/amd64

Server:
 Engine:
  Version:	17.12.1-ce
  API version:	1.35 (minimum version 1.12)
  Go version:	go1.9.4
  Git commit:	7390fc6/17.12.1-ce
  Built:	Tue Apr  3 23:38:52 2018
  OS/Arch:	linux/amd64
  Experimental:	false
  • Docker イメージをリストする。
$ docker images
REPOSITORY                TAG                 IMAGE ID            CREATED             SIZE
httpd                     latest              2a7d646dbba8        11 days ago         177MB
amazon/amazon-ecs-agent   latest              622111e45fde        8 weeks ago         29.4MB
amazon/amazon-ecs-pause   0.1.0               b875fd8f097a        8 weeks ago         963kB
  • 起動中のコンテナをリスト表示する
$ docker ps -a
CONTAINER ID        IMAGE                            COMMAND              CREATED             STATUS                     PORTS               NAMES
256573641bb8        httpd                            "httpd-foreground"   About an hour ago   Exited (0) 2 minutes ago                       ecs-httpd-task-1-httpd-container-80b2d8e5a69893885300
978a01c35740        amazon/amazon-ecs-agent:latest   "/agent"             About an hour ago   Up About an hour                               ecs-agent
  • コンテナで実行中のプロセス一覧を表示する
$ docker top 621192cdd629
UID                 PID                 PPID                C                   STIME               TTY                 TIME                CMD
root                4189                4169                0                   06:30               ?                   00:00:00            httpd -DFOREGROUND
bin                 4241                4189                0                   06:30               ?                   00:00:00            httpd -DFOREGROUND
bin                 4242                4189                0                   06:30               ?                   00:00:00            httpd -DFOREGROUND
bin                 4243                4189                0                   06:30               ?                   00:00:00            httpd -DFOREGROUND
  • コンテナの標準出力を確認する
$ docker logs 256573641bb8
AH00558: httpd: Could not reliably determine the server's fully qualified domain name, using 172.17.0.2. Set the 'ServerName' directive globally to suppress this message
AH00558: httpd: Could not reliably determine the server's fully qualified domain name, using 172.17.0.2. Set the 'ServerName' directive globally to suppress this message
[Sun Jul 08 05:41:32.048832 2018] [mpm_event:notice] [pid 1:tid 139972016797568] AH00489: Apache/2.4.33 (Unix) configured -- resuming normal operations
[Sun Jul 08 05:41:32.049183 2018] [core:notice] [pid 1:tid 139972016797568] AH00094: Command line: 'httpd -D FOREGROUND'
27.0.3.145 - - [08/Jul/2018:05:42:57 +0000] "GET / HTTP/1.1" 200 45
27.0.3.145 - - [08/Jul/2018:05:42:57 +0000] "GET /favicon.ico HTTP/1.1" 404 209
[Sun Jul 08 06:25:00.715569 2018] [mpm_event:notice] [pid 1:tid 139972016797568] AH00491: caught SIGTERM, shutting down

参考